Invoice Number Generation
-
Hi all.
I have a sp that generates Invoice Numbers on records in a table. We truncate the table each time before we use it, so it's a fresh set each time it runs.
Currently, we use a (eerie music) cursor (/eerie music) to loop through each of the records and set the invoice number to (PreviousNumber + 1).
Two problems:
1. It takes forever
2. Even with SET NOCOUNT ON in the sp, it still tells me about EVERY invoice number it generates, thereby loading up my result pane with useless stuff.
My question: I know there must be a better way to load these invoice numbers without going through the (eerie music) cursor (/eerie music). We considered modifying the table in the ap to have the InvNum column be IDENTITY([MinInvNum], 1), but that seems lame. I am open to suggestion. Anyone have a better way?
-
It would be easier if we had an idea of the structure of your tables. Cursors can often be replaced with set based operations but it's difficult to say without seeing some kind of design.
cheers
dbgeezer
-
What you're saying makes absolutely no sense. Is the PKey a Numeric type right now and you're just trying to figure out the next number? Or are you running some maintenance on this Table to update the Primary Keys? It makes no sense to me why you'd ever want to change a Primary key!
If you're just trying to get the next InvoiceNo: Select Max(InvoiceNo) + 1 As NewInvoiceNo From Invoices.
Umm, how about this...
1.) Make your InvoiceNo a Concatenation of [CustNo] + [Date] + [SeqNo]
Ex: CUST01-01012004-01 OR CUST0101012004
If your sure that only one Invoice per customer will be generated in one day, then only use [CustNo] + [Date]
2.) Use a Compound Primary Key which would be using 2 Columns in your Table: [CustNo], [InvoiceDate]. Basically, create these Fields (if they're not there now, bad design
) and then make them both the Primary Key. -
Try this - create a temporary table with an identity on it and a column for the primary key column of your invoices table. Select all the invoices into it. Then update the invoice numbers to the value of the identity column.
Create Table #temp(NewInvoiceNumber Int Identity, PK int)
GO
Insert into #temp(PK) Select ID from Invoice
GO
Update Invoice Set InvoiceInvoiceNumber = NewInvoiceNumber
from #temp inner join Invoice on Invoice.ID = #temp.pk
GO
That should be very fast.
--jim craddock
-
Why you don't want to use identity column to generate your invoice number? It seems to be more efficient to let the system handle the number generation. You can always use
DBCC CHECKIDENT (InvNum, RESEED, 1000)
to reseed your invoice number after you truncate your table.
-
REMEMBER
TO NORMALIZE YOUR DATA (IN TABLES)
-- Without knowing your structure,
-- I see a potential problem which
-- comes from poor design, early on
-- in when the system is running fine
-- under test sets of data (small).
-- Then starts failing once the system
-- is in production.
-- Number one problem is NOT using
-- transactional updates, Secondly a
-- transactional update being used
-- for the whole file (slow), and
-- crashes the system, once the data
-- file is large enough.
-- Update your SP:
-- using an UPDATE CURSOR
-- with
DECLARE @MAX integer,
@CNT integer
SELECT @MAX=2000,
@CNT=0
BEGIN TRANSACTION
-- prior to
OPEN <cursor>
FETCH <cursor>
IF (@CNT >= @MAX)
BEGIN
COMMIT TRANSACTION
BEGIN TRANSACTION
SELECT @CNT=0
END
SELECT @CNT=@CNT+1
END
-- after
COMMIT TRANSACTION
Regards,Coach James
-
Thanks everyone for your input! We are using these suggestions to streamline the code.
-
<snip>REMEMBER TO NORMALIZE YOUR DATA (IN TABLES)</snip>
Normalisation is a structure thing, not a data thing.
The use of cursors is bad practice and they should be avoided wherever possible.
An operation that wraps a series of updates in a single transaction will be faster than a series of updates as the implicit transaction will be replaced.
cheers
dbgeezer
-
/* CODE BETTER, FUNCTION BETTER */
-- Your code should read like it runs,
-- normalize your data or not, and make
-- someone else’s job whole lot bigger!
/* You must have the luxury of working
a relatively small database or working
with enormous memory on hardware???
Take into consideration your maximum
blocking factor when setting up your
blocks of commits. This is calculated
based on total memory in handling
(total records * record-size) per each single commit! Maybe 100,000 rows each
*/
When you overrun your operating system
with a single large commit:
1. FIRST you load-up on memory usage
2. SECONDLY the system writes it to disk
3. THIRDLY your system 'TRASHES'
This means it turns into a dishwasher,
accomplishing nothing but reading & writing
between memory and register functions
(going from disk into memory & back to disk)
But your right, while your operating system
can handle lots of single large commits,
let your predecessors deal with blocking
when it's needed, and everyone says it
ran for months without a problem. They say
Now it's so slow, it even brings down the
system occasionally?
"Ten ways to make good with your boss!"
1. "Work endless hours for very little pay.
2. "Make sure you arrive first and go home last.
3. "Do all the work, no one else wants too.
4. "Never complain, offer suggestions, or talk.
5. "Always complete your work before others.
6. "Always compliment your boss's suggestions.
7. "Always agree with what your boss tells you!
8. "Bring your boss a hot lunch every day.
9. "Meet your boss in the morning with open door.
10. "Have your boss's coat ready when he leaves.
Now for reals, lets impress our boss with years of hard work, good programming technics, readable code.
And offer suggestions which are backed by the many, in saving both his and your career!!
Regards,Coach James
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply