Need help with inefficient coding 1

[EnS]

Hello,

I have developed a module that compares data from a table in the current database with data that is contained on a table in an external database and updates a field in the internal table. The code that I came up with works, but I feel that it is inefficient and creates too much processing overhead.

Basically, I created two recordsets (r1, r2) which compares each record in r1 with each record in r2 and updates the required field in r1 accordingly. However, if r1 contains 1004 records and r2 contains 578 records ... well, I'm sure you get the picture. Here's what I've got:

If rs1.RecordCount <> 0 Then
rs1.MoveFirst
Do Until rs1.EOF
rs2.MoveFirst
Do Until rs2.EOF
If rs1!fld1 = rs2!fld1 And rs1!fld2 = rs2!fld2 _
And rs1!fld3 = rs2!fld3 And rs1!fld4 = rs2!fld4 Then
rs1.Edit
rs1!fld5 = rs2!fld5
rs1.Update
End If
rs2.MoveNext
Loop
rs1.MoveNext
Loop
End If

Any suggestions that will increase performance, efficiency, and lessen processing overhead would be greatly appreciated.

Thanks in advance ERM

 

[JeremyNYC]

Couldn't you make an update query that joins the two tables on the first four fields? That way you would only be operating on records where all four of those fields match. And then you could update the fifth field in one table to the value of the fifth field in the other.

Hope this helps.

jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[EnS]

Thanks for the reply Jeremy.

I thought about that, except I haven't been able to construct an SQL statement that will allow me to update a table in one database with data from another. I have research the UPDATE statement and can't (for the life of me) find where the IN statement would fit in the syntax. In all honesty, I would prefer to use SQL. If you have any suggestion on how I can do this that would be wonderful.

ERM

 

[cmmrfrds]

What vendor database is the external table in?

 

[EnS]

Both Db's are ACCESS ERM

 

[cmmrfrds]

You should be able to link the external table to your current access mdb and then run the query suggested by Jeremy.

 

[EnS]

That is true, however, for simplicity reasons, I neglected to mention that the internal db will actually connect to several different dbs. I apologize for my unclarity. Therefore I need this process to loop through several different dbs connections depending on the reports in which it is being asked to produce. Unfortunately, I can not submit my whole code at this time because I am not at work right now. But I will submit it first thing in the morning so you may be able to get a better idea of what I need this process to do. In a nut shell, I want to decrease the number of times the process goes through the loop while it searches for the matching record. I ran the code as it is shown above and it hit the loop 945 thousand times. The machines that I have to work with are not exactly speed demons and the process took in excess of 5 min. I made a small change that would exit the nested loop and move on to the next step in the outer loop once a matched file was found, but it didn't improve performance all that much. With this change it still hit the loop 902 thousand times and took just under 5 min to complete.

I hope what I am trying to do makes sense, and if not, believe me, you can just tell me! ERM

 

[MichaelRed]

I am continually amazed at the processes seen in these fora. Individuals ask for advice, receieve it from (arguably) some of the more experienced advisors (lurking about in the attempt to provide reasonable advice?) - and then refuse the (perfectly?) reasonable approaches suggested, or at least continue to object to the suggestions?

Why ask for help if the goal is to not to at least consider it in a positive light?

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JeremyNYC]

ERM,

Well, you could still make the query in SQL, with a variable for the table name(s) and then loop through the different table names, so that you hit all of the other databases.

But here's a big question for you: if these are all your databases, and they're all Access, why are they not all one database, and, in fact, all one table? It's possible there's a legit reason, but in most cases, I would say that it sounds like all of these databases should be merged.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[EnS]

MichaelRed,

I am not &quot;shooting down&quot; or refusing to consider any of the suggestions that have been presented to me here (or in any other thread), it is just that we here have tried these methods before and have found them not to work reliably within the infastructure of our network.

Jeremy,

What you pose is what I really wish we here had the capability of doing. It would make my life and the life of our users so much easier. As it stands now, users in this department have the very cumbersome task of opening up one database to do one process then having to switch to other databases for other processes. This is not the way I feel to get the best out of technology, nor is it the best way to get the most out of production. But I, here, am not in a position to implement those technical changes that we so desperately need. I have placed my concerns, suggestions, and ideas to the &quot;powers that be&quot;, but only to be placed onto def ears. Therefore, I am stuck trying to increase the performance with what I've got to the best of my ablility.

Some background information:
We currently use IBM PC 300PL machines running Win95. We logon to the network with Novell, although I am not sure of the release. The IBM's have classic pentium processor which, as far as I can determine, are no more then 100 to 250 Mhz and most of these machines only have 32 Meg of RAM. The problem that we run into with Access dbs (which are all located on the server) is that if a table contains upto 100 thousand records, performance really suffers. It also suffers when more then one (1) user connects to the same database at one time. I do have an archiving processes that archives old data from all the database an stores that onto one db on a daily basis, but because of out current retention requirments, we have easily reach 100 thousand record on more then a dozen databases.

Let me tell you that it is a real nightmare here!

That is why I have come to those here with this question. Is there a more efficient way of making this process work within the constraints I have stated above.

All production processes get done on 22 databases, which are all identical except for the state that the data reflects. I am submitting my module for your consideration. I am only using 4 database as a test environment, which is why I really want to stream-line this process as much as possible. Just know, I am not against linking all of these tables to this reporting database; its just that I have found that if I can do the majority of these kinds of processes in the background through code, performance seems to increase (some!)

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

Dim dbMstr As Database, dbState As Database
Dim rsMstr As Recordset, rsState As Recordset
Dim dbFileID As Byte, dbPathID As Byte, a As Byte
Dim strSQL As String, mstrStateCondition As String
Dim found As Boolean

Public Function testRecSetOption()

dbPathID = 0
found = False
Set dbMstr = CurrentDb()
For a = 1 To 4
Select Case a
Case 1
dbFileID = 5
mstrStateCondition = &quot;WHERE STATE LIKE 'N*'&quot;
Case 2
dbFileID = 8
mstrStateCondition = &quot;WHERE STATE = 'GA'&quot;
Case 3
dbFileID = 13
mstrStateCondition = &quot;WHERE STATE = 'TX'&quot;
Case 4
dbFileID = 14
mstrStateCondition = &quot;WHERE STATE = 'CO'&quot;
End Select

mstrStateCondition = mstrStateCondition & &quot; AND OUT_CODE IS NULL&quot;

strSQL = &quot;SELECT FC, ATM, DATE, AMOUNT, OUT_CODE &quot; _
& &quot;FROM OUTAGETOTAL &quot; & mstrStateCondition & &quot;&quot;

Set rsMstr = dbMstr.OpenRecordset(strSQL)

Set dbState = OpenDatabase(DBLocations(dbFileID, dbPathID))

strSQL = &quot;SELECT CC, [DOC NO], [PST DATE], NET, REMARKS &quot; _
& &quot;FROM TBLRECONPSTNGS &quot; _
& &quot;WHERE DESCRIPTION LIKE '*VS*' AND REMARKS IS NOT NULL&quot;

Set rsState = dbState.OpenRecordset(strSQL)

If rsMstr.RecordCount <> 0 Then
rsMstr.MoveFirst
Do Until rsMstr.EOF
rsState.MoveFirst
Do Until found = True
If rsState.EOF = False Then
If rsMstr!FC = rsState!CC And rsMstr!ATM = rsState![DOC NO] _
And rsMstr!Date = rsState![Pst Date] And rsMstr!AMOUNT = rsState!NET Then
rsMstr.Edit
rsMstr!out_code = rsState!remarks
rsMstr.Update
found = True
End If
rsState.MoveNext
Else
found = True
End If
Loop
rsMstr.MoveNext
found = False
Loop
End If

Set rsMstr = Nothing
Set rsState = Nothing
Set dbState = Nothing

Next a

Set dbMstr = Nothing

MsgBox &quot;Done&quot;

End Function


Thanks again to all of you.
ERM

 

[JeremyNYC]

ERM,

If I read your code very closely I might be able to come up with ways to tinker around the margins to give you marginal (at best) performance returns. But you have described a system that is so dysfunctional (not your making) that it seems rather futile to me. If I were in your shoes I would tell the people making the decisions that their decisions are causing huge performance problems, and that unless they are flexible, they will continue to see such performance problems.

On the other hand, I don't see how having the tables not be linked could possibly help performance.

But the hardware and the insistence on multiple databases are the real killers here. It's at least possible to imagine arguments against upgrading the hardware (cost). But the multiple databases is relatively indefensible, in my opinion.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[EnS]

Jeremy,

I totally agree with your opinion. I am passing you a vote for having the understanding and the patients to help me with this monster. Thanks so very much!

I am going to try linking the tables (of the 4 test dbs) and run the process again to see if that may increase performace some. I'll let you know where it stands. Thanks again for all you help. ERM

 

[JeremyNYC]

No problem on the patience. I've been in your shoes plenty.

Right, but keep in mind that the big advantage to linking the tables is that you can do away with most of the code and just do your operation in a query.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[sshah254]

I think that the best option is to link the tables and run update queries.

If you are going to be somehow linking and updating all the 22 databases, then you
a. Create all the links and keep them
b. Create the links on the fly, update the databases and then delete the links.

This does not take much time (I do it all the time, in all my databases).

I would also look at the size of the databases that you have. A 100,000 record databases is not that big for MS Access (of course it depends on the table structure, but it is not huge). Make sure that you are compacting the databses daily (at least, regularly). MS Access databases tend to grow in size with the type of update queries that you are running. And since you are accessing them over a network, your performance is going to be slow.

Also, the other option could be to do all the updates on a single high powered machine so that the update gets done much quickly.

Sandip
shahs@microdental.com

 

[EnS]

Thanks for all the help. Jeremy, I linked my testing tables and found that performance did increase (although only about 30 sec for the whole process) so I am keeping the links and working it from there. Thanks again for all your help! ERM

 

[JeremyNYC]

Glad to see it helped some.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[dpimental]

One further question.

What determines whether a field in 1 database gets updated by a field in another database, or remains the same?

Is it possible to to have a fieldupdatestatus field that tells you when a field has been updated in a given table.
The query, I'm sure would be much smaller in output?

This would reduce the amount of work, while increase efficiency.

David Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[EnS]

The users add items in the production database which I collect (currently, but will change to accomidate the linking process) at the end of each month for the reporting purposes. These added items may or may not be resolved and closed, therefore before collecting the next months data, I first must updata any changes to those items from the previous month that may have been closed. I do have identifing tags that allow me to extract only those items in which I am looking for at that particular time, wheather or not they are close or open. The linking of the tables will more than likely be the determining factor between a 2 hour process (due to our infrastructure) and a 45 minute process. ERM