SQL using 1 table from 4 databases to generate a complete report
I fixing a report someone made and i'm running into an issue where items are missing.
Is it possible to generate 1 complete report using a product table from 4 different databases on the same server. 90% of this table is mirrored across the 4 databases, but unique records have been added to each.
Currently the report is setup using 1 FROM database03
There are about 22 left outer joins in this report that seem to be handling the information correctly for the records it is suppling.
Question is : Can this be done and will it be handled via FROM or LEFT OUTER JOIN. I can supply the current code if this is possible.
Thanks in advance
Brian Dong replied
In a word OOOUUUCCCHHH!!!!! 22 outer joins!
Can it be done, chances are not very well. That's a lot of joins between a set of tables. Because each table is coming from a different database, if you did it, I'm pretty sure it would take about 20 hours for a simple report to run. That's a conservative estimate and a good chance it won't work still.
An alternative is to take the querying out of Crystal and do it in a stored procedure. Depending on the database you are using, you can use linked or remote databases and create a query that will do this. I'd recommend instead of querying all 4 tables at once, creating a temp table or cursor and drop each table into it. Then you will get all the information you need from each table and send it off to Crystal.
Crystal doesn't handle linking cross databases very well because it wants to generate a single query. When you cross databases, it requires querying each of the databases. So Crystal works around this by querying the first database and then grabs all the records from each of the other databases and processes the records locally. So instead of the database doing the work Crystal does the work on your machine, potentially downloading a huge amount of records to your machine.
If this is going to be something that happens regularly and you have a large amount of data, look into developing a data warehouse that will centralize the information from all the databases into a central location you can report on.