JOINS - int vs. varchar performance
-
This is really a design question. I have always suspected that SQL server performs joins on integers faster than on varchar. I have been unable to locate any official documentation on the subject however. Does anyone know if any such documentation exists.
FYI, I have performed a simple experiment with 2 pairs of parent/child tables. One set had the PK of the parent and FK of the child table as integer, and the other pair had them as varchar. I did 1 million inserts into each parent table. (man my finger was tired
j/k) Then I did 1 million inserts into the child tables with each record referencing a different record in the parent. I.e. each parent record had exactly one child record. I even made the values in the varchar columns to be '1', '2', '3', etc. so that they were storing the same values, but in different data types. My results showed that on a 1 million row INNER JOIN, the integer was about twice as fast. This was on a P4 2.0GHz desktop with a 7,200 RPM IDE HD. Your mileage may vary.
Beyond this empiracle evidence, I would like some hard documentation from an authoritative source telling my why integers are faster than varchar (if that is indeed the case). Thanks!
---
Edited by - bdill on 06/19/2003 10:17:39 PM
---
Brian Dill -
Integers are stored using 32 bits.
1000000 stored as a varchar requires (7*8) 56 bits of storage. The larger the column, the more processor cycles required to make a match.
As soon as you go beyond 32 bits for a column, more work has to be carried out by the server, and thus integers and quicker than varchar columns. Hope this helps. All is taken from MS.
-
Thanks j.o.s. I was looking for that myself. I know that it has been said that there is no difference between comparing varchar and int's but in fact it is a size matters issue as pointed out.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply