Datatype mismatches: SQL Server performance tip
I ran into a very interesting find the other day while trying to optimize some queries by studying the execution plans. We have a bunch of stored procedures that use an underlying view that points to a single table. This table has an "item_id" key and often we use that key in the WHERE clause or for joins.
As there was an existing clustered index on that column, I couldn't figure out why I was always getting table scans on the underlying table. In other words, the query engine was completely ignoring the index or deciding not to use it.
I ran a simplified version of the query with the item_id hard-coded and all of a sudden it did use the index, giving me a huge performance boost because the table is a bit large. Not millions of rows large, but relatively large for our application. That got me thinking about datatypes, so I decided to check the underlying table. Numeric(8,0). Hmmm. We are using int everywhere. Surely that is a simple enough implicit conversion to not cause issues, right? Wrong. Once I changed my input parameters on the stored procedures to use numeric(8,0) instead of int, I started getting nice clustered index seeks everywhere. Bingo. I've seen this type of thing before, but generally it is a more drastic conversion or comparison like datetime vs varchar or int vs char or something like that.
Anyway, another weird performance tip that is certainly not intuitive. This is on SQL Server 2000, not sure if the same behavior exists in SQL Server 2005 or newer.