November 8, 2004 at 3:21 am
My application's search is based on some queries grouped through union or intersect (simulate by exists) operator.
My problem is that I would like to optimize the search and I cannot optimize UNION (or INTERSECT) operator.
If I add the times of all the queries I get a number infinitely small than the time obtain by the reunion or intersection of these queries.
For example, if I have:
select 1
union (intersect with exists)
select 2
union (intersect with exists)
select 3
etc.
If I add the select1, 2, 3 etc. response time I will get a very small result comparing with the union (Intersect) result time. I cannot use union all because I need to eliminate the duplicates.
Can you give me an advice regarding this optimizing problem?
Thanks in advanced.
November 9, 2004 at 12:47 am
Hi Stefan,
It would be very helpfull for SSC members eager to help you if you post as much structure and exact SQL statements as possible. The solution to the problem may be hidden in the parts of SQL statements that are not posted.
Regards,
Goce.
November 9, 2004 at 3:40 pm
If your search is returning a huge resultset, I can see why the union would take so much longer than the three individual selects. The union has to get rid of duplicates.
Generally, the union would be most efficient. But sometimes the optimizer just chooses a poor plan.
Try a multi-step process. Insert the results of each select into a table variable (or temp table); then select distinct from the table variable.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply