Rediculous query run tim

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi guys,

I have a make table query that I've been leaving overnight and just doesn't
run (takes forever although it does seem active and continue to use CPU).
Over 30 hours and nothing. I know that it should work and has worked in the
past for a smaller table of same structure.

Can anybody help identify why it takes forever and how I can make it
quicker? I can never leave access running for the amount of time it might
take for this to run (as yet unknown).

Make table query:
SELECT [qry_CC_DailyDuties_&_Month].EmpNum,
[qry_CC_DailyDuties_&_Month].Month, tblCC2006CategoryDays.Base_Rank_Sort,
tblCC2006CategoryDays.Base, tblCC2006CategoryDays.Short_Rank,
tblCC2006CategoryDays.Min_Work_ID, [qry_CC_DailyDuties_&_Month].Dt,
[qry_CC_DailyDuties_&_Month].DuCat, [qry_CC_DailyDuties_&_Month].DuTm,
[qry_CC_DailyDuties_&_Month].DuPort, [qry_CC_DailyDuties_&_Month].DuCode,
[qry_CC_DailyDuties_&_Month].AndyCat, tblCC2006CategoryDays.Category_ID INTO
tblCC2004DailyDuties_CategoryDays
FROM [qry_CC_DailyDuties_&_Month] INNER JOIN tblCC2006CategoryDays ON
([qry_CC_DailyDuties_&_Month].Month = tblCC2006CategoryDays.Month) AND
([qry_CC_DailyDuties_&_Month].EmpNum = tblCC2006CategoryDays.EmpNum) AND
([qry_CC_DailyDuties_&_Month].Category_ID =
tblCC2006CategoryDays.Original_Category_ID);

Query used within this:
SELECT tblCC2006DailyDuties.*, Format([Dt],'yyyymm') AS [Month],
tblRefCrewDutyCodes.Category_ID
FROM tblCC2006DailyDuties INNER JOIN tblRefCrewDutyCodes ON
tblCC2006DailyDuties.DuCode = tblRefCrewDutyCodes.[DUTY CODE];

tblCC2006CategoryDays has 152,000 records. Appropriate fields are indexed.
tblCC2006DailyDuties has 1.5 million records. Appropriate fields are indexed.

Appreciate any help.

Thanks,

Basil
 
The first query you call " Make table query " seems to use a query,
probably the second one, right?


If so, note that a query cannot use the index(es) of the table(s) on which
it is based.

Also, your second query does not bring much new information short of
computing a string expression on million of records.

So, here are the suggestions:

- push the comparison with the generated string in the end of the ON
clause, in the "make table query" :

... ON ... AND ([qry_CC_DailyDuties_&_Month].Month =
tblCC2006CategoryDays.Month);


If that is still too slow, re-introduce the definition of the query into
your "make table query" (that will make a query with 3 tables), and again,
try to push the necessity, for Jet, to compute

Format([Dt],'yyyymm') AS [Month]

as late as possible.



Vanderghast, Access MVP
 
Michel / Vanderghast,

Thanks so much for your considered response. I think you've hit the nail on
the head.

When I originally wrote the queries, and it worked, I didn't have the
interim query and calculated string field. Instead I looked straight at the
table and performed the calculation on the join:

INNER JOIN tblCC2006CategoryDays ON
(format(tblCC2006DailyDuties.[dt],"yyyymm") = tblCC2006CategoryDays.Month)

I actually thought it would be much slower trying to do this on the join
than it would be by creating it in a new field in an intermediate query. Was
I wrong, and should I go back to this?

Also, I understand from you that the order in which I create the join makes
a difference - and if I put the nasty ones at the end it will work quicker.
Have I understood right?

Thanks so much for your excellent help.

Basil

Michel Walsh said:
The first query you call " Make table query " seems to use a query,
probably the second one, right?


If so, note that a query cannot use the index(es) of the table(s) on which
it is based.

Also, your second query does not bring much new information short of
computing a string expression on million of records.

So, here are the suggestions:

- push the comparison with the generated string in the end of the ON
clause, in the "make table query" :

... ON ... AND ([qry_CC_DailyDuties_&_Month].Month =
tblCC2006CategoryDays.Month);


If that is still too slow, re-introduce the definition of the query into
your "make table query" (that will make a query with 3 tables), and again,
try to push the necessity, for Jet, to compute

Format([Dt],'yyyymm') AS [Month]

as late as possible.



Vanderghast, Access MVP




Basil said:
Hi guys,

I have a make table query that I've been leaving overnight and just
doesn't
run (takes forever although it does seem active and continue to use CPU).
Over 30 hours and nothing. I know that it should work and has worked in
the
past for a smaller table of same structure.

Can anybody help identify why it takes forever and how I can make it
quicker? I can never leave access running for the amount of time it might
take for this to run (as yet unknown).

Make table query:
SELECT [qry_CC_DailyDuties_&_Month].EmpNum,
[qry_CC_DailyDuties_&_Month].Month, tblCC2006CategoryDays.Base_Rank_Sort,
tblCC2006CategoryDays.Base, tblCC2006CategoryDays.Short_Rank,
tblCC2006CategoryDays.Min_Work_ID, [qry_CC_DailyDuties_&_Month].Dt,
[qry_CC_DailyDuties_&_Month].DuCat, [qry_CC_DailyDuties_&_Month].DuTm,
[qry_CC_DailyDuties_&_Month].DuPort, [qry_CC_DailyDuties_&_Month].DuCode,
[qry_CC_DailyDuties_&_Month].AndyCat, tblCC2006CategoryDays.Category_ID
INTO
tblCC2004DailyDuties_CategoryDays
FROM [qry_CC_DailyDuties_&_Month] INNER JOIN tblCC2006CategoryDays ON
([qry_CC_DailyDuties_&_Month].Month = tblCC2006CategoryDays.Month) AND
([qry_CC_DailyDuties_&_Month].EmpNum = tblCC2006CategoryDays.EmpNum) AND
([qry_CC_DailyDuties_&_Month].Category_ID =
tblCC2006CategoryDays.Original_Category_ID);

Query used within this:
SELECT tblCC2006DailyDuties.*, Format([Dt],'yyyymm') AS [Month],
tblRefCrewDutyCodes.Category_ID
FROM tblCC2006DailyDuties INNER JOIN tblRefCrewDutyCodes ON
tblCC2006DailyDuties.DuCode = tblRefCrewDutyCodes.[DUTY CODE];

tblCC2006CategoryDays has 152,000 records. Appropriate fields are indexed.
tblCC2006DailyDuties has 1.5 million records. Appropriate fields are
indexed.

Appreciate any help.

Thanks,

Basil
Click to expand...
Click to expand...
 
While Jet optimizer performs well in some situation, there are some other
situations where it behaves poorly. That also hold for any SQL database
engine, but the conclusion you got from one does not necessary hold for
another db engine, neither between version, of the same engine, since the
engine guarantee you to get the result, not to keep the same way to get it.
So, to make a short answer, yes, putting the nastier tests at the end of the
ON clause or at the end of the WHERE clause MAY improve greatly the response
time, in Jet 4.0. Basically, use the equi joins on the indexed fields that
are highly selective FIRST, and use the computed expression (even adding 1
to a field turns it into a computed expression) LAST. Jet 3.0 was not
optimizing at all non-equijoin, as long as I remember, and I suspect Jet 4.0
does not either, so that is why I would place comparison with something else
than = AFTER those implying a simple = (on not computed expressions).


Vanderghast, Access MVP


Basil said:
Michel / Vanderghast,

Thanks so much for your considered response. I think you've hit the nail
on
the head.

When I originally wrote the queries, and it worked, I didn't have the
interim query and calculated string field. Instead I looked straight at
the
table and performed the calculation on the join:

INNER JOIN tblCC2006CategoryDays ON
(format(tblCC2006DailyDuties.[dt],"yyyymm") = tblCC2006CategoryDays.Month)

I actually thought it would be much slower trying to do this on the join
than it would be by creating it in a new field in an intermediate query.
Was
I wrong, and should I go back to this?

Also, I understand from you that the order in which I create the join
makes
a difference - and if I put the nasty ones at the end it will work
quicker.
Have I understood right?

Thanks so much for your excellent help.

Basil

Michel Walsh said:
The first query you call " Make table query " seems to use a query,
probably the second one, right?


If so, note that a query cannot use the index(es) of the table(s) on
which
it is based.

Also, your second query does not bring much new information short of
computing a string expression on million of records.

So, here are the suggestions:

- push the comparison with the generated string in the end of the ON
clause, in the "make table query" :

... ON ... AND ([qry_CC_DailyDuties_&_Month].Month =
tblCC2006CategoryDays.Month);


If that is still too slow, re-introduce the definition of the query into
your "make table query" (that will make a query with 3 tables), and
again,
try to push the necessity, for Jet, to compute

Format([Dt],'yyyymm') AS [Month]

as late as possible.



Vanderghast, Access MVP




Basil said:
Hi guys,

I have a make table query that I've been leaving overnight and just
doesn't
run (takes forever although it does seem active and continue to use
CPU).
Over 30 hours and nothing. I know that it should work and has worked in
the
past for a smaller table of same structure.

Can anybody help identify why it takes forever and how I can make it
quicker? I can never leave access running for the amount of time it
might
take for this to run (as yet unknown).

Make table query:
SELECT [qry_CC_DailyDuties_&_Month].EmpNum,
[qry_CC_DailyDuties_&_Month].Month,
tblCC2006CategoryDays.Base_Rank_Sort,
tblCC2006CategoryDays.Base, tblCC2006CategoryDays.Short_Rank,
tblCC2006CategoryDays.Min_Work_ID, [qry_CC_DailyDuties_&_Month].Dt,
[qry_CC_DailyDuties_&_Month].DuCat, [qry_CC_DailyDuties_&_Month].DuTm,
[qry_CC_DailyDuties_&_Month].DuPort,
[qry_CC_DailyDuties_&_Month].DuCode,
[qry_CC_DailyDuties_&_Month].AndyCat, tblCC2006CategoryDays.Category_ID
INTO
tblCC2004DailyDuties_CategoryDays
FROM [qry_CC_DailyDuties_&_Month] INNER JOIN tblCC2006CategoryDays ON
([qry_CC_DailyDuties_&_Month].Month = tblCC2006CategoryDays.Month) AND
([qry_CC_DailyDuties_&_Month].EmpNum = tblCC2006CategoryDays.EmpNum)
AND
([qry_CC_DailyDuties_&_Month].Category_ID =
tblCC2006CategoryDays.Original_Category_ID);

Query used within this:
SELECT tblCC2006DailyDuties.*, Format([Dt],'yyyymm') AS [Month],
tblRefCrewDutyCodes.Category_ID
FROM tblCC2006DailyDuties INNER JOIN tblRefCrewDutyCodes ON
tblCC2006DailyDuties.DuCode = tblRefCrewDutyCodes.[DUTY CODE];

tblCC2006CategoryDays has 152,000 records. Appropriate fields are
indexed.
tblCC2006DailyDuties has 1.5 million records. Appropriate fields are
indexed.

Appreciate any help.

Thanks,

Basil
Click to expand...
Click to expand...
Click to expand...
 
That was a little beyond my levels of understanding, but I think I follow.

I've rejigged the query so it only looks at tables now. I am not sure how to
re-arrange the joins beyond what I have already done though. Also, when I go
to design view, Access always seems to want to put the date join first
(obviouslyt before I include the format since this can't be represented in
design view).

SELECT tblCC2006DailyDuties.EmpNum, tblCC2006CategoryDays.Month,
tblCC2006CategoryDays.Base_Rank_Sort, tblCC2006CategoryDays.Base,
tblCC2006CategoryDays.Short_Rank, tblCC2006CategoryDays.Min_Work_ID,
tblCC2006DailyDuties.Dt, tblCC2006DailyDuties.DuCat,
tblCC2006DailyDuties.DuTm, tblCC2006DailyDuties.DuPort,
tblCC2006DailyDuties.DuCode, tblCC2006DailyDuties.AndyCat,
tblCC2006CategoryDays.Category_ID INTO tblCC2006DailyDuties_CategoryDays
FROM (tblCC2006DailyDuties INNER JOIN tblCC2006CategoryDays ON
(tblCC2006DailyDuties.EmpNum = tblCC2006CategoryDays.EmpNum) AND
(format(tblCC2006DailyDuties.Dt,"yyyymm") = tblCC2006CategoryDays.Month))
INNER JOIN tblRefCrewDutyCodes ON (tblRefCrewDutyCodes.Category_ID =
tblCC2006CategoryDays.Original_Category_ID) AND (tblCC2006DailyDuties.DuCode
= tblRefCrewDutyCodes.[DUTY CODE]);

