How can I get Access to run faster

[emik]

Hi,

I've never written a program with this many records before and the time it takes to process is through the roof.

I have 1 table with 5,000 records and another table with 160,000.

What I need to do is loop through the 160,000 and update the 5,000 accordingly.

Essentially my code looks like this:

dim Trans as recordset
Set Traans = currentdb.openrecordset("table1",db_open_dynaset)

dim Recon as recordset
set Recon = currentdb.openrecordset("table2",db_open_dynaset)

If Trans.EOF = False Then
Trans.MoveFirst
Do Until Trans.EOF
Recon.FindFirst "criteria = '" & Trans!field & "' AND criteria2 = '" & Trans!field2 & "'"
If Recon.NoMatch Then
With Recon
.AddNew
.update
End With
Else
With Recon
.edit
.update
End With
End If
End If


Is there any way I can make this run faster or more efficiently? Thanks.

 

[PHV]

Can't you play with an UPDATE query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[emik]

I'm not sure because there are conditions I need to check. I know limiting the number of IFs and conditions will make it go faster but I have no choice.

I need to look through each record, if a match is found determine the type of match, so I have a short CASE statement (only 4 elements).

Right now I can't even get the code to run fully, the program seems to freeze.

 

[PHV]

Perhaps you may play with 4 UPDATE queries with different WHERE clauses or one UPDATE query with some IIf calls ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[WildHare]

Emik, is your 5K record table indexed/sorted on the values that you're looking for, e.g. your Trans!Field and Trans!Field2? If not, that could be a drain.

You're just stepping through the big guy, that shouldn't really be a problem - but if the values you're looking for in the small guy are scattered, it may have to search 4,999 records to find the match, each time.

Also, I'm not certain, but it may save you time to hold off doing the UPDATE on RECON until you're finished - like transaction processing to save the commits until they're all done.



--------------------------------------
"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[CaptainD]

Can you use "Not In" to retrieve the data you want in a query then make your updates?
Example pulls names from table 2 that are not in table 1:

SELECT sName
FROM tTable2
Where sName not in (Select sName from tTable1);

 

[emik]

These are all very good suggestions.

The way I was going to go was to create the 2 queries, one where it matches and one where it finds un-matched and doing it in 2 parts would speed it up (plus I wouldn't have to check conditions).

However what I realized was the data was at a lower level than I needed it to be, so I dropped some fields and grouped it and it ended up being around 3,000 (160,000 to 3,000) I couldn't believe it. In anycase, with that amount of data the program goes fast and apparently in the future the data will be a lot smaller.

If they ever want to expand it I'll definately go with the 2 queries.

Thanks for all your answers.