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 |
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2013-03-20 : 11:46:04
|
Performance issue, it is taking too long. And I only have 180,000 rows.selct * from T1 a where a.id=7380 and lower(ltrim(rtrim(Last))+ltrim(rtrim(First))+ltrim(rtrim(dob))) not in(slect lower(ltrim(rtrim(Last))+ltrim(rtrim(First))+ltrim(rtrim(dob))) from t2 where id=7105)Any hints please?--------------------------Joins are what RDBMS's do for a living |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-20 : 11:58:44
|
problem is usage of so many function and concatination in where condition.try this alternativeSELECT *FROM T1 t1WHERE id = 7380AND NOT EXISTS (SELECT 1 FROM t2 WHERE Last = t1.Last AND First = t1.First AND dob = t1.dob WHERE id = 7105) Unless you use a case sensitive collation you dont need LOWER function logic------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
xhostx
Constraint Violating Yak Guru
277 Posts |
Posted - 2013-03-20 : 12:05:08
|
Visakh Thanks a lot man, that was magic :)--------------------------Joins are what RDBMS's do for a living |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-20 : 13:04:57
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|