How To Slow Down A SQL Query?

Question

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!

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

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.

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!

Answer 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.

Answer 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:

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

and then use a 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.