• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 739
  • Last Modified:

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

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)


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}

 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}
  • 3
  • 3
2 Solutions
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?

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?
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

IO_DorkAuthor Commented:
One is SQL I believe ( microsoft crm) and the other is the old and crusty foxpro.
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.
The link is passive and data is only requested when a query runs that needs it.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now