Can you see a way to tweak the joins?

Thanks again,

Basil

Michel Walsh said:
While Jet optimizer performs well in some situation, there are some other
situations where it behaves poorly. That also hold for any SQL database
engine, but the conclusion you got from one does not necessary hold for
another db engine, neither between version, of the same engine, since the
engine guarantee you to get the result, not to keep the same way to get it.
So, to make a short answer, yes, putting the nastier tests at the end of the
ON clause or at the end of the WHERE clause MAY improve greatly the response
time, in Jet 4.0. Basically, use the equi joins on the indexed fields that
are highly selective FIRST, and use the computed expression (even adding 1
to a field turns it into a computed expression) LAST. Jet 3.0 was not
optimizing at all non-equijoin, as long as I remember, and I suspect Jet 4.0
does not either, so that is why I would place comparison with something else
than = AFTER those implying a simple = (on not computed expressions).


Vanderghast, Access MVP


Basil said:
Michel / Vanderghast,

Thanks so much for your considered response. I think you've hit the nail
on
the head.

When I originally wrote the queries, and it worked, I didn't have the
interim query and calculated string field. Instead I looked straight at
the
table and performed the calculation on the join:

INNER JOIN tblCC2006CategoryDays ON
(format(tblCC2006DailyDuties.[dt],"yyyymm") = tblCC2006CategoryDays.Month)

I actually thought it would be much slower trying to do this on the join
than it would be by creating it in a new field in an intermediate query.
Was
I wrong, and should I go back to this?

Also, I understand from you that the order in which I create the join
makes
a difference - and if I put the nasty ones at the end it will work
quicker.
Have I understood right?

Thanks so much for your excellent help.

Basil

Michel Walsh said:
The first query you call " Make table query " seems to use a query,
probably the second one, right?


If so, note that a query cannot use the index(es) of the table(s) on
which
it is based.

Also, your second query does not bring much new information short of
computing a string expression on million of records.

So, here are the suggestions:

- push the comparison with the generated string in the end of the ON
clause, in the "make table query" :

... ON ... AND ([qry_CC_DailyDuties_&_Month].Month =
tblCC2006CategoryDays.Month);


If that is still too slow, re-introduce the definition of the query into
your "make table query" (that will make a query with 3 tables), and
again,
try to push the necessity, for Jet, to compute

Format([Dt],'yyyymm') AS [Month]

as late as possible.



Vanderghast, Access MVP




Hi guys,

I have a make table query that I've been leaving overnight and just
doesn't
run (takes forever although it does seem active and continue to use
CPU).
Over 30 hours and nothing. I know that it should work and has worked in
the
past for a smaller table of same structure.

Can anybody help identify why it takes forever and how I can make it
quicker? I can never leave access running for the amount of time it
might
take for this to run (as yet unknown).

Make table query:
SELECT [qry_CC_DailyDuties_&_Month].EmpNum,
[qry_CC_DailyDuties_&_Month].Month,
tblCC2006CategoryDays.Base_Rank_Sort,
tblCC2006CategoryDays.Base, tblCC2006CategoryDays.Short_Rank,
tblCC2006CategoryDays.Min_Work_ID, [qry_CC_DailyDuties_&_Month].Dt,
[qry_CC_DailyDuties_&_Month].DuCat, [qry_CC_DailyDuties_&_Month].DuTm,
[qry_CC_DailyDuties_&_Month].DuPort,
[qry_CC_DailyDuties_&_Month].DuCode,
[qry_CC_DailyDuties_&_Month].AndyCat, tblCC2006CategoryDays.Category_ID
INTO
tblCC2004DailyDuties_CategoryDays
FROM [qry_CC_DailyDuties_&_Month] INNER JOIN tblCC2006CategoryDays ON
([qry_CC_DailyDuties_&_Month].Month = tblCC2006CategoryDays.Month) AND
([qry_CC_DailyDuties_&_Month].EmpNum = tblCC2006CategoryDays.EmpNum)
AND
([qry_CC_DailyDuties_&_Month].Category_ID =
tblCC2006CategoryDays.Original_Category_ID);

