Join with multiple DB's

[scooterny]

I am currently using CR10 and I noticed that when I do a join, in this case a join between a DB2 table and a SQL Server table ( but this seems to be the norm ), when you display the SQL, the query looks funny. To be clear, there seems to be 2 seperate queries on top of one another, but they never interact. I am trying to run this query as a true query using for read only or with ur ( uncommited read on DB2 ) to make my query run faster ( as I am going against a table with over 30 million rows and it takes hours to go through. If I cut and paste the CR created query in a command to run, it doesn't even work! What gives?

 

[Turkbear]

Hi,
A join betwen different RDBMS systems will almost always be very inefficient and slow, since it is unlikely that the indexes will be used and it is not the norm..

Can you import the data from one of the DBs to the other and base your report on one database system only?

To use the Sql directly will be difficult since the syntax and connection method/information will be different..( The 2 statements you see are UNIONED by Crystal , I think, to create the dataset).





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[scooterny]

It seems to be true about any join. You can't see that join code when you display the query. I also don't understand why CR does the subtotaling under the covers. You can't see that in the SQL either.

 

[synapsevampire]

The SQL demonstrated is what is passed to the database servers, NOT what Crystal is doing. Since aggregates and joins aren't done on the server when you have disparate databases, then of course you won't see it in the Database->Show SQL Query.

You can build group bys and aggregates into the SQL, most don't design accordingly:
SELECT "Computer_Status"."Store", "Computer_Status"."Computer", SUM("Computer_Status"."Computer"), SUM("Computer_Status"."status")
FROM "ORServer"."dbo"."Computer_Status" "Computer_Status"
GROUP BY "Computer_Status"."Store", "Computer_Status"."Computer"
ORDER BY "Computer_Status"."Store", "Computer_Status"."Computer"

I just created the above by using one datasource, and using every field in eitehr a group or an aggregate, just as you must do when writing SQL.

Hopefully this clarifies what you are seeing and will allow you to write more optimized reports going forward.

I generally suggest writing Views instead to allow for simplified maintenance and reusability, but they aren't always appropriate.

-k