Two Pass Update - best way?

29 Sep 2006 5:52 PM

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

30 Sep 2006 5:10 AM

Cor Ligthert [MVP]

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

Show quoteHide quote

"Hexman" <Hex***@binary.com> schreef in bericht
news:fclqh2p3roonepjfiu868tqbn5lannk19p@4ax.com...
> 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