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 |
|
IceDread
Yak Posting Veteran
66 Posts |
 Posted - 2010-07-08 : 06:59:23
|
| Hi!I've one table, Table1, with ~100 000 rows in it with two columns of interrest, firstname and lastname. I have a new table, Table2, with four columns, firstname, middlename, lastname and wholename. I have to use these columns to find hits. The wholename column contains none to several names and the other columns contains none to one name. I need two successful hits for it to be a match. So I wrote some simple sql. So I wrote something like this below, but it's very very slow.Tips and info would be appreciated. And oh yeah, compability lvl is 80 so no cross applying with udf's to join the tables on. Thanks for info, tips and help!select *from table1 inner join table2 on (( LTRIM(rtrim(table2.firstname)) like LTRIM(rtrim(table1.firstname)) or LTRIM(rtrim(table2.middlename)) like LTRIM(rtrim(table1.firstname)) or LTRIM(rtrim(table2.lastname)) like LTRIM(rtrim(table1.firstname)) or LTRIM(rtrim(table2.wholename)) like ('%'+LTRIM(rtrim(table1.firstname))+'%'))and ( LTRIM(rtrim(table2.firstname)) like LTRIM(rtrim(table1.lastname)) or LTRIM(rtrim(table2.middlename)) like LTRIM(rtrim(table1.lastname)) or LTRIM(rtrim(table2.lastname)) like LTRIM(rtrim(table1.lastname)) or LTRIM(rtrim(table2.wholename)) like ('%'+LTRIM(rtrim(table1.lastname))+'%'))) |
|
|
|
|
|
Slow select, filter on varchars