Fastest way on a Slow Network!

dbay

Registered User.
Local time
Today, 06:27
Joined
Jul 15, 2007
Messages
87
I need some opinions on how to do something. This is what I need to do.

I have two tables (Table1, Table2). I import a single list of numbers into a temperary table (TempTable). Table1 and Table2 have a 1 to many relationship. Table1 being the 1 and Table2 being the many. I need to take the imported list of numbers and add another unique Index from the form to the list. Then compair the list to Table2. If the number and Index already exist in Table2 then ignore, but if it does not exist I need to add it to Table2. If number and Index gets added to Table2 then I need to find the number in Table1 and write "ON HOLD" in a status field.

Table1:
Number Status
1231 ON HOLD
1232
1233 <--------Needs to say ON HOLD
1234 ON HOLD


Table2:
Number Index
1231 3
1232 3
<-----Will Be Added from TempTable
1234 3

TempTable:
Number Index
1231 3
1232 3
1233 3 <-----NEW
1234 3


So, what is the best method for a slow network to handle this?
 
Where does the "form" fit? What is the data source for the form?
Are there representative data for the 1:m records in table1 and 2?
What volumes are involved? How often is this done?
Why the concern with speed?
Just trying to get a handle on your situation.
 
Where does the "form" fit? What is the data source for the form?
Are there representative data for the 1:m records in table1 and 2?
What volumes are involved? How often is this done?
Why the concern with speed?
Just trying to get a handle on your situation.


The Form is the Form that contains the UPDATE Button. On the form there is a Field that contains an Index from the linked table (Table3). This Update can take place several times a day. Being on a slower network, there are 30-40 thousand records involved and growing. Right now I use several INSERT and UPDATE SQL statements to make all this happen, but it is slow and the "ON HOLD" writing to Table1 does not work properly. Trying to avoid using recordsets and having to check each record using the rs.FindFirst method.

This is the statement I use to update the "ON HOLD" status for Table1:
DoCmd.RunSQL "UPDATE TBL_List_Main INNER JOIN TBL_Temp_Import ON TBL_List_Main.MAI_P_Number = TBL_Temp_Import.F1 " & _
"SET TBL_List_Main.MAI_Release_Status = 'ON HOLD' WHERE EXISTS (SELECT A.IMP_Act_Index, A.IMP_P_Number " & _
"FROM TBL_Import_Act AS A LEFT JOIN TBL_List_Act AS B ON A.IMP_Act_Index = B.ACT_Act_Index AND " & _
"A.IMP_P_Number = B.ACT_P_Number WHERE B.ACT_Act_Index IS Null);"
 
Are the 30-40 thousand records the total records or just the "batch" that
happens several times a day?
You mentioned INSERTS and UPDATES, how many of these?

How long does the UPDATE query take?
If you're processing recordsets, and you have a "reasonably large number of updates", you don't necessarily have to do find first. You could just process a "tailored" recordset
in a loop. It would depend on whether you're talking 1-2 % records to be updated or 25%+ (as my first guess).

By tailored recordset I mean based on a query that has only the records to be processed.

I'm wondering if there isn't some optimization that can be done in your UPDATE query.

Do you have a sample database( just enough to show tables and representative data), with no confidential data, that could be posted?
 
Are the 30-40 thousand records the total records or just the "batch" that
happens several times a day?
You mentioned INSERTS and UPDATES, how many of these?

How long does the UPDATE query take?
If you're processing recordsets, and you have a "reasonably large number of updates", you don't necessarily have to do find first. You could just process a "tailored" recordset
in a loop. It would depend on whether you're talking 1-2 % records to be updated or 25%+ (as my first guess).

By tailored recordset I mean based on a query that has only the records to be processed.

I'm wondering if there isn't some optimization that can be done in your UPDATE query.

Do you have a sample database( just enough to show tables and representative data), with no confidential data, that could be posted?

The update can take from 5 to 10 minutes.

The 30-40K are the records that are in Table2 being compaired with The TempTable. The TempTable may only contain 1-900 records, but each of the 1-900 records have to be compaired to the 30-40k to see if they exits.

