Free Software, Source Code, and Best-Practice Documentation Index from your
Cleveland Software, Database, Web Design, Consulting, and SQL / Delphi Experts

/***************************************************************************************/
-- This is a test-jig useful when optimizing and fine-tuning the execution speed
-- of various SQL Statements, procedure/function calls, and the likes.
-- This is especially useful when there are multiple ways to accomplish the same result, 
-- and you need to see which methods are the fastest under different iteration-counts. 
-- Just insert code to test, set the Iterations, execute, and the resulting elapsed
-- time in Milliseconds (MS) will be displayed.
--
-- For optimal speed comparisons, the data-cache and procedure-cache are cleared 
-- before running, and the data-cache is cleared between each iteration.
/***************************************************************************************/
DBCC DROPCLEANBUFFERS	WITH NO_INFOMSGS	-- Clears the data cache
DBCC FREEPROCCACHE	WITH NO_INFOMSGS	-- Clears the procedure cache
GO

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @starttime DATETIME
SELECT  @starttime = GetDate()

DECLARE @Counter INT
SELECT  @Counter = 0

DECLARE @Iterations INT
SELECT  @Iterations = 100  --Set to desired number of iterations

DECLARE @JUNK INT
DECLARE @ProfileID INT
SELECT @ProfileID

WHILE @Counter < @Iterations
BEGIN
	DBCC DROPCLEANBUFFERS	WITH NO_INFOMSGS	-- Clears the data cache

	--*********************************************************
	--EXECUTE WHATEVER STATEMENT(S) YOU WANT TO SPEED-TEST HERE
	--********************************************************* 
        
	SELECT @Counter = @Counter + 1
        
END  --While loop
        
SELECT DateDiff(ms, @starttime, GetDate()) --Display elapsed Milliseconds