Query taking too long

R

Ravi

Hi
I are trying to get the average consumption of our vehicles.
A log is entered every 2 days with "Log Date" and Mileage
In a query for number of days i take MaxDate-MinDate and count 'only' the
working days for which a vb function is used

When the query is run ...it takes too much time since it is calculating the
'working days' for each vehicle..the records go back 5 years so it calculates
from Date() to 5 years.
I feel the approach i am using is not very efficient.
Please give me an idea how to speed up the query
Thanks
Ravi
 
J

John Vinson

Hi
I are trying to get the average consumption of our vehicles.
A log is entered every 2 days with "Log Date" and Mileage
In a query for number of days i take MaxDate-MinDate and count 'only' the
working days for which a vb function is used

When the query is run ...it takes too much time since it is calculating the
'working days' for each vehicle..the records go back 5 years so it calculates
from Date() to 5 years.
I feel the approach i am using is not very efficient.
Please give me an idea how to speed up the query
Thanks
Ravi
Click to expand...

Please post a description of your table, and the SQL view of your
current query. Calling a VBA function for every row in a query is
indeed going to be a major performance drag.


John W. Vinson[MVP]
 
R

Ravi

Hi

Table:
VehicleID....VehId
Log Date.....Dt...Date/time
Mileage......Mile..Number

Then i use a query to group vehiclewise

SELECT Table1.VehID, Min(Table1.Dt) AS MinDt, Max(Table1.Dt) AS MaxDt,
Max(Table1.Mile) AS MaxMiles
FROM Table1
GROUP BY Table1.VehID;

The query below is then used for finding the average:
**Countworkdays(startdate,EndDate)...vb function to count the work days

SELECT Avg1qry.VehID, Avg1qry.MaxDt, Avg1qry.MinDt, Avg1qry.MaxMiles,
(CountWorkDays([MinDt],[MaxDt])) AS WkDay, ([MaxMiles]/[WkDay]) AS Average
FROM Avg1qry;

When i run this query..for just 2 records which are 5 yrs old..i.e
startDate= 01/01/2000 and Enddate=31/12/2004..it takes 8 seconds
Ravi
 
J

John Spencer (MVP)

Pardon me for jumping in.

I would try to do this all in one query. The way you are doing it now, the
first query will calculate ALL the data of Min, Max, and MaxMiles for all the
vehicles. The second query will then limit it to the specified records. If you
limit this with a WHERE clause, not a Having clause, there should be a lot less
records involved. Also, indexes on VehID, Mileage, and Dt would probably affect
the speed of the query.


SELECT VehID, Max(Mileage)/CountWorkDays(MIN(Dt),Max(Dt)) as AvgMiles
FROM Table1
WHERE VehID in (VehID_One, VehId_Two)
GROUP BY VehID
Hi

Table:
VehicleID....VehId
Log Date.....Dt...Date/time
Mileage......Mile..Number

Then i use a query to group vehiclewise

SELECT Table1.VehID, Min(Table1.Dt) AS MinDt, Max(Table1.Dt) AS MaxDt,
Max(Table1.Mile) AS MaxMiles
FROM Table1
GROUP BY Table1.VehID;

The query below is then used for finding the average:
**Countworkdays(startdate,EndDate)...vb function to count the work days

SELECT Avg1qry.VehID, Avg1qry.MaxDt, Avg1qry.MinDt, Avg1qry.MaxMiles,
(CountWorkDays([MinDt],[MaxDt])) AS WkDay, ([MaxMiles]/[WkDay]) AS Average
FROM Avg1qry;

When i run this query..for just 2 records which are 5 yrs old..i.e
startDate= 01/01/2000 and Enddate=31/12/2004..it takes 8 seconds
Ravi

John Vinson said:
Please post a description of your table, and the SQL view of your
current query. Calling a VBA function for every row in a query is
indeed going to be a major performance drag.


John W. Vinson[MVP]
Click to expand...
Click to expand...
 
T

Ted Allen

John, I hope you don't mind if I join in on this one, I was reading it during
lunch and it looked like a fun one to play with (I know it is sad that I'm
doing this at lunch - but it's raining out so I thought I would stay in).

Ravi, you didn't really say what your function is doing. If it is something
simple, like just trying to figure out the number of Mon-Fri weekdays during
the period, you may be better off just calculating everything in one query
using built in functions and/or subqueries (example given at end of message -
I assume that you want the query to be exact, otherwise you could get a
pretty good approximation by just multiplying the number of days by 5/7 over
a long time period). If your function is also neglecting holidays or
something like that, you would probably have to post the vba code to get much
help.

In any case, you may also want to check to see if your table1 is indexed by
VehicleID/Date. I would assume this would be set as a primary key, but if not
it could really slow things down.

Anyway, I believe that the following query will give you what you want if
you are just trying to subtract weekends. It basically just counts the
number of work days in the first and last weeks, calculates the number of
whole weeks in between, and uses those to add up the total number of work
days. Many of the fields could be combined into more complex expressions,
but I showed it in pieces to make it a little easier to follow. I'm sure
there is probably a more efficient way to do it, but this is what came to
mind offhand.

John, please feel free to post any additional suggestions or comments, and I
hope you don't mind me jumping in. Ravi, post back if you were trying to do
more than just subtract weekend days or if the following doesn't work for you.

Here is the sql (I believe if you paste the sql text below into the sql view
of a new query, it should work for you without any modifications - assuming
that the table/field names you posted were the actual names):

SELECT Table1.VehID, Min(Table1.Dt) AS MinDt, DLookUp("Mile","Table1","VehID
= " & [VehID] & " AND Dt = #" & [MinDt] & "#") AS MinMile, Max(Table1.Dt) AS
MaxDt, DLookUp("Mile","Table1","VehID = " & [VehID] & " AND Dt = #" & [MaxDt]
& "#") AS MaxMile, MaxMile-MinMile AS TotalMiles, DatePart("w",[minDt]) AS
MinDtWeekday, IIf([MinDtWeekday]=1,5,7-[MinDtWeekday]) AS WorkdaysFirstWeek,
DatePart("w",[maxDt]) AS MaxDtWeekday,
IIf([MaxDtWeekday]=7,5,[MaxDtWeekday]-1) AS WorkdaysLastWeek,
([MaxDt]-[MinDt]-7+[MinDtWeekday]-[MaxDtWeekday])/7 AS NumberWholeWeeks,
[NumberWholeWeeks]*5+[WorkdaysFirstWeek]+[WorkdaysLastWeek] AS TotalWorkdays,
[TotalMiles]/[TotalWorkdays] AS AvgMilesPerWorkday
FROM Table1
GROUP BY Table1.VehID;

HTH, Ted Allen


Ravi said:
Hi

Table:
VehicleID....VehId
Log Date.....Dt...Date/time
Mileage......Mile..Number

Then i use a query to group vehiclewise

SELECT Table1.VehID, Min(Table1.Dt) AS MinDt, Max(Table1.Dt) AS MaxDt,
Max(Table1.Mile) AS MaxMiles
FROM Table1
GROUP BY Table1.VehID;

The query below is then used for finding the average:
**Countworkdays(startdate,EndDate)...vb function to count the work days

SELECT Avg1qry.VehID, Avg1qry.MaxDt, Avg1qry.MinDt, Avg1qry.MaxMiles,
(CountWorkDays([MinDt],[MaxDt])) AS WkDay, ([MaxMiles]/[WkDay]) AS Average
FROM Avg1qry;

When i run this query..for just 2 records which are 5 yrs old..i.e
startDate= 01/01/2000 and Enddate=31/12/2004..it takes 8 seconds
Ravi






John Vinson said:
Please post a description of your table, and the SQL view of your
current query. Calling a VBA function for every row in a query is
indeed going to be a major performance drag.


John W. Vinson[MVP]
Click to expand...
Click to expand...
 
T

Ted Allen

One follow up note, I just thought about the fact that if you did use the
previous sql, you would run into an error if a vehicle only has one entry
(workdays would be 0 and you would be dividing by 0). So, you may want to
replace the workdays calc with the following, which will substitute a 1 in
that case:

TotalWorkdays:
IIf([NumberWholeWeeks]*5+[WorkdaysFirstWeek]+[WorkdaysLastWeek]=0,1,[NumberWholeWeeks]*5+[WorkdaysFirstWeek]+[WorkdaysLastWeek]).

HTH, Ted Allen

Ted Allen said:
John, I hope you don't mind if I join in on this one, I was reading it during
lunch and it looked like a fun one to play with (I know it is sad that I'm
doing this at lunch - but it's raining out so I thought I would stay in).

Ravi, you didn't really say what your function is doing. If it is something
simple, like just trying to figure out the number of Mon-Fri weekdays during
the period, you may be better off just calculating everything in one query
using built in functions and/or subqueries (example given at end of message -
I assume that you want the query to be exact, otherwise you could get a
pretty good approximation by just multiplying the number of days by 5/7 over
a long time period). If your function is also neglecting holidays or
something like that, you would probably have to post the vba code to get much
help.

In any case, you may also want to check to see if your table1 is indexed by
VehicleID/Date. I would assume this would be set as a primary key, but if not
it could really slow things down.

Anyway, I believe that the following query will give you what you want if
you are just trying to subtract weekends. It basically just counts the
number of work days in the first and last weeks, calculates the number of
whole weeks in between, and uses those to add up the total number of work
days. Many of the fields could be combined into more complex expressions,
but I showed it in pieces to make it a little easier to follow. I'm sure
there is probably a more efficient way to do it, but this is what came to
mind offhand.

John, please feel free to post any additional suggestions or comments, and I
hope you don't mind me jumping in. Ravi, post back if you were trying to do
more than just subtract weekend days or if the following doesn't work for you.

Here is the sql (I believe if you paste the sql text below into the sql view
of a new query, it should work for you without any modifications - assuming
that the table/field names you posted were the actual names):

SELECT Table1.VehID, Min(Table1.Dt) AS MinDt, DLookUp("Mile","Table1","VehID
= " & [VehID] & " AND Dt = #" & [MinDt] & "#") AS MinMile, Max(Table1.Dt) AS
MaxDt, DLookUp("Mile","Table1","VehID = " & [VehID] & " AND Dt = #" & [MaxDt]
& "#") AS MaxMile, MaxMile-MinMile AS TotalMiles, DatePart("w",[minDt]) AS
MinDtWeekday, IIf([MinDtWeekday]=1,5,7-[MinDtWeekday]) AS WorkdaysFirstWeek,
DatePart("w",[maxDt]) AS MaxDtWeekday,
IIf([MaxDtWeekday]=7,5,[MaxDtWeekday]-1) AS WorkdaysLastWeek,
([MaxDt]-[MinDt]-7+[MinDtWeekday]-[MaxDtWeekday])/7 AS NumberWholeWeeks,
[NumberWholeWeeks]*5+[WorkdaysFirstWeek]+[WorkdaysLastWeek] AS TotalWorkdays,
[TotalMiles]/[TotalWorkdays] AS AvgMilesPerWorkday
FROM Table1
GROUP BY Table1.VehID;

HTH, Ted Allen


Ravi said:
Hi

Table:
VehicleID....VehId
Log Date.....Dt...Date/time
Mileage......Mile..Number

Then i use a query to group vehiclewise

SELECT Table1.VehID, Min(Table1.Dt) AS MinDt, Max(Table1.Dt) AS MaxDt,
Max(Table1.Mile) AS MaxMiles
FROM Table1
GROUP BY Table1.VehID;

The query below is then used for finding the average:
**Countworkdays(startdate,EndDate)...vb function to count the work days

SELECT Avg1qry.VehID, Avg1qry.MaxDt, Avg1qry.MinDt, Avg1qry.MaxMiles,
(CountWorkDays([MinDt],[MaxDt])) AS WkDay, ([MaxMiles]/[WkDay]) AS Average
FROM Avg1qry;

When i run this query..for just 2 records which are 5 yrs old..i.e
startDate= 01/01/2000 and Enddate=31/12/2004..it takes 8 seconds
Ravi






John Vinson said:
On Sun, 2 Jan 2005 14:15:02 -0800, "Ravi"

Hi
I are trying to get the average consumption of our vehicles.
A log is entered every 2 days with "Log Date" and Mileage
In a query for number of days i take MaxDate-MinDate and count 'only' the
working days for which a vb function is used

When the query is run ...it takes too much time since it is calculating the
'working days' for each vehicle..the records go back 5 years so it calculates
from Date() to 5 years.
I feel the approach i am using is not very efficient.
Please give me an idea how to speed up the query
Thanks
Ravi

Please post a description of your table, and the SQL view of your
current query. Calling a VBA function for every row in a query is
indeed going to be a major performance drag.


John W. Vinson[MVP]
Click to expand...
Click to expand...
Click to expand...
 
R

Ravi

Thanks everyone for your help...the query works well and is fast<1second
compared to the >8seconds earlier...my approach was inefficient,your
suggestions were very helpful.
Ravi

Ted Allen said:
One follow up note, I just thought about the fact that if you did use the
previous sql, you would run into an error if a vehicle only has one entry
(workdays would be 0 and you would be dividing by 0). So, you may want to
replace the workdays calc with the following, which will substitute a 1 in
that case:

TotalWorkdays:
IIf([NumberWholeWeeks]*5+[WorkdaysFirstWeek]+[WorkdaysLastWeek]=0,1,[NumberWholeWeeks]*5+[WorkdaysFirstWeek]+[WorkdaysLastWeek]).

HTH, Ted Allen

Ted Allen said:
John, I hope you don't mind if I join in on this one, I was reading it during
lunch and it looked like a fun one to play with (I know it is sad that I'm
doing this at lunch - but it's raining out so I thought I would stay in).

Ravi, you didn't really say what your function is doing. If it is something
simple, like just trying to figure out the number of Mon-Fri weekdays during
the period, you may be better off just calculating everything in one query
using built in functions and/or subqueries (example given at end of message -
I assume that you want the query to be exact, otherwise you could get a
pretty good approximation by just multiplying the number of days by 5/7 over
a long time period). If your function is also neglecting holidays or
something like that, you would probably have to post the vba code to get much
help.

In any case, you may also want to check to see if your table1 is indexed by
VehicleID/Date. I would assume this would be set as a primary key, but if not
it could really slow things down.

Anyway, I believe that the following query will give you what you want if
you are just trying to subtract weekends. It basically just counts the
number of work days in the first and last weeks, calculates the number of
whole weeks in between, and uses those to add up the total number of work
days. Many of the fields could be combined into more complex expressions,
but I showed it in pieces to make it a little easier to follow. I'm sure
there is probably a more efficient way to do it, but this is what came to
mind offhand.

John, please feel free to post any additional suggestions or comments, and I
hope you don't mind me jumping in. Ravi, post back if you were trying to do
more than just subtract weekend days or if the following doesn't work for you.

Here is the sql (I believe if you paste the sql text below into the sql view
of a new query, it should work for you without any modifications - assuming
that the table/field names you posted were the actual names):

SELECT Table1.VehID, Min(Table1.Dt) AS MinDt, DLookUp("Mile","Table1","VehID
= " & [VehID] & " AND Dt = #" & [MinDt] & "#") AS MinMile, Max(Table1.Dt) AS
MaxDt, DLookUp("Mile","Table1","VehID = " & [VehID] & " AND Dt = #" & [MaxDt]
& "#") AS MaxMile, MaxMile-MinMile AS TotalMiles, DatePart("w",[minDt]) AS
MinDtWeekday, IIf([MinDtWeekday]=1,5,7-[MinDtWeekday]) AS WorkdaysFirstWeek,
DatePart("w",[maxDt]) AS MaxDtWeekday,
IIf([MaxDtWeekday]=7,5,[MaxDtWeekday]-1) AS WorkdaysLastWeek,
([MaxDt]-[MinDt]-7+[MinDtWeekday]-[MaxDtWeekday])/7 AS NumberWholeWeeks,
[NumberWholeWeeks]*5+[WorkdaysFirstWeek]+[WorkdaysLastWeek] AS TotalWorkdays,
[TotalMiles]/[TotalWorkdays] AS AvgMilesPerWorkday
FROM Table1
GROUP BY Table1.VehID;

HTH, Ted Allen


Ravi said:
Hi

Table:
VehicleID....VehId
Log Date.....Dt...Date/time
Mileage......Mile..Number

Then i use a query to group vehiclewise

SELECT Table1.VehID, Min(Table1.Dt) AS MinDt, Max(Table1.Dt) AS MaxDt,
Max(Table1.Mile) AS MaxMiles
FROM Table1
GROUP BY Table1.VehID;

The query below is then used for finding the average:
**Countworkdays(startdate,EndDate)...vb function to count the work days

SELECT Avg1qry.VehID, Avg1qry.MaxDt, Avg1qry.MinDt, Avg1qry.MaxMiles,
(CountWorkDays([MinDt],[MaxDt])) AS WkDay, ([MaxMiles]/[WkDay]) AS Average
FROM Avg1qry;

When i run this query..for just 2 records which are 5 yrs old..i.e
startDate= 01/01/2000 and Enddate=31/12/2004..it takes 8 seconds
Ravi






:

On Sun, 2 Jan 2005 14:15:02 -0800, "Ravi"

Hi
I are trying to get the average consumption of our vehicles.
A log is entered every 2 days with "Log Date" and Mileage
In a query for number of days i take MaxDate-MinDate and count 'only' the
working days for which a vb function is used

When the query is run ...it takes too much time since it is calculating the
'working days' for each vehicle..the records go back 5 years so it calculates
from Date() to 5 years.
I feel the approach i am using is not very efficient.
Please give me an idea how to speed up the query
Thanks
Ravi

Please post a description of your table, and the SQL view of your
current query. Calling a VBA function for every row in a query is
indeed going to be a major performance drag.


John W. Vinson[MVP]
Click to expand...
Click to expand...
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

Min/Max Date between 2 diff years? 2
Min/Max Dates 2
Min/Max Date only sorting by months? 0
Min/Max Dates only looking at month? 3
IIf statement too long. Other options? 4
Date Tracking Query 19
Help with 3 scenerio query 1
Count of in Range of Dates 17

Top