Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-01-28 : 23:50:56
|
Dave writes "I have a fairly complex query that is joining three tables, all greater than 1,000,000 lines.
From T1
inner join T2
on T2.VarChar6 = T1.VarChar6
and T2.VarChar6 = T1.VarChar6
and T2.TinyInt = T1.TinyInt
and T2.SmallInt = T1.SmallInt
and T2.SmallInt = T1.SmallInt
inner join T3
on T3.VarChar6 = T1.VarChar6
and T3.VarChar6 = T1.VarChar6
and T3.TinyInt = T1.TinyInt
and T3.SmallInt = T1.SmallInt
and (complex non-sargable expression)
The process time as shown above was about 3 minutes for the data size mentioned above. I also have to run the same query against much larger datasets, which takes significantly longer (1.5 hours). This is why I am seeking to improve the performance of my query.
I've always found that integer join conditions perform better than text joins, so I replaced the VarChar(6) fields with smallint keys, and what took 3 minutes now takes 2 hours. I looked at the execution plans for with and without the smallint keys, and they are identical. (It's using hash joins). This is the only change that I made.
I am running sql7.0 on win2k.
Any Ideas on why performance went down with smallint keys?
Dave <><" |
|
|
|
|
|
Text vs. Integer Join Conditions