Are Union's that 'expensive'? 2

[joelwenzel]

I have a problem. In my database, I need to group certain data. I can either do about 20 queries (and get all the data separtly). This is a nuscience because the data will not me in a nice form.

Or, I can create a view and union the 20 queries. This would be easier for me to work with but will it take a lot more processing power/slower? What I mean is, should I do this or not?

 

[SQLDenis]

you can always do stuff like this...
assuming you populated these variables with values from your table


declare @val1 decimal(12,3),@val2 decimal(12,3),@val3 decimal(12,3)

select @val1 = 5,@val2 = 9,@val3 =8

select max(col1) as MaxValue,min(col1) as MinValue,avg(col1) as AverageValue
from(
select @val1 as col1
union all
select @val2
union all
select @val3) x

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLSister]

Depending onthe size of the table you are accessing, it miught be better to do each part separately and insert into a temp table ot table variable as you go along. Then do your final calculations from the table varaible whic can help you out in terms of processing against a large table, expecially if you are doing any kind of calculation or using functions. But the only way to really tell for sure what is faster is to test it both ways.

As fara the union al vice Union thing - remember you can only use union all if the results of the differnt individual selects are mutually exclusive or you will have duplicated records (which you normally don;t want if you are doing some sort of aggregate after you get the data.)

Questions about posting. See faq183-874
Click here to help with Hurricane Relief