SQL indexing and parameters

I’ve spent some time over the last couple of weeks pondering an unusual SQL tuning problem. The situation is as follows:

The following statements are executed against a SQL 2000 database that contains >1M records in the act_item table. They are run using Query Analyzer and the Duration and Reads come from SQL Profiler

exec sp_executesql N'update act_item set Priority = @Priority where activity_code = @activity_code', N'@activity_code nvarchar(40),@Priority int', @activity_code = N'46DF335F-68F7-493F-B55E-5F9BC6CEBC69', @Priority = 0

Reads: ~22000
Duration: 250-350 ms

DECLARE @Priority int
DECLARE @Activity_Code char(36)

SET @Priority = 0
SET @Activity_Code = '46DF335F-68F7-493F-B55E-5F9BC6CEBC69'
update act_item set Priority = @Priority where activity_code = @activity_code

Reads: ~160
Duration: 0 ms

As you can see, the first and second block should do the same thing and you might expect it to take the same amount of time. But it obviously doesn’t. And just so that we’re clear, the activity_code field is indexed in the table. So I would have expected the first update statement to run quickly too.

The reason for the discrepancy is not readily apparent in the information I just provided, although a clue is there. The reason for the difference revolves around the data type for the activity code. In the table definition, it is a char(36). But when the a parameter of type nvarchar(40) is used, rather than converting the nvarchar(40) to a char(36), it converts the char(36) to an nvarchar(40). Which means that instead of using the index to retrieve the row to update, it performs a table scan. Which on a 1 million plus row table is a significant operation.

If you’d like to read about another persons journey down this road, check out http://www.sqlservercentral.com/columnists/dpeterson/bewareofsearchargumentsargdatatypes.asp (thanks to Doug Smyth for this post). This post also refers to a document in SQL Books On-Line that describes the conversion precedence of the various data types. While memorizing the list probably isn’t important, it is important to be aware of this list when defining the parameter types for the values passed through ADO.NET. I’m sure many of you are diligent about this, but for those who aren’t, you need to be keep in mind that not matching the parameter in the database might cause you some performance issues.