Execute against Large Number of Records

Execute against Large Number of Records


One way to figure out whether a stored procedure is written in an optimal (or suboptimal) way regarding the required execution time is running it against tables with large number of records. For example, the following two stored procedures perform the same task: to find out the nth highest number in a column; e.g. third highest image height from the Pictures table/view. However, the first one is far less efficient than the second one. For a large table (1,000,000+ records), one can expect that the second one will be executed (in a few seconds) in 1/6 time required for the first one.


-----------------------------------------
-- An inefficient stored procedure to get
-- the Nth highest number in a column.
-- Author: G. R. Roosta
-- License: Free To Use (No Restriction)
-----------------------------------------CREATE PROC Nth1

@TableName sysname,
@ColumnName sysname,
@N int

AS
BEGIN

SET @TableName = RTRIM(@TableName)
SET @ColumnName = RTRIM(@ColumnName)

DECLARE @SQL CHAR(400)
IF (SELECT OBJECT_ID(@TableName, 'U')) IS NULL
BEGIN
RAISERROR('Invalid table name', 18, 1)
RETURN -1
END

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)
BEGIN
RAISERROR('Invalid column name', 18, 1)
RETURN -1
END

IF @N <= 0 BEGIN RAISERROR('Nth highest number cannot be less than one!', 18, 1) RETURN -1 END SET @SQL = 'SELECT MAX(' + @ColumnName + ') from ' + @TableName + ' WHERE ' + @ColumnName + ' NOT IN ( SELECT TOP ' + LTRIM(STR(@N - 1)) + ' ' + @ColumnName + ' FROM ' + @TableName + ' ORDER BY ' + @ColumnName + ' DESC )' EXEC (@SQL) END


-----------------------------------------
-- An efficient stored procedure to get
-- the Nth highest number in a column.

-- Author: G. R. Roosta
-- License: Free To Use (No Restriction)
-----------------------------------------
CREATE PROC Nth2

@TableName sysname,
@ColumnName sysname,
@N int

AS
BEGIN

SET @TableName = RTRIM(@TableName)
SET @ColumnName = RTRIM(@ColumnName)

DECLARE @SQL CHAR(400)
IF (SELECT OBJECT_ID(@TableName, 'U')) IS NULL
BEGIN
RAISERROR('Invalid table name', 18, 1)
RETURN -1
END

IF NOT EXISTS(SELECT 1
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = @TableName
AND COLUMN_NAME = @ColumnName)
BEGIN
RAISERROR('Invalid column name', 18, 1)
RETURN -1
END

IF @N <= 0 BEGIN RAISERROR('Nth highest number cannot be less than one!', 18, 1) RETURN -1 END SET @SQL = 'SELECT MIN(' + @ColumnName + ') FROM (SELECT TOP ' + STR(@N) + ' ' + @ColumnName + ' FROM ' + @TableName + ' ORDER BY ' + @ColumnName + ' DESC) t000'; EXEC (@SQL) END