Clash Royale CLAN TAG#URR8PPP

How To Slow Down A SQL Query?

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.

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!

3 Answers
3

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.

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()

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.

Tested on SQL Server 2016: (SQL View query takes always exactly two seconds to reply)

/* Call WAITFOR DELAY inside SQL View */
/* Usefull for example for async testing */
CREATE FUNCTION WaitForDelay()
RETURNS INT
AS
BEGIN
RETURN (
SELECT Value FROM OPENROWSET (
'SQLOLEDB', 'Trusted_Connection=yes; Integrated Security=SSPI; Server=localhost; Initial_Catalog=master;',
'WAITFOR DELAY ''00:00:02'' SELECT 0 AS Value'
))
END

GO
CREATE VIEW Wait AS
SELECT dbo.WaitForDelay() AS Value

GO
SELECT * FROM Wait /* Takes sql view 2 seconds to respond */


Shows Management Studio executing simple view slow down intentionally to 2 seconds:

https://i.stack.imgur.com/fE60u.png

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.