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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

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

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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

From novice to tech pro — start learning today.