如何减慢SQL查询？

As strange as it sounds I need to slow down a SQL query. Currently I'm using Microsoft SQL Server 2008 R2 on an in-house development server with the AdventureWorks database. I'm in the process of testing some code and the queries that I'm running are too fast no matter what I try!

听起来很奇怪我需要放慢SQL查询的速度。目前，我正在使用带有AdventureWorks数据库的内部开发服务器上的Microsoft SQL Server 2008 R2。我正在测试一些代码，无论我尝试什么，我运行的查询都太快了！

Basically I'm testing a cut-off feature and need a sufficiently long query to be able to cut it off before it completes.

基本上我正在测试一个截止功能，需要一个足够长的查询才能在它完成之前切断它。

Unfortunately as it is a local installation there isn't a single query or large enough table in the AdventureWorks database to actually give me good data to work with. I've tried

不幸的是，由于它是本地安装，因此AdventureWorks数据库中没有单个查询或足够大的表来实际为我提供良好的数据。我试过了

WAITFOR DELAY '01:00'

Which worked great to just test to make sure it was working, but now I need to test to see if I can cut the data stream off mid-read. The WAITFOR statement doesn't do me justice in that respect because I need it to actively be retrieving data back from the server. My first intuition was to use convoluted calculations to slow it down, however even having SQL server multiply all the numerical values in the query by themselves 37 times only slowed down the query by milliseconds. The second thing I tried was embedding the WAITFOR statement in a sub-query but it appears you can't do that. Finally, the only thing I haven't tried is to execute multiple stored procedures and WAITFOR in between them, but I don't think that would work for what I need.

对于测试以确保它正常工作非常有效，但现在我需要进行测试以确定是否可以在读取中间切断数据流。在这方面，WAITFOR声明并不公正，因为我需要它来主动从服务器检索数据。我的第一个直觉是使用复杂的计算来减慢速度，但是即使让SQL服务器将查询中的所有数值乘以37次，也只会使查询减慢毫秒。我尝试的第二件事是将WAITFOR语句嵌入到子查询中，但看起来你不能这样做。最后，我唯一没有尝试的是在它们之间执行多个存储过程和WAITFOR，但我认为这不会对我需要的东西起作用。

I have to say, I'm impressed at how hard it is to make an absolutely terrible query when you're this close to the server.

我不得不说，当你接近服务器时，我对于制作一个绝对可怕的查询有多么难以接受。

Is there any way I can slow down a query easily?

有什么办法可以轻松减慢查询速度吗？

Thank you!

谢谢！

2 个解决方案

#1

Just do a load of cross joins.

只需加载一些交叉连接。

SELECT T1.*
FROM SomeTable T1,  
     SomeTable T2,  
     SomeTable T3,  
     SomeTable T4

For a 1,000 row table that will generate 1,000 billion rows which should be plenty slow enough.

对于1000行表，将产生1,000亿行，这应该足够慢。

#2

DECLARE @EndTime DATETIME;
SET @EndTime = DATEADD(s, 5, GETDATE()); -- Set your delay here

WHILE @EndTime > GETDATE()
    SELECT 'Test Result'; -- Add your desired query here

EDIT

编辑

Another option using recursion:

使用递归的另一个选项：

Create a UDF wrapper for GETDATE() so that a new date value will be calculated for each row in the result:

为GETDATE（）创建一个UDF包装器，以便为结果中的每一行计算新的日期值：

CREATE FUNCTION dbo.GetExactDate()
RETURNS DATETIME    
AS
BEGIN
    RETURN GETDATE();
END

and then use a cte

然后使用cte

DECLARE @EndTime DATETIME;
SET @EndTime = DATEADD(s, 5, GETDATE()); -- Set your delay here

WITH cte AS (
    SELECT dbo.GetExactDate() Value
    UNION ALL
    SELECT dbo.GetExactDate()
    FROM cte
    WHERE Value < @EndTime
)
SELECT Value
FROM cte
OPTION (MAXRECURSION 0);

This has the advantage of returning the results in one query, not many (like my first solution) while still being able to set the amount of time for which you would like the query to keep returning results.

这样做的好处是可以在一个查询中返回结果，而不是很多（比如我的第一个解决方案），同时仍然可以设置您希望查询保持返回结果的时间量。

#1