Question

Crystal Report Running Very Slow

Integrations

Updated May 9, 2017

asked on May 9, 2017

It takes several hours for a report to refresh that is pulling data from our Laserfiche database. All other reports that run off this database run quickly; it is just this report that has this issue. I included two queries that are used in this report. Does anyone have any suggestions to make this report run more efficiently? Typically this report is run at the end of every month to show matched receivers and invoices.

vendor.str_val as VendorName, dates.date_val as Date, ReceiverNumber.num_val as ReceiverNumber, PONumber.str_val as PONumber, rcvrMatched.str_val as Match

from toc inner join propval as vendor on toc.tocid = vendor.tocid and vendor.prop_id = 13
inner join propval as ReceiverNumber on toc.tocid = ReceiverNumber.tocid and ReceiverNumber.prop_id = 158
inner join propval as dates on toc.tocid = dates.tocid and dates.prop_id = 236 
inner join propval as PONumber on toc.tocid = PONumber.tocid and PONumber.prop_id = 87
inner join propval as rcvrMatched on toc.tocid = rcvrMatched.tocid and rcvrMatched.prop_id = 461


select  PONumber.str_val as PONumber, InvNumber.str_val as InvoiceNumber, dates.date_val as Date, NumReceivers.num_val as NumReceivers


from toc inner join propval as PONumber on toc.tocid = PONumber.tocid and PONumber.prop_id = 87
inner join propval as InvNumber on toc.tocid = InvNumber.tocid and InvNumber.prop_id = 35
inner join propval as dates on toc.tocid = dates.tocid and dates.prop_id = 41
inner join propval as NumReceivers on toc.tocid = NumReceivers.tocid and NumReceivers.prop_id = 188

Replies

replied on May 9, 2017

Hi Dane,

It's been a while since I've worked with CR, so my thoughts might be based on a outdated version, but here goes nothing: where is this query being executed? Are you calling it from within Crystal as a command, or is this query part of a view within the SQL database itself? If it's a command within CR, I know those can be pretty slow and I'd recommend putting that query into a view and calling the view directly within CR. That's what I always used to do and it always seemed to work well.

Good luck!

Rob

replied on May 9, 2017

The queries themselves look efficient, and the query plan it produces on my machine indicates that it makes good use of indexes. I expect that if you use something like SQL Management Studio and run the request directly it will return quickly. If that's the case, you'll know that the issue is in CR.

replied on May 9, 2017

Oh, I just saw that you've previously run that test and determined that it is a problem in CR. I think you'll have better luck in their support forums.

You are not allowed to follow up in this post.