Extremely long runtime for Access query, suppressing error msgs
Hi, Experts -
We are experiencing very long run-times with a query that ties up nearly all of our PC's resources so that we can't do anything else but wait until this Access make-table query finishes. This usually is 6-7 hours later...! What's worse, whoever kicks off the execution must respond to error messages issued periodically over the duration of the run. AUGH!
The query is using ODBC connectivity to link to Oracle databases that are part of an old JDEdwards ERP system. There are approximately 7 million rows in the database and for each row, the query must:
We will be migrating the execution of this job onto a server (waiting for the server to be configured with the ODBC connectivity to the JDEdwards system) - but we want to suppress all messages - even the error messages - and just let the query run to completion and create the table. We currently have the query tied to the click of a button, with the code behind as follows:
QUESTIONS: How can we suppress ALL messages and how can we get this query to run more quickly?
We are experiencing very long run-times with a query that ties up nearly all of our PC's resources so that we can't do anything else but wait until this Access make-table query finishes. This usually is 6-7 hours later...! What's worse, whoever kicks off the execution must respond to error messages issued periodically over the duration of the run. AUGH!
The query is using ODBC connectivity to link to Oracle databases that are part of an old JDEdwards ERP system. There are approximately 7 million rows in the database and for each row, the query must:
Rename the 112 columns that it extracts,
Reformat any date fields found using a couple of custom-built functions,
Then write the row out to a new table.
PLUS...as it is doing its thing on each row, there are occassional "Type Mismatch" errors that issue a pop-up box message - to which we click the "END" button and the query continues on its merry way.
We will be migrating the execution of this job onto a server (waiting for the server to be configured with the ODBC connectivity to the JDEdwards system) - but we want to suppress all messages - even the error messages - and just let the query run to completion and create the table. We currently have the query tied to the click of a button, with the code behind as follows:
Private Sub btn_F4801T_Click()
On Error GoTo Err_btn_F4801T_Click
DoCmd.SetWarnings False
Dim strQryName As String
strQryName = "Dump Work Order Master Tag Table - Table Build"
DoCmd.OpenQuery strQryName, acNormal, acEdit
DoCmd.SetWarnings True
Exit_btn_F4801T_Click:
Exit Sub
Err_btn_F4801T_Click:
MsgBox Err.Description
Resume Exit_btn_F4801T_Click
End SubQUESTIONS: How can we suppress ALL messages and how can we get this query to run more quickly?
Yes, "long runtime" can be caused by many things.
Depending on what you are doing, it may never be what you might call "Fast"
In other words, what you are asking it to do, may very well take 6-7 hours...
As jim stated, you need to take a long hard look at your coding.
many parts of it may be inefficient:
Poorly declared variables, using recordsets where SQL might be a better choice, inefficient looping code, poor indexing strategy, to many concurrent users, un-Split configuration, complex AND/OR Logic, Multiple wildcards, the DB not being compacted, code not being compiled, ...etc
Not to mention network issues, or hardware deficiencies...
Again, far to many possibilities to take a wild guess here...
Note that all of the things that you say are happening here would (to me) seem to indicate that there is more than just *one query* involved here...
See these links as a starting point:
http://office.microsoft.com/en-us/access-help/improve-performance-of-an-access-database-HP005187453.aspx
http://www.granite.ab.ca/access/performancefaq.htm
http://www.fmsinc.com/MicrosoftAccess/Performance.html
<How can we suppress ALL messages >
Type mismatch is an *error*, not simply a message.
So here your goal would be to avoid the error in the first place, not simply "suppress" it...
You may be able to catch the error in an error handler though...
Jim could help you with this...
Jeffcoachman
Depending on what you are doing, it may never be what you might call "Fast"
In other words, what you are asking it to do, may very well take 6-7 hours...
As jim stated, you need to take a long hard look at your coding.
many parts of it may be inefficient:
Poorly declared variables, using recordsets where SQL might be a better choice, inefficient looping code, poor indexing strategy, to many concurrent users, un-Split configuration, complex AND/OR Logic, Multiple wildcards, the DB not being compacted, code not being compiled, ...etc
Not to mention network issues, or hardware deficiencies...
Again, far to many possibilities to take a wild guess here...
Note that all of the things that you say are happening here would (to me) seem to indicate that there is more than just *one query* involved here...
See these links as a starting point:
http://office.microsoft.com/en-us/access-help/improve-performance-of-an-access-database-HP005187453.aspx
http://www.granite.ab.ca/access/performancefaq.htm
http://www.fmsinc.com/MicrosoftAccess/Performance.html
<How can we suppress ALL messages >
Type mismatch is an *error*, not simply a message.
So here your goal would be to avoid the error in the first place, not simply "suppress" it...
You may be able to catch the error in an error handler though...
Jim could help you with this...
Jeffcoachman
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<What's worse, whoever kicks off the execution must respond to error messages issued periodically over the duration of the run. AUGH!>>
Not to be flippant, but fix the code! Type mis-match is the result of a coding error.
Beyond that in regards to performance, someone would need to go through what your doing in detail to help speed it up.
Jim.