I have a selection of queries that are being run through a form after selection of 3 fields in combo boxes. One Totals query is extremely slow, taking several minutes for the selections in the combo boxes to become visible – especially for one of the combo boxes.
The code is as follows:
However, this is essentially the same code as for all the other queries in my navigation form and I don’t have a problem with any of the other detailed or totals queries.
So, I’m wondering whether the issue lies with some of the calculated fields in my query.
One field contains a really good function that someone helped me with on this site, which checks whether the phone number called is one of the company mobiles:
Another field then checks whether the number called is one of 2 company offices:
And another field puts the two together: If the number called is not a company mobile and it is not to one of the campany offices, it is external. If not, it is internal.
Given the above information, would somebody be able to offer some suggestions why the query performance is so slow and how I could speed it up a bit?
The code is as follows:
Code:
[FONT=Arial]Private Sub cmdOK_Click()[/FONT]
[FONT=Arial]' Pointer to error handler[/FONT]
[FONT=Arial]On Error GoTo cmdOK_Click_err[/FONT]
[FONT=Arial]' Declare variables[/FONT]
[FONT=Arial]Dim db As DAO.Database[/FONT]
[FONT=Arial]Dim qdf As DAO.QueryDef[/FONT]
[FONT=Arial]Dim strCategory As String[/FONT]
[FONT=Arial]Dim strMonth As String[/FONT]
[FONT=Arial]Dim strDept As String[/FONT]
[FONT=Arial]Dim strSQL As String[/FONT]
[FONT=Arial]' Identify the database and assign it to the variable[/FONT]
[FONT=Arial]Set db = CurrentDb[/FONT]
[FONT=Arial]' Check for the existence of the query, create it if not found,[/FONT]
[FONT=Arial]' and assign it to the variable[/FONT]
[FONT=Arial]If Not QueryExists("qryCall0CumulativeInternalSplitTOTALS") Then[/FONT]
[FONT=Arial] Set qdf = db.CreateQueryDef("qryCall0CumulativeInternalSplitTOTALS")[/FONT]
[FONT=Arial]Else[/FONT]
[FONT=Arial] Set qdf = db.QueryDefs("qryCall0CumulativeInternalSplitTOTALS")[/FONT]
[FONT=Arial]End If[/FONT]
[FONT=Arial]' Get the values from the combo boxes[/FONT]
[FONT=Arial]If IsNull(Me.cboCategory.Value) Then[/FONT]
[FONT=Arial] strCategory = " Like '*' "[/FONT]
[FONT=Arial]Else[/FONT]
[FONT=Arial] strCategory = "='" & Me.cboCategory.Value & "' "[/FONT]
[FONT=Arial]End If[/FONT]
[FONT=Arial]If IsNull(Me.cboMonth.Value) Then[/FONT]
[FONT=Arial] strMonth = " Like '*' "[/FONT]
[FONT=Arial]Else[/FONT]
[FONT=Arial] strMonth = "='" & Me.cboMonth.Value & "' "[/FONT]
[FONT=Arial]End If[/FONT]
[FONT=Arial]If IsNull(Me.cboDept.Value) Then[/FONT]
[FONT=Arial] strDept = " Like '*' "[/FONT]
[FONT=Arial]Else[/FONT]
[FONT=Arial] strDept = "='" & Me.cboDept.Value & "' "[/FONT]
[FONT=Arial]End If[/FONT]
[FONT=Arial]' Build the SQL string[/FONT]
[FONT=Arial]strSQL = "SELECT Call0InternalSplit.[/FONT]請求年月[FONT=Arial], Call0InternalSplit.Category, Call0InternalSplit.CODE, Call0InternalSplit.EnglishDeptName, Sum(Call0InternalSplit.[/FONT]通話料金[FONT=Arial]) AS [/FONT]通話料金[FONT=Arial]OfSum " & _[/FONT]
[FONT=Arial] "FROM Call0InternalSplit " & _[/FONT]
[FONT=Arial] "WHERE Call0InternalSplit.Category" & strCategory & _[/FONT]
[FONT=Arial] "AND Call0InternalSplit.[/FONT]請求年月[FONT=Arial]" & strMonth & _[/FONT]
[FONT=Arial] "AND Call0InternalSplit.CODE" & strDept & _[/FONT]
[FONT=Arial] "GROUP BY Call0InternalSplit.[/FONT]請求年月[FONT=Arial], Call0InternalSplit.Category, Call0InternalSplit.CODE, Call0InternalSplit.EnglishDeptName " & _[/FONT]
[FONT=Arial] "ORDER BY Call0InternalSplit.[/FONT]請求年月[FONT=Arial], Call0InternalSplit.Category;"[/FONT]
[FONT=Arial]' Pass the SQL string to the query[/FONT]
[FONT=Arial]qdf.SQL = strSQL[/FONT]
[FONT=Arial]' Turn off screen updating[/FONT]
[FONT=Arial]DoCmd.Echo False[/FONT]
[FONT=Arial]' Check the state of the query and close it if it is open[/FONT]
[FONT=Arial]If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryCall0CumulativeInternalSplitTOTALS") = acObjStateOpen Then[/FONT]
[FONT=Arial] DoCmd.Close acQuery, "qryCall0CumulativeInternalSplitTOTALS"[/FONT]
[FONT=Arial]End If[/FONT]
[FONT=Arial]' Open the query[/FONT]
[FONT=Arial]DoCmd.OpenQuery "qryCall0CumulativeInternalSplitTOTALS"[/FONT]
[FONT=Arial]cmdOK_Click_exit:[/FONT]
[FONT=Arial]' Turn on screen updating[/FONT]
[FONT=Arial]DoCmd.Echo True[/FONT]
[FONT=Arial]' Clear the object variables[/FONT]
[FONT=Arial]Set qdf = Nothing[/FONT]
[FONT=Arial]Set db = Nothing[/FONT]
[FONT=Arial]Exit Sub[/FONT]
[FONT=Arial]cmdOK_Click_err:[/FONT]
[FONT=Arial]' Handle errors[/FONT]
[FONT=Arial]MsgBox "An unexpected error has occurred." & _[/FONT]
[FONT=Arial] vbCrLf & "Please note of the following details:" & _[/FONT]
[FONT=Arial] vbCrLf & "Error Number: " & Err.Number & _[/FONT]
[FONT=Arial] vbCrLf & "Description: " & Err.Description _[/FONT]
[FONT=Arial] , vbCritical, "Error"[/FONT]
[FONT=Arial]Resume cmdOK_Click_exit[/FONT]
[FONT=Arial]End Sub[/FONT]
So, I’m wondering whether the issue lies with some of the calculated fields in my query.
Code:
[FONT=Arial]SELECT Call0CumulativeTbl.ID, Call0CumulativeTbl.[/FONT]電話番号[FONT=Arial], Call0CumulativeTbl.[/FONT]通話開始日[FONT=Arial], Call0CumulativeTbl.[/FONT]通話開始時刻[FONT=Arial], Call0CumulativeTbl.[/FONT]通話時間[FONT=Arial], Call0CumulativeTbl.[/FONT]相手先電話番号[FONT=Arial], IIf(DCount("[/FONT]電話番号[FONT=Arial]","Call0CumulativeTbl","[/FONT]電話番号[FONT=Arial] ='" & [/FONT]相手先電話番号[FONT=Arial] & "'")>0,"Internal","External") AS CallType, Call0CumulativeTbl.[/FONT]付加使用種別[FONT=Arial], Call0CumulativeTbl.[[/FONT]ローミング[FONT=Arial]], Call0CumulativeTbl.[/FONT]通話料金[FONT=Arial], Call0CumulativeTbl.[/FONT]請求年月[FONT=Arial], IIf(Mid([[/FONT]相手先電話番号[FONT=Arial]],4,4)="1111","Osaka",IIf(Mid([[/FONT]相手先電話番号[FONT=Arial]],4,4)="2222","Tokyo","No Office")) AS Office, IIf([CallType]="External" And [Office]="No Office","External","Internal") AS Category, PhoneByUserAndCCR.User, PhoneByUserAndCCR.CODE, PhoneByUserAndCCR.EnglishDeptName[/FONT]
[FONT=Arial]FROM Call0CumulativeTbl LEFT JOIN PhoneByUserAndCCR ON Call0CumulativeTbl.[/FONT]電話番号[FONT=Arial] = PhoneByUserAndCCR.Number[/FONT]
[FONT=Arial]ORDER BY Call0CumulativeTbl.[/FONT]通話料金[FONT=Arial] DESC;[/FONT]
Code:
[FONT=Arial]CallType: IIf(DCount("[/FONT]電話番号[FONT=Arial]","Call0CumulativeTbl","[/FONT]電話番号[FONT=Arial] ='" & [/FONT]相手先電話番号[FONT=Arial] & "'")>0,"Internal","External")[/FONT]
Code:
[FONT=Arial]Office: IIf(Mid([[/FONT]相手先電話番号[FONT=Arial]],4,4)="1111","Osaka",IIf(Mid([[/FONT]相手先電話番号[FONT=Arial]],4,4)="2222","Tokyo","No Office"))[/FONT]
Code:
[FONT=Arial]Category: IIf([CallType]="External" And [Office]="No Office","External","Internal")[/FONT]