Availability calculation - a nice T-SQL problem

Question

Availability calculation - a nice T-SQL problem

Viewing 11 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Dwain Camps SSC Guru Points: 86978 More actions · Answer 1

Jeff,

First of all, thanks for not laughing. 🙂

Clearly you've made a wonderful improvement to the speed. And I do recognize the trick (CROSS APPLYs right?). I've been meaning to add the WITH SCHEMABINDING but just never got around to it.

I do have a question that you may be able to answer. Will this expand to inline code on usage? I'm curious and unsure because of the tally CTE.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 2

Not just the CROSS APPLYs but the fact that the 2nd CA refers to the 1st. Until ChrisM showed me, I haven't even tried that wonderful trick. Instead, I used "Cascading CTEs" which don't always work in the manner I'd like them to.

The function I wrote is an "iTVF" and behaves like one on usage.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86978 More actions · Answer 3

Jeff Moden (6/28/2012)
Not just the CROSS APPLYs but the fact that the 2nd CA refers to the 1st. Until ChrisM showed me, I haven't even tried that wonderful trick. Instead, I used "Cascading CTEs" which don't always work in the manner I'd like them to.
The function I wrote is an "iTVF" and behaves like one on usage.

iTVF was the term I was looking for when I posted but it didn't come to mind, probably because of my preoccupation at the moment with Les Cardwell's Sweden redistricting problem.

I like "cascading CTEs," which I've been calling "stacked CTEs" - perhaps I need to rework my terminology!

I learned the "cascading CROSS APPLYs" from ColdCoffee (didn't know to refer to it like that at the time) probably 2 months ago. I saw where you suggested Chris write an article on it. I agree it is not only pretty cool but pretty darn nice for code readability. I did run a speed test on it though (vs. a couple of alternatives) and in the case I applied it to, it did not win the race.

Next I'm guessing you'll want me to post that. 😛

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 4

dwain.c (6/28/2012)
Next I'm guessing you'll want me to post that. 😛

Heh... "It Depends.":-P

What I'm really waiting for next if for Phil to answer the qauestion as to whether or not we can either change the date column to a DATETIME or add a persisted computed column instead because I think we can smoke even 16 milliseconds if we can. At least it'll be fun trying.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 5

dwain.c (6/28/2012)
Jeff Moden (6/28/2012)
dwain.c (6/27/2012)
I like this problem and wanted to contribute.
Let other people in on the fun. 🙂 What does dbo.GenerateCalendar() look like?
Please don't laugh! ...

1) Keep min mind, the things you laughed about, have the longest retention period in your brain.

You may forget the details, but you'll always remember it's been a good laugh 😀

2) If you allow us to guess about your solution, who knows, we may even choke from laughter 😉

3) Knowing that Jeff takes a look at it, you know you'll end up with a better solution or valid alternatives for your approach.

3 of the things why I like SSC :w00t:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Phil Parkin (6/28/2012)
...ChrisM - think you've got some tuning to do 🙂
...

It's awful isn't it! I don't think it can be improved either.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

dwain.c (6/28/2012)
Jeff Moden (6/28/2012)
Not just the CROSS APPLYs but the fact that the 2nd CA refers to the 1st. Until ChrisM showed me, I haven't even tried that wonderful trick. Instead, I used "Cascading CTEs" which don't always work in the manner I'd like them to.
The function I wrote is an "iTVF" and behaves like one on usage.
iTVF was the term I was looking for when I posted but it didn't come to mind, probably because of my preoccupation at the moment with Les Cardwell's Sweden redistricting problem.
I like "cascading CTEs," which I've been calling "stacked CTEs" - perhaps I need to rework my terminology!
I learned the "cascading CROSS APPLYs" from ColdCoffee (didn't know to refer to it like that at the time) probably 2 months ago. I saw where you suggested Chris write an article on it. I agree it is not only pretty cool but pretty darn nice for code readability. I did run a speed test on it though (vs. a couple of alternatives) and in the case I applied it to, it did not win the race.
Next I'm guessing you'll want me to post that. 😛

It scales almost arithmetically on speed tests - each new cCA block adds about 30% to the execution time. In practical terms this equates to extracting at best twoor three separate words from a string, compared to using Jeff's split() function. I did some tests on it at about the same time that folks were performing the comparative speed tests for the Tally Oh! article, prompted by the eagle-eyed Wayne S. Can't find that post though.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Phil Parkin SSC Guru Points: 247180 More actions · Answer 8

Jeff Moden (6/28/2012)
dwain.c (6/28/2012)
Next I'm guessing you'll want me to post that. 😛
Heh... "It Depends.":-P
What I'm really waiting for next if for Phil to answer the qauestion as to whether or not we can either change the date column to a DATETIME or add a persisted computed column instead because I think we can smoke even 16 milliseconds if we can. At least it'll be fun trying.

Thanks Jeff

We can't change the date column, but we could add a persisted computed column - it's our DW, so we have plenty of control.

Glad this thread has generated so much interest - I thought it might.

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 9

I haven't been able to beat Adi's code for performance. He did a really good job on it even with the integer dates. The only thing he forgot is that there can be more than one HotelID. I've modified his code to handle that.

;with MyCTE as (

select HotelId, RoomTypeId, cast (DateKey as char(8)) as DateKey, FreeCount,

row_number() over (partition by [font="Arial Black"]HotelID[/font], RoomTypeId, case when FreeCount = 0 then 0 else 1 end order by DateKey) as Num

from @Booking)

select HotelId,RoomTypeId ,DateKey ,FreeCount,

case when FreeCount = 0 then 0 else row_number() over (partition by [font="Arial Black"]HotelID[/font], RoomTypeId, datediff(dd,'20120101',DateKey) - Num order by DateKey desc) end as Booking from MyCTE

order by HotelId, RoomTypeId, DateKey

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86978 More actions · Answer 10

ALZDBA (6/29/2012)
dwain.c (6/28/2012)
Jeff Moden (6/28/2012)
dwain.c (6/27/2012)
I like this problem and wanted to contribute.
Let other people in on the fun. 🙂 What does dbo.GenerateCalendar() look like?
Please don't laugh! ...
1) Keep min mind, the things you laughed about, have the longest retention period in your brain.
You may forget the details, but you'll always remember it's been a good laugh 😀
2) If you allow us to guess about your solution, who knows, we may even choke from laughter 😉
3) Knowing that Jeff takes a look at it, you know you'll end up with a better solution or valid alternatives for your approach.
3 of the things why I like SSC :w00t:

Let's not forget:

4) He who laughs last laughs best.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 11

dwain.c (7/4/2012)
ALZDBA (6/29/2012)
dwain.c (6/28/2012)
Jeff Moden (6/28/2012)
dwain.c (6/27/2012)
I like this problem and wanted to contribute.
Let other people in on the fun. 🙂 What does dbo.GenerateCalendar() look like?
Please don't laugh! ...
1) Keep min mind, the things you laughed about, have the longest retention period in your brain.
You may forget the details, but you'll always remember it's been a good laugh 😀
2) If you allow us to guess about your solution, who knows, we may even choke from laughter 😉
3) Knowing that Jeff takes a look at it, you know you'll end up with a better solution or valid alternatives for your approach.
3 of the things why I like SSC :w00t:
Let's not forget:
4) He who laughs last laughs best.

or is slow in interpreting it all :hehe:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me