January 24, 2008 at 6:24 am
I have complex query with joins with necessary index. The query searches based on a given text value. The query returns the data in 11 seconds for the first time. The same query with same search text returns data in less than a second from the next executions.
The same thing happens when I change the search text to some other value. It takes 5 seconds for first time and less than 1 second from then on.
Please suggest things that I have to check.
I am using SQL Server 2005 with SP2
Thanks in advance.
January 24, 2008 at 7:04 am
Can you post the query please, the schema of the tables and any indexes on those tables?
Thanks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 24, 2008 at 3:12 pm
When you run same query second time SQL Server does not actually execute it, it uses resultset stored in cache.
Try to run
DBCC FREEPROCCACHE
after 1st execution and run query again.
2nd time will be just like 1st time.
🙂
_____________
Code for TallyGenerator
January 24, 2008 at 3:29 pm
This is normal behavior in SQL databases.
The first time you run it, it has to come up with an execution plan, so that takes the longest.
After that, the first time you run it with one set of parameters, it has to find that data, but it saves the results in memory. So, when you run the exact same thing again, it runs very fast.
The whole idea is that, as the database is used more and more, it stores the most important and common data and the most important and common queries, so that they run very fast.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 24, 2008 at 10:04 pm
The query has ROW_NUMBER function and I am getting only rows with certain range like 50 to 100 which will be passed through variable using CTE.
The same query if use with another logic like having one identity column it returns data in same time.
Is that like CTE wont flush out the buffer...? Or some other thing is happening
January 25, 2008 at 10:41 am
I'm not entirely clear on what you're asking. Please post the code you have a question about (both CTE and the other version), and we can take a look at it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 25, 2008 at 10:47 am
I think you're running into SQL Server not being able to detect that the two pieces of code are the same, and is not able to appropriate parameterize (using "simple" auto-parameterization), so when it runs into the second "similar" piece of code with the new criteria, it flushes the execution plan out, and starts over.
You should take a look at BOL topics re: auto-parameterization, and/or forced auto-parameterization. If any of that rings true - perhaps help out the CTE by actually manually parameterizing it and see if the plan stays the same and is then re-used.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply