SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Search query fluctuation in exection


Search query fluctuation in exection

Author
Message
aravind-305595
aravind-305595
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 119
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226211 Visits: 46323
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Sergiy
Sergiy
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25756 Visits: 12467
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.
Smile
GSquared
GSquared
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58201 Visits: 9730
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
aravind-305595
aravind-305595
Old Hand
Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)Old Hand (329 reputation)

Group: General Forum Members
Points: 329 Visits: 119
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
GSquared
GSquared
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58201 Visits: 9730
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
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29703 Visits: 19006
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search