UNION query works slow

Question

On an SAP Sybase ASE 15.5 database.

UNION works very slow

select col1,col2,col3
from table1(INDEX nn MRU),table2 (INDEX hh MRU)
where key1=key2
UNION
select col1,col2,col3
from table3(INDEX pp MRU),table2 (INDEX ll MRU)
where key4=key3

table 1 and 3 are small(10 records each tables 1 and 4 - are very large (68 mln records each) and I neeed only active records, which we have 1,5 mln in each big table,

Actually, I tried separate queries. The same slow. Even if I purge inactive records or select active record to different table, it still takes long time. Yesterday I rebuild one index in table1, but it doesn't help.

The problem is that I cannot delete inactive records by the company rule

Please stop using answers to make comments. Use the comment function, or edit your question to add additional information. — Mike Gardner, Feb 25 '14 at 14:57
Why are you specifying the indexes, as opposed to letting the optimizer chose? Without knowing more about your table and index structure it's going to be hard to help. You could also try posting the query plan to let us see where things are bogging down. Maybe you could create views against the large tables that only include the active records, then UNION against the views? — Mike Gardner, Feb 25 '14 at 15:03
Actually, I tried separate queries. The same slow. Even if I purge inactive records or select active record to different table, it still takes long time. Yesterday I rebuild one index in table1, but it doesn't help. — user2843135, Feb 25 '14 at 14:50
We must inspect the actual query plan to take a idea and check the real culprit. It can be the join, the union or some odd thing in the schema. it's not likely the union. It merely matches the records from both selects (well, it can be the union, in a special case where it ill do some implicit cast to match columns from selects...i guess that can be tested with a UNION ALL) It, in general, bad indexing on the join match. Please post the query plan for a further investigation. — jean, Feb 27 '14 at 14:19

score 0 · Answer 1 · answered Feb 25 '14 at 14:37

My guess is that your problem isn't the union, but rather the join with a table that has 65 million rows in it (twice). If you are certain the union is your problem, why not just make two separate queries?

You could also add new tables to hold the inactive records and purge the active record tables. I don't know what other application logic you have, but that would speed up this query.

UNION query works slow

1 Answers1