Speed Up report.why is it so slow while no Pages or Stored Procedu

C

Candy

The report using a crosstab query as record sources is very slow. It could
take hours to generate one report. There are 3 queries:qUniCurrSche
(*Union),
qcartJobOpers (*cartesian), and qxtbOperSchedule (*crosstab), the one
bounded with the report is the qxtbOperSchedule. I've listed the SQL code as
following. Any advice for speeding up will be appreciated.

qUniCurrSche:
SELECT DDue,JobNo,dOrder, "CUT" as Operation, Cut_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "NIT" as Operation, NIT_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "MAN" as Operation, Man_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "DIS" as Operation, Dis_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "EMB" as Operation, EMB_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE1" as Operation, OE1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE2" as Operation, OE2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT1" as Operation, PRT1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT2" as Operation, PRT2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN1" as Operation, BUN1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN2" as Operation, BUN2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "INW" as Operation, INW_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "SUB" as Operation, SUB_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "QC" as Operation, QC_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
ORDER BY dDue;

qcartJobOpers:
SELECT quniCurrSche.ddue, quniCurrSche.dOrder, quniCurrSche.JobNo,
quniCurrSche.Operation, quniCurrSche.OperDate, tblAllDates.theDate
FROM tblAllDates, quniCurrSche
WHERE (((tblAllDates.theDate) Between [OperDate] And (SELECT
Max(OperDate)+1
FROM quniCurrSche j
WHERE (j.JobNo = quniCurrSche.JobNo AND j.OperDate <
quniCurrSche.OperDate) ) )) AND
tblAllDates.theDate >= quniCurrSche.dOrder And
(Year(quniCurrSche.OperDate) >2000)
ORDER BY quniCurrSche.ddue, tblAllDates.theDate;

qxtbOperSchedule:
TRANSFORM First(qcartJobOpers.Operation) AS FirstOfOperation
SELECT Year([TheDate]) AS Yr, DatePart("ww",[TheDate]-1) AS WeekNum,
qcartJobOpers.ddue, qcartJobOpers.JobNo
FROM qcartJobOpers
GROUP BY Year([TheDate]), DatePart("ww",[TheDate]-1), qcartJobOpers.ddue,
qcartJobOpers.JobNo
PIVOT Format([TheDate],"dddd") In
("Monday","Tuesday","Wednesday","Thursday","Friday");
 
D

Duane Hookom

Did you read my reply in your previous thread?

Is DIS_A indexed?

Why use:
WHERE (DIS_A < DateValue('01/01/2000'))
when you could use:
WHERE (DIS_A < #01/01/2000#)

I also suggested using a temporary report table.

--
Duane Hookom
MS Access MVP
--

Candy said:
The report using a crosstab query as record sources is very slow. It could
take hours to generate one report. There are 3 queries:qUniCurrSche
(*Union),
qcartJobOpers (*cartesian), and qxtbOperSchedule (*crosstab), the one
bounded with the report is the qxtbOperSchedule. I've listed the SQL code
as
following. Any advice for speeding up will be appreciated.

qUniCurrSche:
SELECT DDue,JobNo,dOrder, "CUT" as Operation, Cut_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "NIT" as Operation, NIT_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "MAN" as Operation, Man_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "DIS" as Operation, Dis_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "EMB" as Operation, EMB_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE1" as Operation, OE1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE2" as Operation, OE2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT1" as Operation, PRT1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT2" as Operation, PRT2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN1" as Operation, BUN1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN2" as Operation, BUN2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "INW" as Operation, INW_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "SUB" as Operation, SUB_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "QC" as Operation, QC_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
ORDER BY dDue;

qcartJobOpers:
SELECT quniCurrSche.ddue, quniCurrSche.dOrder, quniCurrSche.JobNo,
quniCurrSche.Operation, quniCurrSche.OperDate, tblAllDates.theDate
FROM tblAllDates, quniCurrSche
WHERE (((tblAllDates.theDate) Between [OperDate] And (SELECT
Max(OperDate)+1
FROM quniCurrSche j
WHERE (j.JobNo = quniCurrSche.JobNo AND j.OperDate <
quniCurrSche.OperDate) ) )) AND
tblAllDates.theDate >= quniCurrSche.dOrder And
(Year(quniCurrSche.OperDate) >2000)
ORDER BY quniCurrSche.ddue, tblAllDates.theDate;

qxtbOperSchedule:
TRANSFORM First(qcartJobOpers.Operation) AS FirstOfOperation
SELECT Year([TheDate]) AS Yr, DatePart("ww",[TheDate]-1) AS WeekNum,
qcartJobOpers.ddue, qcartJobOpers.JobNo
FROM qcartJobOpers
GROUP BY Year([TheDate]), DatePart("ww",[TheDate]-1), qcartJobOpers.ddue,
qcartJobOpers.JobNo
PIVOT Format([TheDate],"dddd") In
("Monday","Tuesday","Wednesday","Thursday","Friday");
Click to expand...
 
C

Candy

Hi Duane,
Yes, DIS_A is indexed. I've replaced DateValue function with '#', it did
speed up a little, but still can not satisfy our client:( Well, it doesn't
surprise me, since it reduce down to 7 hours(before it takes 8 hours) to
generate the report for 200 jobs...
I can't find your reply in my previous thread. Which thread is it?
Any other suggestions?

TIA.
Candy



Duane Hookom said:
Did you read my reply in your previous thread?

Is DIS_A indexed?

Why use:
WHERE (DIS_A < DateValue('01/01/2000'))
when you could use:
WHERE (DIS_A < #01/01/2000#)

I also suggested using a temporary report table.

--
Duane Hookom
MS Access MVP
--

Candy said:
The report using a crosstab query as record sources is very slow. It could
take hours to generate one report. There are 3 queries:qUniCurrSche
(*Union),
qcartJobOpers (*cartesian), and qxtbOperSchedule (*crosstab), the one
bounded with the report is the qxtbOperSchedule. I've listed the SQL code
as
following. Any advice for speeding up will be appreciated.

qUniCurrSche:
SELECT DDue,JobNo,dOrder, "CUT" as Operation, Cut_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "NIT" as Operation, NIT_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "MAN" as Operation, Man_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "DIS" as Operation, Dis_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "EMB" as Operation, EMB_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE1" as Operation, OE1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE2" as Operation, OE2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT1" as Operation, PRT1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT2" as Operation, PRT2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN1" as Operation, BUN1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN2" as Operation, BUN2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "INW" as Operation, INW_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "SUB" as Operation, SUB_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "QC" as Operation, QC_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
ORDER BY dDue;

qcartJobOpers:
SELECT quniCurrSche.ddue, quniCurrSche.dOrder, quniCurrSche.JobNo,
quniCurrSche.Operation, quniCurrSche.OperDate, tblAllDates.theDate
FROM tblAllDates, quniCurrSche
WHERE (((tblAllDates.theDate) Between [OperDate] And (SELECT
Max(OperDate)+1
FROM quniCurrSche j
WHERE (j.JobNo = quniCurrSche.JobNo AND j.OperDate <
quniCurrSche.OperDate) ) )) AND
tblAllDates.theDate >= quniCurrSche.dOrder And
(Year(quniCurrSche.OperDate) >2000)
ORDER BY quniCurrSche.ddue, tblAllDates.theDate;

qxtbOperSchedule:
TRANSFORM First(qcartJobOpers.Operation) AS FirstOfOperation
SELECT Year([TheDate]) AS Yr, DatePart("ww",[TheDate]-1) AS WeekNum,
qcartJobOpers.ddue, qcartJobOpers.JobNo
FROM qcartJobOpers
GROUP BY Year([TheDate]), DatePart("ww",[TheDate]-1), qcartJobOpers.ddue,
qcartJobOpers.JobNo
PIVOT Format([TheDate],"dddd") In
("Monday","Tuesday","Wednesday","Thursday","Friday");
Click to expand...
Click to expand...
 
D

Duane Hookom

I find all of your initial posts by sorting the news group post in order of
the "from" column.

In this thread and in your earlier thread, I suggested using a temporary
table created from possibly the union query.

--
Duane Hookom
MS Access MVP
--

Candy said:
Hi Duane,
Yes, DIS_A is indexed. I've replaced DateValue function with '#', it
did
speed up a little, but still can not satisfy our client:( Well, it doesn't
surprise me, since it reduce down to 7 hours(before it takes 8 hours) to
generate the report for 200 jobs...
I can't find your reply in my previous thread. Which thread is it?
Any other suggestions?

TIA.
Candy



Duane Hookom said:
Did you read my reply in your previous thread?

Is DIS_A indexed?

Why use:
WHERE (DIS_A < DateValue('01/01/2000'))
when you could use:
WHERE (DIS_A < #01/01/2000#)

I also suggested using a temporary report table.

--
Duane Hookom
MS Access MVP
--

Candy said:
The report using a crosstab query as record sources is very slow. It
could
take hours to generate one report. There are 3 queries:qUniCurrSche
(*Union),
qcartJobOpers (*cartesian), and qxtbOperSchedule (*crosstab), the one
bounded with the report is the qxtbOperSchedule. I've listed the SQL
code
as
following. Any advice for speeding up will be appreciated.

qUniCurrSche:
SELECT DDue,JobNo,dOrder, "CUT" as Operation, Cut_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "NIT" as Operation, NIT_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "MAN" as Operation, Man_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "DIS" as Operation, Dis_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "EMB" as Operation, EMB_S as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE1" as Operation, OE1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE2" as Operation, OE2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT1" as Operation, PRT1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT2" as Operation, PRT2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN1" as Operation, BUN1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN2" as Operation, BUN2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "INW" as Operation, INW_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "SUB" as Operation, SUB_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "QC" as Operation, QC_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
ORDER BY dDue;

qcartJobOpers:
SELECT quniCurrSche.ddue, quniCurrSche.dOrder, quniCurrSche.JobNo,
quniCurrSche.Operation, quniCurrSche.OperDate, tblAllDates.theDate
FROM tblAllDates, quniCurrSche
WHERE (((tblAllDates.theDate) Between [OperDate] And (SELECT
Max(OperDate)+1
FROM quniCurrSche j
WHERE (j.JobNo = quniCurrSche.JobNo AND j.OperDate <
quniCurrSche.OperDate) ) )) AND
tblAllDates.theDate >= quniCurrSche.dOrder And
(Year(quniCurrSche.OperDate) >2000)
ORDER BY quniCurrSche.ddue, tblAllDates.theDate;

qxtbOperSchedule:
TRANSFORM First(qcartJobOpers.Operation) AS FirstOfOperation
SELECT Year([TheDate]) AS Yr, DatePart("ww",[TheDate]-1) AS WeekNum,
qcartJobOpers.ddue, qcartJobOpers.JobNo
FROM qcartJobOpers
GROUP BY Year([TheDate]), DatePart("ww",[TheDate]-1),
qcartJobOpers.ddue,
qcartJobOpers.JobNo
PIVOT Format([TheDate],"dddd") In
("Monday","Tuesday","Wednesday","Thursday","Friday");
Click to expand...
Click to expand...
Click to expand...
 
C

Candy

Hi, Duane,
I still haven't found that thread, could you post the link please?

What do you mean by "temporary table"? How to create one?I don't have a
clue...

THX
Candy

Duane Hookom said:
I find all of your initial posts by sorting the news group post in order of
the "from" column.

In this thread and in your earlier thread, I suggested using a temporary
table created from possibly the union query.

--
Duane Hookom
MS Access MVP
--

Candy said:
Hi Duane,
Yes, DIS_A is indexed. I've replaced DateValue function with '#', it
did
speed up a little, but still can not satisfy our client:( Well, it doesn't
surprise me, since it reduce down to 7 hours(before it takes 8 hours) to
generate the report for 200 jobs...
I can't find your reply in my previous thread. Which thread is it?
Any other suggestions?

TIA.
Candy



Duane Hookom said:
Did you read my reply in your previous thread?

Is DIS_A indexed?

Why use:
WHERE (DIS_A < DateValue('01/01/2000'))
when you could use:
WHERE (DIS_A < #01/01/2000#)

I also suggested using a temporary report table.

--
Duane Hookom
MS Access MVP
--

The report using a crosstab query as record sources is very slow. It
could
take hours to generate one report. There are 3 queries:qUniCurrSche
(*Union),
qcartJobOpers (*cartesian), and qxtbOperSchedule (*crosstab), the one
bounded with the report is the qxtbOperSchedule. I've listed the SQL
code
as
following. Any advice for speeding up will be appreciated.

qUniCurrSche:
SELECT DDue,JobNo,dOrder, "CUT" as Operation, Cut_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "NIT" as Operation, NIT_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "MAN" as Operation, Man_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "DIS" as Operation, Dis_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "EMB" as Operation, EMB_S as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE1" as Operation, OE1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE2" as Operation, OE2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT1" as Operation, PRT1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT2" as Operation, PRT2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN1" as Operation, BUN1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN2" as Operation, BUN2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "INW" as Operation, INW_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "SUB" as Operation, SUB_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "QC" as Operation, QC_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
ORDER BY dDue;

qcartJobOpers:
SELECT quniCurrSche.ddue, quniCurrSche.dOrder, quniCurrSche.JobNo,
quniCurrSche.Operation, quniCurrSche.OperDate, tblAllDates.theDate
FROM tblAllDates, quniCurrSche
WHERE (((tblAllDates.theDate) Between [OperDate] And (SELECT
Max(OperDate)+1
FROM quniCurrSche j
WHERE (j.JobNo = quniCurrSche.JobNo AND j.OperDate <
quniCurrSche.OperDate) ) )) AND
tblAllDates.theDate >= quniCurrSche.dOrder And
(Year(quniCurrSche.OperDate) >2000)
ORDER BY quniCurrSche.ddue, tblAllDates.theDate;

qxtbOperSchedule:
TRANSFORM First(qcartJobOpers.Operation) AS FirstOfOperation
SELECT Year([TheDate]) AS Yr, DatePart("ww",[TheDate]-1) AS WeekNum,
qcartJobOpers.ddue, qcartJobOpers.JobNo
FROM qcartJobOpers
GROUP BY Year([TheDate]), DatePart("ww",[TheDate]-1),
qcartJobOpers.ddue,
qcartJobOpers.JobNo
PIVOT Format([TheDate],"dddd") In
("Monday","Tuesday","Wednesday","Thursday","Friday");
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Don't worry about the other thread since I have repeated most of it here.
==== from previous thread =====================
I would try create a normalized table with a strucuture mirroring the
qUniCurSche fields. Index this table on all fields. Then prior to opening
your report, delete all the records from the table and use qUniCurSche to
append new records.

Then base qcartJobOpers on the "reporting" table rather than the union
query.
==========================================

--
Duane Hookom
MS Access MVP
--

Candy said:
Hi, Duane,
I still haven't found that thread, could you post the link please?

What do you mean by "temporary table"? How to create one?I don't have a
clue...

THX
Candy

Duane Hookom said:
I find all of your initial posts by sorting the news group post in order
of
the "from" column.

In this thread and in your earlier thread, I suggested using a temporary
table created from possibly the union query.

--
Duane Hookom
MS Access MVP
--

Candy said:
Hi Duane,
Yes, DIS_A is indexed. I've replaced DateValue function with '#', it
did
speed up a little, but still can not satisfy our client:( Well, it
doesn't
surprise me, since it reduce down to 7 hours(before it takes 8 hours)
to
generate the report for 200 jobs...
I can't find your reply in my previous thread. Which thread is it?
Any other suggestions?

TIA.
Candy



:

Did you read my reply in your previous thread?

Is DIS_A indexed?

Why use:
WHERE (DIS_A < DateValue('01/01/2000'))
when you could use:
WHERE (DIS_A < #01/01/2000#)

I also suggested using a temporary report table.

--
Duane Hookom
MS Access MVP
--

The report using a crosstab query as record sources is very slow. It
could
take hours to generate one report. There are 3 queries:qUniCurrSche
(*Union),
qcartJobOpers (*cartesian), and qxtbOperSchedule (*crosstab), the
one
bounded with the report is the qxtbOperSchedule. I've listed the SQL
code
as
following. Any advice for speeding up will be appreciated.

qUniCurrSche:
SELECT DDue,JobNo,dOrder, "CUT" as Operation, Cut_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "NIT" as Operation, NIT_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "MAN" as Operation, Man_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "DIS" as Operation, Dis_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "EMB" as Operation, EMB_S as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE1" as Operation, OE1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE2" as Operation, OE2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT1" as Operation, PRT1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT2" as Operation, PRT2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN1" as Operation, BUN1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN2" as Operation, BUN2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "INW" as Operation, INW_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "SUB" as Operation, SUB_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "QC" as Operation, QC_S as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
ORDER BY dDue;

qcartJobOpers:
SELECT quniCurrSche.ddue, quniCurrSche.dOrder, quniCurrSche.JobNo,
quniCurrSche.Operation, quniCurrSche.OperDate, tblAllDates.theDate
FROM tblAllDates, quniCurrSche
WHERE (((tblAllDates.theDate) Between [OperDate] And (SELECT
Max(OperDate)+1
FROM quniCurrSche j
WHERE (j.JobNo = quniCurrSche.JobNo AND j.OperDate <
quniCurrSche.OperDate) ) )) AND
tblAllDates.theDate >= quniCurrSche.dOrder And
(Year(quniCurrSche.OperDate) >2000)
ORDER BY quniCurrSche.ddue, tblAllDates.theDate;

qxtbOperSchedule:
TRANSFORM First(qcartJobOpers.Operation) AS FirstOfOperation
SELECT Year([TheDate]) AS Yr, DatePart("ww",[TheDate]-1) AS WeekNum,
qcartJobOpers.ddue, qcartJobOpers.JobNo
FROM qcartJobOpers
GROUP BY Year([TheDate]), DatePart("ww",[TheDate]-1),
qcartJobOpers.ddue,
qcartJobOpers.JobNo
PIVOT Format([TheDate],"dddd") In
("Monday","Tuesday","Wednesday","Thursday","Friday");
Click to expand...
Click to expand...
Click to expand...
 
C

Candy

Thank you, Duane, you saved my life.
It now narrow down the reporting time from hours to minutes for 200 jobs.
Considering there might be more than 500 jobs in the same time, I need to be
a little greedy.
I'll use the similar strategy you've suggested, to create another temporary
table for the qCartJobOpers and use this table to generate the crosstab
query. Do You think this will work?
But, I don't actually understand why that replacing query data with table
data could speed up the report. Doesn't it using the same procedure? Why does
it make such differences?

Candy

Duane Hookom said:
Don't worry about the other thread since I have repeated most of it here.
==== from previous thread =====================
I would try create a normalized table with a strucuture mirroring the
qUniCurSche fields. Index this table on all fields. Then prior to opening
your report, delete all the records from the table and use qUniCurSche to
append new records.

Then base qcartJobOpers on the "reporting" table rather than the union
query.
==========================================

--
Duane Hookom
MS Access MVP
--

Candy said:
Hi, Duane,
I still haven't found that thread, could you post the link please?

What do you mean by "temporary table"? How to create one?I don't have a
clue...

THX
Candy

Duane Hookom said:
I find all of your initial posts by sorting the news group post in order
of
the "from" column.

In this thread and in your earlier thread, I suggested using a temporary
table created from possibly the union query.

--
Duane Hookom
MS Access MVP
--

Hi Duane,
Yes, DIS_A is indexed. I've replaced DateValue function with '#', it
did
speed up a little, but still can not satisfy our client:( Well, it
doesn't
surprise me, since it reduce down to 7 hours(before it takes 8 hours)
to
generate the report for 200 jobs...
I can't find your reply in my previous thread. Which thread is it?
Any other suggestions?

TIA.
Candy



:

Did you read my reply in your previous thread?

Is DIS_A indexed?

Why use:
WHERE (DIS_A < DateValue('01/01/2000'))
when you could use:
WHERE (DIS_A < #01/01/2000#)

I also suggested using a temporary report table.

--
Duane Hookom
MS Access MVP
--

The report using a crosstab query as record sources is very slow. It
could
take hours to generate one report. There are 3 queries:qUniCurrSche
(*Union),
qcartJobOpers (*cartesian), and qxtbOperSchedule (*crosstab), the
one
bounded with the report is the qxtbOperSchedule. I've listed the SQL
code
as
following. Any advice for speeding up will be appreciated.

qUniCurrSche:
SELECT DDue,JobNo,dOrder, "CUT" as Operation, Cut_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "NIT" as Operation, NIT_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "MAN" as Operation, Man_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "DIS" as Operation, Dis_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "EMB" as Operation, EMB_S as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE1" as Operation, OE1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE2" as Operation, OE2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT1" as Operation, PRT1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT2" as Operation, PRT2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN1" as Operation, BUN1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN2" as Operation, BUN2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "INW" as Operation, INW_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "SUB" as Operation, SUB_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "QC" as Operation, QC_S as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
ORDER BY dDue;

qcartJobOpers:
SELECT quniCurrSche.ddue, quniCurrSche.dOrder, quniCurrSche.JobNo,
quniCurrSche.Operation, quniCurrSche.OperDate, tblAllDates.theDate
FROM tblAllDates, quniCurrSche
WHERE (((tblAllDates.theDate) Between [OperDate] And (SELECT
Max(OperDate)+1
FROM quniCurrSche j
WHERE (j.JobNo = quniCurrSche.JobNo AND j.OperDate <
quniCurrSche.OperDate) ) )) AND
tblAllDates.theDate >= quniCurrSche.dOrder And
(Year(quniCurrSche.OperDate) >2000)
ORDER BY quniCurrSche.ddue, tblAllDates.theDate;

qxtbOperSchedule:
TRANSFORM First(qcartJobOpers.Operation) AS FirstOfOperation
SELECT Year([TheDate]) AS Yr, DatePart("ww",[TheDate]-1) AS WeekNum,
qcartJobOpers.ddue, qcartJobOpers.JobNo
FROM qcartJobOpers
GROUP BY Year([TheDate]), DatePart("ww",[TheDate]-1),
qcartJobOpers.ddue,
qcartJobOpers.JobNo
PIVOT Format([TheDate],"dddd") In
("Monday","Tuesday","Wednesday","Thursday","Friday");
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

I expect the query plan calls for the qCartJobOpers again and again and
again. I assume you have some indexes created on your temporary table.

--
Duane Hookom
MS Access MVP
--

Candy said:
Thank you, Duane, you saved my life.
It now narrow down the reporting time from hours to minutes for 200 jobs.
Considering there might be more than 500 jobs in the same time, I need to
be
a little greedy.
I'll use the similar strategy you've suggested, to create another
temporary
table for the qCartJobOpers and use this table to generate the crosstab
query. Do You think this will work?
But, I don't actually understand why that replacing query data with table
data could speed up the report. Doesn't it using the same procedure? Why
does
it make such differences?

Candy

Duane Hookom said:
Don't worry about the other thread since I have repeated most of it here.
==== from previous thread =====================
I would try create a normalized table with a strucuture mirroring the
qUniCurSche fields. Index this table on all fields. Then prior to opening
your report, delete all the records from the table and use qUniCurSche to
append new records.

Then base qcartJobOpers on the "reporting" table rather than the union
query.
==========================================

--
Duane Hookom
MS Access MVP
--

Candy said:
Hi, Duane,
I still haven't found that thread, could you post the link please?

What do you mean by "temporary table"? How to create one?I don't have a
clue...

THX
Candy

:

I find all of your initial posts by sorting the news group post in
order
of
the "from" column.

In this thread and in your earlier thread, I suggested using a
temporary
table created from possibly the union query.

--
Duane Hookom
MS Access MVP
--

Hi Duane,
Yes, DIS_A is indexed. I've replaced DateValue function with '#',
it
did
speed up a little, but still can not satisfy our client:( Well, it
doesn't
surprise me, since it reduce down to 7 hours(before it takes 8
hours)
to
generate the report for 200 jobs...
I can't find your reply in my previous thread. Which thread is
it?
Any other suggestions?

TIA.
Candy



:

Did you read my reply in your previous thread?

Is DIS_A indexed?

Why use:
WHERE (DIS_A < DateValue('01/01/2000'))
when you could use:
WHERE (DIS_A < #01/01/2000#)

I also suggested using a temporary report table.

--
Duane Hookom
MS Access MVP
--

The report using a crosstab query as record sources is very slow.
It
could
take hours to generate one report. There are 3
queries:qUniCurrSche
(*Union),
qcartJobOpers (*cartesian), and qxtbOperSchedule (*crosstab), the
one
bounded with the report is the qxtbOperSchedule. I've listed the
SQL
code
as
following. Any advice for speeding up will be appreciated.

qUniCurrSche:
SELECT DDue,JobNo,dOrder, "CUT" as Operation, Cut_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "NIT" as Operation, NIT_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "MAN" as Operation, Man_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "DIS" as Operation, Dis_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "EMB" as Operation, EMB_S as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE1" as Operation, OE1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE2" as Operation, OE2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT1" as Operation, PRT1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT2" as Operation, PRT2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN1" as Operation, BUN1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN2" as Operation, BUN2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "INW" as Operation, INW_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "SUB" as Operation, SUB_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "QC" as Operation, QC_S as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
ORDER BY dDue;

qcartJobOpers:
SELECT quniCurrSche.ddue, quniCurrSche.dOrder,
quniCurrSche.JobNo,
quniCurrSche.Operation, quniCurrSche.OperDate,
tblAllDates.theDate
FROM tblAllDates, quniCurrSche
WHERE (((tblAllDates.theDate) Between [OperDate] And (SELECT
Max(OperDate)+1
FROM quniCurrSche j
WHERE (j.JobNo = quniCurrSche.JobNo AND j.OperDate <
quniCurrSche.OperDate) ) )) AND
tblAllDates.theDate >= quniCurrSche.dOrder And
(Year(quniCurrSche.OperDate) >2000)
ORDER BY quniCurrSche.ddue, tblAllDates.theDate;

qxtbOperSchedule:
TRANSFORM First(qcartJobOpers.Operation) AS FirstOfOperation
SELECT Year([TheDate]) AS Yr, DatePart("ww",[TheDate]-1) AS
WeekNum,
qcartJobOpers.ddue, qcartJobOpers.JobNo
FROM qcartJobOpers
GROUP BY Year([TheDate]), DatePart("ww",[TheDate]-1),
qcartJobOpers.ddue,
qcartJobOpers.JobNo
PIVOT Format([TheDate],"dddd") In
("Monday","Tuesday","Wednesday","Thursday","Friday");
Click to expand...
Click to expand...
Click to expand...
 
C

Candy

Another temporary table for qCartJobOpers does not speed up the process. I
think it is because there are so many records generated by qCartJobOpers that
it even consume time to save them into the temporary table.
So, is there anyway to append new records from the query into the
'temporary' table instead of inserting all ? (well, in this case, it would be
a permanent table)

Many thanks.

Duane Hookom said:
I expect the query plan calls for the qCartJobOpers again and again and
again. I assume you have some indexes created on your temporary table.

--
Duane Hookom
MS Access MVP
--

Candy said:
Thank you, Duane, you saved my life.
It now narrow down the reporting time from hours to minutes for 200 jobs.
Considering there might be more than 500 jobs in the same time, I need to
be
a little greedy.
I'll use the similar strategy you've suggested, to create another
temporary
table for the qCartJobOpers and use this table to generate the crosstab
query. Do You think this will work?
But, I don't actually understand why that replacing query data with table
data could speed up the report. Doesn't it using the same procedure? Why
does
it make such differences?

Candy

Duane Hookom said:
Don't worry about the other thread since I have repeated most of it here.
==== from previous thread =====================
I would try create a normalized table with a strucuture mirroring the
qUniCurSche fields. Index this table on all fields. Then prior to opening
your report, delete all the records from the table and use qUniCurSche to
append new records.

Then base qcartJobOpers on the "reporting" table rather than the union
query.
==========================================

--
Duane Hookom
MS Access MVP
--

Hi, Duane,
I still haven't found that thread, could you post the link please?

What do you mean by "temporary table"? How to create one?I don't have a
clue...

THX
Candy

:

I find all of your initial posts by sorting the news group post in
order
of
the "from" column.

In this thread and in your earlier thread, I suggested using a
temporary
table created from possibly the union query.

--
Duane Hookom
MS Access MVP
--

Hi Duane,
Yes, DIS_A is indexed. I've replaced DateValue function with '#',
it
did
speed up a little, but still can not satisfy our client:( Well, it
doesn't
surprise me, since it reduce down to 7 hours(before it takes 8
hours)
to
generate the report for 200 jobs...
I can't find your reply in my previous thread. Which thread is
it?
Any other suggestions?

TIA.
Candy



:

Did you read my reply in your previous thread?

Is DIS_A indexed?

Why use:
WHERE (DIS_A < DateValue('01/01/2000'))
when you could use:
WHERE (DIS_A < #01/01/2000#)

I also suggested using a temporary report table.

--
Duane Hookom
MS Access MVP
--

The report using a crosstab query as record sources is very slow.
It
could
take hours to generate one report. There are 3
queries:qUniCurrSche
(*Union),
qcartJobOpers (*cartesian), and qxtbOperSchedule (*crosstab), the
one
bounded with the report is the qxtbOperSchedule. I've listed the
SQL
code
as
following. Any advice for speeding up will be appreciated.

qUniCurrSche:
SELECT DDue,JobNo,dOrder, "CUT" as Operation, Cut_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "NIT" as Operation, NIT_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "MAN" as Operation, Man_S as OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "DIS" as Operation, Dis_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "EMB" as Operation, EMB_S as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE1" as Operation, OE1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE2" as Operation, OE2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT1" as Operation, PRT1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT2" as Operation, PRT2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN1" as Operation, BUN1_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN2" as Operation, BUN2_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "INW" as Operation, INW_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "SUB" as Operation, SUB_S as OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "QC" as Operation, QC_S as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
ORDER BY dDue;

qcartJobOpers:
SELECT quniCurrSche.ddue, quniCurrSche.dOrder,
quniCurrSche.JobNo,
quniCurrSche.Operation, quniCurrSche.OperDate,
tblAllDates.theDate
FROM tblAllDates, quniCurrSche
WHERE (((tblAllDates.theDate) Between [OperDate] And (SELECT
Max(OperDate)+1
FROM quniCurrSche j
WHERE (j.JobNo = quniCurrSche.JobNo AND j.OperDate <
quniCurrSche.OperDate) ) )) AND
tblAllDates.theDate >= quniCurrSche.dOrder And
(Year(quniCurrSche.OperDate) >2000)
ORDER BY quniCurrSche.ddue, tblAllDates.theDate;

qxtbOperSchedule:
TRANSFORM First(qcartJobOpers.Operation) AS FirstOfOperation
SELECT Year([TheDate]) AS Yr, DatePart("ww",[TheDate]-1) AS
WeekNum,
qcartJobOpers.ddue, qcartJobOpers.JobNo
FROM qcartJobOpers
GROUP BY Year([TheDate]), DatePart("ww",[TheDate]-1),
qcartJobOpers.ddue,
qcartJobOpers.JobNo
PIVOT Format([TheDate],"dddd") In
("Monday","Tuesday","Wednesday","Thursday","Friday");
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

You have lost me. I guess my only attempt to create a temp table would be
from the UNION query. Then make sure fields used in joins and filtering are
all indexed.

--
Duane Hookom
MS Access MVP
--

Candy said:
Another temporary table for qCartJobOpers does not speed up the process. I
think it is because there are so many records generated by qCartJobOpers
that
it even consume time to save them into the temporary table.
So, is there anyway to append new records from the query into the
'temporary' table instead of inserting all ? (well, in this case, it would
be
a permanent table)

Many thanks.

Duane Hookom said:
I expect the query plan calls for the qCartJobOpers again and again and
again. I assume you have some indexes created on your temporary table.

--
Duane Hookom
MS Access MVP
--

Candy said:
Thank you, Duane, you saved my life.
It now narrow down the reporting time from hours to minutes for 200
jobs.
Considering there might be more than 500 jobs in the same time, I need
to
be
a little greedy.
I'll use the similar strategy you've suggested, to create another
temporary
table for the qCartJobOpers and use this table to generate the crosstab
query. Do You think this will work?
But, I don't actually understand why that replacing query data with
table
data could speed up the report. Doesn't it using the same procedure?
Why
does
it make such differences?

Candy

:

Don't worry about the other thread since I have repeated most of it
here.
==== from previous thread =====================
I would try create a normalized table with a strucuture mirroring the
qUniCurSche fields. Index this table on all fields. Then prior to
opening
your report, delete all the records from the table and use qUniCurSche
to
append new records.

Then base qcartJobOpers on the "reporting" table rather than the union
query.
==========================================

--
Duane Hookom
MS Access MVP
--

Hi, Duane,
I still haven't found that thread, could you post the link please?

What do you mean by "temporary table"? How to create one?I don't
have a
clue...

THX
Candy

:

I find all of your initial posts by sorting the news group post in
order
of
the "from" column.

In this thread and in your earlier thread, I suggested using a
temporary
table created from possibly the union query.

--
Duane Hookom
MS Access MVP
--

Hi Duane,
Yes, DIS_A is indexed. I've replaced DateValue function with
'#',
it
did
speed up a little, but still can not satisfy our client:( Well,
it
doesn't
surprise me, since it reduce down to 7 hours(before it takes 8
hours)
to
generate the report for 200 jobs...
I can't find your reply in my previous thread. Which thread is
it?
Any other suggestions?

TIA.
Candy



:

Did you read my reply in your previous thread?

Is DIS_A indexed?

Why use:
WHERE (DIS_A < DateValue('01/01/2000'))
when you could use:
WHERE (DIS_A < #01/01/2000#)

I also suggested using a temporary report table.

--
Duane Hookom
MS Access MVP
--

The report using a crosstab query as record sources is very
slow.
It
could
take hours to generate one report. There are 3
queries:qUniCurrSche
(*Union),
qcartJobOpers (*cartesian), and qxtbOperSchedule (*crosstab),
the
one
bounded with the report is the qxtbOperSchedule. I've listed
the
SQL
code
as
following. Any advice for speeding up will be appreciated.

qUniCurrSche:
SELECT DDue,JobNo,dOrder, "CUT" as Operation, Cut_S as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "NIT" as Operation, NIT_S as
OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "MAN" as Operation, Man_S as
OperDate
FROM jobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "DIS" as Operation, Dis_S as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "EMB" as Operation, EMB_S
as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE1" as Operation, OE1_S as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "OE2" as Operation, OE2_S as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT1" as Operation, PRT1_S as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "PRT2" as Operation, PRT2_S as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN1" as Operation, BUN1_S as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "BUN2" as Operation, BUN2_S as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "INW" as Operation, INW_S as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL
SELECT DDue,JobNo,dOrder, "SUB" as Operation, SUB_S as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
UNION ALL SELECT DDue,JobNo,dOrder, "QC" as Operation, QC_S as
OperDate
FROM JobPath
WHERE (DIS_A < DateValue('01/01/2000'))
ORDER BY dDue;

qcartJobOpers:
SELECT quniCurrSche.ddue, quniCurrSche.dOrder,
quniCurrSche.JobNo,
quniCurrSche.Operation, quniCurrSche.OperDate,
tblAllDates.theDate
FROM tblAllDates, quniCurrSche
WHERE (((tblAllDates.theDate) Between [OperDate] And (SELECT
Max(OperDate)+1
FROM quniCurrSche j
WHERE (j.JobNo = quniCurrSche.JobNo AND j.OperDate <
quniCurrSche.OperDate) ) )) AND
tblAllDates.theDate >= quniCurrSche.dOrder And
(Year(quniCurrSche.OperDate) >2000)
ORDER BY quniCurrSche.ddue, tblAllDates.theDate;

qxtbOperSchedule:
TRANSFORM First(qcartJobOpers.Operation) AS FirstOfOperation
SELECT Year([TheDate]) AS Yr, DatePart("ww",[TheDate]-1) AS
WeekNum,
qcartJobOpers.ddue, qcartJobOpers.JobNo
FROM qcartJobOpers
GROUP BY Year([TheDate]), DatePart("ww",[TheDate]-1),
qcartJobOpers.ddue,
qcartJobOpers.JobNo
PIVOT Format([TheDate],"dddd") In
("Monday","Tuesday","Wednesday","Thursday","Friday");
Click to expand...
Click to expand...
Click to expand...
 
Top