473,326 Members | 2,126 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Two Pass Update - best way?

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
Sep 29 '06 #1
1 1292
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

"Hexman" <He****@binary.comschreef in bericht
news:fc********************************@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

Sep 30 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: DFS | last post by:
Architecture: Access 2003 client, Oracle 9i repository, no Access security in place, ODBC linked tables. 100 or so users, in 3 or 4 groups (Oracle roles actually): Admins, Updaters and ReadOnly....
0
by: Lewe22 | last post by:
I am creating a small Access db which performs a series of updates to an SQL database. The db consists of a ‘Main Form’, from which the user can run each update via a series of command buttons. ...
2
by: Lewe22 | last post by:
I am creating a small Access db which performs a series of updates to a SQL db. The Access db consists of a ‘Main Form’, from which the user can run each update via a series of command buttons. ...
22
by: Lewe22 | last post by:
I am creating a small Access db which performs a series of updates to a SQL database. The Access db consists of a ‘Main Form’, from which the user can run each update via a series of command...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.