February 27, 2009 at 8:58 am
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
February 27, 2009 at 9:12 am
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). 🙁
February 27, 2009 at 9:15 am
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
February 27, 2009 at 9:21 am
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!
February 27, 2009 at 9:37 am
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.
February 27, 2009 at 9:44 am
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.
February 27, 2009 at 11:23 am
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/
February 27, 2009 at 11:58 am
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
February 27, 2009 at 11:59 am
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.)
February 27, 2009 at 12:04 pm
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.
February 27, 2009 at 12:13 pm
David McKinney (2/27/2009)
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.
Recognizing my own short comings, this is exactly why I would have ignored it had it read "vallah".
February 27, 2009 at 1:08 pm
mdonnelly (2/27/2009)
David McKinney (2/27/2009)
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.
Recognizing my own short comings, this is exactly why I would have ignored it had it read "vallah".
Yeah, thanks David. I didn't think I saw any stringed instruments in the original article!
Ooo eee, ooo ah ah
ting tang
voilà voilà, bing bang
Nice article, and great discussion. I must admit, I've been so buried in other work, I never had time to explore how powerful ROW_NUMBER() is. Might not have even paid attention to it 2 years ago.
And thanks McD for the example with the middle nesting level removed. The original looked more complicated than it had to be.
February 28, 2009 at 2:37 pm
This article is good, finally i solved my problem today with this solution.
Thank you
February 28, 2009 at 4:05 pm
Great article Gregory!
The one thing that really jumps out to me is the fact that you moved away from using UDFs as most of the articles out there explain APPLY statements using UDFs.
If only I had come across this article earlier, I wouldn't have taken so long to wrap my head around the real difference between the logical JOINs (OUTER and INNER) and the APPLY statements.
It is unfortunate however that a lot of people do not know about the APPLY functionality in SQL 2005 or are simply unaware that such a thing (or ROW_NUMBER) exists and that these would eliminate a lot of hair pulling and teeth gritting!
March 2, 2009 at 11:14 am
Thanks Everyone,
I've been in Vegas the past few days for the NASCAR so I'm a little late in replying.
Thanks for the comments and discussion.
Greg J
Gregory A Jackson MBA, CSM
Viewing 15 posts - 31 through 45 (of 49 total)
You must be logged in to reply to this topic. Login to reply