CODEWITHSUNDEEP
sqlunion
Mike
Mike

Reputation: 2339

UNION causes SQL statement to run a lot slower

I wrote a SQL statement, the individual statements work well, but when I combine them using UNION the statement takes dramatically longer to run. Is there an error that I am missing or a way to make this faster?

select bundle.bundle, bundle.week, bundle.sched_dt, dropper_assign.dropper_id 
from bundle, dropper_assign
where bundle.bundle not in 
    (select bundle from forecast_entry)
and bundle.week = dropper_assign.week
and bundle.sched_zip3 = dropper_assign.zip3
and bundle.sched_dt = dropper_assign.sched_date
and bundle.project_cd = dropper_assign.project_code
and dropper_assign.dropper_id <> 10002
and bundle.project_cd = 'EXFC'

union

select bundle.bundle, bundle.week, bundle.sched_dt, splits.dropper_id
from bundle, splits
where bundle.bundle not in 
    (select bundle from forecast_entry)
and bundle.bundle = splits.bundle
and splits.dropper_id <> 10002
and bundle.project_cd = 'EXFC';

Upvotes: 2

Views: 152

Answers (2)

user610217
user610217

Reputation:

UNION will apply a DISTINCT filter on the final result, which, on large data sets, can be an expensive operation.

Upvotes: 0

MatBailie
MatBailie

Reputation: 86715

UNION takes two data sets and returns the unique overlap. In other words, it spends time removing duplicates.

UNION ALL, however, does not compare the result sets to remove the duplicates.

Upvotes: 4

Related Questions