Re: Cursor problem for teired pricing
- From: roger_poulin@xxxxxxxxxxx
- Date: Tue, 11 Sep 2007 07:30:37 -0700
Erland thanks for pointing out the issue with @Trans - (@TransCount -
@VolumeThreshold), that was incorrect. During regular processsing
(Else portion) I should not update the trans field.
After considering the advice received and further analysis of my
scheme I think I am going with a hybrid solution. Let me know what
you think.
1) start a transaction to lock the table current_month_loc_nine
against inserts and updates while processing
2) create a new table (Loc9) with all of the fields as the original
and a Loc9ID column that is an identity, primary key, clustered index.
(I may make this a temp table depending on server memory)
3) copy current_month_loc_nine to Loc9 allowing it to assign Loc9ID to
each row. (This gives we a predictable order and a identity column for
updates) (all 2.8 million records)
4) I will use the single command method from the SQLMag article to
create a temp table (#TEMP) with just the id and accumulated volume
for that company only. (170 k records)
5) Do an update query that joins Loc9 to #TEMP to set loc9.Rate to the
low volume rate where volume <= @VolumeThreshold)
6) Do an update query that joins Loc9 to #TEMP to set loc9.Rate to the
high volume rate where volume > @VolumeThreshold)
7) if needed split the threshold record into 2 (one with the low
volume rate and insert the remaining recordsin a new record with the
high volume rate).
8) Truncate the original current_month_loc_nine table, then copy the
Loc9 (w/o Loc9ID) into the now empty table.
9) Drop my Loc9 and #TEMP tables
10) Commit transaction
.
- Prev by Date: Re: Error reading recordset from DB2/AS400
- Next by Date: Re: Error reading recordset from DB2/AS400
- Previous by thread: Select and update
- Next by thread: Class Not registered error
- Index(es):
Relevant Pages
|
|
