June 6, 2006 at 3:10 am
I have got 4 tables. one acting as a master and 3 as child.
All these have one column in common, i.e. SessionID VARCHAR(50). this column is used to join master with 3 of its related tables.
I have had thousands of records in master table and tens of thousands of records in detail tables.
I am planning to optimize the queries for this resultset.
I am confused that whether the VARCHAR(50) is not degrading the performance while joining the tables.
Could anybody tell me what datatype is supposed to be most performance efficient and how much would it make difference when changed from VARCHAR(50)
Thanks in advance.
June 6, 2006 at 4:17 am
Murtaza
I would imagine that joining on such a wide column would cause performance problems, especially as your table gets to millions of rows instead of thousands. Assuming you have a non-clustered index on SessionID in your child tables, this means that fewer rows can fit on a page and therefore more IO is required to perform the join.
You should change SessionID to an integer data type if you can. For example, bigint will give you 2^64 (about 18,000,000,000,000,000,000) different values and is only 8 bytes wide.
Even if you are constrained to have non-numeric characters in SessionID, does it really need to be varchar(50)? Work out what is going to be the longest string you will ever have as an ID and set the varchar size accordingly.
I can't say how much difference my suggestions will make - you'll have to find that out by testing.
John
June 6, 2006 at 5:51 am
Also, if you must have non-numeric characters, the char datatype is going to be better for joining than varchar. Assuming that SessionID values are pretty much all the same length, you should definitely go with char. Because SQL Server stores all the char values as equal length strings, it can be much more efficient about indexing them.
June 6, 2006 at 6:50 am
Lookup CHECKSUM in BOL, the recommendation of the use of an additional indexed hash column (int) may improve your queries.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply