Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Correlated Joins Using "Apply" Expand / Collapse
Author
Message
Posted Friday, February 27, 2009 8:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 22, 2011 4:01 PM
Points: 7, Visits: 26
Take it from the top!

Ok. Sorry, I had a minor error in the WHERE Clause. I meant:

SELECT
lvt.ID,
lvt.PhoneNumber,
lvt.CreateDate,
lvc.CallWindowStart,
lvc.CallWindowEnd,
lvc.LVCallDispositionID
FROM
LVTransaction lvt
WHERE lvt.ID in ( --<<<<<<<<<<<<<<< THIS IS WHAT I MEANT
SELECT top 3 lvtTransactionID --<<<<<<<<<<<<<<<<<<< ANOTHER correction ... :)
FROM LVCall
WHERE lvtransactionID = lvt.ID
Order By CreateDate DESC
) as lvc
Post #665813
Posted Friday, February 27, 2009 9:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 07, 2012 6:59 AM
Points: 1,386, Visits: 823
you've almost got it m. unfortunately, once you switch to using the IN condition, you can no longer pull the other data from LVCalls (disposition, call date) because they're still not part of the from statement.
you really do have to use one of the techniques mentioned (apply, row_number, CTE).
Post #665824
Posted Friday, February 27, 2009 9:15 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
Good article.

I've used Cross and Outer Apply a few times very successfully. They work better with sub-queries than with UDFs. Even a single-select UDF has a performance hit of a few milliseconds at least, compared to having the query directly in the calling routine.

For something like "top 3 of each", row_number is generally significantly faster. But for more complex queries, Apply works just fine.

Just make sure the outer query has as few rows as possible, because it is essentially RBAR at that point. Can still be fast, but keep it to only what you actually need.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #665828
Posted Friday, February 27, 2009 9:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 22, 2011 4:01 PM
Points: 7, Visits: 26
Alright, now I'm feeling like a rookie. I didn't notice the table alias' for the lvc on the output list. Sorry for wasting your time!

Post #665831
Posted Friday, February 27, 2009 9:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 27, 2009 8:21 AM
Points: 9, Visits: 14
for what it's worth, I can give you a little simplification. The partitioning query doesn't have to be stated twice:

select acct.[Name], cont.fullname, cont.createdon
FROM [AccountBase] acct
JOIN (SELECT accountid, fullname, createdon
, ROW_NUMBER() OVER (PARTITION BY [AccountId]
ORDER BY createdon Desc) ROW_NUM
FROM [ContactBase]) cont
on cont.[AccountId] = acct.[AccountId]
where cont.ROW_NUM <= 3
order by acct.[Name], cont.fullname

You could have joined lvt directly to the sub query removing the middle level of nesting.

I'm betting this is vastly superior in performance to APPLY.
Post #665843
Posted Friday, February 27, 2009 9:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 27, 2009 8:21 AM
Points: 9, Visits: 14
Peter E. Kierstead (6/16/2008)


I'll index this table appropriately, and wallah!, home-grown full-text index...



This would have been on the bubble of being excusable had the original article not had a big, bold Viola' in it.
Post #665851
Posted Friday, February 27, 2009 11:23 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 04, 2014 4:40 PM
Points: 751, Visits: 917
Alex Ionine (6/16/2008)
it could be a descent article had it been written 2 years ago.


I have to disagree. Given SQL Server 2005's age now, I would not have emphasized the "newness" of the feature personally, but it is a good article at any time.

It highlights a seldom used feature which I suspect many developers do not know of and provides a good explanation and good examples. It then provides an alternate solution to the same problem (also using a technique I suspect many developers are not familiar with) and discusses why one might be preferable over the other. I found it informative personally.

As a side note, you might want to think about refactoring the partitioned solution at the bottom with a CTE. I know it is a matter of opinion, but personally, I find it much easier to read when the subqueries are separated from the main query and it provides a more clear delineation as tot he different parts of the query if someone is skimming over it quickly.


---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #665910
Posted Friday, February 27, 2009 11:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 27, 2009 8:21 AM
Points: 9, Visits: 14
don_goodman (2/27/2009)
If you are going to write a piece of code then you have an obligation to the user. Why? This is a service business. The user experience is the most important part of the service business.

If your code runs slower than another piece of code and both return the same result, you should use the other code. An article explaining the new "feature" runs poorer than the old "feature" may be providing a public service. But, I would really like to see code samples that help me provide the user with a better experience instead of a worse one.


...which does make you wonder if APPLY is intended for some other predicament
Post #665927
Posted Friday, February 27, 2009 11:59 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 6:56 AM
Points: 641, Visits: 1,794
Alex Ionine (6/16/2008)
it could be a descent article had it been written 2 years ago.


or even an ascent article! (no s in decent.)
Post #665928
Posted Friday, February 27, 2009 12:04 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 6:56 AM
Points: 641, Visits: 1,794
mdonnelly (2/27/2009)
Peter E. Kierstead (6/16/2008)


I'll index this table appropriately, and wallah!, home-grown full-text index...



This would have been on the bubble of being excusable had the original article not had a big, bold Viola' in it.


While I'm being pedantic...Viola is a musical instrument while voilà is French - but très amusant! Bravo.
Post #665931
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse