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 - 2005-02-15 : 07:06:44
|
| Marc writes "Dear sir,when i run this query it takes about 14 seconds------------- 1 --select cpycod as klantnr, ranking from slwCust where slw1 in ('reisbureau' , 'aalst') and (len(slw2) = 0 or slw2 is null) and (len(slw3) = 0 or slw3 is null) and (len(slw4) = 0 or slw4 is null) union select cpycod as klantnr, ranking from slwCust where slw1 in ('reisbureau' , 'aalst') and slw2 in ('reisbureau' , 'aalst') and (len(slw3) = 0 or slw3 is null) and (len(slw4) = 0 or slw4 is null) union -- 2 --select distinct cpycod as klantnr, ranking from slwCust where slw1 in ('reisbureau' , 'aalst') and slw2 in ('reisbureau' , 'aalst')and slw3 in ('reisbureau' , 'aalst') and (len(slw4) = 0 or slw4 is null) union select cpycod as klantnr, ranking from slwCust where slw1 in ('reisbureau' , 'aalst') and slw2 in ('reisbureau' , 'aalst') and slw3 in ('reisbureau' , 'aalst') and slw4 in ('reisbureau' , 'aalst') union -- 3 --SELECT distinct woord1.cpycod as 'klantnr', sum(woord1.ranking) as 'ranking' from (select distinct cpycod, ranking from bigtabel where omschrijving like 'reisbureau%' ) woord1 , (select distinct cpycod, ranking from bigtabel where omschrijving like 'aalst%') woord2 where woord1.cpycod = woord2.cpycod group by woord1.cpycod ------------------------When i only union 1 and 2 it works in less than 1 second.When i run 3 separatly it takes les than 1 second.slwcust: aprox 100.000 recorsbigtabel: approx 19.000.000 records (index om omschrijving)do you have anny idea to help me please?thanks a lot" |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-02-15 : 08:07:54
|
do you care if you have duplicates? if not try 'Union All'...If this is a ranking sproc, why do you need the 3 queries like that? Is there not anyway to use case statements and combine at least the first 2?Corey "If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain |
 |
|
|
|
|
|
|
|
union