Stephent
asked on
Query Speed - First Run Slow - Next Run Very Fast
Hi Experts,
I have a number of tables. I have just created and popluated and created indexes on the NumberX tables.
Numbers
Numbers9
Numbers8
Numbers7
Numbers6
Numbers5
Numbers4
When I first run the query below it took 5116 mseconds. Any further executions of the query took 16 mseconds.
select top 10 a.[id], a.num4,b.number from numbers4 a, numbers b where a.num4 = '2011' and
a.[id] = b.[id]
When I changed the numbers4 table in the query to test again all the other tables I got the same results.
I need the query to run at 16 ms the first time it is requested.
Is there some kind of Cache going on?
If i reboot the server when my application runs for the first time will I get this performace on the first query?
Cheers
Stephen
I have a number of tables. I have just created and popluated and created indexes on the NumberX tables.
Numbers
Numbers9
Numbers8
Numbers7
Numbers6
Numbers5
Numbers4
When I first run the query below it took 5116 mseconds. Any further executions of the query took 16 mseconds.
select top 10 a.[id], a.num4,b.number from numbers4 a, numbers b where a.num4 = '2011' and
a.[id] = b.[id]
When I changed the numbers4 table in the query to test again all the other tables I got the same results.
I need the query to run at 16 ms the first time it is requested.
Is there some kind of Cache going on?
If i reboot the server when my application runs for the first time will I get this performace on the first query?
Cheers
Stephen
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys
So is it the execution plan that is stored in cache or the results? Once the application is running then these queries will search for many different numbers.
What i don't want is every time the query is run with a new search parameter to take 5 seconds.
My thinking is I can run a script after a reboot to force the execution plan into cache thus when the application starts users will get the speedy response.
Cheers
Stephen
So is it the execution plan that is stored in cache or the results? Once the application is running then these queries will search for many different numbers.
What i don't want is every time the query is run with a new search parameter to take 5 seconds.
My thinking is I can run a script after a reboot to force the execution plan into cache thus when the application starts users will get the speedy response.
Cheers
Stephen
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes there is a cache
have you updated / created statistics for the indexes after you've created them?
have you updated / created statistics for the indexes after you've created them?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
----------
you are correct it maintains a cache SQL Server in memory with recently accessed data pages, as well as query plan for recently submitted queries and invoked stored procedure
this is what you see when you run a query or stored procedure once it will be faster on the next run it retrieves the result of a query without accessing the disk for frequently accessed tables
so yes after a server reboot the chache will be cleared and you'll get the performance hit on the first query
----------
bruintje
share what you know, learn what you don't