我用储存的程序来抓捕约25人。
我先利用SSMS的执行计划特征来追踪业绩瓶颈,并发现我有一个非常宽广的问询,我曾多次从单一的角度进行自我参与。 我将这一报告附在一份联合评估报告中,再次启动了我的储存程序,检查了我的批量中这一具体询问的相对权重从30%降至2%,并开始寻找进一步优化的机会。
实际上,我刚才所听到的观点在整个地方都得到了利用,因此,我决定将我所需要的价值储存在“当地桌上,并在我所储存的程序中把这个表放在任何地方。 我的批量中有93%现在用于这一行:
insert into @vwActivityCache
select * from vwActivity where n in (@n,@n-1,@n-2)
And this line takes around 2s to execute. (@vwActivityCache contains around 10k rows)
迄今为止,情况良好。
我所储存的程序仍需要25人才能执行。 (在第一个需要更长时间的外壳之后)
我试图在以上段落之后对所有问题发表评论,而按预期执行涉及2个问题。
I ve speculated that it could be caused by the time needed to transfer the data back to my sql client (despite the client statistics didn t imply this at all), so I tried removing the final select of the procedure (which now returns nothing), but I m still stuck at 25s. I ve also tried executing directly from the sql server box, 25s as well.
如果我删除除当地表格声明外的一切,加上填表的文字,则所储存的程序按预期需要执行2项。
For each query making use of my @vwActivityCache table that I uncomment, the execution time increases by a few seconds (even-though the execution plan claims the uncommented query took 0% of the batch time to execute), till I reach my initial 25s when everything is uncommented.
Unfortunately I can t post the stored procedure nor the execution plan here, but I was wondering if someone had any idea of the reasons that could cause a 2s query to take 93% of the execution time of a 25s procedure !
Update : switching @vwActivityCache to a temp table (#vwActivityCache ) reduced the execution time from 25s to 6s.