Juliet,
Wow. I can run the example from my previous message in RB or Access in
less than five seconds (although our db is only 2.5GB, the server is
350MHz).
The general rule of thumb, as Todd noted, is to always join on indexes.
The corollary is to filter and sort on indexes, if possible.
But to give you an idea of why this is important, here's what a query does.
If you query LaborHed for EmployeeNum "XYZ" the query has to read the
whole dataset to find your records.
If you start with Company, and join to LaborHed on Company.Company, then
filter on Company.Company (not LaborHed.Company), your query dataset is
instantly reduced.
Further, if you then filter on any index (such as LaborHed.PayrollDate),
the query dataset is reduced further.
The objective is to reduce the dataset to the smallest possible size for
your query to search. The smaller the dataset, the quicker the query
runs.
If you filter on any non-indexed field, you're automatically increasing
the size of the initial dataset, because the Progress server will just
dump everything into your PC for your query to pick through. I imagine
that's what's happening with your query.
Also watch out for outer joins. If you start with Company and create an
inner join to LaborHed, then an outer join to EmpBasic, be sure that any
sorting or filtering is done in the first two tables, and not in EmpBasic.
And that brings up one final caveat: If you filter on a field that is
returned from an outer join, your outer join becomes an inner join, and
thus you "lose" data from your query. If you have to do this, the best
thing to do is dump to a static table and then do your filtering there.
have fun,
john
Juliet Martin wrote:
Wow. I can run the example from my previous message in RB or Access in
less than five seconds (although our db is only 2.5GB, the server is
350MHz).
The general rule of thumb, as Todd noted, is to always join on indexes.
The corollary is to filter and sort on indexes, if possible.
But to give you an idea of why this is important, here's what a query does.
If you query LaborHed for EmployeeNum "XYZ" the query has to read the
whole dataset to find your records.
If you start with Company, and join to LaborHed on Company.Company, then
filter on Company.Company (not LaborHed.Company), your query dataset is
instantly reduced.
Further, if you then filter on any index (such as LaborHed.PayrollDate),
the query dataset is reduced further.
The objective is to reduce the dataset to the smallest possible size for
your query to search. The smaller the dataset, the quicker the query
runs.
If you filter on any non-indexed field, you're automatically increasing
the size of the initial dataset, because the Progress server will just
dump everything into your PC for your query to pick through. I imagine
that's what's happening with your query.
Also watch out for outer joins. If you start with Company and create an
inner join to LaborHed, then an outer join to EmpBasic, be sure that any
sorting or filtering is done in the first two tables, and not in EmpBasic.
And that brings up one final caveat: If you filter on a field that is
returned from an outer join, your outer join becomes an inner join, and
thus you "lose" data from your query. If you have to do this, the best
thing to do is dump to a static table and then do your filtering there.
have fun,
john
Juliet Martin wrote:
> Thanks. I have done all of the following except the LaborDTL table I don't need. Just laborhed and empbasic. Still slow. The same report runs in roughly 1 - 2 minutes in Report Builder (and I am not using Company on that one as the master table). I just ran this report with all the changes suggested and it has been going for 10 minutes now.
>
> Why such a performance drop?
>
> I will look this over again maybe I am missing something.
>
> Juliet