Does this Union first and then fillter, or filter first then union?

[joelwenzel]

Hi,

I have this select statment in a view

(SELECT
s.MyTime,p.FK_CategoryID,
(s.Col1-p.Col1) as Diff
FROM stats_main s,stats_comparison p WHERE s.FK_ComID = p.ComID)
UNION ALL
(SELECT
s.MyTime,p.FK_CategoryID,
(s.Col2-p.Col2) as Diff
FROM stats_main s,stats_comparison p WHERE s.FK_ComID = p.ComID)
UNION ALL
(SELECT
s.MyTime,p.FK_CategoryID,
(s.Col3-p.Col3) as Diff
FROM stats_main s,stats_comparison p WHERE s.FK_ComID = p.ComID)

Ok, so I'm trying to optimize this selection. Assume that stats_main is a very long table (several million rows). If I want to select from this view all records with categoryID = 100 , does it first do the 3 unions and then filter out records with the proper categoryID, or does it filter first, and then union?

I ask because if it filters after the union, it is probably going to be pretty slow to select from this view. A simple alternative that I could use would be to just create a temporary table when I need it and fill it with the proper values. What do you think?

 

[vongrunt]

It filters before the union.

Btw. there is no need for UNION [ALL] in this case.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]