Help with Query Performance 1

[scottyjohn]

Hi all,
Sorry in advance for the size of this query! It was the only way I knew how to achieve what I wanted. The problem is that although the query eventually returns the desired result, it takes approximately 11 minutes to run. Im using access as a front end to a number of linked tables on the same remote Oracle DB. I am displaying the results of this query in a subform on a main form. its the only query running on that form. Any help you guys can give about speeding up my query would be much appreciated as my knowledge of tuning is very limited!

SELECT "GLASGOW" AS SITE, tblBUSINESS_GROUPS.ALIAS AS [BUSINESS GROUP], Count(tblBUSINESS_GROUPS.ALIAS) AS CALLS
FROM ((SOFTPHONE INNER JOIN qryAGENTSGLASGOW ON SOFTPHONE.EMPID = qryAGENTSGLASGOW.EMPLOYEE_ID) INNER JOIN ROUTING ON SOFTPHONE.PRODUCT = ROUTING.PRODUCT) INNER JOIN tblBUSINESS_GROUPS ON ROUTING.BUSINESS_GROUP = tblBUSINESS_GROUPS.ALIAS
GROUP BY "GLASGOW", tblBUSINESS_GROUPS.ALIAS, SOFTPHONE.STARTD, SOFTPHONE.DIRECTION
HAVING (((SOFTPHONE.STARTD)=[Forms]![frmSTART]![txtOVERFLOWDATE]) AND ((SOFTPHONE.DIRECTION)="I"))
UNION ALL
SELECT "SKYDEALS" AS SITE, tblBUSINESS_GROUPS.ALIAS AS [BUSINESS GROUP], Count(tblBUSINESS_GROUPS.ALIAS) AS CALLS
FROM ((SOFTPHONE INNER JOIN qryAGENTSSKYDEALS ON SOFTPHONE.EMPID = qryAGENTSSKYDEALS.EMPLOYEE_ID) INNER JOIN ROUTING ON SOFTPHONE.PRODUCT = ROUTING.PRODUCT) INNER JOIN tblBUSINESS_GROUPS ON ROUTING.BUSINESS_GROUP = tblBUSINESS_GROUPS.ALIAS
GROUP BY "SKYDEALS", tblBUSINESS_GROUPS.ALIAS, SOFTPHONE.STARTD, SOFTPHONE.DIRECTION
HAVING (((SOFTPHONE.STARTD)=[Forms]![frmSTART]![txtOVERFLOWDATE]) AND ((SOFTPHONE.DIRECTION)="I"))
UNION ALL
SELECT "TFR" AS SITE, tblBUSINESS_GROUPS.ALIAS AS [BUSINESS GROUP], Count(tblBUSINESS_GROUPS.ALIAS) AS CALLS
FROM ((SOFTPHONE INNER JOIN qryAGENTSTFR ON SOFTPHONE.EMPID = qryAGENTSTFR.EMPLOYEE_ID) INNER JOIN ROUTING ON SOFTPHONE.PRODUCT = ROUTING.PRODUCT) INNER JOIN tblBUSINESS_GROUPS ON ROUTING.BUSINESS_GROUP = tblBUSINESS_GROUPS.ALIAS
GROUP BY "TFR", tblBUSINESS_GROUPS.ALIAS, SOFTPHONE.STARTD, SOFTPHONE.DIRECTION
HAVING (((SOFTPHONE.STARTD)=[Forms]![frmSTART]![txtOVERFLOWDATE]) AND ((SOFTPHONE.DIRECTION)="I"))
UNION ALL
SELECT "SWANSEA" AS SITE, tblBUSINESS_GROUPS.ALIAS AS [BUSINESS GROUP], Count(tblBUSINESS_GROUPS.ALIAS) AS CALLS
FROM ((SOFTPHONE INNER JOIN qryAGENTSSWANSEA ON SOFTPHONE.EMPID = qryAGENTSSWANSEA.EMPLOYEE_ID) INNER JOIN ROUTING ON SOFTPHONE.PRODUCT = ROUTING.PRODUCT) INNER JOIN tblBUSINESS_GROUPS ON ROUTING.BUSINESS_GROUP = tblBUSINESS_GROUPS.ALIAS
GROUP BY "SWANSEA", tblBUSINESS_GROUPS.ALIAS, SOFTPHONE.STARTD, SOFTPHONE.DIRECTION
HAVING (((SOFTPHONE.STARTD)=[Forms]![frmSTART]![txtOVERFLOWDATE]) AND ((SOFTPHONE.DIRECTION)="I"))
UNION ALL
SELECT "TEAM LINCOLN" AS SITE, tblBUSINESS_GROUPS.ALIAS AS [BUSINESS GROUP], Count(tblBUSINESS_GROUPS.ALIAS) AS CALLS
FROM ((SOFTPHONE INNER JOIN qryAGENTSTEAMLINCOLN ON SOFTPHONE.EMPID = qryAGENTSTEAMLINCOLN.EMPLOYEE_ID) INNER JOIN ROUTING ON SOFTPHONE.PRODUCT = ROUTING.PRODUCT) INNER JOIN tblBUSINESS_GROUPS ON ROUTING.BUSINESS_GROUP = tblBUSINESS_GROUPS.ALIAS
GROUP BY "TEAM LINCOLN", tblBUSINESS_GROUPS.ALIAS, SOFTPHONE.STARTD, SOFTPHONE.DIRECTION
HAVING (((SOFTPHONE.STARTD)=[Forms]![frmSTART]![txtOVERFLOWDATE]) AND ((SOFTPHONE.DIRECTION)="I"))
UNION ALL
SELECT "COVENTRY" AS SITE, tblBUSINESS_GROUPS.ALIAS AS [BUSINESS GROUP], Count(tblBUSINESS_GROUPS.ALIAS) AS CALLS
FROM ((SOFTPHONE INNER JOIN qryAGENTSCOVENTRY ON SOFTPHONE.EMPID = qryAGENTSCOVENTRY.EMPLOYEE_ID) INNER JOIN ROUTING ON SOFTPHONE.PRODUCT = ROUTING.PRODUCT) INNER JOIN tblBUSINESS_GROUPS ON ROUTING.BUSINESS_GROUP = tblBUSINESS_GROUPS.ALIAS
GROUP BY "COVENTRY", tblBUSINESS_GROUPS.ALIAS, SOFTPHONE.STARTD, SOFTPHONE.DIRECTION
HAVING (((SOFTPHONE.STARTD)=[Forms]![frmSTART]![txtOVERFLOWDATE]) AND ((SOFTPHONE.DIRECTION)="I"));
UNION ALL SELECT "RETAIL" AS SITE, tblBUSINESS_GROUPS.ALIAS AS [BUSINESS GROUP], Count(tblBUSINESS_GROUPS.ALIAS) AS CALLS
FROM ((SOFTPHONE INNER JOIN qryAGENTSRETAIL ON SOFTPHONE.EMPID = qryAGENTSRETAIL.EMPLOYEE_ID) INNER JOIN ROUTING ON SOFTPHONE.PRODUCT = ROUTING.PRODUCT) INNER JOIN tblBUSINESS_GROUPS ON ROUTING.BUSINESS_GROUP = tblBUSINESS_GROUPS.ALIAS
GROUP BY "RETAIL", tblBUSINESS_GROUPS.ALIAS, SOFTPHONE.STARTD, SOFTPHONE.DIRECTION
HAVING (((SOFTPHONE.STARTD)=[Forms]![frmSTART]![txtOVERFLOWDATE]) AND ((SOFTPHONE.DIRECTION)="I"));


