Why SQL Server queries are sometimes slow to execute for the first time, and the second time, the third execution becomes faster

Foreigner question:

Hi, I have a SQL query which takes 8 seconds in the first run. The next run there after takes 0.5 seconds and all consecutive runs take 0.5 seconds. Is the plan getting cached? How does I make this query run with 0.5 second in the first run itself? Please find the query below.

Select IsNull(Joblabor. IDNumber,-1)'Processtransactionid', EmpNo'EmployeeID', Case(Joblabor.ljob) when '-1'  Then '0' ElseJoblabor.ljobEnd 'JobID', Joblabor. Procno'ActivityID', process. Process'Actvityname', Joblabor.shift'Shift', Case(IsNull(Suspend,0)) when 1  Then 'true' Else 'false' End 'Suspended', Joblabor. StartTime'StartDateTime', Joblabor.starttime'StartTime', joblabor.updated'updateddate', Procquant'Quantity', prochours'Hours',IsNull(Remarks,"')'Remark',IsNull(Jcnotes,"')'Notes',IsNULL(Jobformspecs.partdes,"') asFormdesc,IsNull(Job.estdes1,"')'Jobdesc',0 ' Standard',0 'MINIMUMSTD',0 'MAXIMUMSTD', Joblabor.partno'FormID', Joblabor. Costdate'costdate',IsNull(Joblabor.linenum,1)'Processtransactionindex', Case(Joblabor.suspend) when 1  Then 1 Else 0 End 'Processtype', (joblabor.timepct* -)'Percent',IsNull(Job.fcustno,"')'CustomerID',IsNull(Job.fcompany,"')'CustomerName', Joblabor. EndTime'EndDateTime', process. Procgroup'Activitygroup', Case(LEN(LTRIM(SUBSTRING(Job.remark1,1, -)))) when 0  Then 'false' Else 'true' End 'hasjobnotes' , Case(IsNull(Processremarks.containsprocessremarks,0)) when 0  Then 'false' Else 'true' End 'Hasprocessremarks' , Case(IsNull(Changeorder.containschangeorders,0)) when 0  Then 'false' Else 'true' End 'hasalteration',IsNull(Joblabor.costcode,"')'Coststatus',IsNull(Complete,"')'Completioncode', Gryield'Grossyield', Netyield'Netyield' frombbjobcst Joblabor (nolock) Left Outer JoinBbjthead Job (NOLOCK) on(Joblabor.ljob=job.ljob)Inner JoinSsproces asProcess (NOLOCK) on(Process. Procno=Joblabor. Procno andProcess.archive= 0) Left outer JoinBbpthead Jobformspecs (NOLOCK) on(LTrim(RTrim(Joblabor. Ljob))= LTrim(RTrim(Jobformspecs.ljob)) and LTrim(RTrim(Joblabor.partno))=LTrim(RTrim(Jobformspecs.partno))) Left outer Join (SELECT Count(Bbchghdr.ljob)'containschangeorders', Bbchghdr.ljob fromBbchghdr (NOLOCK)Inner JoinBbchglin (NOLOCK) onBbchghdr.ljob=Bbchglin.ljob andBbchghdr.changeno=Bbchglin.changenoWHEREType= 'P'  andDescript not  like "' Group  byBbchghdr.ljob) Changeorder onChangeorder.ljob=Joblabor.ljob Left Join (SELECT Count(Bbjobcst.ljob)'Containsprocessremarks', Bbjobcst.ljob fromBBJOBCST (NOLOCK)WHERE(LEN(LTRIM(SUBSTRING(Bbjobcst.jcnotes,1, -)))> 0 or LEN(LTRIM(remarks))> 0)Group  byBbjobcst.ljob) Processremarks onProcessremarks.ljob=Joblabor.ljobwhereJoblabor.empno= '000002013'  and(IsNull(Joblabor.endtime,"')= "' orSuspend=1) and(Joblabor. Prochours= 0 orSuspend=1 ) andJoblabor.ljob<> 0Order  byJoblabor. Costdatedesc, Joblabor.starttimedesc,[LineNum] ASC

Thanks in advance.

Reply:

It isn ' t the query plan that's getting cached–it is the actual data and indexes which is being cached. This is common behavior of any database. If you ran the query, then waited a while and ran again (keeping the session active), you would see the query take longer Again, based on the cached data/indexes being flushed to make-to-other data.

Can see if you can reduce the 0.5 seconds repeat time and and/or you can see if you can reduce the intermediate result SE TS (which is usually what get cached and speed up the the queries the second. Nth runs).

If the query is producing a large intermediate result set (perhaps a large join where most records be then discarded), yo  U May is able to speed it up by changing parts of your query. Also, sometimes just adding the right index can solve issues like this.

Look at the execution plan and see if there is any "table access full", "Index access full", "Cartesian", or similar join s indicating inefficient join/indexing.

This means that the reason SQL statements are slow for the first time is not only because the execution plan is not cached so easily, but sometimes you find that the SQL statement reuses the execution plan, but the first query is still slow. As the answer above, the main reason is that when the first query, SQL Server will query the part of the data and index from the disk loaded into memory as a cache, and the second query directly from the memory cache to take out the data, it is naturally more than loading data from the disk much faster, SQL The server periodically clears the cache, so if a SQL statement is not executed for a long time, it will need to load the data from the disk anew.

Original link

Why SQL Server queries are sometimes slow to execute for the first time, and the second time, the third execution becomes faster