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]

jus keep in mind that union all is much faster than union

--BAD
select * from table1
union
select * from table2


--Better
select * from table1
union all
select * from table2

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[joelwenzel]

Thanks for the tip. I didn't realize that union all was faster. Still, is it bad to have 20 unions as in the situation I described?

 

[SQLDenis]

are you going against 20 tables?

you could do 20 queries and assign the value to a variable

select @id =id from table1
select @name =name from table2
...
...
...


select @id as ID, @name as Name......

do both use whatever is faster

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[r937]

jus keep in mind that union all is much faster than union

well, that only works in certain cases

r937.com | rudy.ca

 

[vongrunt]

Generally speaking, guess which one is faster: simple concatenation (UNION ALL) or de-duping (UNION)?

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

 

[SQLDenis]

??

>>Generally speaking, guess which one is faster: simple concatenation (UNION ALL) or de-duping (UNION)?

do you mean this:
simple concatenation (UNION) or de-duping (UNION ALL)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

> do you mean this:
> simple concatenation (UNION) or de-duping (UNION ALL)

UNION does de-duping, right?

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

 

[SQLDenis]

>>UNION does de-duping, right?
yes that's why i don't understand the question

is your question this?
which is faster

select distinct * from (
select * from table1
union all
select * from table2) z

versus

select * from table1
union
select * from table2

Is that what you are asking?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[joelwenzel]

Ok, Here is a sample of the query I am doing...only it is way longer

(SELECT Col1,Col2,
IF(ColA = 0,NULL,((ColA - ColA1 )) as diff
FROM Table1)
UNION ALL
(SELECT Col1,Col2,
IF(ColB = 0,NULL,((ColB - ColB1 )) as diff
FROM Table1))
UNION ALL
(SELECT Col1,Col2,
IF(ColC = 0,NULL,((ColC - ColC1 )) as diff
FROM Table1))
UNION ALL
(SELECT Col1,Col2,
IF(ColD = 0,NULL,((ColD - ColD1 )) as diff
FROM Table1))

 

[SQLDenis]

This is not valid SQL Server syntax
are you using SQL Server ?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

> Is that what you are asking?

I asked rudy why he thinks union all is much faster than union only in certain cases. UNION ALL is definitely simpler inside.

> Ok, Here is a sample of the query I am doing...only it is way longer

IF?

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

 

[SQLDenis]

Ok, I guess I misunderstood and lost my marbles
Oh no pacman ate them

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[r937]

yes that's why i don't understand the question

i think perhaps vongrunt's question is the same as mine


sure, walking from new york to boston is faster than walking from new york to san diego

similarly, UNION ALL is faster than UNION, except it's totally wrong if you wanted to end up in san diego, er, i mean, if you wanted duplicates removed

r937.com | rudy.ca

 

[SQLDenis]

true, this doesn't make any sense

select distinct * from (
select * from table1
union all
select * from table2) z

If you need distinct values
this is better in that case

select * from table1
union
select * from table2


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[joelwenzel]

I'm using mySQL. Stupid me...I use both SQL Server and MySQL but mostly message this board for SQL related issues because it has more activity. Just change the IF statement to a CASE WHEN one. I basically just wanted to show that my unions were all on the same table and only looking at slightly different things.

 

[SQLDenis]

what about something like this??

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

select @val1 = case when ColA =0 then null else ColA  - ColA1 end,
@val2 = case when ColB =0 then null else ColB  - ColB1 end,
@val3 = case when ColC =0 then null else ColC  - ColC1 end
.......
from Table1

then do a select with the variables

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[joelwenzel]

that would work except that I want to preform some aggregate functions on the resulting columns (I'm not returning a single row in each of the unioned select statments). Basically I will do some averages of diff, and some other stuff.

Its just that most of my SQL experience comes from school where they say stuff like joins/unions areexpensive...but they give not quantativve amount as to how expensive so I don't know if doing 20 is that big of a deal or not. I can't tell right now because my table is small but it ill eentually have millions of rows.

 

[r937]

i would love to know which school teaches you that "stuff like joins/unions are expensive"

what do they teach you to do instead? read the table into memory and manipulate it with code? sheesh

in any case, with all the "diff" values you're calculating, why couldn't you calculate them when you store the rows? and why aren't you storing them into a normalized table?

r937.com | rudy.ca

 

[joelwenzel]

Table 1 is actually a view that results in 20 columns of related data. It was normalized (mostly) before that. What I'm trying to do is convert 20 columns into rows. I can calculate the diff values when I store thte rows, but then I have 20 diff values. I still want to combine them all into 1 column so that I can preform aggregate functions on them.