DB connection - make report run faster

What is the best way to configure my database connections to maximize the speed when refreshing the report. currently, to return 15 records it is taking 15 seconds. I expect this should take much less time like many other reports I have.

I am connecting to 3 dbf tables using the odbc (rdo) connection. see attachment showing the database expert configs in crystal reports 2013.

How else can I organize my connections to make the report run faster?DB-expert-configs.docx
IO_DorkAsked:
Who is Participating?
 
mlmccCommented:
I agree with Vitor.  When you search you have to do a full table scan to find the related records.  Even for 15 records that is process 15* 282,696 records which is a little over 4 million rows.

What are the filters on the report?

Did you link the tables correctly?
It seems to me the TRADE table should be primary then join the others in.

After running the report look at the SQL.  You may find the filters aren't being passed to the database.
Post the SQL from the report so we can see what the query is trying to do.

mlmcc
mlmcc
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
It looks like your proposalhistory table doesn't have any index. And being an history table, I would guess that has an huge amount of data stored, right?
0
 
IO_DorkAuthor Commented:
we archive it from every so often. right now its only 282,696KB.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
IO_DorkAuthor Commented:
yes, that is my ultimate question, how to determine which table should be primary or come first to ensure the most efficient and quick query,  and when to enforce the order of links. I know nothing about structuring proper queries, I just click and drag my way through Crystal .

 here is the SQL query:

Sterling DBC
 SELECT `trade`.`iagentid`, `trade`.`tradeid`, `trade`.`settle_i`, `trade`.`mdate_i`, `trade`.`basis_i`, `trade`.`interest_i`, `trade`.`period_i`, `trade`.`papers_i`, `trade`.`client_i`, `trade`.`blocknum`, `trade`.`days_i`, `trade`.`prin_i`, `trade`.`cgross`, `trade`.`cnet`, `trade`.`cspread`, `trade`.`raccountid`, `trade`.`iaccountid`, `proposalhistory`.`invsetupcmt`, `trade`.`admcmt`
 FROM   `proposalhistory` `proposalhistory` INNER JOIN `trade` `trade` ON `proposalhistory`.`tradeid`=`trade`.`tradeid`
 WHERE  `trade`.`settle_i`={d '2018-02-16'} AND `trade`.`iagentid`>''
 EXTERNAL JOIN trade.raccountid={?Sterling Free Tables: receiver.raccountid} AND trade.iaccountid={?Sterling Free Tables: investor.iaccountid}


Sterling Free Tables
 SELECT `receiver`.`r_letter`, `receiver`.`r_address1`, `receiver`.`r_city`, `receiver`.`r_state`, `receiver`.`r_zip`, `receiver`.`nworth_c`, `receiver`.`tasset_c`, `receiver`.`date_c`, `receiver`.`profit_c`, `receiver`.`nwtoa_c`, `receiver`.`raccountid`, `receiver`.`r_wire`
 FROM   `receiver` `receiver`
 WHERE  `receiver`.`raccountid`={?Sterling DBC: trade.raccountid}


Sterling Free Tables
 SELECT `investor`.`iaccountid`, `investor`.`i_letter1`, `investor`.`i_address1`, `investor`.`i_city`, `investor`.`i_state`, `investor`.`i_zip`, `investor`.`i_address2`, `investor`.`contact`, `investor`.`ach`
 FROM   `investor` `investor`
 WHERE  `investor`.`iaccountid`={?Sterling DBC: trade.iaccountid}
0
 
IO_DorkAuthor Commented:
clarification, the history table file size is 282mb, not 282,000 records.
0
 
IO_DorkAuthor Commented:
Mlmcc:

I did what you suggested, make Trade table primary, and it worked. The report now runs in 3 seconds. see attachment. I thought I already tried that before coming here, but this time it did work. Anyway, what is the conventional wisdom on how to choose which table should be the primary table. Also, does the Crystal Report db expert function "Order Links" do anything at all to the performance if you already have drawn the connection lines between tables? and in what case would I want to check the box for "link ordering is enforced"?

Is there a good book or source to learn and understand sql queries...knowing the difference between inner join, left outer join, right outer join, full outer join, enforce join, etc.DB-expert-configs.docx
0
 
mlmccCommented:
I don't know any good books.

What database do you use?

mlmcc
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm not aware of the EXTERNAL JOIN keyword. It supposed to join to a table from an external database?
Anyway, I can see that you didn't add a primary key in the history table. If you do that, your query will run than a less of a second.
0
 
IO_DorkAuthor Commented:
we are using (yes I know, very outdated) visual FoxPro database. I will look into having a primary key added. This was a table we did not use much, but now I am finding myself needing to run queries on it. There was a lot of bad programing and designing done when we originally had our DB systems built way back when,
0
 
IO_DorkAuthor Commented:
thanks!
0
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.

All Courses

From novice to tech pro — start learning today.