Two Pass Update - best way?

  • Thread starter Thread starter Hexman
  • Start date Start date
H

Hexman

Hello Again,

I have a table that contains data that needs to be ranked. There are actually 2 columns that need to be ranked. I have already completed the task
but did it in a "Brute Force" way. I created a datatable did a select (got 72,000 recs) and order by. Looped thru the dt updating each record with
the ranking value and accepting changes. I copied that program and used the new criteria for selection (got 33,000 recs) and order by went thru the
same process again. It worked....BUT..... I know there's a better way (more efficient) - I just don't know what it is. The "Brute Force" way updates
95,000 records. It seems if I could order by the selected datatable from the first pass I could get away with only 72,000 updates.

Basically I need to change the order by on the same datatable as the first pass. When I thought of how to issue a plain "order by" I thought I would
have to do a new select against the datasource which would eliminate the updates in the first pass unless I did an update and accept changes.

What's a better way to accomplish this?

Pseudo Code Below.

Thanks,

Hexman


=========================================================
SELECT * from AnalysisTbl
WHERE ANDate > '07/31/06' and ANDate < '09/01/06'
ORDER BY ANProdLn, ANStockLvl

Loop thru datatable
If change in ANProdLn reset RANK to 1
put RANK in row
update datatable
increment RANK
accept changes
go to loop again

================Second Pass ==============================
SELECT * from AnalysisTbl
WHERE ANDate > '07/31/06' and ANDate < '09/01/06'
ORDER BY ANCustomer, ANItem, ANSoldQty

Loop thru datatable
If change in ANCustomer or ANItem reset RANK to 1
put RANK in row
update datatable
increment RANK
accept changes
go to loop again
 
Hexman,

Be aware that if you are using loops while there is a class method which
does something, that class method is most probably doing the looping behind
the scene. Using your own loops will not affect the performance much. One
move of the window on the screen cost normally much more time than any in
memory process. It can even be that your own loop will be faster than a
given method, because those methods sometimes have overhead.

The advance from using a class method, however, is that your code will
probably be more compact and therefore better readable because it is as well
uniform.

Just my idea reading your message.

Cor
 
Back
Top