To create an example of the database would take several hours. I just don't have the time at the moment...

One main thing I need at the moment is a way to write "ON HOLD" in Table1 when a record does not exist in Table2. But, with SQL there is no way to get a T/F without opening a recordset and getting a count.
 
Using your Table1, Table2 and TempTable sample, can you give some sample data values showing the 1:M from Table1 to Table2?

I'll try to create a sample db but need to see some sample data.

Also, are there PKs on your tables?
What values go in INdex? (which may be a poor choice of field names)
 
Last edited:
Using your Table1, Table2 and TempTable sample, can you give some sample data values showing the 1:M from Table1 to Table2?

I'll try to create a sample db but need to see some sample data.

Also, are there PKs on your tables?
What values go in INdex? (which may be a poor choice of field names)


The names in these posts are not the names I use in the database. The actual names i can not reveal, but they portray exactly what i need.

Table1 has a primary key which is a number field we will call TBL1_Number_Index which is linked to Table2 TBL2_Number_Index. The TempTable contains a Number which matches the Number_Indexes in Table1 and Table2...I have to add another Index Number we will call ACT_Index to the TempTable list to distinguish it from the other TBL2_Number_Indexes that already exist in Table2. A good example is in my first post. I am now checking to see if the pair of Index Numbers in the TempTable are in Table2. If they are then we skip them. If they are not we write them to Table2 and write "ON HOLD" in the Status Field of Table1 where the Number_Index match.


Table1:
TBL1_Number_Index..........Status
1231..........................." " <----Write "ON HOLD"
1232..........................."RELEASED"
1233..........................."RELEASED"
1234..........................."ON HOLD"

Table2:
TBL2_Number_Index..........ACT_Index
1231.................................1
1231.................................2
1231.................................6
1231.................................4
1232.................................1
1232.................................3
1232.................................5
1233.................................2
1233.................................3
1233.................................5
1234.................................1
1234.................................3
1234.................................4

TempTable:
Temp_Number_Index...........Temp_Index
1233.....................................3 <----Already Exists... SKIP
1234.....................................3 <----Already Exists... SKIP
1231.....................................3 <----Does Not Exist...ADD
1232.....................................3 <----Already Exitst...SKIP


1231........3 Is not in the list. So we write it into Table2 and in the Table1 Status Field we write "ON HOLD".
 
I created a mockup using TableOne, TableTwo and TemporaryTable along the lines we discussed.
In TableTwo and TemporaryTable I created composite unique indexes using the Numfld and MIndex (TableTwo), and Numfld +TempINdex(TemporaryTable).

There are 2 records in TemporaryTable that are not in TableTwo
'1231 3 <-----your record
'1255 4 <-----an additional dummy record for test

The intent was to reduce complexity if possible. As I see it, there is a condition to check
If TemporayTable has records that are not in TableTwo,then Insert them into TableTwo, then Update the Stat field in TableOne (to On HOLD) for these Numfld record(s).

I used a BaseSQL for the heart of the necessary SQL, and a few other pieces of SQL that can be concatenated to create statements to
-Count the number of records to be inserted(if any)
-Insert records as required into TableTwo
- and to do the Update of TableOne

There is a recordset to create a list of Numfld values for the UPdate. This can't be done as an SQL query using my SQLBase because the conditoin is no longer True (records were inserted into TableTwo so at this point there are no records in TemporaryTable that are not in TableTwo). So I make a list of values in "MyList" and use it directly in the Update.

Detail comments and instructions are within the comments in the code.

The database is in 2000 format and is attached as a .zip.

There are Debug.Print statements within to "Print" the sql strings used in the Count, INSERT and UPDATE.

You can run the code, then Delete the 2 records from TableTwo and reset the Stat field in TableOne and run it again.

It seems to work OK as a mockup and I hope the approach can be adapted to your real database. You should try it with some volume to see if it is quicker. I think it should be because it isn't processing extraneous data - no full table read etc and rather simple queries.
I hope this is useful and can be applied or assist in your efforts to speed up your process.

Good luck.
 

Attachments

Last edited:

Users who are viewing this thread

Back
Top Bottom