Poor query performance (1 Viewer)

[KirRoyale]

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:

[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]

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.

[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]

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:

[FONT=Arial]CallType: IIf(DCount("[/FONT]電話番号[FONT=Arial]","Call0CumulativeTbl","[/FONT]電話番号[FONT=Arial] ='" & [/FONT]相手先電話番号[FONT=Arial] & "'")>0,"Internal","External")[/FONT]

Another field then checks whether the number called is one of 2 company offices:

[FONT=Arial]Office: IIf(Mid([[/FONT]相手先電話番号[FONT=Arial]],4,4)="1111","Osaka",IIf(Mid([[/FONT]相手先電話番号[FONT=Arial]],4,4)="2222","Tokyo","No Office"))[/FONT]

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.

[FONT=Arial]Category: IIf([CallType]="External" And [Office]="No Office","External","Internal")[/FONT]

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?

 

[pr2-eugin]

KirRoyale, the main problem is using DCount in your Query.. You should understand that Dcount is a simplified Query by itself.. So when you try to run the Query..

If the rcordset had minimum of 10 records you are Querying the DB 10 times; one for each record.. So multiplying the records in the result would exponentially increase your Performance.. To increase the Performance you need to look into creating JOINS by adding the count values to another built in Query..

 

[KirRoyale]

Thank you for the reply. I understand that Dcount is causing a problem.
Could you please expand on what you mean by “you need to look into creating JOINS by adding the count values to another built in Query”.
I was thinking perhaps of creating another table of all the numbers called by a SELECT DISTINCT and then somehow trying to check whether the number called appeared in the list of company mobiles….

 

[pr2-eugin]

I do not know completely how your tables are set up.. So I will explain with a simple example.. Take two table Agents and tblLogger

Agents

A_ID    AgentName    
1        ABBIE
2        ALEX
3        ANDE B

tblLogger

loggerID    loggerAgent    loggerDate
1            1            24/05/2013
2            1            24/06/2013
3            2            28/06/2013

So if you want a new field say NewInfo as Internal or External.. Then your Query will be something like..

SELECT DISTINCT Agents.A_ID, Agents.AgentName, IIf(IsNull(tblLogger.loggerAgent),"Internal","External") AS NewInfo
FROM Agents LEFT JOIN tblLogger ON Agents.A_ID = tbl_Logger.loggerAgent;

Then you will get results something like..

A_ID    AgentName    NewInfo
1        ABBIE        External
2        ALEX         External
3        ANDE B       Internal

 

[KirRoyale]

Thank you. I have worked through your example. It works and I understand it.
In my particular case, the company mobile (number doing the calling) and the number called are 2 fields in the same table.
Even if I create a new table with 1 field – the distinct values from the ‘Number called’ and another table with the distinct values from the ‘number doing the calling’ field, I can’t see how I can join the 2 tables in order to run the sort of query that you provided.
Could you please help further?

 

[pr2-eugin]

Okay I am trying to understand this.. Lets re-word some fields.. We will try to adapt it with the example I have given.. Lets name the field Number Doing the Call as callOutNum and the Number being called as callToNum.. And with this, could you provide some sample data and the result you wish to see?

It would be better if you upload a Stripped version of DB, but it would be easier if the field names were in English.. As many of us do not know Japanese..

 

[Pat Hartman]

I don't know if anyone else is seeing what I see but most of your posted code is obscured by Chinese characters so I can't really see the queries. For starters, you don't need to build the query in VBA. You can use the following to work with optional arguments.

Select ...
From ...
Where ...
(fldA = Forms!yourform!fldA OR Forms!yourform!fldA Is Null)
AND (fldB = Forms!yourform!fldB OR Forms!yourform!fldB Is Null)
AND (fldC = Forms!yourform!fldC OR Forms!yourform!fldC Is Null)
AND ....

Notice the parentheses - They are REQUIRED. The query uses both AND and OR operators and so the parentheses are required to specify the order of operations. Essentially each criteria set says select if tablefielda = formfielda or if formfielda is null.

I can't see the DCount() part of the query but inside the function you have some criteria that specifies how to count the data. That criteria is most likely what you would need to join to a totals query. Say I want to produce a simple list of students with a count of the classes they take. I create a totals query that selects StudentID, and Counts the records returned (classes) from the class table. Then I join this query to the student query and for each student, I have a count of their classes. You will probably need to use left joins since at least in my example, you may want the student to appear on the list even if he has 0 classes.

 

[KirRoyale]

Apologies for the confusion and thank you for the posts.

A reworked version of the VBA with English field names:

