Query Time

[aftabn10]

I was just wondering why some queries in Access take ages to load and if their is anything i can do to quicken this. Btw the queries are being run on my local machine.

If somebody could please.

Thanks in advance

 

[Sephiroth0327]

A number of factors come in to play in regards to query runtime. If you are working with extremely large datasources and your query is pulling from multiple subqueries, then this can certainly affect performance.

If you provide the SQL of one of your queries which is taking a long time, I can take a quick look to see if there is anything that can be done to speed it up (also include a brief summary of the tables/other queries involved). Or better yet, could you attach a sample Access database?

 

[gemma-the-husky]

are you calling functions within the query - depending what they do, that may have an effect.

how long is ages, though? ....

 

[aftabn10]

I am running alot of queries based on subqueries, but in total i am dealing with a max of 3600 rows of data. Also when i say ages, its taking atleast 45 mins for each query to run.

I have attached an example of a query that i am running that is from another query that i have created but this takes 45 minutes to load up.

SELECT AHTVolData_TopMidBott.[Friendly Queue Name], Count(AHTVolData_TopMidBott.[Friendly Queue Name]) AS [Count Advisors], Sum(AHTVolData_TopMidBott.Top) AS [Count Top Advisors], Sum(AHTVolData_TopMidBott.Mid) AS [Count Mid Advisors], Sum(AHTVolData_TopMidBott.Bott) AS [Count Bott Advisors]
FROM AHTVolData_TopMidBott
GROUP BY AHTVolData_TopMidBott.[Friendly Queue Name];

Hope this gives abit more of an explanation... Thanks for your help guys.

 

[Sephiroth0327]

Wow - 45 minutes is a bit ridiculous. A couple questions come to mind:

• How many levels of subqueries do you have? I am assuming this query points to subqueries and those subqueries are themselves pointing to tables. Is this correct?
• Are the tables that you are pulling from on your local machine? Or are you pulling from external data sources (SQL Server, for example)
• The query you posted above is very simple, so that in itself shouldn't be causing these delays. What are you subqueries doing? Are they running functions, as Gemma mentioned, or doing anything else "complicated"?

 

[aftabn10]

Sephiroth0327, the query i pasted (previous post) is based on the following query (sub query) which is a combination of 4 queries, so i dont know if this is the problem.

SELECT [2 AHTVolData_QRank].[Agent EIN], [2 AHTVolData_QRank].[Agent First Name], [2 AHTVolData_QRank].[Agent Last Name], [2 AHTVolData_QRank].OUC, [2 AHTVolData_QRank].LOB, [2 AHTVolData_QRank].[T2R/L2C], [2 AHTVolData_QRank].[Friendly Queue Name], [2 AHTVolData_QRank].[Interaction Type], [2 AHTVolData_QRank].[Volume Count], [2 AHTVolData_QRank].[Agent AHT for this Q], [2 AHTVolData_QRank].[Advisor Total Handle Time for these Items], [2 AHTVolData_QRank].[% of Agents work on this Q], [% of Agents work on this Q]*([TMB]) AS [Prod Score for Q], [Prod Score for Q] AS [PROD SCORE], [2 AHTVolData_QRank].[Q Rank], [3 AHTVolData_OutOf].[Out Of], ([Top]+[Mid]+[Bott]) AS TMB, IIf([Q Rank]<=([Out of]/3),1,0) AS [Top], IIf([Top]+[Bott]=0,2,0) AS Mid, IIf([Count Advisors]<3,0,(IIf([Q Rank]>(([Count Advisors]/3)*2),3,0))) AS Bott, AHTVolData_THT.[Total Handle Time for Advisor]
FROM (([2 AHTVolData_QRank] INNER JOIN HIDE ON [2 AHTVolData_QRank].[Friendly Queue Name] = HIDE.[Friendly Queue Name]) INNER JOIN [3 AHTVolData_OutOf] ON [2 AHTVolData_QRank].[Friendly Queue Name] = [3 AHTVolData_OutOf].[Friendly Queue Name]) INNER JOIN AHTVolData_THT ON [2 AHTVolData_QRank].[Agent EIN] = AHTVolData_THT.[Agent EIN];

The 4 queries are based on results of the original table. Also the tables are on my local machine and in total I have just 2 tables, 1 has 3600 rows and the other 2900.

Hope that explains a bit more.

 

[Sephiroth0327]

This query appears to also be pretty straightforward. If I am understanding you correctly, you have a main query which pulls from a subquery which pulls from 4 subqueries, correct?

I have run into performance issues when using subqueries this way, but no where near 45 minutes. How about those 4 other sub-subqueries that this subquery is pulling from? What exactly are they doing? With it being 45 minutes, it makes me think you are doing some type of complicated lookup/calculations.

If you think about it, multiple levels of subqueries can affect performance. When you run the main query, it then needs to launch the subquery which then launches the 4 sub-subqueries. The database will hang until it can work its way through everything and I've noticed Access doesn't seem to like this very much.

Can you post a sample db so I can look at it first hand? Perhaps we can eliminate one level of those subqueries, depending on what you actually need them to do.

 

[gemma-the-husky]

are you running on a local machine.

you arent doing this on a remote data table, are you?

===========
sorry - you said you were on a local PC


how long do the 4 subqueries take to run? separately , I mean?

how many rows do you get in the final query?