Very slow query

P

Pietro

Hi,
The below query runs very slowly,does anybody have an idea about
improvement areas ?

SELECT CapAll.ID AS Code, CapAll.[Service Request] AS SR, CapAll.Subject,
CapAll.Turn, CapAll.Language, CapAll.Received, CapAll.Replied,
CapAll.Delayed, IIf([delayed]=1,"Delayed","On Time") AS Tipo,
IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs CBR") AS Class,
Format(IIf((DateDiff("s",[received],Now())/3600)>24,0,24-(DateDiff("s",[received],Now())/3600)),"00.00000")
AS HRS, Format(Trim(Mid([HRS],InStr([HRS],".")+1/100000))*60,"00.00000") AS
MNT, Format(Trim(Mid([MNT],InStr([MNT],".")+1/100000))*60,"00.00000") AS SCS,
Left([hrs],2) & ":" & Left([mnt],2) & ":" & Left([scs],2) AS TimeLeft,
IIf([subject]="XCD CAR CONTACT","Care Contact","Escalations") AS Type
FROM CapAll
WHERE (((CapAll.Language)=[forms]![queue]![language2]) AND
((IIf([delayed]=1,"Delayed","On Time"))=[forms]![queue]![Tipo2]) AND
((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((IIf([subject]="XCD CAR CONTACT","Care
Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open")) OR (((IIf([delayed]=1,"Delayed","On
Time"))=[forms]![queue]![Tipo2]) AND
((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((IIf([subject]="XCD CAR CONTACT","Care
Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![language2]) Is Null)) OR
(((CapAll.Language)=[forms]![queue]![language2]) AND
((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((IIf([subject]="XCD CAR CONTACT","Care
Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![Tipo2]) Is Null)) OR
(((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((IIf([subject]="XCD CAR CONTACT","Care
Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![language2]) Is Null) AND
(([forms]![queue]![Tipo2]) Is Null)) OR
(((CapAll.Language)=[forms]![queue]![language2]) AND
((IIf([delayed]=1,"Delayed","On Time"))=[forms]![queue]![Tipo2]) AND
((IIf([subject]="XCD CAR CONTACT","Care
Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![Class2]) Is Null)) OR
(((IIf([delayed]=1,"Delayed","On Time"))=[forms]![queue]![Tipo2]) AND
((IIf([subject]="XCD CAR CONTACT","Care
Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![language2]) Is Null) AND
(([forms]![queue]![Class2]) Is Null)) OR
(((CapAll.Language)=[forms]![queue]![language2]) AND ((IIf([subject]="XCD CAR
CONTACT","Care Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![Tipo2]) Is Null) AND
(([forms]![queue]![Class2]) Is Null)) OR (((IIf([subject]="XCD CAR
CONTACT","Care Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![language2]) Is Null) AND
(([forms]![queue]![Tipo2]) Is Null) AND (([forms]![queue]![Class2]) Is Null))
OR (((CapAll.Language)=[forms]![queue]![language2]) AND
((IIf([delayed]=1,"Delayed","On Time"))=[forms]![queue]![Tipo2]) AND
((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((CapAll.Status)="Open") AND
(([forms]![queue]![Type2]) Is Null)) OR (((IIf([delayed]=1,"Delayed","On
Time"))=[forms]![queue]![Tipo2]) AND
((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((CapAll.Status)="Open") AND
(([forms]![queue]![language2]) Is Null) AND (([forms]![queue]![Type2]) Is
Null)) OR (((CapAll.Language)=[forms]![queue]![language2]) AND
((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((CapAll.Status)="Open") AND
(([forms]![queue]![Tipo2]) Is Null) AND (([forms]![queue]![Type2]) Is Null))
OR (((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((CapAll.Status)="Open") AND
(([forms]![queue]![language2]) Is Null) AND (([forms]![queue]![Tipo2]) Is
Null) AND (([forms]![queue]![Type2]) Is Null)) OR
(((CapAll.Language)=[forms]![queue]![language2]) AND
((IIf([delayed]=1,"Delayed","On Time"))=[forms]![queue]![Tipo2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![Class2]) Is Null) AND
(([forms]![queue]![Type2]) Is Null)) OR (((IIf([delayed]=1,"Delayed","On
Time"))=[forms]![queue]![Tipo2]) AND ((CapAll.Status)="Open") AND
(([forms]![queue]![language2]) Is Null) AND (([forms]![queue]![Class2]) Is
Null) AND (([forms]![queue]![Type2]) Is Null)) OR
(((CapAll.Language)=[forms]![queue]![language2]) AND ((CapAll.Status)="Open")
AND (([forms]![queue]![Tipo2]) Is Null) AND (([forms]![queue]![Class2]) Is
Null) AND (([forms]![queue]![Type2]) Is Null)) OR (((CapAll.Status)="Open")
AND (([forms]![queue]![language2]) Is Null) AND (([forms]![queue]![Tipo2]) Is
Null) AND (([forms]![queue]![Class2]) Is Null) AND (([forms]![queue]![Type2])
Is Null))
ORDER BY CapAll.Received;
 
A

Arvin Meyer [MVP]

Run 2 queries instead of 1. In the first use the criteria (the Where clause)
to limit the data that the subsequent query works on. In any case, it will
be slow, because your criteria includes many IIf() functions. Limiting the
data fed to the functions can produce some increase in performance.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Pietro said:
Hi,
The below query runs very slowly,does anybody have an idea about
improvement areas ?

SELECT CapAll.ID AS Code, CapAll.[Service Request] AS SR, CapAll.Subject,
CapAll.Turn, CapAll.Language, CapAll.Received, CapAll.Replied,
CapAll.Delayed, IIf([delayed]=1,"Delayed","On Time") AS Tipo,
IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs CBR") AS
Class,
Format(IIf((DateDiff("s",[received],Now())/3600)>24,0,24-(DateDiff("s",[received],Now())/3600)),"00.00000")
AS HRS, Format(Trim(Mid([HRS],InStr([HRS],".")+1/100000))*60,"00.00000")
AS
MNT, Format(Trim(Mid([MNT],InStr([MNT],".")+1/100000))*60,"00.00000") AS
SCS,
Left([hrs],2) & ":" & Left([mnt],2) & ":" & Left([scs],2) AS TimeLeft,
IIf([subject]="XCD CAR CONTACT","Care Contact","Escalations") AS Type
FROM CapAll
WHERE (((CapAll.Language)=[forms]![queue]![language2]) AND
((IIf([delayed]=1,"Delayed","On Time"))=[forms]![queue]![Tipo2]) AND
((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((IIf([subject]="XCD CAR
CONTACT","Care
Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open")) OR (((IIf([delayed]=1,"Delayed","On
Time"))=[forms]![queue]![Tipo2]) AND
((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((IIf([subject]="XCD CAR
CONTACT","Care
Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![language2]) Is Null)) OR
(((CapAll.Language)=[forms]![queue]![language2]) AND
((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((IIf([subject]="XCD CAR
CONTACT","Care
Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![Tipo2]) Is Null)) OR
(((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((IIf([subject]="XCD CAR
CONTACT","Care
Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![language2]) Is Null) AND
(([forms]![queue]![Tipo2]) Is Null)) OR
(((CapAll.Language)=[forms]![queue]![language2]) AND
((IIf([delayed]=1,"Delayed","On Time"))=[forms]![queue]![Tipo2]) AND
((IIf([subject]="XCD CAR CONTACT","Care
Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![Class2]) Is Null)) OR
(((IIf([delayed]=1,"Delayed","On Time"))=[forms]![queue]![Tipo2]) AND
((IIf([subject]="XCD CAR CONTACT","Care
Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![language2]) Is Null) AND
(([forms]![queue]![Class2]) Is Null)) OR
(((CapAll.Language)=[forms]![queue]![language2]) AND ((IIf([subject]="XCD
CAR
CONTACT","Care Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![Tipo2]) Is Null) AND
(([forms]![queue]![Class2]) Is Null)) OR (((IIf([subject]="XCD CAR
CONTACT","Care Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![language2]) Is Null) AND
(([forms]![queue]![Tipo2]) Is Null) AND (([forms]![queue]![Class2]) Is
Null))
OR (((CapAll.Language)=[forms]![queue]![language2]) AND
((IIf([delayed]=1,"Delayed","On Time"))=[forms]![queue]![Tipo2]) AND
((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((CapAll.Status)="Open") AND
(([forms]![queue]![Type2]) Is Null)) OR (((IIf([delayed]=1,"Delayed","On
Time"))=[forms]![queue]![Tipo2]) AND
((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((CapAll.Status)="Open") AND
(([forms]![queue]![language2]) Is Null) AND (([forms]![queue]![Type2]) Is
Null)) OR (((CapAll.Language)=[forms]![queue]![language2]) AND
((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((CapAll.Status)="Open") AND
(([forms]![queue]![Tipo2]) Is Null) AND (([forms]![queue]![Type2]) Is
Null))
OR (((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((CapAll.Status)="Open") AND
(([forms]![queue]![language2]) Is Null) AND (([forms]![queue]![Tipo2]) Is
Null) AND (([forms]![queue]![Type2]) Is Null)) OR
(((CapAll.Language)=[forms]![queue]![language2]) AND
((IIf([delayed]=1,"Delayed","On Time"))=[forms]![queue]![Tipo2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![Class2]) Is Null) AND
(([forms]![queue]![Type2]) Is Null)) OR (((IIf([delayed]=1,"Delayed","On
Time"))=[forms]![queue]![Tipo2]) AND ((CapAll.Status)="Open") AND
(([forms]![queue]![language2]) Is Null) AND (([forms]![queue]![Class2]) Is
Null) AND (([forms]![queue]![Type2]) Is Null)) OR
(((CapAll.Language)=[forms]![queue]![language2]) AND
((CapAll.Status)="Open")
AND (([forms]![queue]![Tipo2]) Is Null) AND (([forms]![queue]![Class2]) Is
Null) AND (([forms]![queue]![Type2]) Is Null)) OR
(((CapAll.Status)="Open")
AND (([forms]![queue]![language2]) Is Null) AND (([forms]![queue]![Tipo2])
Is
Null) AND (([forms]![queue]![Class2]) Is Null) AND
(([forms]![queue]![Type2])
Is Null))
ORDER BY CapAll.Received;
Click to expand...
 
J

Jeff Boyce

Pietro

In addition to Arvin's suggestion, check on the indexing of any field use
for:
* selection
* sorting
* joining between tables (probably not applicable for this query)

Also, you don't mention where the data is stored. Is the back-end/data in
MS Access, or SQL-Server, or ...?

If the back-end is other than MS Access, it may be that functions like
"DateDiff()" are NOT part of that back-end's repetoire. If you get the data
from the back-end first (similar to Arvin's suggestion), then create another
query that applies local-only functions (?DateDiff()?), it should run
faster...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Pietro said:
Hi,
The below query runs very slowly,does anybody have an idea about
improvement areas ?

SELECT CapAll.ID AS Code, CapAll.[Service Request] AS SR, CapAll.Subject,
CapAll.Turn, CapAll.Language, CapAll.Received, CapAll.Replied,
CapAll.Delayed, IIf([delayed]=1,"Delayed","On Time") AS Tipo,
IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs CBR") AS
Class,
Format(IIf((DateDiff("s",[received],Now())/3600)>24,0,24-(DateDiff("s",[received],Now())/3600)),"00.00000")
AS HRS, Format(Trim(Mid([HRS],InStr([HRS],".")+1/100000))*60,"00.00000")
AS
MNT, Format(Trim(Mid([MNT],InStr([MNT],".")+1/100000))*60,"00.00000") AS
SCS,
Left([hrs],2) & ":" & Left([mnt],2) & ":" & Left([scs],2) AS TimeLeft,
IIf([subject]="XCD CAR CONTACT","Care Contact","Escalations") AS Type
FROM CapAll
WHERE (((CapAll.Language)=[forms]![queue]![language2]) AND
((IIf([delayed]=1,"Delayed","On Time"))=[forms]![queue]![Tipo2]) AND
((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((IIf([subject]="XCD CAR
CONTACT","Care
Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open")) OR (((IIf([delayed]=1,"Delayed","On
Time"))=[forms]![queue]![Tipo2]) AND
((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((IIf([subject]="XCD CAR
CONTACT","Care
Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![language2]) Is Null)) OR
(((CapAll.Language)=[forms]![queue]![language2]) AND
((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((IIf([subject]="XCD CAR
CONTACT","Care
Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![Tipo2]) Is Null)) OR
(((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((IIf([subject]="XCD CAR
CONTACT","Care
Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![language2]) Is Null) AND
(([forms]![queue]![Tipo2]) Is Null)) OR
(((CapAll.Language)=[forms]![queue]![language2]) AND
((IIf([delayed]=1,"Delayed","On Time"))=[forms]![queue]![Tipo2]) AND
((IIf([subject]="XCD CAR CONTACT","Care
Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![Class2]) Is Null)) OR
(((IIf([delayed]=1,"Delayed","On Time"))=[forms]![queue]![Tipo2]) AND
((IIf([subject]="XCD CAR CONTACT","Care
Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![language2]) Is Null) AND
(([forms]![queue]![Class2]) Is Null)) OR
(((CapAll.Language)=[forms]![queue]![language2]) AND ((IIf([subject]="XCD
CAR
CONTACT","Care Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![Tipo2]) Is Null) AND
(([forms]![queue]![Class2]) Is Null)) OR (((IIf([subject]="XCD CAR
CONTACT","Care Contact","Escalations"))=[forms]![queue]![Type2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![language2]) Is Null) AND
(([forms]![queue]![Tipo2]) Is Null) AND (([forms]![queue]![Class2]) Is
Null))
OR (((CapAll.Language)=[forms]![queue]![language2]) AND
((IIf([delayed]=1,"Delayed","On Time"))=[forms]![queue]![Tipo2]) AND
((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((CapAll.Status)="Open") AND
(([forms]![queue]![Type2]) Is Null)) OR (((IIf([delayed]=1,"Delayed","On
Time"))=[forms]![queue]![Tipo2]) AND
((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((CapAll.Status)="Open") AND
(([forms]![queue]![language2]) Is Null) AND (([forms]![queue]![Type2]) Is
Null)) OR (((CapAll.Language)=[forms]![queue]![language2]) AND
((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((CapAll.Status)="Open") AND
(([forms]![queue]![Tipo2]) Is Null) AND (([forms]![queue]![Type2]) Is
Null))
OR (((IIf(Mid([Turn],1,13)="Investigation","Investigation","24hrs
CBR"))=[forms]![queue]![Class2]) AND ((CapAll.Status)="Open") AND
(([forms]![queue]![language2]) Is Null) AND (([forms]![queue]![Tipo2]) Is
Null) AND (([forms]![queue]![Type2]) Is Null)) OR
(((CapAll.Language)=[forms]![queue]![language2]) AND
((IIf([delayed]=1,"Delayed","On Time"))=[forms]![queue]![Tipo2]) AND
((CapAll.Status)="Open") AND (([forms]![queue]![Class2]) Is Null) AND
(([forms]![queue]![Type2]) Is Null)) OR (((IIf([delayed]=1,"Delayed","On
Time"))=[forms]![queue]![Tipo2]) AND ((CapAll.Status)="Open") AND
(([forms]![queue]![language2]) Is Null) AND (([forms]![queue]![Class2]) Is
Null) AND (([forms]![queue]![Type2]) Is Null)) OR
(((CapAll.Language)=[forms]![queue]![language2]) AND
((CapAll.Status)="Open")
AND (([forms]![queue]![Tipo2]) Is Null) AND (([forms]![queue]![Class2]) Is
Null) AND (([forms]![queue]![Type2]) Is Null)) OR
(((CapAll.Status)="Open")
AND (([forms]![queue]![language2]) Is Null) AND (([forms]![queue]![Tipo2])
Is
Null) AND (([forms]![queue]![Class2]) Is Null) AND
(([forms]![queue]![Type2])
Is Null))
ORDER BY CapAll.Received;
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Change the form source through a VB code 2
External field in a query 5
If Statement with "*" in Query 2
Help with Aggregate Query 6
Complex query coding Question 7
Problem with my query expression 5
open a form with a union query 0
open a subform or hide it based on query results 15

Top