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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select Performance issues

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 alternative


SELECT *
FROM T1 t1
WHERE id = 7380
AND 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-20 : 13:04:57
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -