Why is my query taking so long?

  • Thread starter Thread starter Amin
  • Start date Start date
A

Amin

So this is my query:

SELECT "a 15 Minutes" AS Category,
ROUND((COUNT(*) / (Forms!Switchie!Days)),1) AS [Workloads Completed]
FROM [Strategy Fix 1]
WHERE (QUEUETIME < 15)
UNION
SELECT "b 30 Minutes" AS Category,
ROUND((COUNT(*) / (Forms!Switchie!Days)),1) AS [Workloads Completed]
FROM [Strategy Fix 1]
WHERE (QUEUETIME BETWEEN 15 AND 30)
UNION
SELECT "c 45 Minutes" AS Category,
ROUND((COUNT(*) / (Forms!Switchie!Days)),1) AS [Workloads Completed]
FROM [Strategy Fix 1]
WHERE (QUEUETIME BETWEEN 30 AND 45)
UNION
SELECT "d 1 hour" AS Category,
ROUND((COUNT(*) / (Forms!Switchie!Days)),1) AS [Workloads Completed]
FROM [Strategy Fix 1]
WHERE (QUEUETIME BETWEEN 45 AND 60)
UNION
SELECT "e Over 1 hour" AS Category,
ROUND((COUNT(*) / (Forms!Switchie!Days)),1) AS [Workloads Completed]
FROM [Strategy Fix 1]
WHERE (QUEUETIME > 60)
ORDER BY Category;


What I want to see is how many workloads per day is seen with different
queue times. The form refers to the number of business days between two start
dates. When I run this without the unions, just the first queuetime under 15,
it finishes in a few seconds, but with the unions it takes forever (I know it
should take longer, but it's on the order of ten minutes). Why is this, and
is there a way I can get around it?

Thanks so much for any ideas,
Amin
 
Amin said:
So this is my query:

SELECT "a 15 Minutes" AS Category,
ROUND((COUNT(*) / (Forms!Switchie!Days)),1) AS [Workloads Completed]
FROM [Strategy Fix 1]
WHERE (QUEUETIME < 15)
UNION
Click to expand...
Because UNIONs take a long time. Especially when you force the query
engine to eliminate duplicate rows by leaving out the ALL keyword.
 
What do you mean by the ALL keyword? And do you have any suggestions? Should
I run it in four different queries and then JOIN them?


Bob Barrows said:
Amin said:
So this is my query:

SELECT "a 15 Minutes" AS Category,
ROUND((COUNT(*) / (Forms!Switchie!Days)),1) AS [Workloads Completed]
FROM [Strategy Fix 1]
WHERE (QUEUETIME < 15)
UNION
Click to expand...
Because UNIONs take a long time. Especially when you force the query
engine to eliminate duplicate rows by leaving out the ALL keyword.
Click to expand...
 
Your query has to touch the table many times, which is one cause of a slow
response time.

If you use Jet, you can consider using a crosstab and pivot using the
Partition function.

In Northwind:

TRANSFORM 100*Count(Orders.OrderID)/DCount("*", "Orders") AS CountOfOrderID
SELECT Orders.EmployeeID, Count(Orders.OrderID) AS [Total Of OrderID]
FROM Orders
GROUP BY Orders.EmployeeID
PIVOT Partition([OrderDate], #11/1/1996#, #5/1/1998#, 45)


to get a % of the total count, per employee, per period of 45 days, and
which has the advantage to touch the table just once.

Vanderghast, Access MVP

Amin said:
What do you mean by the ALL keyword? And do you have any suggestions?
Should
I run it in four different queries and then JOIN them?


Bob Barrows said:
Amin said:
So this is my query:

SELECT "a 15 Minutes" AS Category,
ROUND((COUNT(*) / (Forms!Switchie!Days)),1) AS [Workloads Completed]
FROM [Strategy Fix 1]
WHERE (QUEUETIME < 15)
UNION
Click to expand...
Because UNIONs take a long time. Especially when you force the query
engine to eliminate duplicate rows by leaving out the ALL keyword.
Click to expand...
Click to expand...
 
Instead of
SELECT ... UNION SELECT ...
Use
SELECT ... UNION ALL SELECT ...

Only use this when
a) you know the select statements cannot be creating duplicates
b) you don't want duplicates eliminated

Your Category field is preventing the creation of duplicates, so use the
ALL keyword.

It's been a while, but doesn't Access have a Partition function that can
be used in this situation?
What do you mean by the ALL keyword? And do you have any suggestions?
Should I run it in four different queries and then JOIN them?


Bob Barrows said:
Amin said:
So this is my query:

SELECT "a 15 Minutes" AS Category,
ROUND((COUNT(*) / (Forms!Switchie!Days)),1) AS [Workloads Completed]
FROM [Strategy Fix 1]
WHERE (QUEUETIME < 15)
UNION
Click to expand...
Because UNIONs take a long time. Especially when you force the query
engine to eliminate duplicate rows by leaving out the ALL keyword.
Click to expand...
Click to expand...
 
Try this version as an alternative:

SELECT Nz(Switch(QUEUETIME < 15, "a 15 Minutes", QUEUETIME < 30, "b 30
Minutes", QUEUETIME < 45, "c 45 Minutes", QUEUETIME < 60, "d 1 Hour"), "e
Over 1 Hour) As Category, SUM(ROUND((COUNT(*) / (Forms!Switchie!Days)),1))
AS [Workloads Completed]
FROM [Strategy Fix 1] GROUP BY Nz(Switch(QUEUETIME < 15, "a 15 Minutes",
QUEUETIME < 30, "b 30 Minutes", QUEUETIME < 45, "c 45 Minutes", QUEUETIME <
60, "d 1 Hour"), "e Over 1 Hour) ORDER BY Category;

Not sure my syntax is absolutely correct, but basically it is a totals query
rather than a bunch of unions
 
Thank you for your responses. So I'm trying to use a crosstab query with a
partition, but I am new to these functions and I'm having trouble. So what I
have is a table with a field that measures queue time, and what I would like
to do is count the number of records that have a queue time less than 15
minutes, between 15 minutes and 30 minutes, 30-45, 45-60, and over 60. My
table uses a form with a StartDate and EndDate to narrow the records.

So the best way to do this is with a Partition function and a crosstab query.
One problem with the partition function is how do I group the number that
are over 60? Do I need a ridiculously long query?

This is what I have:

TRANSFORM COUNT(*) AS Occurences
SELECT Queuetime, COUNT(*)
FROM [Strategy Fix 1]
GROUP BY QueueTime
PIVOT Partition([QueueTime], 0, 60, 15)

However, I am prompted with a message that says Access does not recognize my
Forms!Switchie!StartDate as a valid field or expression.

Can you tell me what's wrong with my code?

Thanks for the help,
Amin

Michel Walsh said:
Your query has to touch the table many times, which is one cause of a slow
response time.

If you use Jet, you can consider using a crosstab and pivot using the
Partition function.

In Northwind:

TRANSFORM 100*Count(Orders.OrderID)/DCount("*", "Orders") AS CountOfOrderID
SELECT Orders.EmployeeID, Count(Orders.OrderID) AS [Total Of OrderID]
FROM Orders
GROUP BY Orders.EmployeeID
PIVOT Partition([OrderDate], #11/1/1996#, #5/1/1998#, 45)


to get a % of the total count, per employee, per period of 45 days, and
which has the advantage to touch the table just once.

Vanderghast, Access MVP

Amin said:
What do you mean by the ALL keyword? And do you have any suggestions?
Should
I run it in four different queries and then JOIN them?


Bob Barrows said:
Amin wrote:
So this is my query:

SELECT "a 15 Minutes" AS Category,
ROUND((COUNT(*) / (Forms!Switchie!Days)),1) AS [Workloads Completed]
FROM [Strategy Fix 1]
WHERE (QUEUETIME < 15)
UNION
Because UNIONs take a long time. Especially when you force the query
engine to eliminate duplicate rows by leaving out the ALL keyword.
Click to expand...
Click to expand...
Click to expand...
 
When you use a crosstab, you are obliged to define the data type of each
parameters. As example:


PARAMETERS Forms!Switchie!StartDate DateTime;
TRANSFORM ...



the values over 60 will be under the field labeled 60:


Vanderghast, Access MVP


Amin said:
Thank you for your responses. So I'm trying to use a crosstab query with a
partition, but I am new to these functions and I'm having trouble. So what
I
have is a table with a field that measures queue time, and what I would
like
to do is count the number of records that have a queue time less than 15
minutes, between 15 minutes and 30 minutes, 30-45, 45-60, and over 60. My
table uses a form with a StartDate and EndDate to narrow the records.

So the best way to do this is with a Partition function and a crosstab
query.
One problem with the partition function is how do I group the number that
are over 60? Do I need a ridiculously long query?

This is what I have:

TRANSFORM COUNT(*) AS Occurences
SELECT Queuetime, COUNT(*)
FROM [Strategy Fix 1]
GROUP BY QueueTime
PIVOT Partition([QueueTime], 0, 60, 15)

However, I am prompted with a message that says Access does not recognize
my
Forms!Switchie!StartDate as a valid field or expression.

Can you tell me what's wrong with my code?

Thanks for the help,
Amin

Michel Walsh said:
Your query has to touch the table many times, which is one cause of a
slow
response time.

If you use Jet, you can consider using a crosstab and pivot using the
Partition function.

In Northwind:

TRANSFORM 100*Count(Orders.OrderID)/DCount("*", "Orders") AS
CountOfOrderID
SELECT Orders.EmployeeID, Count(Orders.OrderID) AS [Total Of OrderID]
FROM Orders
GROUP BY Orders.EmployeeID
PIVOT Partition([OrderDate], #11/1/1996#, #5/1/1998#, 45)


to get a % of the total count, per employee, per period of 45 days, and
which has the advantage to touch the table just once.

Vanderghast, Access MVP

Amin said:
What do you mean by the ALL keyword? And do you have any suggestions?
Should
I run it in four different queries and then JOIN them?


:

Amin wrote:
So this is my query:

SELECT "a 15 Minutes" AS Category,
ROUND((COUNT(*) / (Forms!Switchie!Days)),1) AS [Workloads Completed]
FROM [Strategy Fix 1]
WHERE (QUEUETIME < 15)
UNION
Because UNIONs take a long time. Especially when you force the query
engine to eliminate duplicate rows by leaving out the ALL keyword.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top