List of all numbers between low and high

Question

List of all numbers between low and high

Viewing 6 posts - 16 through 20 (of 20 total)

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

texpic SSCertifiable Points: 5887 More actions · Answer 1

This was an odd project, since it was a one-time task sql processing performance was not an issue. I should have mentioned that.

I had to add a new reference table. I was being provided 50,000 records with ranges. Although the numbers were large (11 digits) I was able to use the left 6. The luck was there were no more than 99 combinations. I applied the formula to the ref table, took the result and verified I had no overlap.

Thanks again.

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

texpic (8/9/2012)
This was an odd project, since it was a one-time task sql processing performance was not an issue. I should have mentioned that.

It's not a problem. One-time tasks are my favorite place to learn new things about performance because there's no real pressure. As would anyone else, I first make it work so that when someone is ready for the code, I have it. Then, if I'm not happy with the performance, I can play with it at my leisure to figure out how to make it fast so that if I ever need to do such a thing in production code, I can just pull it from my library.

--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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

dwain.c (8/8/2012)
Are you suggesting something else?
</eager-to-learn-more>

Yes and I apologize for the rather lame question. Looking back at it, if someone had asked me the same question, I would have considered it a wee bit too abstract for what I was trying to get at.

What I was trying to get at is the potential for a major performance problem if you were ever need to do such a thing in production. Try the following code with the actual execution plan turned on. It's your code with slightly different data for #TempBet.

DROP TABLE #TempBet

GO

CREATE TABLE #TempBet

(High int, Low int)

INSERT INTO #TempBet (Low, High)

SELECT 1000001, 1000003 UNION ALL

SELECT 1, 13 UNION All

SELECT 5, 8

DECLARE @MaxBet INT

SELECT @MaxBet = MAX(High) FROM #TempBet

;WITH Tally (n) AS (

SELECT TOP (@MaxBet) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns t1 CROSS JOIN sys.all_columns t2)

SELECT High, Low, Bet=n

FROM #TempBet

CROSS APPLY (

SELECT n

FROM Tally

WHERE n BETWEEN Low AND High) a

The hint here is that if you're not using the number "1" from the Tally CTE for every row processed, then your Tally CTE is probably returning too much. In this case, the max size of the Tally Table should be based on High-Low+1. Of course, you need to make another mod to return the correct numbers but I'll let you figure that simple thing out. Then, you can get rid of the other potential performance problem. The Tally Table doesn't like constraints for both the Low and High value whether you use BETWEEN or the >=/<= method which will frequently cause a scan of the Tally Table instead of doing just a SEEK/Range Scan. It's odd that the optimizer would make such a "mistake", but it does. This is one of the reasons why some folks claim that certain Tally Table solutions are a performance problem and that a While Loop is actually quicker. While they've used it correctly logically speaking, the optimizer frequently has other ideas about it. Of course, a simple tweak like the one I'm suggesting will fix the performance problem but you have to recognize what the problem is.

--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 4

Jeff Moden (8/9/2012)
dwain.c (8/8/2012)
Are you suggesting something else?
</eager-to-learn-more>
Yes and I apologize for the rather lame question. Looking back at it, if someone had asked me the same question, I would have considered it a wee bit too abstract for what I was trying to get at.

No apologies are necessary! I considered the question enigmatic rather than lame.

And it got me to thinking about it. I came to the same conclusion that you offered above. Had I thought it through fully, it was blindingly obvious, especially considering one of the first tests I ran on my solution without the TOP clause.

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: 1004406 More actions · Answer 5

dwain.c (8/9/2012)
Jeff Moden (8/9/2012)
dwain.c (8/8/2012)
Are you suggesting something else?
</eager-to-learn-more>
Yes and I apologize for the rather lame question. Looking back at it, if someone had asked me the same question, I would have considered it a wee bit too abstract for what I was trying to get at.
No apologies are necessary! I considered the question enigmatic rather than lame.
And it got me to thinking about it. I came to the same conclusion that you offered above. Had I thought it through fully, it was blindingly obvious, especially considering one of the first tests I ran on my solution without the TOP clause.

I also figured that you liked a challenge and was trying not to give it away, although I see it wasn't much of a challenge for you at all1 🙂

--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 6

Jeff Moden (8/9/2012)
dwain.c (8/9/2012)
Jeff Moden (8/9/2012)
dwain.c (8/8/2012)
Are you suggesting something else?
</eager-to-learn-more>
Yes and I apologize for the rather lame question. Looking back at it, if someone had asked me the same question, I would have considered it a wee bit too abstract for what I was trying to get at.
No apologies are necessary! I considered the question enigmatic rather than lame.
And it got me to thinking about it. I came to the same conclusion that you offered above. Had I thought it through fully, it was blindingly obvious, especially considering one of the first tests I ran on my solution without the TOP clause.
I also figured that you liked a challenge and was trying not to give it away, although I see it wasn't much of a challenge for you at all1 🙂

Hehe. You do know me too well.

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