老外提问：
Hi, I have an 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 do i make this query run in 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' else joblabor.ljob end '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,'') as FormDesc,
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*100) 'Percent',IsNull(Job.FCustNo,'') 'CustomerID', IsNull(Job.FCompany,'') 'CustomerName' , joblabor.EndTime 'EndDateTime',process.ProcGroup 'ActivityGroup',
case (LEN(LTRIM(SUBSTRING(Job.remark1, 1, 25)))) 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'
from BBJobCST joblabor (nolock)
Left Outer Join bbjthead Job (nolock) on (joblabor.lJob = Job.lJob)
inner join SSProces as Process (nolock) on( process.ProcNo = joblabor.ProcNo AND process.archive = 0)
left outer join bbPthead 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 FROM bbchghdr (nolock)
inner join bbchglin (nolock) ON bbchghdr.ljob = bbchglin.ljob AND bbchghdr.changeno = bbchglin.changeno
WHERE type = 'P' AND descript NOT LIKE '' group by bbchghdr.ljob) ChangeOrder on ChangeOrder.ljob=joblabor.ljob
left join (
SELECT Count(bbjobcst.ljob) 'ContainsProcessRemarks', bbjobcst.ljob
FROM bbjobcst (nolock) WHERE ( LEN(LTRIM(SUBSTRING(bbjobcst.jcnotes, 1, 25))) > 0 or LEN(LTRIM(remarks)) > 0)
Group By bbjobcst.ljob) ProcessRemarks on ProcessRemarks.ljob=joblabor.ljob
where joblabor.empno = '000002013' and
(isnull(joblabor.endtime,'') = '' or suspend=1 ) and (joblabor.ProcHours = 0 or suspend=1 )
and joblabor.ljob <> 0
order by joblabor.Costdate desc,joblabor.starttime desc,[linenum] asc
Thanks in advance.
回答：
It isn’t the query plan that is getting cached – it is the actual data and indexes which are 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 room for other data.
You can see if you can reduce the 0.5 seconds repeat time, and/or you can see if you can reduce the intermediate result sets (which are usually what get cached and speed up the queries the second..nth runs).
If the query is producing a large intermediate result set (perhaps a large join where most records are then discarded), you may be 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 are any “table access full”, “index access full”, “cartesian”, or similar joins indicating inefficient join/indexing.
意思就是说Sql语句第一次查询慢的原因不仅仅是因为执行计划没有被缓存这么简单，有时候你会发现Sql语句重用了执行计划，但是第一次查询还是很慢。就如同上面回答一样，最主要的原因是第一次查询的时候，Sql Server会将查询出的部分数据和索引从磁盘加载到内存作为缓存，而第二次查询的时候就直接从内存缓存中拿出数据了，自然要比从磁盘上加载数据快很多，Sql Server会定期清除缓存，所以一段Sql语句如果长期不执行后，就需要从磁盘从新加载数据。