Friday, February 16, 2007

SQL Server Bind Variables

Lately I have been looking for new ways to improve the performance/scalability of my t-sql code. I had remembered that with ORACLE and using cfqueryparam, took advantage of ORACLE's bind variables functionality.

Basically a bind variable, means it treats the queries the same, in terms of performance/explanation plans, even though certain variables are different.

Now while cfqueryparam is always a good practice for preventing sql injection and protecting, I was not aware of how to do bind variables for SQL Server, until now.

sp_executesql

is a very powerful but limited dynamic sql building tool.

execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl = @level',
N'@level tinyint',
@level = 35

Above is an example from http://doc.ddart.net/mssql/sql70/sp_ea-ez_4.htm

Notice how N is in front of each line, that is to force it to be nvarchar, because the stored procedure accepts only nvarchar, even though the fields in your table may just be int, varchar etc.

Secondly this allows 3 parameters

first parameter is the sql query
second parameter is the declaration of any incoming parameters
third parameter is the setting of those parameters

The problem is that if you have multiple incoming parameters, they all have to be declared on one line. The Sql query can be multiple lines but itself consists of the first slot in this stored procedure.

So if you want to not use up memory by caching a query, this is one way to improve performance, by taking advantage of the bind variables, capability.

BTW, if you want more tips on t-sql query optimization, I recommend that you check out this article.

http://www.sql-server-performance.com/transact_sql.asp

No comments:

Post a Comment