计算 SQL 查询的执行时间？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/595762/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculate execution time of a SQL query?
提问by Prashant
I am providing search functionality in my website, when user searches a record then I want to display the time the query taken to get the results same as google does. When we search anything then google displays how much time it takes to get results?
我在我的网站中提供搜索功能，当用户搜索记录时，我想显示查询获得与谷歌相同的结果所花费的时间。当我们搜索任何东西时，谷歌会显示获得结果需要多长时间？
For this I have declared a @start variable in my SP and finding the difference in the end, as below;
为此，我在我的 SP 中声明了一个 @start 变量，并最终找到了差异，如下所示；
DECLARE @start_time DATETIME
SET @start_time = GETDATE()
-- my query
SELECT * FROM @search_temp_table
SELECT RTRIM(CAST(DATEDIFF(MS, @start_time, GETDATE()) AS CHAR(10))) AS 'TimeTaken'
Is there any other easy and fast way, or a single line of query with which we can find out the time a query taken to execute?
有没有其他简单快捷的方法，或者我们可以用一行查询来找出执行查询所花费的时间？
I'm using SQL Server 2005.
我正在使用 SQL Server 2005。
采纳答案by Josh Stodola
We monitor this from the application code, just to include the time required to establish/close the connection and transmit data across the network. It's pretty straight-forward...
我们从应用程序代码中监控这一点，只是为了包括建立/关闭连接和通过网络传输数据所需的时间。这很直接......
Dim Duration as TimeSpan
Dim StartTime as DateTime = DateTime.Now
'Call the database here and execute your SQL statement
Duration = DateTime.Now.Subtract(StartTime)
Console.WriteLine(String.Format("Query took {0} seconds", Duration.TotalSeconds.ToString()))
Console.ReadLine()
回答by Faiz
回答by Navaneet
I found this one more helpful and simple
我发现这个更有用，更简单
DECLARE @StartTime datetime,@EndTime datetime
SELECT @StartTime=GETDATE()
--Your Query to be run goes here--
SELECT @EndTime=GETDATE()
SELECT DATEDIFF(ms,@StartTime,@EndTime) AS [Duration in milliseconds]
回答by Rakesh Sahu
Please use
请用
-- turn on statistics IO for IO related
SET STATISTICS IO ON
GO
and for time calculation use
并用于时间计算
SET STATISTICS TIME ON
GO
then It will give result for every query . In messages window near query input window.
然后它将为每个查询提供结果。在查询输入窗口附近的消息窗口中。
回答by XXX
declare @sttime datetime
set @sttime=getdate()
print @sttime
Select * from ProductMaster
SELECT RTRIM(CAST(DATEDIFF(MS, @sttime, GETDATE()) AS CHAR(10))) AS 'TimeTaken'
回答by Jon Skeet
Why are you doing it in SQL? Admittedly that does show a "true" query time as opposed to the query time + time taken to shuffle data each way across the network, but it's polluting your database code. I doubt that your users will care - in fact, they'd probably ratherinclude the network time, as it all contributes to the time taken for them to see the page.
你为什么用 SQL 做这件事？诚然，这确实显示了“真实”的查询时间，而不是查询时间 + 跨网络以每种方式对数据进行混洗所花费的时间，但它会污染您的数据库代码。我怀疑您的用户是否会关心 - 事实上，他们可能宁愿包括网络时间，因为这一切都会影响他们查看页面所花费的时间。
Why not do the timing in your web application code? Aside from anything else, that means that for cases where you don'twant to do any timing, but you want to execute the same proc, you don't need to mess around with something you don't need.
为什么不在您的 Web 应用程序代码中进行计时？除了别的，这意味着，对于您的情况不希望做任何时机，但要执行相同的进程内，你不需要浪费时间的东西你不需要。
回答by Nayas Subramanian
You can use
您可以使用
SET STATISTICS TIME { ON | OFF }
Displays the number of milliseconds required to parse, compile, and execute each statement
显示解析、编译和执行每个语句所需的毫秒数
When SET STATISTICS TIME is ON, the time statistics for a statement are displayed. When OFF, the time statistics are not displayed
当 SET STATISTICS TIME 为 ON 时，将显示语句的时间统计信息。关闭时，不显示时间统计
USE AdventureWorks2012;
GO
SET STATISTICS TIME ON;
GO
SELECT ProductID, StartDate, EndDate, StandardCost
FROM Production.ProductCostHistory
WHERE StandardCost < 500.00;
GO
SET STATISTICS TIME OFF;
GO
回答by Tanmay Nehete
try this
尝试这个
DECLARE @StartTime DATETIME
SET @StartTime = GETDATE()
SET @EndTime = GETDATE()
PRINT 'StartTime = ' + CONVERT(VARCHAR(30),@StartTime,121)
PRINT ' EndTime = ' + CONVERT(VARCHAR(30),@EndTime,121)
PRINT ' Duration = ' + CONVERT(VARCHAR(30),@EndTime -@starttime,114)
If that doesn't do it, then try SET STATISTICS TIME ON
如果那不这样做，然后尝试 SET STATISTICS TIME ON
