December 1, 2004 at 7:13 am
I recently worked with Microsoft SQL Server technical support team on a performance problem my application was having. They looked at a few queries we had and one of the recommendations they made was to explicitly cast to a bit anytime we are performing a comparison on a bit field. An example would be the following:
Say we have a table called Import that has two columns: ImportDate DATETIME and ImportComplete BIT. The ImportComplete column stores a 0 if the import has not occurred that day and a 1 if it has. We had the following SQL statement:
SELECT ImportDate FROM Import WHERE ImportComplete = 1
Microsoft strongly recommended that we change it to the following because 1 is an integer by default:
SELECT ImportDate FROM Import WHERE ImportComplete = CAST(1 AS BIT)
Does anyone have any thoughts as to whether this comparison of like data types will actually improve performance or whether the function call in the WHERE clause hinders the performance (I know its not as bad as if it were on the left side of the equals sign).
December 2, 2004 at 7:06 pm
I have seen problems when using implicit casting. casting the 1 as a bit can be done explicitely (as in example 2) or implicitely (as in example 1). Implicit casting can cause performance problems, mainly when SQL is deciding whether to use an index or not. I've seen SQL ignore a valid index until I explicitely casted the value to be the same as the index.
I've never had a problem with bit datatypes (no indexing), but you are correct to be careful.
cl
Signature is NULL
December 2, 2004 at 7:39 pm
performance gain, you will not get from that example. BIT datatypes, index them not.
explicit conversions, yes, is the tool of the jedi. For performance? minimum gain you may get. For more accurate query results? yes, indeed.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply