LaborDtl Queries take forever

Yahoo Archive
1
Here's several suggestions which you might've heard before.

1. Purge the data in System Management\Utilities\Database Purge and
Summarize.

2. Reindex the database.

3. If you are querying the LaborDtl with fields that aren't indexed,
consider creating your own. You must be in single-user mode to create
the index and have ProVision/OpenEdge. For example, we created one
called EiLabor for LaborDtl in v8 with the following fields: Company,
ActiveTrans, EmployeeNum, JobNum, Payroll Date. This allowed us to
write a dashboard query for the current user's labor input of last
180 days so they could right-click and go to Job Entry for the
selected job. Without the index, the query takes a long time. But now
it's instantaneous. Query is generated by BAQ based on form input:

for each UserFile where ( UserFile.DcdUserID =
Constants.CurrentUserID) no-lock , each JobHead where (
JobHead.Company = Constants.CurComp And JobHead.JobClosed = no) no-
lock , first LaborDtl where ( LaborDtl.Company = Constants.CurComp
And LaborDtl.ActiveTrans = no And LaborDtl.EmployeeNum =
UserFile.EmpID And LaborDtl.JobNum = JobHead.JobNum And
LaborDtl.PayrollDate >= TODAY + -180) no-lock by JobHead.JobNum Desc.

4. If you can't purge the data, consider consolidating it. Summarize
the hours by month by employee by job or whatever your reporting
criteria requires. This would break references to other records, like
the G/L postings, but you could do it in a "historical" copy of the
database.

--- In vantage@yahoogroups.com, "cac_areed" <areed@...> wrote:
>
> In drilling through past postings about the LaborDtl table, it
seems
> that we are not the only company having issues with high
recordcounts.
> Our LaborDtl table has about 2.5 million records and our DB is one
of
> the larger ones out there (Progress 9.1d). We have tweaked many of
our
> reports and have brought their export times via ODBC from Crystal
8.x
> down to minutes and in some cases seconds. But most/all of the
reports
> that pull their final data from the LaborDtl table take at least
1.5
> hours or worst case 6 hours depending upon server loads, thus
wreaking
> havoc with our automated reporting system schedule when they run
too
> long.
>
> I have read and re-read the data dictionary and looked for an index
to
> help performance, but so far I have not found anything that really
> works after testing. I did tweak one report that targets "JOBS
> RELEASED" from the day before (which targets a single workcenter,
takes
> about 2-7 minutes), but any attempts to use that same logic fail
when I
> try to utilize the entire Workcenter table as a starting point.
>
> I was wondering what performance gains (if any) has anyone been
able to
> find with this table other than what Todd mentions in this post
> (http://groups.yahoo.com/group/vantage/message/41788) ??
>
2
In drilling through past postings about the LaborDtl table, it seems
that we are not the only company having issues with high recordcounts.
Our LaborDtl table has about 2.5 million records and our DB is one of
the larger ones out there (Progress 9.1d). We have tweaked many of our
reports and have brought their export times via ODBC from Crystal 8.x
down to minutes and in some cases seconds. But most/all of the reports
that pull their final data from the LaborDtl table take at least 1.5
hours or worst case 6 hours depending upon server loads, thus wreaking
havoc with our automated reporting system schedule when they run too
long.

I have read and re-read the data dictionary and looked for an index to
help performance, but so far I have not found anything that really
works after testing. I did tweak one report that targets "JOBS
RELEASED" from the day before (which targets a single workcenter, takes
about 2-7 minutes), but any attempts to use that same logic fail when I
try to utilize the entire Workcenter table as a starting point.

I was wondering what performance gains (if any) has anyone been able to
find with this table other than what Todd mentions in this post
(http://groups.yahoo.com/group/vantage/message/41788) ??