How to speed up program

[TonyG]

I'm having a problem with an AccessXP program using externally linked tables thru ODBC. The program works fine with a small amount of test data in the tables(couple hundred records in each table). When i attempt to use it on live client data, it runs for over 24 hours and never finishes. We have to terminate it by using Task manager/applications(alt-ctrl-del). It uses 100% of the resources, shows in task manager as not responding. The following tables and their record counts are used.

Customers 27,069
Items 2,698
Order Headers 92,502
Order lines 159,366

I'm running it on a new DELL Dimension 2350, 2.0ghz, 256meg RAM, 18gig HDD

The program is pretty simple. It creates an export file and optional report using:
DoCmd.OpenReport strcReportName, acViewPreview
DoCmd.TransferText acExportDelim, , strcQueryName, strFile

If any anybody has any ideas on how to speed this program up or how i can track the progress of the query, i would greatly appreciate it.

Thanks,
Tony

 

[jrbarnett]

Hello

There are a few things that can be looked at.
The first is to ensure that primary/foreign keys in tables are set as is proper referential integrity and that appropriate indexes are set for other fields that are searched or sorted on.

The next is that you say it runs through ODBC: Therefore there must be a source external to the data; does it run this slowly on the live data if you just execute the query from the native query tools (eg Sybase Interactive SQL, Oracle PL/SQL etc)? (ie is it Access or the remote source that is the problem).

What is the code that underlies the report (and presumably the transfertext) and also the type of system that the data comes from (database type and version).

John

 

[TonyG]

Hello John,

I really appreciate your help on this matter.

I'm not sure i know how to check the items in the first paragraph of your reply, but if you can direct me i certainly will check those items.

The tables for this project are created from a Pervasive SQL 2000i database using COBOL programs. The COBOL programs use the data in Btrieve indexed files. When i run a report on the same data through the COBOL systems programs, it runs for about 90 minutes and does give the expected results.

Here is the code behind the form that starts the whole thing by clicking OK. Thanks for any other help that you can provide.

Tony

***********************************************************
Option Compare Database
Option Explicit
'Name of query used for export/reporting.
Const strcQueryName = "qryExport"

'Name of report used for previewing/printing the export.
Const strcReportName = "rptExport"

'SQL statement (without the WHERE clause) for the export query.
Const strcStub = "SELECT Trim(cust.nam) AS TrimNam, " & _
"Trim(cust.adrs_1) AS TrimAdrs_1, " & _
"Trim(cust.adrs_2) AS TrimAdrs_2, " & _
"Trim(cust.city) AS TrimCity, " & _
"Trim(cust.state) AS TrimState, " & _
"Trim(cust.zip_cod) AS TrimZip_cod, " & _
"Trim(cust.email_adrs) AS TrimEmail_adrs, " & _
"Trim(cust.phone_no_1) AS TrimPhone_no_1, " & _
"CLng(sa_lin.item_no,) AS itemnumber, " & _
"MAX(sa_hdr.post_dat) AS postdate " & _
"FROM (cust " & _
"INNER JOIN sa_hdr " & _
"ON cust.nbr = sa_hdr.cust_no) " & _
"INNER JOIN sa_lin " & _
"ON sa_hdr.ticket_no = sa_lin.hdr_ticket_no "

Const strcTail = "GROUP BY Trim(cust.nam), " & _
"Trim(cust.adrs_1), " & _
"Trim(cust.adrs_2), " & _
"Trim(cust.city), " & _
"Trim(cust.state), " & _
"Trim(cust.zip_cod), " & _
"Trim(cust.email_adrs), " & _
"Trim(cust.phone_no_1), " & _
"CLng(sa_lin.item_no) " & _
"ORDER BY Trim(cust.nam);"

Function CustQuery() As Boolean
'On Error GoTo CustQueryError
'Purpose: Assign the SQL property of the query, based on the controls where user entered something.
' The query is then used for export and/or report in cmdOk_Click.
Dim strWhere As String
Dim lngLen As Long
Dim CusQry As String

