Slow query when using UNION and VIEWs.

Hi guys.

I am having such query:

SELECT * FROM
(SELECT * FROM Archive_data
LEFT JOIN MyView
ON Archive_Data.ID = MyView.ID)
UNION ALL
SELECT * FROM Data
LEFT JOIN MyView
ON Data.ID = MyView.ID

The query is executing in 45 sec when using UNION and MyView VIEW.

If I separate query onto two queries then each of the queries is executing below one second.

Why UNION has such impact on the query when I am using views?

Thanks for the answer.

Because it has to search * data

What is the size of your database?

Are your indexes OK?

In my opinion a SELECT * FROM X is always slow in case of huge amount of data.

Simply the difference between a cheap query and an expensive one, right?.

Try to limit your output:

http://scott.yang.id.au/2004/01/limit-in-select-statements-in-firebird/

Thanks rapgame, but I do not think that selecting ALL columns is the problem here. (I could be always wrong). But if separated queries are executing 1 second each and UNION is simply adding one result set to the other than how can it slow down the whole operation 20 times? This is simply not logical. Unless UNION is diong something more that adding one result to the other...

UNION removes the duplicate records from the both queries. So it's like GROUP BY all of your fields. Just for the test - how long takes if you use UNION ALL( UNION ALL doesn't remove duplicates)