Can anyone think of a way to speed this up?

[Chucklez]

This is the situation. I have created a routine to count the number of detail records in a file (AMARTRS & AMARTRSH), place what record number it is (1,2,3, etc.) in the same files, and give a sum count in the header file(AMARREC & AMARRECH). The relationship between the two files is finvno.
When I hit the findfirst part of the routine, the program really bogs down and takes 20 minutes to run when there is 900 records in the master file. I am afraid to time how long it will take with live data that numbers in the tens of thousands.

Here is the code:

Set rstARREC = dbs.OpenRecordset("SELECT * FROM amARREC ORDER BY finvno&quot
Set rstARRECH = dbs.OpenRecordset("SELECT * FROM amARRECH ORDER BY finvno&quot
Set rstARTRS = dbs.OpenRecordset("SELECT * FROM amARTRS ORDER BY finvno&quot
Set rstARTRSH = dbs.OpenRecordset("SELECT * FROM amARTRSH ORDER BY finvno&quot

'****Begin adding record counts
lblMsg.Caption = "Matching the Header records with the Detail records"
rstARREC.MoveFirst
rstARTRSH.MoveFirst
Do While rstARREC.EOF <> True
intRecCounter = 0
'this is the line that really slows down the program
rstARTRSH.FindFirst (&quot;finvno = '&quot; + rstARREC!finvno + &quot;'&quot
Do While rstARTRSH.EOF <> True
If rstARTRSH!finvno <> rstARREC!finvno Then
Exit Do
End If
intRecCounter = intRecCounter + 1
'updating artrs with the total number of lines
rstARTRSH.Edit
rstARTRSH!fnumlines = intRecCounter
rstARTRSH.Update
rstARTRSH.MoveNext
Loop
'updating arrec with the total number of lines
rstARREC.Edit
rstARREC!fnumlines = intRecCounter
rstARREC.Update
rstARREC.MoveNext
rstARTRSH.MoveFirst
Loop


Can anyone come up with any ideas on how to speed this up? I thought of creating a primary key on finvno in the detail file, but there are duplicate records, and no other records in the file are unique. My only other thought was to create a new auto-number field in the detail file, and populate it, thereby giving me my second unique field (finvno + autonumber). The only problem with this is would it speed it up enough to justify spending the extra time?

ANy question or suggestions, please reply.
Thanks for looking,
-Chuck

 

[WayneRyan]

Chuck,

Looking at your code I'm confused ...

Sequentially read the ARREC table
For each record find the ARTRSH entries
Update each ARTRS entry with the record #
Update the ARREC entry with the record #


Questions:

The code seems to expect multiple header
records for each ARREC. The header information should
be summary, no?

If you have duplicate &quot;finvno&quot; entries
then the record #s are irrevelant as
their order within &quot;finvno&quot; is random each
time you select them.

I think that maybe your header tables
can be handled by SQL:

Select unique(finvno), count(*)
from ARREC ...

Need more info.

Good luck,
Wayne

 

[Chucklez]

Ill try and clarify.

ARREC is the header file for invoices. Here is a sample of what the file look like.
Finvno Fcustno Fsalespn Fsubtot Fnumlines
1125 Philch CWP 126.78
1126 Smitad DRS 26.84
1127 Mattco DRS 1746.98
1128 Palone MDL 1.58
....
....
....
etc.

ARTRS is the detail file for each invoice. it displays the items that were listed in each invoice.
Finvno Fcustno Fitemno Fprice Fnumlines
1125 Philch 112-awo 26.84
1125 Philch 53-ap-g 5.42
1125 Philch VertBox 9.58
1125 Philch 7432-hi 84.94
1126 Smitad 112-awo 26.84
1127 Matco P4CPU 699.95
1127 Matco MicMous 19.95
1127 Matco 17inLCD 269.99
...
...
...
etc.

What I want to do Is add the detail line number to the ARTRS file, and the total number of detail records PER INVOICE to the ARREC File. Like this:

ARREC
Finvno Fcustno Fsalespn Fsubtot Fnumlines
1125 Philch CWP 126.78 4
1126 Smitad DRS 26.84 1
1127 Mattco DRS 1746.98 3
1128 Palone MDL 1.58

ARTRS
Finvno Fcustno Fitemno Fprice Fnumlines
1125 Philch 112-awo 26.84 1
1125 Philch 53-ap-g 5.42 2
1125 Philch VertBox 9.58 3
1125 Philch 7432-hi 84.94 4
1126 Smitad 112-awo 26.84 1
1127 Matco P4CPU 699.95 1
1127 Matco MicMous 19.95 2
1127 Matco 17inLCD 269.99 3
etc.

I Hope this helps clear up the misunderstanding.
After the above peice of code runs, a very similar peice will execute, except ARREC will turn into ARRECH (History Header file), Then ARREC (Current Header File) will be ran with ARTRS(Current Transaction File). By doing this I will find and appropriatly number all invoices in the header file and detail files, wether past or present.
I can almost see where you are heading to with the SQL statement. Can I have ya feed another small bit to me to jog my memory?

Thanks for the help
-Chuck

 

[WayneRyan]

Chuck,

Table ARREC:

Finvno Primary key (unique)
Fcustno
Fsalespn
Fsubtot *** Not needed ***
FDate Transaction date
Fnumlines *** Not needed ***

Table ARTRS:

Finvno Indexed (duplicates OK)
Fcustno *** Not needed ***
Fitemno
Fprice
Fnumlines *** Not needed ***
Flineitem *** see note at end ***

As I see it; let's take a report for
example:


Finvno Fcustno Fsalespn Fitemno Fprice
====== ======= ======== ======= =======
1125 Philch CWP 112-awo 26.84
53-ap-g 5.42
VertBox 9.58
7432-hi 84.94
=======
Total: $126.78

1126 Smitad DRS 112-awo 26.84
=======
Total: $26.84

All of the data is there in the above tables
without the use of &quot;record numbers&quot;. The
query for this report would be:

Select ARREC.Finvno,
ARREC.Fcustno,
ARREC.Fsalespn,
ARTRS.Fitemno,
ARTRS.Fprice
From ARREC, ARTRS
Where ARREC.Finvno = ARTRS.Finvno
Order by ARREC.Finvno;

The Access report writer will do the totalling.
The operation of the form is a similar thing.

I would add the FDate (transaction date) to
avoid having to deal with a &quot;history&quot; table.
This would provide data for annual, quarterly,
etc. reports.

The only &quot;record number&quot; that I could see would
be, if during data entry, you assigned a line
item number. This would keep your detail
items in the same order over a series of
reports.

HTH,
Wayne

 

[Chucklez]

I see where you are going with the SQL statement. 1 problem tho. I am not creating a report.

This will be used in a data conversion program. The reason I am needing to add the record numbers is that the new data files require:
a.)In the header, a total record count is needed.
b.)In the detail, a counter is needed to tell the system what record number you are on, such as 1 of 4.

I do agree with you that in the code you typed above, the selection of the records would be much faster.
I am trying to figure out how to apply this to my need.

Just curious now, upon programatically importing a table (which I am doing using a ODBC driver that I create at runtime), How can you define a field to be indexed, but still allow duplicates? The field I am talking about is finvno in the table ARTRS. I beleive this will speed it up also. I know how to program the field to be a primary key, but that dosent work because there are duplicates in it.

Once again, I appreciate your knowledge.
-Chuck

 

[WayneRyan]

Chuck,

OK, I understand.

For performance reasons you will need an index on
the ARTRS table. It is easy to do in Access, but I
don't know how to do it programmatically. If your
table exists in Access:
1) Go to the Database window
2) Go to the Tables Tab
3) Select ARTRS
4) On the bottom-left select: indexed (duplicates OK)

The only problem with the code in your original post
is that you can't use the same recordcounter for both
tables. The inner loop should have a seperate counter
(initialized at 1) for the ARTRS table.

Good luck,
Wayne