Query question

[pd123]

I have a query which pulls things from many tables.

If I do a select top 269 from aaa, bbb, etc it returns the data back in about 2 seconds.

If I do a select top 270 from aaa, bbb, etc it returns the data back in about 8 seconds.

Does anybody know why the query takes 6 seconds longer just to return 1 more record in the query? If so, is there any db runtime value I can adjust to speed up the response time?

Many thanks,
pd123

 

[TitleistDBA]

Pd, Did you try to run UPDATE STATISTICS? This should speed up your query.

http://www.acushnetcompany.com

 

[pd123]

Yes, tried that but makes no difference unfortunately.

Thanks anyway.

 

[TitleistDBA]

How about set rowcount 270?
you will have to set your rowcount back to 0 after you are done.

http://www.acushnetcompany.com

 

[pd123]

Behaves in exactly the same manner.

set rowcount 269 - query takes 2 seconds

set rowcount 270 - query takes 8 seconds

 

[TitleistDBA]

Are they using the same query plan?

http://www.acushnetcompany.com

 

[pd123]

Yes, same query plan.

If I set showplan_text on and run both queries, the same execution paths are returned.

The only difference is one more record !

 

[TitleistDBA]

The only thing else I can think of is to put a index on the fields your selecting. (in the same order) If you haven't already done that.

http://www.acushnetcompany.com

 

[pd123]

All the indexes are the same - it's in the same query analyser window in fact.

I'll have a dig through the documentation to see if anything seems relevant.

Thanks for you help anyway.

Regards,
pd123