Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Search query fluctuation in exection Expand / Collapse
Author
Message
Posted Thursday, January 24, 2008 6:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 7:30 AM
Points: 37, 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.
Post #446874
Posted Thursday, January 24, 2008 7:04 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 40,438, Visits: 36,894
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 2008, MVP
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

Post #446896
Posted Thursday, January 24, 2008 3:12 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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.
:)
Post #447222
Posted Thursday, January 24, 2008 3:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Post #447234
Posted Thursday, January 24, 2008 10:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 7:30 AM
Points: 37, 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
Post #447300
Posted Friday, January 25, 2008 10:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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
Post #447672
Posted Friday, January 25, 2008 10:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:53 AM
Points: 7,154, Visits: 15,646
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?
Post #447678
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse