Database Query slow two databases

I have cyrstal reports that need to read from two different databases.  Normally when my reports look at just one or the other database they read and return the records extremely fast.  But when I connect to both in one report, record reading slows to a craw.  What used to take a split second to run a portfolio holdings report now takes up to a minute or more.  You can imagine if I have a grouped report pulling 100 or more accounts, this is not acceptable.

See the  example below where I connect to both databases (FoxPro & CRM).  Please note that this example is a little more complicated then my typical report that connects to both databases b/c i had a situation that required me to connect the "Trade" table in the CRM database to itself to pull related (linked) trades:

Also, link order and "link ordering is enforced"...how does that play into the speed of it all?

Version: CR-XI

Connections:
investor.iaccountid --> trade.iaccountid (inner join)
receiver.raccountid --> trade.raccountid (inner join)
trade.tradeid --> FilteredNew_trade_New.new_tradenumber (left outer join)
FilteredNew_trade_New.new_sourceid --> FilteredNew_trade_OLD.new_tradeid (inner join)

Query:

Sterling Free Tables
 SELECT `receiver`.`r_letter`, `receiver`.`r_address1`, `receiver`.`r_city`, `receiver`.`r_state`, `receiver`.`r_zip`, `receiver`.`raccountid`, `receiver`.`r_email`
 FROM   `receiver` `receiver`
 WHERE  `receiver`.`raccountid`='60343'
 EXTERNAL JOIN receiver.raccountid={?Sterling DBC: trade.raccountid}


Sterling DBC
 SELECT `trade`.`aafsuffix`, `trade`.`iagentid`, `trade`.`settle_i`, `trade`.`prin_i`, `trade`.`basis_i`, `trade`.`days_i`, `trade`.`mdate_i`, `trade`.`rgross`, `trade`.`rnet`, `trade`.`cgross`, `trade`.`igross`, `trade`.`blocknum`, `trade`.`rbill`, `trade`.`cbill`, `trade`.`ibill`, `trade`.`icobbill`, `trade`.`rcobbill`, `trade`.`i_comment`, `trade`.`tradeid`, `trade`.`raccountid`, `trade`.`iaccountid`
 FROM   `trade` `trade`
 WHERE  `trade`.`raccountid`={?Sterling Free Tables: receiver.raccountid} AND `trade`.`mdate_i`>{d '2013-09-19'} AND `trade`.`settle_i`<={d '2013-09-19'} AND  NOT (`trade`.`rcobbill`='B' OR `trade`.`rcobbill`='P') AND  NOT (`trade`.`i_comment` LIKE 'non uss' OR `trade`.`i_comment` LIKE 'Non-uss')
 EXTERNAL JOIN trade.iaccountid={?Sterling Free Tables: investor.iaccountid} AND trade.tradeid={?uss_crm: FilteredNew_trade_New.new_tradenumber}


Sterling Free Tables
 SELECT `investor`.`i_letter1`, `investor`.`i_city`, `investor`.`i_state`, `investor`.`iaccountid`
 FROM   `investor` `investor`
 WHERE  `investor`.`iaccountid`={?Sterling DBC: trade.iaccountid}


uss_crm
 SELECT "FilteredNew_trade_New"."new_sourceid", "FilteredNew_trade_OLD"."new_settleon", "FilteredNew_trade_OLD"."new_days", "FilteredNew_trade_OLD"."new_bcgrossrate", "FilteredNew_trade_OLD"."new_brname", "FilteredNew_trade_OLD"."new_brnetrate", "FilteredNew_trade_New"."new_tradenumber"
 FROM   "crm_MSCRM"."dbo"."FilteredNew_trade" "FilteredNew_trade_New" INNER JOIN "crm_MSCRM"."dbo"."FilteredNew_trade" "FilteredNew_trade_OLD" ON "FilteredNew_trade_New"."new_sourceid"="FilteredNew_trade_OLD"."new_tradeid"
 WHERE  "FilteredNew_trade_New"."new_tradenumber"={?Sterling DBC: trade.tradeid}
IO_DorkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Crystal does not really handle multiple data sources very well as you are finding out.

The problem is all the data is being brought to the PC for filtering, sorting, and grouping.  With a single data source that is handle on the very fast server.  

Is it possible to use one source for the main report and use the other for a subreport?

Can you set up one source and link it to the other or use MS Access to link to both sources and pull from there?

mlmcc
0
IO_DorkAuthor Commented:
Subreports... I don't know, I guess I'd have to sort that out/figure that out and try it.

On the other recommendation:
So you're suggesting using a third source like access to fool Crystal Reports in thinking that it's only connecting to one database, correct? I guess I could try that too, I'll just have to figure out how to do that.

Thirdly, should I be looking at other reporting software that would handle this duel database situation better? Any suggestions or recommendations if there are any?
0
mlmccCommented:
Are they both SQL Server databases?  If so you should be able to link the tables from one of them to the other.

There are really no good ways to use multiple data sources if you need to link the tables because it forces the EXTERNAL JOIN which moves it to the PC and ALL records from ALL sources are brought in

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

James0628Commented:
This is basically echoing what mlmcc said, but I would see if it's possible to create a "link" from one db to the other.  For example, in MS SQL Server you can create a linked server that points to another db, an Excel file, etc., and then access things via that linked server like they were tables in the MS SQL db.  If neither of your db's has that kind of capability, then maybe (as mlmcc suggested) you could use something else that could link to each of them.

 Other than that or subreports, the only other thing that I can think of would be copying the data from one db to another, so that the report can get everything from one db.  But, of course, that presents problems of its own, assuming that it's even possible.

 James
0
IO_DorkAuthor Commented:
One is SQL I believe ( microsoft crm) and the other is the old and crusty foxpro.
0
IO_DorkAuthor Commented:
does linking a database like ms crm cause any disruption in the performance or is it really just passive link to the other database.  I don't want to do anything that will be to invasive or put a drag on the performance of either database and the front ends that are used to interface with them.
0
mlmccCommented:
The link is passive and data is only requested when a query runs that needs it.

mlmcc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.