Free Range SQL - It tastes better.

A common requirement from users is the ability to be able to search on the columns they want.
In affect they would like to roll there own queries.
As an example take the Employees table in Northwind. The user wants to be able to search on any/all fields.
If your development environment only allows stored procedures then the usual solution is to use the ISNULL or COALESCE for each column in the WHERE clause.

SELECT * from Employees WHERE EmployeeID = ISNULL(@EmployeeID, EmployeeID)

Problem is, performance sucks..At best you will get an index scan...
There are some ways to build the predicate that can make use of index efficiently depending on the data type, but in most cases, the column needs to be NOT NULL. Some techniques

The other solution is dynamic SQL. This raises the stress on security ,specifically injection, compared to the type parameters used in Stored Procedures.
But by bringing together sp_executesql, column ordinal positions, some bit wise arithmetic and finally a standard naming convention, we can overcome the security and performance issues while satisfying the users requirements..

sp_executesql has the ability to accept typed parameters and it is this that reduces the risk of injection significantly. But it has a quirky behaviour that we can leverage for our free range SQL. The second parameter for this proc is a string that takes a combination of parameter name and types.
[@params =] N'@parameter_name  data_type [,...n]' }
The quirky bit is that the parameters declared do not have to exist in the underlying SQL, but the reverse is not true. This behaviour also applies for the third parameter which is a parameter array.. We can use this behaviour to our advantage....

Each column in SQL is given a unique ordinal position when the table is created. Although relational redundant, this can come in handy...Using the POWER function in TSQL we can compute the bit wise integer for each column....

SELECT COLUMN_NAME, POWER(2,ORDINAL_POSITION) as BitWise
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME= 'Employees'

Now that we have these values, we can create a WHERE clause that covers every possible combination requested.

Using the Northwind database, focusing on the Employees table and its first 5 columns, the stored procedure below outlines the technique..

CREATE PROC upEmployeesSearch
@EmployeeID INT = null,
@LastName NVARCHAR(40) = null,
@FirstName NVARCHAR(20) = null,
@Title NVARCHAR(60) = null,
@TitleOfCourtesy NVARCHAR(50) = null
AS
SET NOCOUNT ON
DECLARE @BITMASK INT
DECLARE @WHERE NVARCHAR(4000)
SELECT @BITMASK = 0, @WHERE =''
--Build the BitWise value
IF @EmployeeID IS NOT NULL SET @BITMASK = @BITMASK + 2
IF @LastName IS NOT NULL SET @BITMASK = @BITMASK + 4
IF @FirstName IS NOT NULL SET @BITMASK = @BITMASK + 8
IF @Title IS NOT NULL SET @BITMASK = @BITMASK + 16
IF @TitleOfCourtesy IS NOT NULL SET @BITMASK = @BITMASK + 32
--Build the Clause filtered by the bitwise expression
SELECT @WHERE = COLUMN_NAME + ' = @' + COLUMN_NAME + ' AND ' + @WHERE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME= 'Employees' AND @BITMASK = POWER(2,ORDINAL_POSITION) | @BITMASK
ORDER BY ORDINAL_POSITION DESC
--Complete the Where expression
IF LEN(@WHERE) > 0
SET @WHERE = ' WHERE ' + LEFT(@WHERE,LEN(@WHERE)-4)
--Final SELECT
SET @WHERE = 'SELECT * FROM Employees' + @Where
--PRINT @WHERE
--Notice that every single parameter is declared....
EXEC sp_executesql @WHERE, N'@EmployeeID int, @LastName nvarchar(40),
@FirstName nvarchar(20), @Title nvarchar(60), @TitleOfCourtesy nvarchar(50)',
@EmployeeID = @EmployeeID, @LastName = @LastName, @FirstName = @FirstName,
@Title = @Title, @TitleOfCourtesy = @TitleOfCourtesy
go
--Examples
exec upEmployeesSearch 3, 'Leverling', 'JANET', NULL, 'Ms.'
exec upEmployeesSearch NULL, NULL, NULL, NULL, 'Ms.'
exec upEmployeesSearch NULL, NULL, NULL, 'Sales Representative', 'Ms.'
exec upEmployeesSearch NULL, 'Leverling'
exec upEmployeesSearch

