|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 02/15/2005 : 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 recors
bigtabel: approx 19.000.000 records (index om omschrijving)
do you have anny idea to help me please?
thanks a lot" |
|
|
Seventhnight
Flowing Fount of Yak Knowledge
USA
2419 Posts |
Posted - 02/15/2005 : 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
