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 |
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
 Posted - 2007-05-02 : 23:57:13
|
| does anyone know if this query could be optimized:select p.address,n.stnfrom dbdsnz..tbl_NZphone pinner join dbnzprop.dbo.tbl_nzprop n on p.address like '%'+n.road_name+'%'inner join dbaddress.dbo.tbl_sts a on (p.address like '%'+n.road_name +' '+ a.street_type_code_small + '%'or p.address like '%'+n.road_name +' '+ a.street_type_code + '%')and (p.address like '%'+n.city_town+'%' or p.address like '%'+n.region+'%' or p.address like '%'+n.suburb+'%')and(p.surname_1 in (n.surname_1,n.surname_2,n.surname_3,n.surname_4,n.surname_5)or p.surname_2 in (n.surname_1,n.surname_2,n.surname_3,n.surname_4,n.surname_5))it takes a while to run as each table has over a million records... but i understand if it cant be changed....if u need more info on table contents let me know, mainly curious about structure of query |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-05-03 : 00:36:38
|
| No. The way you have written the query (with leading and trailing percentage sign), no indexes can be used.Peter LarssonHelsingborg, Sweden |
 |
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2007-05-03 : 01:31:54
|
| sweet thanx, i was kinda hoping something like this:p.address like '%'+n.city_town+'%' or p.address like '%'+n.region+'%' or p.address like '%'+n.suburb+'%'could be summed into an IN statement but i dont think its possible |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2007-05-03 : 23:54:10
|
| okay i dont know if this helps but ive removed the %% and replaced the like statements with charinxed != 0 statements... |
|
|
|
|
|
|
|
|
query optimization ... query