[FONT=Arial][COLOR=#000000]Private Sub cmdOK_Click()[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]' Pointer to error handler[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   On Error GoTo cmdOK_Click_err[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]' Declare variables[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Dim db As DAO.Database[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Dim qdf As DAO.QueryDef[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Dim strCategory As String[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Dim strMonth As String[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Dim strDept As String[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Dim strSQL As String[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]' Identify the database and assign it to the variable[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Set db = CurrentDb[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]' Check for the existence of the query, create it if not found,[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]' and assign it to the variable[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   If Not QueryExists("qryCall0CumulativeInternalSplitTOTALS") Then[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       Set qdf = db.CreateQueryDef("qryCall0CumulativeInternalSplitTOTALS")[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Else[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       Set qdf = db.QueryDefs("qryCall0CumulativeInternalSplitTOTALS")[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   End If[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]' Get the values from the combo boxes[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   If IsNull(Me.cboCategory.Value) Then[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       strCategory = " Like '*' "[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Else[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       strCategory = "='" & Me.cboCategory.Value & "' "[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   End If[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   If IsNull(Me.cboMonth.Value) Then[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       strMonth = " Like '*' "[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Else[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       strMonth = "='" & Me.cboMonth.Value & "' "[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   End If[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   If IsNull(Me.cboDept.Value) Then[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       strDept = " Like '*' "[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Else[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       strDept = "='" & Me.cboDept.Value & "' "[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   End If[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]' Build the SQL string[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   strSQL = "SELECT Call0InternalSplit.MONTH, Call0InternalSplit.Category, Call0InternalSplit.CODE, Call0InternalSplit.EnglishDeptName, Sum(Call0InternalSplit.CHARGEAMOUNT) AS CHARGEAMOUNT OfSum " & _[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]            "FROM Call0InternalSplit " & _[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]            "WHERE Call0InternalSplit.Category" & strCategory & _[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]            "AND Call0InternalSplit. MONTH " & strMonth & _[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]            "AND Call0InternalSplit.CODE" & strDept & _[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]            "GROUP BY Call0InternalSplit. MONTH, Call0InternalSplit.Category, Call0InternalSplit.CODE, Call0InternalSplit.EnglishDeptName " & _[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]            "ORDER BY Call0InternalSplit. MONTH, Call0InternalSplit.Category;"[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]' Pass the SQL string to the query[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   qdf.SQL = strSQL[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]' Turn off screen updating[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   DoCmd.Echo False[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]' Check the state of the query and close it if it is open[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   If Application.SysCmd(acSysCmdGetObjectState, acQuery, "qryCall0CumulativeInternalSplitTOTALS") = acObjStateOpen Then[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       DoCmd.Close acQuery, "qryCall0CumulativeInternalSplitTOTALS"[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   End If[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]' Open the query[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   DoCmd.OpenQuery "qryCall0CumulativeInternalSplitTOTALS"[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]cmdOK_Click_exit:[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]' Turn on screen updating[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   DoCmd.Echo True[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]' Clear the object variables[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Set qdf = Nothing[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Set db = Nothing[/COLOR][/FONT]
[COLOR=#000000][FONT=Arial]Exit Sub[/FONT][/COLOR]
[FONT=Arial][COLOR=#000000]cmdOK_Click_err:[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]' Handle errors[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   MsgBox "An unexpected error has occurred." & _[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       vbCrLf & "Please note of the following details:" & _[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       vbCrLf & "Error Number: " & Err.Number & _[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       vbCrLf & "Description: " & Err.Description _[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]       , vbCritical, "Error"[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]   Resume cmdOK_Click_exit[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]End Sub[/COLOR][/FONT]

And the query:

[COLOR=#000000][FONT=Arial]SELECT Call0CumulativeTbl.ID, Call0CumulativeTbl.[/FONT][FONT=Arial]CALLOUTNUM, Call0CumulativeTbl.CALLDATE, Call0CumulativeTbl.CALLTIME, Call0CumulativeTbl.CALLDURATION, Call0CumulativeTbl.NUMBERCALLED, IIf(DCount("CALLOUTNUM","Call0CumulativeTbl"," CALLOUTNUM ='" & CALLTONUM & "'")>0,"Internal","External") AS CallType, Call0CumulativeTbl.[/FONT][FONT=Arial]ADDITIONALUSAGECLASSIFICATION, Call0CumulativeTbl.[[/FONT][FONT=Arial]ROAMINGAREA], Call0CumulativeTbl.[/FONT][FONT=Arial]CHARGEAMOUNT, Call0CumulativeTbl.[/FONT][FONT=Arial]MONTH, IIf(Mid([NUMBERCALLED],4,4)="1111","Osaka",IIf(Mid([CALLTONUM],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][/COLOR]
[FONT=Arial][COLOR=#000000]FROM Call0CumulativeTbl LEFT JOIN PhoneByUserAndCCR ON Call0CumulativeTbl. CALLOUTNUM = PhoneByUserAndCCR.Number[/COLOR][/FONT]
[COLOR=#000000][FONT=Arial]ORDER BY Call0CumulativeTbl.[/FONT][FONT=Arial]CHARGEAMOUNT DESC;[/FONT][/COLOR]

And the dcount function:

[FONT=Arial][COLOR=#000000]CallType: IIf(DCount("CALLOUTNUM ","Call0CumulativeTbl"," CALLOUTNUM ='" & CALLTONUM & "'")>0,"Internal","External")[/COLOR][/FONT]

Paul, thank you:
Not sure if I can upload a database due to the number of posts but say I have the following:
Main table:

[B][COLOR=black][FONT=Verdana]callOutNum            callToNum[/FONT][/COLOR][/B]
[FONT=Arial][COLOR=#000000]080-111-1111                     080-1111-1111[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]080-1111-2222                   080-1234-1234[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]080-1111-2222                   080-1111-2222[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]080-1111-3333                   080-1111-4567[/COLOR][/FONT]

I would want to add an additional field as follows:

[B][COLOR=black][FONT=Verdana]callOutNum            callToNum              Status[/FONT][/COLOR][/B]
[FONT=Arial][COLOR=#000000]080-111-1111                     080-1111-1111                   Internal[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]080-1111-2222                   080-1234-1234                   External[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]080-1111-2222                   080-1111-2222                   Internal[/COLOR][/FONT]
[FONT=Arial][COLOR=#000000]080-1111-3333                   080-1111-4567                   External[/COLOR][/FONT]

Pat, thank you. I’m still trying to get my head around your post and how that would relate to my situation. It’s interesting what you say about not needing to build the query in VBA.

 

[gemma-the-husky]

Kir

can you clarify - what are you actually trying to do.

given a set of internal and external phone numbers (i take it internal has 1111 as the second set of numbers?) what are trying to with them.

it sounds to me like you can get what you want with a relatively simple query - even if it may be a select distinct with a cartesian join.

 

[KirRoyale]

Thank you for the post.
For each of my 120,000+ rows I want to identify whether the number called (callToNum) is also one of the company mobiles (callOutNum). For calls to ‘external’ numbers (those to numbers other than company mobiles – or branch offices, which is part 2 of the problem - especially the long, expensive calls or frequently called numbers) further analysis will be conducted.
Does that make sense?

 

[Guus2005]

There are several ways to improve query performance:
1. Domain functiona are very slow, don't use them in queries. If you must, use a faster equivalent like TLOOKUP and the likes
2. Don't use immediate ifs (IIF), join a table instead.

If that still isn't fast enough and you want to fine-tune your query:
3. Use the SQL Server like Showplan, the JetShowPlan


HTH

Share & Enjoy!

 

[Pat Hartman]

Even TLOOKUP should not be used in a query. While it is more efficient than the equivalent domain function, it still runs a new query for each row in the base query. Create totals queries to count/sum the records and then join to the totals queries.

 

[gemma-the-husky]

first index the callto num, and calloutnum

now have a table of company phone numbers. make sure they are formatted exactly as in this table. add other stuff you need like phone type, phone user etc

now join the tables together as you require to establish what you want, left joins/ right joins etc to establish unmatched (ie external numbers), and so forth.

if you need it use split to divide the phone number

given 080-1111-1234

split([callto],"-")(0) is 080
split([callto],"-")(1) is 1111
split([callto],"-")(2) is 1234

hope this helps

 

[KirRoyale]

After a lot of trial and error, I was able to create a new table of just the ‘calloutnum’ records and join that to the main table (joining the ‘callto’ field on the main table with the ‘callout’ field on the newly created table) and adding the extra fields e.g.:

[FONT=Arial]CallType: IIf(IsNull([Listofcalloutnum].[calloutnum]),"External","Internal")[/FONT]

and THEN performing the joins to other tables to include the extra info such as user and department to which each phone is allocated.
After also ensuring that the ‘calloutnum’ and ‘callto’ fields were indexed, the query seemed to work much better.
I was then able to incorporate the new query into the form and the final query response time is much, much faster.
The ‘split’ to divide the phone numbers is a nice trick to know!
Thank you all for your help with this!