bad poetry - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

bad poetry

Author

Topic

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-11-24 : 00:30:27

I have a table of purchase orders. The next purchase order must be the lowest unused purchase order number,

eg
PurchaseOrders
PON001
PON002
...
PON034
PON036
PON037

The next number should be PON035.

Once PON035 is filled, the next number assigned should be PON038. I've written an sp which returns the next available number, but at the moment I'm using a while loop to check for any "gaps".

But this is too slow!

Got a better idea? I'd love to hear it...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-11-24 : 00:43:54

Oh rob, what a mess!! numbers and characters mix as well as...uh...milk and martini (I know Brett is going to kill me for this

)

Do you have a tally table (ya know... numbers 1-99999, always a useful thing to have around)? Try this off the top of my head:

SELECT TOP 1 t.Number FROM
TallyTable t LEFT JOIN PurchaseOrders p
ON t.Number = CAST(SUBSTRING(p.PONumber, 4, 3) AS SMALLINT)
WHERE p.PONumber IS NULL
ORDER BY t.Number

Owais

Where there's a will, I want to be in it.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-11-24 : 00:52:48

Got a Numbers table RRB?

CAST the Purchase order number component into an INT, perform an existence (NOT EXISTS) check against your numbers table, find minimum..

IF minimum less than maximum use minimum else MAX + 1...

DavidM

"SQL-3 is an abomination.."

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-11-24 : 01:11:14

Ta guys - no I was suspecting that the join on the Numbers table would also be slow, but I'll give it a go and see what happens

Thanks guys you're all beautiful.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-24 : 01:11:36

Slight tweak - a versitle Tally Table usually has more than 1000 numbers.


SELECT TOP 1 t.Number
  FROM TallyTable t 
  LEFT JOIN PurchaseOrders p ON t.Number = CAST(SUBSTRING(p.PONumber, 4, 3) AS SMALLINT)
  WHERE p.PONumber IS NULL
  AND t.Number BETWEEN 1 AND 999
  ORDER BY t.Number

This one reads better (to me) but it probably has the same execution plan


SELECT MIN(Number)
  FROM dbo.Tally T
  WHERE Number BETWEEN 1 AND 999
  AND Number NOT IN (SELECT CAST(RIGHT(p.PONumber, 3) AS INT) FROM dbo.PurchaseOrders)

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-11-24 : 01:14:17

Ta muchly all

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-24 : 01:26:59

Where's the poetry in all this

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-11-24 : 01:32:32

There once was a young SQL yak
Who's Sequel was no more than hack
Testing a theory
He posted a query
"If it mentions poetry, they'll post back"

and it worked

Ta

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-11-24 : 01:49:37

And the bad news is that a join with the Tally table (of any large enough size) takes almost the same time to execute...

bummer

Thanks anyway guys...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-24 : 02:05:55

The index! Forget not!
joins labor merciless without
resolving ever so slowly
and curse the cursor !
Tis nobler to execute set wise
Than like a idle village idiot loop !

Poetry not!

No way a cursor could perform as well.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-11-24 : 02:24:20

lol...so this is what a gathering of poetic geeks looks like! Rob, I suspect the CAST is slowing your execution plan a bit too much, that's why I am always wary of prefixes, suffixes and the like. Btw, what do you do when you hit 999 Purchase Orders? I was under the impression that you will never have more than 999 rows and hence, performance shouldn't really be a problem, but it seems there is more to the story...?

Owais

Where there's a will, I want to be in it.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-11-24 : 02:26:40

Yeah, the number extends to more digits... - the reason its a dog is because it's likely to go over 100000 and joining on that numbers table is large, and I'm worried about designing in my own "Y2K" equivalent.

I'm going to play around for a while and see what I might be able to achieve with some carefully positioned indexes, table variables and tweaking.

Thanks for your help, I really appreciate it.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-11-24 : 05:02:33

