Hi all,
After consulting a few of the experts here, I have found one a few
tips/tricks to obtain the fastest query from Progress 9.1D (Vantage
6.1) via Crystal reports 8.0x and an ODBC connection.
Many thanks to Todd Caughney for graciously pointing me in the right
direction... Here are the steps I took and the results I obtained.
The task was to increase the speed of a "Job Last Scan Location"
report, which typically took at least 1.5 hours overnight to run, and
would typically take 2-3 hours to run mid-day.
According to Todd, using indexes specified in the Data Dictionary was
my best bet to increase the performance, which goes with what I was
taught in Vegas just last year. I had made some attempts prior to
this, but had no success at increasing the performance of queries
that specifically targeted the LaborDtl table. This is largely due
to the fact that our DB is well over 18gb, and the LaborDtl table
contains over 2.5 million records, with on average of maybe 1-3000
records being added on a daily basis.
Since the existing report targeted Open Jobs only and had the
LaborDtl table linked (it was on the left of the link, which was
incorrect) to the JobHead table for JobComplete field validation, I
considered still using the JOBHEAD table as my left-most table. So I
opened a new blank report, added JobHead and LaborDtl.
In reviewing the Vantage Data Dictionary, I found that the JOBHEAD
table had an index called "CMPLJOBNUM", which used the fields:
Company (as usual)
JobClosed
JobComplete
JobNum
I then looked at the LaborDtl table in Data Dictionary and found an
index called "JOBLASTLOC", which used the fields:
Company
JobNum
AssemblySeq
ClockOutMinute
OprSeq
Since I am interested in Open Jobs, both JobClosed and JobComplete
will be set to false in Crystal Reports via the [Selection Expert]
menu item under the main [Report] menu item. And since I want all
the assembly/opr sequence items, I will follow Todd's advice and add
them as well to the Selection Expert and set them to "Is Not Equal
to" with a value of -1, as they are both integer values (always
positive in Vantage). So, after adding the tables into the linking
screen, I created a link from JobHead.Company TO LaborDtl.Company and
I created a link from JobHead.JobNum TO LaborDtl.JobNum. I then set
the links to Left Outer Join. In this case, Left Outer Joins seem to
always run faster on this table and since I expect to always get back
records with clock activity against them and it is highly unlikely I
will have jobs with no clock activity (Production control clocks in
on OprSeq 10 as soon as the jobs are released).
So, we have the following structure
-----------------------------------
JobHead LaborDtl
Company ---------> Company (Left Outer Join, *=)
JobNum ---------> JobNum (Left Outer Join, *=)
JobClosed = False
JobComplete = False
JobNum <> ""
AssemblySeq <> -1
OprSeq <> -1
ClockOutMinute = Formula
Crystal Formula:
Not ({LaborDtl.ClockOutMinute} < (((DateDiff ("d",Date
(1953,10,30),Date (2004,1,1))) * 24) * 60))
Please note that the above formula creates the correct (?) clockout
minute based upon the Epicor provided startdate for the Minute
counter. This is the number of minutes since the specified date of
October 30th, 1953.
At this point, I have covered ALL of the fields in the indexes for
both tables, and have linked the relevant fields to obtain the
correct jobs that are open. Again following Todd's advice, I needed
to ensure that all the indexed fields I was using were in the exact
order as they appeared in the Data Dictionary. So I drilled down
Crystal's menu structure [Database] -> [Show SQL Query] and looked at
the order of the WHERE items. Joins will always appear first and in
the order you linked them, but the remaining items seem to have no
manageable means of ordering them correctly, or so it seemed. After
clicking items in the [Selection Expert] and then reviewing the SQL
statement, I found that Crystal ordered the items in the same order
that you clicked the tabs in the [Selection Expert]. In other words,
if you click JobHead.Company first then JobHead.JobClosed second,
etc., then the remaining items in Order (first table indexed fields
first, second table fields next, IN ORDER), you will have all the
fields properly sequenced in the SQL statement! This technique
should ensure that the server matches your index usage with the
proper index that you intended for it to use.
Next, the origional report sorted the jobs by JobNum,
AssemblySeq,ClockInDate,ClockInTime and OprSeq all ascending in that
order. This usage of sorting forced the sort onto the server and
typically caused 30+ minutes more processing time to be required from
the server. A test of the initial unsorted report resulted in a much
faster report, so I began reviewing my options for client side
sorting.
Fortunately, my query is not dynamic in the sense that I need the
ClockOutMinute to change on a daily basis. So I went into Crystal
and grouped the report on the same fields that I want sorted. This
forces Crystal to also sort each grouped field, as the "sort
Ascending" option is available when you group a field. After I added
each item, I would delete the header and remove the gap added to the
report for that group's header and footer sections. This made the
grouping transparent to the report itself, still maintaining the
initial results spacing without breaks for the groupings.
***Be sure to save a copy of this report BEFORE you alter the SQL
statement***
Altering the SQL Statement in my case caused the Selection Expert
settings to disappear from the report design. Thus any new changes
would need to be re-implemented....
This grouping still forced the server to sort the records, so I went
back to view the SQL Query and removed the entire "ORDER BY" section
(the bottom section of the query). I also made sure that the
[Database] -> [Perform Grouping On Server] menu option was not
checked and I disabled the [File] -> [Use Indexes On Server For
Speed] option was disabled. When Enabled, this option causes the
report to populate the WHERE and ORDER BY sections of the SQL Query,
but since we have overriden the SQL statement, we can safely turn
this option off. This forces Crystal Reports to perform the sorting
on the client side, which in this case is a sort of only 250,000+
records that this example returns.
Although the final time results for this report are still slow by
some standards, I consider the 20-30 minute time from start to finish
to be the best ever for this report, especially during full
production hours. I expect this report to scream for most of you, as
this report begins returning records almost immediately once it is
started, whereas it never would return records until hours after it
started, or even when this report was set to sort on the server it
would take 40-50 minutes before starting to return data.
Thanks again Todd Caughney (and others that posted here in the past
few years regarding LaborDtl table reports).
After consulting a few of the experts here, I have found one a few
tips/tricks to obtain the fastest query from Progress 9.1D (Vantage
6.1) via Crystal reports 8.0x and an ODBC connection.
Many thanks to Todd Caughney for graciously pointing me in the right
direction... Here are the steps I took and the results I obtained.
The task was to increase the speed of a "Job Last Scan Location"
report, which typically took at least 1.5 hours overnight to run, and
would typically take 2-3 hours to run mid-day.
According to Todd, using indexes specified in the Data Dictionary was
my best bet to increase the performance, which goes with what I was
taught in Vegas just last year. I had made some attempts prior to
this, but had no success at increasing the performance of queries
that specifically targeted the LaborDtl table. This is largely due
to the fact that our DB is well over 18gb, and the LaborDtl table
contains over 2.5 million records, with on average of maybe 1-3000
records being added on a daily basis.
Since the existing report targeted Open Jobs only and had the
LaborDtl table linked (it was on the left of the link, which was
incorrect) to the JobHead table for JobComplete field validation, I
considered still using the JOBHEAD table as my left-most table. So I
opened a new blank report, added JobHead and LaborDtl.
In reviewing the Vantage Data Dictionary, I found that the JOBHEAD
table had an index called "CMPLJOBNUM", which used the fields:
Company (as usual)
JobClosed
JobComplete
JobNum
I then looked at the LaborDtl table in Data Dictionary and found an
index called "JOBLASTLOC", which used the fields:
Company
JobNum
AssemblySeq
ClockOutMinute
OprSeq
Since I am interested in Open Jobs, both JobClosed and JobComplete
will be set to false in Crystal Reports via the [Selection Expert]
menu item under the main [Report] menu item. And since I want all
the assembly/opr sequence items, I will follow Todd's advice and add
them as well to the Selection Expert and set them to "Is Not Equal
to" with a value of -1, as they are both integer values (always
positive in Vantage). So, after adding the tables into the linking
screen, I created a link from JobHead.Company TO LaborDtl.Company and
I created a link from JobHead.JobNum TO LaborDtl.JobNum. I then set
the links to Left Outer Join. In this case, Left Outer Joins seem to
always run faster on this table and since I expect to always get back
records with clock activity against them and it is highly unlikely I
will have jobs with no clock activity (Production control clocks in
on OprSeq 10 as soon as the jobs are released).
So, we have the following structure
-----------------------------------
JobHead LaborDtl
Company ---------> Company (Left Outer Join, *=)
JobNum ---------> JobNum (Left Outer Join, *=)
JobClosed = False
JobComplete = False
JobNum <> ""
AssemblySeq <> -1
OprSeq <> -1
ClockOutMinute = Formula
Crystal Formula:
Not ({LaborDtl.ClockOutMinute} < (((DateDiff ("d",Date
(1953,10,30),Date (2004,1,1))) * 24) * 60))
Please note that the above formula creates the correct (?) clockout
minute based upon the Epicor provided startdate for the Minute
counter. This is the number of minutes since the specified date of
October 30th, 1953.
At this point, I have covered ALL of the fields in the indexes for
both tables, and have linked the relevant fields to obtain the
correct jobs that are open. Again following Todd's advice, I needed
to ensure that all the indexed fields I was using were in the exact
order as they appeared in the Data Dictionary. So I drilled down
Crystal's menu structure [Database] -> [Show SQL Query] and looked at
the order of the WHERE items. Joins will always appear first and in
the order you linked them, but the remaining items seem to have no
manageable means of ordering them correctly, or so it seemed. After
clicking items in the [Selection Expert] and then reviewing the SQL
statement, I found that Crystal ordered the items in the same order
that you clicked the tabs in the [Selection Expert]. In other words,
if you click JobHead.Company first then JobHead.JobClosed second,
etc., then the remaining items in Order (first table indexed fields
first, second table fields next, IN ORDER), you will have all the
fields properly sequenced in the SQL statement! This technique
should ensure that the server matches your index usage with the
proper index that you intended for it to use.
Next, the origional report sorted the jobs by JobNum,
AssemblySeq,ClockInDate,ClockInTime and OprSeq all ascending in that
order. This usage of sorting forced the sort onto the server and
typically caused 30+ minutes more processing time to be required from
the server. A test of the initial unsorted report resulted in a much
faster report, so I began reviewing my options for client side
sorting.
Fortunately, my query is not dynamic in the sense that I need the
ClockOutMinute to change on a daily basis. So I went into Crystal
and grouped the report on the same fields that I want sorted. This
forces Crystal to also sort each grouped field, as the "sort
Ascending" option is available when you group a field. After I added
each item, I would delete the header and remove the gap added to the
report for that group's header and footer sections. This made the
grouping transparent to the report itself, still maintaining the
initial results spacing without breaks for the groupings.
***Be sure to save a copy of this report BEFORE you alter the SQL
statement***
Altering the SQL Statement in my case caused the Selection Expert
settings to disappear from the report design. Thus any new changes
would need to be re-implemented....
This grouping still forced the server to sort the records, so I went
back to view the SQL Query and removed the entire "ORDER BY" section
(the bottom section of the query). I also made sure that the
[Database] -> [Perform Grouping On Server] menu option was not
checked and I disabled the [File] -> [Use Indexes On Server For
Speed] option was disabled. When Enabled, this option causes the
report to populate the WHERE and ORDER BY sections of the SQL Query,
but since we have overriden the SQL statement, we can safely turn
this option off. This forces Crystal Reports to perform the sorting
on the client side, which in this case is a sort of only 250,000+
records that this example returns.
Although the final time results for this report are still slow by
some standards, I consider the 20-30 minute time from start to finish
to be the best ever for this report, especially during full
production hours. I expect this report to scream for most of you, as
this report begins returning records almost immediately once it is
started, whereas it never would return records until hours after it
started, or even when this report was set to sort on the server it
would take 40-50 minutes before starting to return data.
Thanks again Todd Caughney (and others that posted here in the past
few years regarding LaborDtl table reports).