SQL statement question -- sql server programming

Steve Kass
3/3/2004 7:45:41 PM

Anith,

I thought I'd see if using UNION instead of UNION ALL can make a real
difference here, and so I ran this repro. The version without ALL does
three times the work, according to the stats i/o. Granted, the
difference in time is smaller than the time it takes to type ALL twice,
but it's worth filing away that it matters even in a case like this.

set statistics io on
go

select
CustomerID,
max(
case d when 1 then OrderDate
when 2 then ShippedDate
when 3 then RequiredDate
end) as maxDate
from Northwind..Orders, (
select 1 d union all select 2 union all select 3
) T
group by CustomerID
go

select
CustomerID,
max(
case d when 1 then OrderDate
when 2 then ShippedDate
when 3 then RequiredDate
end) as maxDate
from Northwind..Orders, (
select 1 d union select 2 union select 3
) T
group by CustomerID
go
set statistics io off
go

SK

[quoted text, click to view]

Leon Lien
3/3/2004 9:54:18 PM

Let's say I have a table Table1 has fields like below

ID Date1 Date2 Date3 Date4
1 2/23/03 4/15/03 3/11/03 3/18/03
2 2/24/03 2/10/03 4/12/03 3/15/03
3 1/11/03 3/12/03 2/11/03 3/31/03

I need a SQL statement that will let me find out the
largest date in Date1 to Date4 for each ID ?

Can anyone help ?

David Portas
3/3/2004 10:12:38 PM

To return the highest non-NULL date:

SELECT id,
(SELECT MAX(dt)
FROM
(SELECT date1 AS dt
UNION ALL
SELECT date2
UNION ALL
SELECT date3
UNION ALL
SELECT date4) AS X) AS max_date
FROM SomeTable

--
David Portas
SQL Server MVP
--

Anith Sen
3/4/2004 12:32:34 AM

Well said. In these cases the merge join due to the UNION ALL seems to be

--
Anith

Isaac Blank
3/5/2004 9:56:40 AM

I have always thought order of evaluation in CASE expressions is always top
to bottom. If that's true, then one can save a few CPU cycles:

CREATE VIEW BigFoo (foo_id, latest_date)
AS
SELECT foo_id,
CASE WHEN date1 >= date2
AND date1 >= date3
AND date1 >= date4
THEN date1
WHEN date2 >= date3
AND date2 >= date4
THEN date2
WHEN date3 >= date4
THEN date3
ELSE date4 END
FROM Foobar;

[quoted text, click to view]

Joe Celko
3/5/2004 12:11:38 PM

[quoted text, click to view]
always top to bottom. If that's true, then one can save a few CPU
cycles: <<

Yep! I saw that after I posted what makes this more embarassing is that
I have a tutorial example in which there are four possible cases and one
of them has an ugly bit of logic.

I first do it with the complex case in the first WHEN clause, followed
by the others. Then I re-arrange the WHEN's so that the complex WHEN
becomes the ELSE because the three simple cases exhausted everything.

"We must fall back upon the old axiom that when all other contingencies
fail, whatever remains, however improbable, must be the truth." --
Sherlock Holmes

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

Joe Celko
3/5/2004 1:12:12 PM

Keep going with the idea of a binary tree search:

CASE WHEN
CASE WHEN date1 >= date2 THEN date1 ELSE date2 END
[quoted text, click to view]
CASE WHEN date3 >= date4 THEN date3 ELSE date4 END
THEN CASE WHEN date1 >= date2 THEN date1 ELSE date2 END
ELSE CASE WHEN date3 >= date4 THEN date3 ELSE date4 END
END
FROM Foobar;

I'd guess that the optimizer can see the common subexpressions easier in
one CASE expression than in a materialized table in a FROM clause.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

Steve Kass
3/5/2004 3:02:17 PM

Isaac,

Your algorithm requires between 3 and 6 comparisons for each set of
dates, and I believe it requires an average of 2+2ln2, or about 3.386,
comparisons for random (date1, date2, date3, date4) values chosen from
the same large range of dates (in the limit as the date range grows
large). You can get by with exactly 3 comparisons regardless of values
this way (if we have storage available for two intermediate results):

select
foo_id,
case when date12 >= date34 then date12 else date34 end as MaxDate
from (
select date1, date2, date3, date4,
case when date1 >= date2 then date1 else date2 end as date12,
case when date3 >= date4 then date3 else date4 end as date34
from Foobar
) as Foobar

Of course there's no guarantee that the optimizer will preserve this
efficiency.

SK

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : SQL statement question