I have a report I'm working on that gets data from 3 sources.
First is a database that has two tables I'm interested in, Regions and Stores (where each store is in some region).
Then in another database there is an SQL command that does some joins and returns some totals for a given day (the parameter) for every active region-store-register-cashier ( an example row would be for cashier 1 on register 1 at store 3 in region 1 and it would show that 50 people came through the line and spent $500, and the register ended the day with $750). This list is also in an outer join with the list of all registers, so on a register where nobody worked on that particular day, the totals would be null (cashier NULL on register 99 at store 3 in region 1 with NULL people and NULL dollars spent and ended the day with NULL)
Now, these two bits worked together fine. What I needed to do was have a third dataset to fill in the NULL end of day total in the report. What this was supposed to do was get the end of day value for the most recent day prior to the date being looked at where the register was used (i.e. if doing the report for 12/25 and the register wasn't active from 12/20 through 12/25 it would return the value from 12/19). The command that does this works fine. However, now it takes 30-60 seconds to generate the report on data that previously only took at most 5 seconds.
Running all these queries themselves only takes a second or two, the rest of the time is spent by Crystal Reports doing something. The second command returns about 30 rows and the third command is only used to fill in some blanks. What can I do to speed up the report generation?
First is a database that has two tables I'm interested in, Regions and Stores (where each store is in some region).
Then in another database there is an SQL command that does some joins and returns some totals for a given day (the parameter) for every active region-store-register-cashier ( an example row would be for cashier 1 on register 1 at store 3 in region 1 and it would show that 50 people came through the line and spent $500, and the register ended the day with $750). This list is also in an outer join with the list of all registers, so on a register where nobody worked on that particular day, the totals would be null (cashier NULL on register 99 at store 3 in region 1 with NULL people and NULL dollars spent and ended the day with NULL)
Now, these two bits worked together fine. What I needed to do was have a third dataset to fill in the NULL end of day total in the report. What this was supposed to do was get the end of day value for the most recent day prior to the date being looked at where the register was used (i.e. if doing the report for 12/25 and the register wasn't active from 12/20 through 12/25 it would return the value from 12/19). The command that does this works fine. However, now it takes 30-60 seconds to generate the report on data that previously only took at most 5 seconds.
Running all these queries themselves only takes a second or two, the rest of the time is spent by Crystal Reports doing something. The second command returns about 30 rows and the third command is only used to fill in some blanks. What can I do to speed up the report generation?