Query used within this:
SELECT tblCC2006DailyDuties.*, Format([Dt],'yyyymm') AS [Month],
tblRefCrewDutyCodes.Category_ID
FROM tblCC2006DailyDuties INNER JOIN tblRefCrewDutyCodes ON
tblCC2006DailyDuties.DuCode = tblRefCrewDutyCodes.[DUTY CODE];

tblCC2006CategoryDays has 152,000 records. Appropriate fields are
indexed.
tblCC2006DailyDuties has 1.5 million records. Appropriate fields are
indexed.

Appreciate any help.

Thanks,

Basil
Click to expand...
Click to expand...
Click to expand...
 
SELECT ....
FROM ( tblCC2006CategoryDays AS a
INNER JOIN tblCC2006DailyDuties As b
ON ( a.EmpEnum=b.EmpEnum
AND a.category_id=b.categpory_id )
) INNER JOIN tblRefCrewDutyCodes AS c
ON (b.DueCode=c.Duty Code]
AND Format(c.dt, "yyyymm") = a.Month)




a, b and c are just aliases I used to avoid having to type the long table
name each time. I assumed the field dt comes from tblRefCrewDutyCode.




Hoping it may help
Vanderghast, Access MVP


Basil said:
That was a little beyond my levels of understanding, but I think I follow.

I've rejigged the query so it only looks at tables now. I am not sure how
to
re-arrange the joins beyond what I have already done though. Also, when I
go
to design view, Access always seems to want to put the date join first
(obviouslyt before I include the format since this can't be represented in
design view).

SELECT tblCC2006DailyDuties.EmpNum, tblCC2006CategoryDays.Month,
tblCC2006CategoryDays.Base_Rank_Sort, tblCC2006CategoryDays.Base,
tblCC2006CategoryDays.Short_Rank, tblCC2006CategoryDays.Min_Work_ID,
tblCC2006DailyDuties.Dt, tblCC2006DailyDuties.DuCat,
tblCC2006DailyDuties.DuTm, tblCC2006DailyDuties.DuPort,
tblCC2006DailyDuties.DuCode, tblCC2006DailyDuties.AndyCat,
tblCC2006CategoryDays.Category_ID INTO tblCC2006DailyDuties_CategoryDays
FROM (tblCC2006DailyDuties INNER JOIN tblCC2006CategoryDays ON
(tblCC2006DailyDuties.EmpNum = tblCC2006CategoryDays.EmpNum) AND
(format(tblCC2006DailyDuties.Dt,"yyyymm") = tblCC2006CategoryDays.Month))
INNER JOIN tblRefCrewDutyCodes ON (tblRefCrewDutyCodes.Category_ID =
tblCC2006CategoryDays.Original_Category_ID) AND
(tblCC2006DailyDuties.DuCode
= tblRefCrewDutyCodes.[DUTY CODE]);

Can you see a way to tweak the joins?

Thanks again,

Basil

Michel Walsh said:
While Jet optimizer performs well in some situation, there are some other
situations where it behaves poorly. That also hold for any SQL database
engine, but the conclusion you got from one does not necessary hold for
another db engine, neither between version, of the same engine, since the
engine guarantee you to get the result, not to keep the same way to get
it.
So, to make a short answer, yes, putting the nastier tests at the end of
the
ON clause or at the end of the WHERE clause MAY improve greatly the
response
time, in Jet 4.0. Basically, use the equi joins on the indexed fields
that
are highly selective FIRST, and use the computed expression (even adding
1
to a field turns it into a computed expression) LAST. Jet 3.0 was not
optimizing at all non-equijoin, as long as I remember, and I suspect Jet
4.0
does not either, so that is why I would place comparison with something
else
than = AFTER those implying a simple = (on not computed expressions).


Vanderghast, Access MVP


Basil said:
Michel / Vanderghast,

Thanks so much for your considered response. I think you've hit the
nail
on
the head.

When I originally wrote the queries, and it worked, I didn't have the
interim query and calculated string field. Instead I looked straight at
the
table and performed the calculation on the join:

INNER JOIN tblCC2006CategoryDays ON
(format(tblCC2006DailyDuties.[dt],"yyyymm") =
tblCC2006CategoryDays.Month)

I actually thought it would be much slower trying to do this on the
join
than it would be by creating it in a new field in an intermediate
query.
Was
I wrong, and should I go back to this?

Also, I understand from you that the order in which I create the join
makes
a difference - and if I put the nasty ones at the end it will work
quicker.
Have I understood right?

Thanks so much for your excellent help.

Basil

:

The first query you call " Make table query " seems to use a query,
probably the second one, right?


If so, note that a query cannot use the index(es) of the table(s) on
which
it is based.

Also, your second query does not bring much new information short of
computing a string expression on million of records.

So, here are the suggestions:

- push the comparison with the generated string in the end of the
ON
clause, in the "make table query" :

... ON ... AND ([qry_CC_DailyDuties_&_Month].Month =
tblCC2006CategoryDays.Month);


If that is still too slow, re-introduce the definition of the query
into
your "make table query" (that will make a query with 3 tables), and
again,
try to push the necessity, for Jet, to compute

Format([Dt],'yyyymm') AS [Month]

as late as possible.



Vanderghast, Access MVP




Hi guys,

I have a make table query that I've been leaving overnight and just
doesn't
run (takes forever although it does seem active and continue to use
CPU).
Over 30 hours and nothing. I know that it should work and has worked
in
the
past for a smaller table of same structure.

Can anybody help identify why it takes forever and how I can make it
quicker? I can never leave access running for the amount of time it
might
take for this to run (as yet unknown).

Make table query:
SELECT [qry_CC_DailyDuties_&_Month].EmpNum,
[qry_CC_DailyDuties_&_Month].Month,
tblCC2006CategoryDays.Base_Rank_Sort,
tblCC2006CategoryDays.Base, tblCC2006CategoryDays.Short_Rank,
tblCC2006CategoryDays.Min_Work_ID, [qry_CC_DailyDuties_&_Month].Dt,
[qry_CC_DailyDuties_&_Month].DuCat,
[qry_CC_DailyDuties_&_Month].DuTm,
[qry_CC_DailyDuties_&_Month].DuPort,
[qry_CC_DailyDuties_&_Month].DuCode,
[qry_CC_DailyDuties_&_Month].AndyCat,
tblCC2006CategoryDays.Category_ID
INTO
tblCC2004DailyDuties_CategoryDays
FROM [qry_CC_DailyDuties_&_Month] INNER JOIN tblCC2006CategoryDays
ON
([qry_CC_DailyDuties_&_Month].Month = tblCC2006CategoryDays.Month)
AND
([qry_CC_DailyDuties_&_Month].EmpNum = tblCC2006CategoryDays.EmpNum)
AND
([qry_CC_DailyDuties_&_Month].Category_ID =
tblCC2006CategoryDays.Original_Category_ID);

Query used within this:
SELECT tblCC2006DailyDuties.*, Format([Dt],'yyyymm') AS [Month],
tblRefCrewDutyCodes.Category_ID
FROM tblCC2006DailyDuties INNER JOIN tblRefCrewDutyCodes ON
tblCC2006DailyDuties.DuCode = tblRefCrewDutyCodes.[DUTY CODE];

tblCC2006CategoryDays has 152,000 records. Appropriate fields are
indexed.
tblCC2006DailyDuties has 1.5 million records. Appropriate fields are
indexed.

Appreciate any help.

Thanks,

Basil
Click to expand...
Click to expand...
Click to expand...
 
Back
Top