Trying to get rid of a correlated subquery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Trying to get rid of a correlated subquery

Author

Topic

malachi151
Posting Yak Master

152 Posts

Posted - 2011-10-04 : 12:26:49

I've got a correlated subquery inside an APPLY, and it's killing performance, but I also can't figure out how to get rid of it.

The issue is that I have data with missing values in it, and I need to select the most recent value prior to a given date, but if the value for that date is missing I need to return a NULL as opposed to selecting a later date.

Basically the data is like this:


LOOKUP_TABLE:
event_date | state | value
1/1/2010   | CO    | 1
1/1/2009   | CO    | 2
1/1/2008   | CO    | 3
1/1/2009   | AZ    | 1.3
1/1/2008   | AZ    | 2.4

TARGET_TABLE:
some_date | state
2/3/2009  | CO
5/10/2010 | CO
11/1/2008 | CO
2/3/2009  | AZ
5/10/2009 | AZ
11/1/2008 | AZ

DESIRED_RESULT:
some_date | state | value
2/3/2009  | CO    | 2
5/10/2010 | CO    | 1
11/1/2008 | CO    | 3
2/3/2009  | AZ    | 1.3
5/10/2010 | AZ    | NULL
11/1/2008 | AZ    | 2.4

If I just do something like the following the problem is that for he 2010 / AZ value instead of getting a NULL I'll get the value from 2009, 1.3.

 
UPDATE t
SET
   t.value = l.value
FROM
   TARGET_TABLE t
     OUTER APPLY (SELECT TOP 1 * 
                  FROM LOOKUP_TABLE
                  WHERE state_code = t.state_code AND event_date <= t.some_date
                  ORDER BY event_date DESC)

So as a result I'm currently doing something like:


UPDATE t
SET
   t.value = l.value
FROM
   TARGET_TABLE t
     OUTER APPLY (SELECT *
                  FROM LOOKUP_TABLE
                  WHERE state_code = t.state_code AND event_date <= (SELECT MAX(event_date) FROM LOOKUP_TABLE WHERE event_date <= t.some_date))

So I'm looking for some alternative, preferably without adding place holder records to the "LOOKUP_TABLE", that can help me eliminate the correlated subquery within the apply.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 12:38:50

[code]UPDATE t
SET
t.value = l.value
FROM
TARGET_TABLE t
OUTER APPLY (SELECT TOP 1 *
FROM LOOKUP_TABLE
WHERE state_code = t.state_code AND event_date <= t.some_date
AND DATEDIFF(yy,0,event_date) = DATEDIFF(yy,0,some_date)
ORDER BY event_date DESC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

malachi151
Posting Yak Master

152 Posts

Posted - 2011-10-04 : 12:46:37

That would work in my example, but it wouldn't work in reality, because in reality the "event_date" values aren't all "1/1", which means that the years may not match between the event_date and some_date values. IOW, if event_date = [4/1/2008, 4/1/2009, 4/1/2010) and some_date = 3/1/2009, then it should pull the value from 4/1/2008, not 4/1/2009.

quote:

Originally posted by visakh16

UPDATE t
SET
   t.value = l.value
FROM
   TARGET_TABLE t
     OUTER APPLY (SELECT TOP 1 * 
                  FROM LOOKUP_TABLE
                  WHERE state_code = t.state_code AND event_date <= t.some_date
                  AND DATEDIFF(yy,0,event_date) = DATEDIFF(yy,0,some_date)
                  ORDER BY event_date DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 12:59:10

going by same rule 5/10/2010 should take data from 1/1/2009 itself. then why do you think its wrong. or is there some additional rule which you've not told us till now?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-10-04 : 13:45:51

UPDATE t
SET
t.value = l.value
FROM
TARGET_TABLE t
OUTER APPLY (SELECT TOP 1 *
FROM LOOKUP_TABLE
WHERE state_code = t.state_code AND event_date <= t.some_date
~~AND DATEDIFF(yy,0,event_date) = DATEDIFF(yy,0,some_date)~~
ORDER BY event_date DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 13:50:07

quote:
Originally posted by namman

UPDATE t
SET
t.value = l.value
FROM
TARGET_TABLE t
OUTER APPLY (SELECT TOP 1 *
FROM LOOKUP_TABLE
WHERE state_code = t.state_code AND event_date <= t.some_date
~~AND DATEDIFF(yy,0,event_date) = DATEDIFF(yy,0,some_date)~~
ORDER BY event_date DESC

thats same as what OP posted which gave extra value in result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-10-04 : 15:08:23

Your are right, visakh16

LOOKUP_TABLE:
event_date | state | value
1/1/2010 | CO | 1
1/1/2009 | CO | 2
1/1/2008 | CO | 3
1/1/2009 | AZ | 1.3
1/1/2008 | AZ | 2.4

TARGET_TABLE:
some_date | state
2/3/2009 | CO
5/10/2010 | CO
11/1/2008 | CO
2/3/2009 | AZ
5/10/2009 | AZ
11/1/2008 | AZ

DESIRED_RESULT:
some_date | state | value
2/3/2009 | CO ','2
5/10/2010 | CO ','1
11/1/2008 | CO ','3
2/3/2009 | AZ ','1.3
5/10/2010 | AZ ','NULL
11/1/2008 | AZ ','2.4

Why 5/10/2010 ?

malachi151
Posting Yak Master

152 Posts

Posted - 2011-10-04 : 16:05:06

Well I came up with something that, while it isn't exactly what I was looking for, is defiantly faster.


UPDATE t
SET
   t.value = l.value
FROM
   TARGET_TABLE t
     OUTER APPLY (SELECT TOP 1 event_date FROM LOOKUP_TABLE WHERE event_date <= t.some_date ORDER BY event_date DESC) d
     OUTER APPLY (SELECT TOP 1 * 
                  FROM LOOKUP_TABLE
                  WHERE state_code = t.state_code AND event_date = d.event_date
                  ORDER BY event_date DESC)

I'm not sure why this is faster because as near as I can tell tell its basically the same thing as the correlated subquery with a MAX date, but its definitely faster.

malachi151
Posting Yak Master

152 Posts

Posted - 2011-10-04 : 16:07:40

quote:
Originally posted by namman

Your are right, visakh16

LOOKUP_TABLE:
event_date | state | value
1/1/2010 | CO | 1
1/1/2009 | CO | 2
1/1/2008 | CO | 3
1/1/2009 | AZ | 1.3
1/1/2008 | AZ | 2.4

TARGET_TABLE:
some_date | state
2/3/2009 | CO
5/10/2010 | CO
11/1/2008 | CO
2/3/2009 | AZ
5/10/2010 | AZ
11/1/2008 | AZ

DESIRED_RESULT:
some_date | state | value
2/3/2009 | CO ','2
5/10/2010 | CO ','1
11/1/2008 | CO ','3
2/3/2009 | AZ ','1.3
5/10/2010 | AZ ','NULL
11/1/2008 | AZ ','2.4

Why 5/10/2010 ?

Oh, whoops, that was a mistake in the original that I didn't catch. It should have read as above.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-05 : 00:41:56

sorry even now its NULL isnt it? why its not 1.3?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources