Best Practice for Multi Database Report

Former Member — Tue, 21 Oct 2014 20:25:48 GMT

Hopefully I can explain my issue with clear terminology, but I am fairly new to Crystal Reports so I apologize if this is cloudy.

I am attempting to run a report involving two separate databases on separate servers. One database (we'll call it the information database) gives information about a client while the other database (production database) gives financial production numbers for the salespeople belonging to that client. These two databases can be linked by joining their tables using the salesperson ID field.

My report output needs to show financial production which is grouped by client ID which is grouped by the client's state. This production should be given for the specified date range.

I am able to run two separate reports; one that gives salespeople by client state, and another showing financial production by salesperson for a date range. The production database report is able to sort through 250,000 records and run efficiently in this manner (around 15 - 30 seconds), while the information database can run its report quickly as expected. My issue comes when I combine these two reports in the fashion I mentioned above (financial production by client by state). The report slows to a crawl and is only able to process 500 records per second using my most successful attempt meaning that it takes between 8 to 10 minutes to run the full report. As expected, the total number of records processed grows only slightly from the production database report.

I have attempted using subreports, reordering links in many different ways, and trying a few other table joining techniques to see if any would speed up the report. What I want to know is, what is the best way to link these two databases that would yield the fastest accurate results?

Thank you!

Re: Best Practice for Multi Database Report

abhilash_kumar — Tue, 21 Oct 2014 20:43:37 GMT

Hi,

When you link tables from multiple 'databases', the joins are Not processed on the database.

CR reads everything from database A's tables and then reads from B's eventually Joining them locally. This, in all likelihood, will slow down things.

Here are some of the options to improve performance:

1) Setup a 'dblink' (Oracle) between the two sources and create a View with just the fields you need. Use the View as the datasource for the report

2) Consolidate the two datasources using tools like BO DataServices/Informatica/Pentaho so that everything is available in a single table

-Abhilash

Re: Best Practice for Multi Database Report

Former Member — Tue, 21 Oct 2014 20:56:08 GMT

In my opinion, you can just run two SQL commands and join them together via a UNION.

Question Re: Best Practice for Multi Database Report in Technology Q&A

Best Practice for Multi Database Report

Re: Best Practice for Multi Database Report

Re: Best Practice for Multi Database Report