OPtimizing Performance for Select query on huge table

[Former Member]

Hi All,

We are fetching a single record from NAST table. The table has around 10 Million Entries.

The Select Query takes around 5-6 minutes to return.

We are not using the Primary key completely. We are using only one field of the primary key.

The field is also a part of the Index but we are not using all the fields in the index as well.

We need to bring down the time. What can be the solution? I cant see any changes to the code, since its a single query and we cant use the Entire Primary key.

Would creating an Index on the fields that we are concerned with help in this regard.

Open to all solutions.

Thanks in Advance,

Imran

[Former Member]

Before thinking about creating an index, try to improve your SELECT. What field(s) of the primary key are you using?

Rob

[Former Member]

Hi Rob,

The issue is that we cannot be doing anything with the Select Quesry. We are querying on two fields and there is no possibilty on including more fields of the primary key.

Thanks for your response.

Thanks,

Imran

[Former Member]

There are sometimes tricks you can use to get it to use the index more efficiently. If you let us know which fields you are using in the SELECT (all of them actually), we might be able to help.

Or are you saying you can't change the code at all?

Please don't create duplicate posts though.

Rob

Message was edited by:

Rob Burbank

[KjetilKilhavn]

Yes, an index will most likely help. Use the field that varies most as the first field of your index to get the best performance.

However, as I am sure you are aware, creating an index gives a performance hit on table updates/inserts. So you should do it when you can't find another solution (cf. suggestion about sharing your WHERE statement with us), not as an easy way out.