topic Re: OPtimizing Performance for Select query on huge table in Application Development and Automation Discussions

OPtimizing Performance for Select query on huge table

Former Member — Fri, 06 Jul 2007 14:51:23 GMT

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

Re: OPtimizing Performance for Select query on huge table

Former Member — Fri, 06 Jul 2007 14:55:52 GMT

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

Rob

Re: OPtimizing Performance for Select query on huge table

Former Member — Fri, 06 Jul 2007 15:06:02 GMT

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

Re: OPtimizing Performance for Select query on huge table

Former Member — Fri, 06 Jul 2007 15:11:48 GMT

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

Re: OPtimizing Performance for Select query on huge table

KjetilKilhavn — Mon, 09 Jul 2007 13:53:59 GMT

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.