It looks like a lot of typing but is far less than others, it is relatively easy to script out the arguments, the bit wise IF's and the arguments for sp_executesql.

The use of the INFORMATION_SCHEMA view used in this example can (and should) be replaced by a custom table that holds the relevant bit wise values, columns names and operators (if you are using more than equality).
Expand the custom table setup to also contain the SELECT expression then create a UDF that returns the entire expression and you have a framework to use across multiple queries with minimal work.

EDIT: I am getting some feedback regarding the overall performance of the entire procedure including the bitwise table work. So in the interests of a true comparison, below is a simple schema for the BITWISE Table and the altered statment that should be replaced.

--DDL
CREATE TABLE [BITWISE] ([TABLE_NAME] [sysname] NOT NULL , [COLUMN_NAME] [sysname] NOT NULL ,
[BitWise] [int] NOT NULL, PRIMARY KEY(TABLE_NAME, COLUMN_NAME))
GO
--DML
INSERT BITWISE
SELECT 'Employees' AS TABLE_NAME, COLUMN_NAME, POWER(2,ORDINAL_POSITION) as BitWise
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME= 'Employees'
go --Replace the INFORMATION_SCHEMA view work with this in the above procedure...
SELECT @WHERE = COLUMN_NAME + ' = @' + COLUMN_NAME + ' AND ' + @WHERE
FROM BITWISE
WHERE TABLE_NAME= 'Employees' AND @BITMASK = BITWISE | @BITMASK

Print | posted on Wednesday, April 07, 2004 11:40 AM

Feedback

# re: Free Range SQL - It tastes better.

left by Derrick Leggett at 4/7/2004 1:28 PM Gravatar
CREATE PROCEDURE upEmployeesSearchCompare

@EmployeeID INT = NULL,
@LastName NVARCHAR(40) = NULL,
@FirstName NVARCHAR(20) = NULL,
@Title NVARCHAR(60) = NULL,
@TitleOfCourtesy NVARCHAR(50) = NULL

AS

SELECT
E.EmployeeID,
E.LastName,
E.FirstName,
E.Title,
E.TitleOfCourtesy,
E.BirthDate,
E.HireDate,
E.Address,
E.City,
E.Region,
E.PostalCode,
E.Country,
E.HomePhone,
E.Extension,
E.Photo,
E.Notes,
E.ReportsTo,
E.PhotoPath
FROM
Employees E
WHERE
(@EmployeeID IS NULL OR E.EmployeeID = @EmployeeID)
AND (@LastName IS NULL OR E.LastName = @LastName)
AND (@FirstName IS NULL OR E.FirstName = @FirstName)
AND (@Title IS NULL OR E.Title = @Title)
AND (@TitleOfCourtesy IS NULL OR E.TitleOfCourtesy = @TitleOfCourtesy)


I just created this proc that does the exact same thing. It runs in less time, and your version takes 60 scans compared to 2. It takes 29 logical reads compared to 2.

So, I wouldn't even think of allowing a developer to release this in our environment. Did I do something wrong?

# re: Free Range SQL - It tastes better.

left by DavidM at 4/7/2004 1:59 PM Gravatar
Thanks for the feedback Derrick.

You seem to be taking into account the SCHEMA view query. Your method will NEVER use a SEEK on any index.. only SCANS...

Ignore the SCHEMA view (it was only for demonstration, in real life this is replaced by custom table that is far less expensive (a single seek on a small table) than the view...) and compare the actual Employees Query..

Add in a Query that should use an index (LastName has an index)

exec upEmployeesSearch null, 'Leverling'
exec upEmployeesSearchCompare null, 'Leverling'

# re: Free Range SQL - It tastes better.

left by Clayton Firth at 4/7/2004 2:17 PM Gravatar
I've been able to achieve a significant improvement in performance over David’s original article by instead of using the information_schema.columns view to create my where clause I just took out the bit of that that I needed from the system tables.

I created a view like:

create view Schema_Columns
AS
SELECT SO.name TABLE_NAME, SC.Name COLUMN_NAME, SC.ColID ORDINAL_POSITION FROM SysObjects SO
INNER JOIN SysColumns SC ON SO.ID = SC.ID
WHERE SO.XType ='u'

And replaced the call to Information_Schema. Also i threw out David's stupid order by clause (I gotta say, what where you thinking?).

All this resulted in almost a 200% saving and on AVERAGE performed better than t Derrick’s example. I say average because it all depends on what sort of initial call to his example is used to create the execution plan.

# re: Free Range SQL - It tastes better.

left by DavidM at 4/7/2004 2:24 PM Gravatar
I love your work Clayton... I have added an EDIT portion to show a custom table (BITWISE) that holds the info... Runs like the shower of shit!!!!!

The ORDER BY clause was to ensure that the Columns generated in the WHERE expression followed there ordinal... I thought it would matter with the position of the arguments in the sp_executesql.. Obviously it does not...

# re: Free Range SQL - It tastes better.

left by Derrick Leggett at 4/7/2004 3:03 PM Gravatar
It would be interesting to see the same comparisons done on multi-million row tables ran about 20 different ways.

If I saw it still outperform, then I would be sold. Although, I still can't imagine explaining this one to my developers. (grin)

# re: Free Range SQL - It tastes better.

left by DavidM at 4/7/2004 3:17 PM Gravatar
Thanks Derrick,

Actually this is in production against a million row table...

We basically had a query that was trying to accomodate about 30 combinations of WHERE predicates.. It rain like a dog (from 5 to 15 seconds) ... Most of the searched columns are indexed, but it never used them.. Altered it to use this method (with a bitwise table) and it now rarely runs over 1 second....

You get a compilation hit every time, but using SEEK's instead of SCAN's makes up for it easily...

The only time it will ever be slower (I think) is when both execution plans are identical and thus the bitwise work is the extra load....

# re: Free Range SQL - It tastes better.

left by Lavos at 4/8/2004 8:53 AM Gravatar
Nice trick, I hadn't picked up on the bitwise comparison you're doing in the bitmask check.

If you used named parameters, your examples look a lot better and don't depend on ordinals. E.G.:

exec upEmployeesSearch @TitleOfCourtesy='Ms.'
exec upEmployeesSearch @Title='Sales Representative', @TitleOfCourtesy='Ms.'


And one final note, I've switched to using templates with Eric Smith's CodeSmith tool for a lot of tasks that are repeatable like this.

(sp_addextendedproperty is a good friend when you start using templates :)

You can drop the bitmask table and ordinal position checks, and "hard code" the presence checks quite easily using a template, and you would only need to rerun the template when your schema changes.

For instance, I would prefer to have my template generate this:

SELECT @WHERE =
CASE WHEN @EmployeeID IS NOT NULL THEN 'AND EmployeeID = @EmployeeID ' ELSE '' END +
CASE WHEN @LastName IS NOT NULL THEN 'AND LastName = @LastName ' ELSE '' END +
CASE WHEN @FirstName IS NOT NULL THEN 'AND FirstName = @FirstName ' ELSE '' END +
CASE WHEN @Title IS NOT NULL THEN 'AND Title = @Title ' ELSE '' END +
CASE WHEN @TitleOfCourtesy IS NOT NULL THEN 'AND TitleOfCourtesy = @TitleOfCourtesy ' ELSE '' END

-- Bah, I forget the order of arguements and
-- where to start counting, but basically chop
-- off the initial AND and last space
SELECT @Where = SUBSTRING (@Where, 4, LEN(@Where)-5)


Of course, the cost of hitting the target table will probably dwarf any time saved by avoiding bitshifts, so this would be an minimal gain at best.

# Take Outs for 7 April 2004

left by Enjoy Every Sandwich at 4/8/2004 12:18 AM Gravatar
Take Outs for 7 April 2004

# re: Free Range SQL - It tastes better.

left by tekheang at 11/3/2005 8:04 PM Gravatar
hello i would like to take this code
can you send to me?bye good luck
Comments have been closed on this topic.