Listing gaps in numeric sequences

Question

Post reply

Listing gaps in numeric sequences

Viewing 3 posts - 16 through 17 (of 17 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

We'll I'll be... just tried the CTE equivelent of how I build Tally tables (NO, NO RECURSION PLEASE! 😛 ) on the same example data... it does surprisingly well! Still not as fast as a full blown Tally table, but it comes close! 8-10 CPU seconds instead of the 5-6 for the Tally table. Advantage here is that you don't very often need a 6 million row Tally table.

Here's the code I ran against the previous test table...

[font="Courier New"]SET&nbspSTATISTICS&nbspIO&nbspON

SET&nbspSTATISTICS&nbspTIME&nbspON

;WITH&nbspcTally&nbspAS

(SELECT&nbspTOP&nbsp6000000

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspN&nbsp=&nbspROW_NUMBER()&nbspOVER&nbsp(ORDER&nbspBY&nbspsc1.OBJECT_ID)

&nbsp&nbsp&nbspFROM&nbspMaster.sys.ALL_Columns&nbspsc1

&nbsp&nbspCROSS&nbspJOIN&nbspMaster.sys.ALL_Columns&nbspsc2)

&nbspSELECT&nbspN&nbsp

&nbsp&nbsp&nbspFROM&nbspcTally&nbspt

&nbsp&nbsp&nbspLEFT&nbspOUTER&nbspJOIN&nbspyourtable&nbspy

&nbsp&nbsp&nbsp&nbsp&nbspON&nbspt.N&nbsp=&nbspy.SerialNumber

&nbsp&nbspWHERE&nbspy.SerialNumber&nbspIS&nbspNULL&nbsp

SET&nbspSTATISTICS&nbspIO&nbspOFF

SET&nbspSTATISTICS&nbspTIME&nbspOFF

[/font]

... and here's the results...

Table 'syscolrdb'. Scan count 2, logical reads 153, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'syscolpars'. Scan count 2, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'yourtable'. Scan count 1, logical reads 8863, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 8640 ms, elapsed time = 51484 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

--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)
Intro to Tally Tables and Functions

jschroeder Right there with Babe Points: 727 More actions · Answer 2

Thank you all for your help. I see that my question developed a life of it's own. 🙂

Much to understand in all that, besides the solution to the stated problem. I learn so much every time I post!

To update you, after discussions with users, the concept of a master serial number table with item dispositions was seen as a benefit, so I will have a full number sequence to compare against. Now, I have to design a business process where serial numbers are generated, logged, and dispositions are recorded.

As a tangent to the current discussion, how many DBA's end up having to change business practices in order to implement the solutions they have been tasked with digitally? My mantra around here is "tools are not solutions" (they are starting to get it) and even though I can design and implement software 'tools', I have to design the 'solution' first. The 'solution' process is not just to better define the scope of the tool (a definite benefit), but to solve the problem regardless of the tool, and therefore, not focused on the technology itself. How many database professionals go through this? Is this a common cycle for DBA's?

GSquared SSC Guru Points: 260824 More actions · Answer 3

I've never had to change a business process to match the system. I've had business processes that I couldn't enforce with the system, because they weren't things a computer could check (or, earlier in my career, because I simply didn't know how to code them), but I've just left those in the realm of human judgement and policy.

Yes, absolutely yes, you need to know what you're solving and why and how, before you begin to build a system to solve it. I've had plenty of requests for changes to systems which, if implemented, would have had serious negative consequences to the business. I've had solutions proposed that didn't have a problem, or where the problem wasn't clearly understood. I've even had a manager tell me that, "we're not sure what's wrong, but we're assigning the solution to you, since you're good at that kind of thing" (yes, for those who are curious, he was the Vice President of Sales and Marketing).

This doesn't mean spend six years trying to figure out what's going on and how to build a perfect solution to it. It means at least have a good idea of what you're doing and why before you start. Simple as that.

If, on the other hand, your job is to implement pieces of code per specification, with no input on what gets done or why, then this rule doesn't apply.

- 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