Search query fluctuation in exection

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Performance Tuning » Search query fluctuation in exection

Search query fluctuation in exection

Rate Topic

Display Mode

Topic Options

Author

Message

aravind-305595

Posted Thursday, January 24, 2008 6:24 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 10:07 AM
Points: 37, Visits: 114

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

GilaMonster

Posted Thursday, January 24, 2008 7:04 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

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

Sergiy

Posted Thursday, January 24, 2008 3:12 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:01 PM
Points: 4,540, Visits: 8,184

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

GSquared

Posted Thursday, January 24, 2008 3:29 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 15,442, Visits: 9,571

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

aravind-305595

Posted Thursday, January 24, 2008 10:04 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 24, 2012 10:07 AM
Points: 37, Visits: 114

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

GSquared

Posted Friday, January 25, 2008 10:41 AM

SSCoach

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 15,442, Visits: 9,571

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

Matt Miller (#4)

Posted Friday, January 25, 2008 10:47 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 6,998, Visits: 13,947

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 »

Permissions