Sql查询在几秒钟内运行，但存储过程中的相同查询需要时间来执行 [英] Sql query runs in seconds but same query inside store procedure is taking time to execute

问题描述

我创建了一个存储过程，这个过程花费了太多时间编译，但是当我在存储过程中单独运行相同的查询时，它会在几秒钟内执行。我无法找到背后的原因。请帮助我解决背后的问题。这对我来说是非常有帮助的。在此先感谢。

解决方案

我假设你在这里使用SQL Server。

当你在不同的环境中有不同的执行时间通常计划缓存是罪魁祸首。

为了更好地理解，我会引用你的文章 [ ^ ] by真正的SQL Server Guru，Erland Sommarskog。

我多次遇到过这个问题。如果你多次执行相同的查询，那么SQL将从缓存中返回查询结果。所以它会提供快速的结果。



请在执行查询前使用下面的行。

  CHECKPOINT ; 
  GO  
  DBCC  DROPCLEANBUFFERS; 
  GO  

这将删除缓存并为您提供新的结果。



我相信您的查询将与您的存储过程同时进行。



如果您对此有任何疑问或疑问，请与我们联系。



谢谢

Advay Pandya

I have created one store procedure which is taking too much time compile but when i ran same query within stored procedure separately it gets executed with in seconds. I am not able to find the reason behind that. Please help me out what will be the problem behind that. It is very helpful for me. Thanks in advance.

解决方案

I'm assuming you're using SQL Server here.
When you have different execution times in different environments it's usually the plan cache that's the culprit.
For a better understanding I'll refer you to an article[^] by the real SQL Server Guru, Erland Sommarskog.

Hi,

I have faced this issue many times. If u execute the same query many times then SQL will return the query result from cache. So it will provide fast result.

Please use below lines before executing your query.

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

That will remove the cache and provide you the fresh result.

I believe your query will take the same time as your stored procedure.

Please let me know if you have any concern or query on this.

Thanks
Advay Pandya

这篇关于Sql查询在几秒钟内运行，但存储过程中的相同查询需要时间来执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！