You could investigate why you have gaps in the sequence, and take steps to prevent them.
You could also point out the performance cost of keeping the sequence continuous - maybe the business sponsor will trade a faster application for the gaps.
You could also 'remove' numbers from the tally table, once you are guaranteed that that number is already used as a PO. This might look like shaving fractions off the size of your table, but as the PO's grow, more and more of the tally numbers will be redundant.
You could also NOT put in too many records at the upper end of the tally table....i.e. once a month add x records into tha tally table, where x is a predicted growth factor for the PO numbers....In effect there's no benefit in having a CURRENT tally table running upto 100000 when the current PO numbers only go to 100.

mr_mist
Grunnio

1870 Posts

Posted - 2003-11-24 : 05:27:37

I think (though it's likely a road to nowhere) that I would definitely look into the business need to be filling gaps in the numbers. Firstly because I don't see how there can be gaps and secondly because, if there are gaps, surely this is where a PO has been created and later deleted for some reason. If that's the case, surely overwriting that number with a valid PO is a bad plan?

Would this be quicker if you split numbers and letters?

-------
Moo. :)

SamC
White Water Yakist

3467 Posts

Posted - 2003-11-24 : 09:49:40

Could you add a new column to the table with a DEFAULT value derived from the other column? If Default doesn't work, maybe a trigger could fill in the column?

With a numeric order number, the query oughta run like a greased pig at a barbecue.

Sam

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-11-24 : 17:09:33

Reason for the gaps is simple. We are required to maintain auditability (that's human auditors I'm talking about) of every Purchase Order (PO) generated. Unfortunately, some bright spark decided that it is a requirement that a PO number be generated, but if the PO is cancelled without saving, then the PO number is released. In this way, all PO numbers are eventually used, meaning that every PO can be accounted for in numeric order. POs can never be deleted for audit reasons.

And before you all say "they should only have the PO number generated after they save the PO", I know, I know! But that's the requirement...

I like the Irish suggestion of removing tally numbers as I go...might try that but for now, clever indexing looks the easiest solution.

Thanks for all suggestions.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-24 : 19:09:56

How does this perform:


declare @Pos table (PO varchar(10))

insert into @pos
select 'PON031' union
select 'PON032' union
select 'PON033' union
select 'PON034' union
select 'PON037'

SELECT
  Min(NewPO)
FROM
  (SELECT left(PO,3) + right(convert(varchar, 1001+ convert(int, right(PO,3))),3) as NewPO
  FROM @Pos) a
LEFT OUTER JOIN
  @Pos b
ON
  a.NewPO = b.PO 
WHERE 
  b.PO is null

Not sure if it's any better .... but it should do the job nicely and i think actually it works quite well ! Or you could try NOT EXITS() instead of the LEFT OUTER JOIN.

- Jeff

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-11-24 : 19:14:39

Hi Jeff

not sure you're including the time to build @pos with 100000 odd purchase orders - have you got a suggestion about how I'd do that?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-24 : 19:19:44

NO !! ... that's only a sample table ... replace the table variable with your actual table of existing purchase orders!

I just provided the table and data as a proof of concept and to help me write/test it.

- Jeff

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-11-24 : 19:22:15

oooooooohhhhh!

Sorry, of course. Woops

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-11-25 : 06:33:13

as stated before re "Unfortunately, some bright spark decided that it is a requirement that a PO number be generated, but if the PO is cancelled without saving, then the PO number is released"
maybe this "Bright Spark" could be shown the 'performance' cost of the decision and get the decision changed.

Alternatively....and maybe this is the best way to go from a system usability/auditability frame....why not ....mark a cancelled PO as a CANCELLED PO....and leave it in the system and stop the gaps!

Say, if I was selling stuff over the web, I would be very interested in the number of incomplete orders....because it would point to problems my potential customers are having buying from my site - be that technical, timing, cost or usability. By re-using CANCELLED PO's and not keeping a history of them, I lose the benefit of learning from my customers behaviours. Does the same not apply here? Over time, the % of CANCELLED PO's should drop as the usability of the system improves and/or as the users become educated.

Alternatively, why not ONLY generate a PO when the order is accepted. While the order is being constructed, you could use a 'dummy' number which would be converted into a 'real' number when accepted.

It's like providing a quote to a customer....are you interested in seeing how many quotes are issued and not followed through?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources