query runs in fraction of time when run more than once

Question

query runs in fraction of time when run more than once

mngong

SSCertifiable

Points: 6167
More actions
May 22, 2006 at 5:38 pm

#114247

Is there anything that could be done to improve return times? Queries take more than five minutes but when run once the time reduces to sub minutes.
Is there any parameter that could help this.Or I need to tell every one on the production system to be ready to run the queries twice

Thanks very much for you time

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

It's not unusual for queries to run faster the second time. The data is probably still in memory during the second execution and therefore all the physical IO is avoided.

There are lots of ways to speed things up, but we'll need some specifics before anyone here can be of much help. Could you post the table definitions, along with the existing indexes, and the text of the query you'd like to see improved? Otherwise, the best you can get is some generic tuning parameters and suggestions like 'make sure you're using all the memory you have', 'don't share the SQL Server box with web or application servers', 'spread out data, indexes, and logs'.

And then again, I might be wrong ...
David Webb

mngong SSCertifiable Points: 6167 More actions · Answer 2

Thanks for your reply David.

Almost every query which joins a couple of tables has to be run twice or more to get timely results <2 minute from about 10 or so.

Thought it will be a bit burdensome to post the table structures and sql,s here but if that will be of use I will be glad to do so.Started considereing table caching for a start

Mike

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

table caching is a dangerous thing to do ! because when you pinn a table, it will not be cached out ! Maybe your table grows beyond your expectations consuming all your sql-server's ram.

better thing to do first is monitor your servers I/O, buffer hit ratio, ...

and perform some query-tuning. Check that you avoid as mutch scanning as possible. Provide propre indexes to match your typical query-loads.

use profiler to capture a "typical" window of workload and combine it with windows perfmon to know the io, cpu, .... load during this window.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me