'***********************************************
'Starting and ending ITEMS: sa_lin.item_no is Text, converted to Long.
'***********************************************
If Len(Nz(Me.StrItm, vbNullString)) = 0 Then
If Len(Nz(Me.EndItm, vbNullString)) > 0 Then 'End, no start
strWhere = strWhere & &quot;(CLng(sa_lin.item_no) <= &quot; & CLng(Me.EndItm) & &quot AND &quot;
End If
Else
If Len(Nz(Me.EndItm, vbNullString)) = 0 Then 'Start, no end
strWhere = strWhere & &quot;(CLng(sa_lin.item_no) >= &quot; & _
CLng(Me.StrItm) & &quot AND &quot;
Else 'Both start and end
strWhere = strWhere & &quot;(CLng(sa_lin.item_no) Between &quot; & _
CLng(Me.StrItm) & &quot; And &quot; & CLng(Me.EndItm) & &quot AND &quot;
End If
End If

'***********************************************
'Starting and ending DATES: sa_Hdr.post_dat is Text.
'***********************************************
If IsDate(Me.StrDat) Then
If IsDate(Me.EndDat) Then 'Both start and end
strWhere = strWhere & &quot;(sa_hdr.post_dat Between &quot;&quot;&quot; & Format(Me.StrDat, &quot;yyyymmdd&quot & &quot;&quot;&quot; And &quot;&quot;&quot; & Format(Me.EndDat, &quot;yyyymmdd&quot & &quot;&quot;&quot AND &quot;
Else 'Start, no end
strWhere = strWhere & &quot;(sa_hdr.post_dat >= &quot;&quot;&quot; & Format(Me.StrDat, &quot;yyyymmdd&quot & &quot;&quot;&quot AND &quot;
End If
Else
If IsDate(Me.EndDat) Then 'End, no start
strWhere = strWhere & &quot;(sa_hdr.post_dat <= &quot;&quot;&quot; & Format(Me.EndDat, &quot;yyyymmdd&quot & &quot;&quot;&quot AND &quot;
End If
End If

'***********************************************
'ZIP code range: cust.zip_cod is Text.
'***********************************************
If Len(Nz(Me.StrZip, vbNullString)) = 0 Then
If Len(Nz(Me.EndZip, vbNullString)) > 0 Then 'End, no start
strWhere = strWhere & &quot;(Trim(cust.zip_cod) <= &quot;&quot;&quot; & Me.EndZip & &quot;&quot;&quot AND &quot;
End If
Else
If Len(Nz(Me.EndZip, vbNullString)) = 0 Then 'Start, no end
strWhere = strWhere & &quot;(Trim(cust.zip_cod) >= &quot;&quot;&quot; & Me.StrZip & &quot;&quot;&quot AND &quot;
Else 'Both start and end
strWhere = strWhere & &quot;(Trim(cust.zip_cod) Between &quot;&quot;&quot; & Me.StrZip & &quot;&quot;&quot; And &quot;&quot;&quot; & Me.EndZip & &quot;&quot;&quot AND &quot;
End If
End If

'***********************************************
'SALE AMOUNT range: cust.bal is Decimal (16,2).
'***********************************************
If IsNull(Me.StrSalAmt) Then
If Not IsNull(Me.EndSalAmt) Then 'End, no start.
strWhere = strWhere & &quot;(cust.bal <= &quot; & Me.EndSalAmt & &quot AND &quot;
End If
Else
If IsNull(Me.EndSalAmt) Then 'Start, no end.
strWhere = strWhere & &quot;(cust.bal >= &quot; & Me.StrSalAmt & &quot AND &quot;
Else 'Both start and end.
strWhere = strWhere & &quot;(cust.bal Between &quot; & Me.StrSalAmt & &quot; And &quot; & Me.EndSalAmt & &quot AND &quot;
End If
End If

'***********************************************
'CATEGORY: cust.cat is Text.
'***********************************************
If Len(Nz(Me.CusCat, vbNullString)) > 0 Then
strWhere = strWhere & &quot;(cust.cat = &quot;&quot;&quot; & Me.CusCat & &quot;&quot;&quot AND &quot;
End If
'***********************************************
'EMAIL: cust.email_adrs is Text.
'***********************************************
If Me.ExcCustChk.Value Then
strWhere = strWhere & &quot; (NOT (cust.email_adrs Is Null OR cust.email_adrs = &quot;&quot;&quot;&quot) AND &quot;
End If

'***********************************************
'Combine this WHERE clause with the stub and tail of the SQL statement, and assign to query.
'***********************************************
'Remove trailing &quot; AND &quot;, and add &quot; WHERE &quot;
lngLen = Len(strWhere) - 5 'Without trailing &quot; AND &quot;.
If lngLen > 0 Then
strWhere = &quot; WHERE &quot; & Left$(strWhere, lngLen) & vbCrLf
End If

'Set the SQL property of the saved query.
DBEngine(0)(0).QueryDefs(strcQueryName).SQL = strcStub & strWhere & strcTail
CustQuery = True
Exit_Handler:
Exit Function

CustQueryError:
MsgBox &quot;Error &quot; & Err.Number & &quot; (&quot; & Err.Description & &quot&quot;, vbCritical
Resume Exit_Handler
End Function

Private Sub cmdCancel_Click()
DoCmd.Close acForm, Me.Name
End Sub

Private Sub cmdOk_Click()
Dim strFile As String 'Name of outputfile
Dim bSuccess As Boolean

strFile = &quot;c:\guystest&quot; & Format(Now(), &quot;mmddyyyyhhnn&quot & &quot;.txt&quot;

'Write the SQL statement.
If CustQuery() Then
Select Case Me.grpOuputType
Case Me.optPrintReport.OptionValue
DoCmd.OpenReport strcReportName, acViewPreview
bSuccess = True

Case Me.optCreateFile.OptionValue
DoCmd.TransferText acExportDelim, , strcQueryName, strFile
MsgBox &quot;File created: &quot; & vbCrLf & strFile
bSuccess = True

Case Me.optBoth.OptionValue
DoCmd.OpenReport strcReportName, acViewPreview
DoCmd.TransferText acExportDelim, , strcQueryName, strFile
MsgBox &quot;File created: &quot; & vbCrLf & strFile
bSuccess = True

Case Else
MsgBox &quot;Output type not recognised.&quot;
End Select
End If

If bSuccess Then
Call cmdCancel_Click
End If
End Sub
***********************************************************

 

[jrbarnett]

Hello Tony,

Firstly, I have to say that what I know about Pervasive can be written on a stamp, so you may want to ask about this in the Pervasive.SQL and BTrieve forums here as well (use search at the top to locate them and ask how to check that primary and foreign keys and referential integrity are set, and whether indexes on fields other than the primary key are beneficial in this case).
However, a few general points from the Access perspective are as follows:

1. When the query qryExport runs, it contains several calls to the Trim function, as well as CLng functions from within your dynamic SQL generation code. This is fine, but remember that calling VBA functions from within the database slows down execution significantly. If there is a Pervasive.SQL equivalent, then maybe replace them with that, and see item #2 below.

2. Once #1 is done, the query type can be set to Pass Through, which means that Access will pass the query to Pervasice, which will run the query and return the results to Access. With a pass through query, Access (and the Jet engine that underlies Access) will not attempt to interpret it whereas with other types it will.

You can set the type to Pass Through programmatically by putting the line:

DBEngine(0)(0).QueryDefs(strcQueryName).Type = dbQSQLPassThrough

between the

DBEngine(0)(0).QueryDefs(strcQueryName).SQL
and
CustQuery = True

lines, or by editing the query in design mode, setting the property from the View menu.

You don't say if the Pervasive server is running on the same system as your access database, but if not, setting the query to pass through will reduce the workload that Access deals with. This will not increase the workload that Pervasive has to deal with however, because it still has to return the results rather than return everything and let Access only select the results that meet the criteria in the Where clause.
The main disadvantage of this approach is that the SQL you write has to be in the Pervasive syntax, and can't be generated by the Access query builder, or syntax checked.

3. Some of the VBA code can be optimised slightly in terms of the SQL that it produces.
Assuming that Pervasive works in a similar way to Jet and Sybase (the two database engines that I know well), something like your code for email address checking can be rewritten slightly from:

    '***********************************************
    'EMAIL: cust.email_adrs is Text.
    '***********************************************
    If Me.ExcCustChk.Value Then
        strWhere = strWhere & " (NOT (cust.email_adrs Is Null OR cust.email_adrs = """")) AND "
    End If

to

    '***********************************************
    'EMAIL: cust.email_adrs is Text.
    '***********************************************
    If Me.ExcCustChk.Value Then
        strWhere = strWhere & &quot; (cust.email_adrs Is Not Null AND cust.email_adrs <> &quot;&quot;&quot;&quot;) AND &quot;
    End If

This is because using the word &quot;NOT&quot; in a query means that it first has to check that one or other of the two conditions are true, and then invert it.
My approach simply tests for a non null value in the email address. I left the test for an empty string in there, but this may be able to be removed because in most cases, a null value and an empty string are treated as identical, but I am not sure how Pervasive treats this; if it works like this then everything from the word &quot;AND&quot; to the close bracket can also be removed.
There are a few other things as well that could be looked at, but these are more minor points and anyway, this has given you more than enough to look at and think about already.

4. Not a major thing, but just nice to have:
In the CustQuery error handler, put
CustQuery = False

so that if for whatever reason the function fails, the negative error code will cause the cmdOK_Click() sub to stop running and give you time to look at the code before generating the output file or report and tying the machine up for another few hours producing what could turn out to be rubbish.

I hope that this gives you food for thought on how to to go about it, but if you need any more help just ask.

John

 

[JoeEskimo]

Most of the time ODBC are not very efficient for queries. I have been quite successful with this:

1.Import your data from the ODBC Pervasive data into your access app using a &quot;make a table query&quot;

2.Run your process against this table

Most of the time speed is no longer a problem

3.Make a &quot;Delete&quot; query to purge your table when you are done

All of this can be tied into a macro so it is automated very easily.

I hope this helps.

 

[JoeEskimo]

By the way,

Is this Macola you are attaching to?

 

[TonyG]

Hi Joe,

Thanks for the info. I will try that and let you know how it comes out.

No, not Macola, it's CounterPoint from Synchronics.

Thanks,
Tony

 

[TonyG]

Hi Joe,

I tried importing. Two of the tables gave me a &quot;Record is too large&quot; error. Here is the help on that error.

Record is too large. (Error 3047)
You defined or imported a table with records larger than 2K. This error occurs when you enter data into the record — not when you define the table structure. Redefine the table by making some fields shorter, removing unneeded fields, or moving some fields to other tables.

Is there something i can do to get around that ?

Thanks,
Tony

 

[TonyG]

Hi Joe

If you are still monitoring this thread, i'd like to know if you have any idea of how to resolve this problem.

I would really appreciate any help that you can provide to do these imports.

Thanks,
Tony