InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 07:24:30 GMT20http://www.sqlservercentral.com/Forums/Topic446874-65-1.aspxI 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.Fri, 25 Jan 2008 10:47:33 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic446874-65-1.aspxI'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.Fri, 25 Jan 2008 10:41:17 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic446874-65-1.aspxThe 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 happeningThu, 24 Jan 2008 22:04:44 GMTaravind-305595http://www.sqlservercentral.com/Forums/Topic446874-65-1.aspxThis 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.Thu, 24 Jan 2008 15:29:22 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic446874-65-1.aspxWhen 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.:)Thu, 24 Jan 2008 15:12:42 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic446874-65-1.aspxCan you post the query please, the schema of the tables and any indexes on those tables?ThanksThu, 24 Jan 2008 07:04:17 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic446874-65-1.aspxI 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 SP2Thanks in advance.Thu, 24 Jan 2008 06:24:56 GMTaravind-305595