Thanks in advance

John
ski_69@hotmail.com

 

[AlexMidd]

Sorry in advance for the size of this query!"

I think you answered your own question.

Have fun! )

Alex Middleton

 

[scottyjohn]

Err Thanks Alex,
As explained the query achieves what I want but I have had experience in the past whereby a fresh approach has halved the execution time of the query.

Any help?

John
ski_69@hotmail.com

 

[AlexMidd]

Sorry John, I was being a little mischevous and felt that a large part of the time of the query would be down to its size. As you say, there may be another approach but due to the scale of the query I'm struggling somewhat to see it at the moment.

Have fun! )

Alex Middleton

 

[scottyjohn]

Thanks anyways Alex.

John
ski_69@hotmail.com

 

[PHV]

As you don't use aggregate function as criteria, convert all GROUP BY clauses into WHERE clauses.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[scottyjohn]

Hi guys,
I made the alteration to the structure that I think PHV meant but it doesnt really improve performance. Or did I get it wrong. Ive just shown one snippet of the query this time!

SELECT "RETAIL" AS SITE, tblBUSINESS_GROUPS.ALIAS AS [BUSINESS GROUP], Count(tblBUSINESS_GROUPS.ALIAS) AS CALLS
FROM ((SOFTPHONE INNER JOIN qryAGENTSRETAIL ON SOFTPHONE.EMPID = qryAGENTSRETAIL.EMPLOYEE_ID) INNER JOIN ROUTING ON SOFTPHONE.PRODUCT = ROUTING.PRODUCT) INNER JOIN tblBUSINESS_GROUPS ON ROUTING.BUSINESS_GROUP = tblBUSINESS_GROUPS.ALIAS
WHERE (((SOFTPHONE.STARTD)=[Forms]![frmSTART]![txtOVERFLOWDATE]) AND ((SOFTPHONE.DIRECTION)="I"))
GROUP BY tblBUSINESS_GROUPS.ALIAS;

Am I barking up the wrong tree?

John
ski_69@hotmail.com

 

[JerryKlmns]

scottyjohn,
can't you replace all the qryAGENTS* by a general qryAGENTS with the field "SITE" ? Then that huge query could be

SELECT SITE, t.ALIAS AS [BUSINESS GROUP], Count(t.ALIAS) AS CALLS
FROM ((SOFTPHONE AS S INNER JOIN qryAGENTS AS q
ON S.EMPID = q.EMPLOYEE_ID)
INNER JOIN ROUTING AS R
ON S.PRODUCT = R.PRODUCT)
INNER JOIN tblBUSINESS_GROUPS AS t
ON R.BUSINESS_GROUP = t.ALIAS
WHERE (((S.STARTD)=[Forms]![frmSTART]![txtOVERFLOWDATE])
AND ((S.DIRECTION)="I"))
GROUP BY 1, 2;

Or perhaps a pass-through query running on the Oracle db?

 

[scottyjohn]

Hi JerryKlmns,
I tried your suggestion and it worked fantastically! The original query was taking upwards of 10 minutes to execute, now its down to less than a minute! Thanks for all your help, and a star for your trouble!

John
ski_69@hotmail.com