Crystal Report Very Slow
Hello,
I have a report that is looking at labor times vs standard times for our employees. It is a very slow running report. I believe this is due to the amount of records it is looking through. This report is run for a single day using a parameter by date. This report is only run for the day before for managers to look at the next day to spot issues either with the system or our processes. I guess my question is: is there a way to tell Crystal to ignore all dates, say up until a month ago, so it only has to look at a month’s worth of data? I would think this would speed up the reporting. I have attached a copy of a similar report.
Thank you
Westfield_Labor.rpt
I have a report that is looking at labor times vs standard times for our employees. It is a very slow running report. I believe this is due to the amount of records it is looking through. This report is run for a single day using a parameter by date. This report is only run for the day before for managers to look at the next day to spot issues either with the system or our processes. I guess my question is: is there a way to tell Crystal to ignore all dates, say up until a month ago, so it only has to look at a month’s worth of data? I would think this would speed up the reporting. I have attached a copy of a similar report.
Thank you
Westfield_Labor.rpt
I just noticed you have 2 subreports that seem to use the same tables and the same filtering. That means you are creating the dataset 3 times.
The reports also seem to be very similar.
Is there a reason for that?
This might run quicker as 3 separate reports.
mlmcc
The reports also seem to be very similar.
Is there a reason for that?
This might run quicker as 3 separate reports.
mlmcc
ASKER
I will post the sql in a moment.
The reason there are actually 3 reports is it is running the 3 reports against 3 identical databases that have different data. Three business units and they want the data on one report. Yes, running each does go faster, there is one that has much more data, (older data as well), than the others.
The reason there are actually 3 reports is it is running the 3 reports against 3 identical databases that have different data. Three business units and they want the data on one report. Yes, running each does go faster, there is one that has much more data, (older data as well), than the others.
Ok, I didn't look at the specifics of the data source to see they were different databases.
mlmcc
mlmcc
ASKER
SELECT `tcslabor`.`lempl`, `tcsempl`.`first`, `tcsempl`.`last`, `tcslabor`.`ldte`, `tcslabor`.`lhrs`, `tcslabor`.`lwo`, `tcslabor`.`lrel`, `tcslabor`.`lseq`, `tcslabor`.`lmctr`, `tcslabor`.`lproccd`, `tcslabor`.`lacc`, `tcslabor`.`lrej`, `tcslabor`.`ldone`, `tcstvhdr`.`thpn`, `tcslabor`.`ltype`, `tcsempl`.`status`, `tcsempl`.`prempl`, `tcstvdet`.`tdrun`, `tcstvdet`.`tdsetup`
FROM ((`tcslabor` `tcslabor` INNER JOIN `tcstvdet` `tcstvdet` ON ((`tcslabor`.`lwo`=`tcstvd
WHERE `tcsempl`.`status`='A' AND (`tcsempl`.`prempl`='1088 SASA' OR `tcsempl`.`prempl`='1166 ALRI' OR `tcsempl`.`prempl`='1225 NOBU' OR `tcsempl`.`prempl`='1269 ALBO' OR `tcsempl`.`prempl`='1271 JULA' OR `tcsempl`.`prempl`='1278 KEMU' OR `tcsempl`.`prempl`='1280 NAHE' OR `tcsempl`.`prempl`='1375 KIBE' OR `tcsempl`.`prempl`='1413CA
ORDER BY `tcsempl`.`first`
FROM ((`tcslabor` `tcslabor` INNER JOIN `tcstvdet` `tcstvdet` ON ((`tcslabor`.`lwo`=`tcstvd
WHERE `tcsempl`.`status`='A' AND (`tcsempl`.`prempl`='1088 SASA' OR `tcsempl`.`prempl`='1166 ALRI' OR `tcsempl`.`prempl`='1225 NOBU' OR `tcsempl`.`prempl`='1269 ALBO' OR `tcsempl`.`prempl`='1271 JULA' OR `tcsempl`.`prempl`='1278 KEMU' OR `tcsempl`.`prempl`='1280 NAHE' OR `tcsempl`.`prempl`='1375 KIBE' OR `tcsempl`.`prempl`='1413CA
ORDER BY `tcsempl`.`first`
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You said that one of the reports (Main or subreport?) has much more data. Does that one take much longer than the others if you run them separately?
You posted the query from one report. Is the query from the other reports the same? If not, we may be looking at the wrong query.
James
You posted the query from one report. Is the query from the other reports the same? If not, we may be looking at the wrong query.
James
ASKER
The Main report has the most data in the database to sift through. It is from a larger company with many, many more labor records to look at.
When they're run separately, does the main report take much longer than the subreports?
James
James
ASKER
Yes it does.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is the date field indexed?
IS the prempl field indexed?
Can you show the SQL it is running?
Run the report
Click DATABASE --> SHOW SQL QUERY
Copy it and paste it here.
mlmcc