Report reponse is slow

[Former Member]

I have created a report in Crystal XI that takes for ever to complete. Approximately 5 Minutes. My data base only contain 300 and 50 records respectively. How can I speed up my report. The SQL is as follows:

SELECT `'Updated_List_'`.`Firm`, `'Updated_List_'`.`Email`, `'Updated_List_'`.`Type of Appraiser`, `'Updated_List_'`.`Appraiser`

FROM `'Updated List$'` `'Updated_List_'`

WHERE `'Updated_List_'`.`Type of Appraiser`='Residential'

ORDER BY `'Updated_List_'`.`Firm`

SELECT `'2009_'`.`City`, `'2009_'`.`Order #`, `'2009_'`.`Order Date`, `'2009_'`.`Appraiser `

FROM `'2009$'` `'2009_'`

WHERE `'2009_'`.`City`='Chesapeake'

Please help!

[Former Member · Accepted solution]

check how much time it is taking when you run the same query at the database level.

regards,

Raghavendra.G

[Former Member · Accepted solution]

my data bases are excel file. how do you run a query at the database level in crystal?

[Former Member · Accepted solution]

create a new report by selecting the connection type access/excel (DAO) and connect to your excel sheet and use "add command" and paste the query

SELECT `'Updated_List_'`.`Firm`, `'Updated_List_'`.`Email`, `'Updated_List_'`.`Type of Appraiser`, `'Updated_List_'`.`Appraiser`

FROM `'Updated List$'` `'Updated_List_'`

WHERE `'Updated_List_'`.`Type of Appraiser`='Residential'

ORDER BY `'Updated_List_'`.`Firm`

Now check the report by placing the fields in the report.

regards,

Raghavendra.G

[JWiseman · Accepted solution]

you have 2 select statements from 2 separate tables...this would be why it is taking so long.

two choices:

1) change the 2 selects into 1 by doing a join by appraiser

2) eliminate the 2nd select and use this in a subreport

jw