Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Results take too long... Post Reply Post New Topic
Author Message
kmclaugh
Newbie
Newbie


Joined: 12 Aug 2021
Online Status: Offline
Posts: 4
Quote kmclaugh Replybullet Topic: Results take too long...
    Posted: 09 Jun 2022 at 10:39am
What can I do to speed up the results? I have created a very simple report based on one table and pulling just a few fields. Unfortunately this table is VERY large. It is housed on our vendor's servers and I connect via ODBC. The report works and if I only look at a portion the results are correct. I am trying to filter on one field and it appears the problem is that it is going through every single row to filter. Is there a setting or something else I can do to make it faster?   When I do the same thing in Sequel ViewPoint the results display almost immediately. ViewPoint is our vendor's application we can use to query the data....it just isn't as user friendly as Crystal.

Thanks for any help.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 10 Jun 2022 at 6:59am
I am guessing that you are using a command to pull the results...If not, I would try to create a SQL command for CR to use, otherwise, CR is reading every row of the table(s).

If your data is coming from a Microsof SQL Server, first thing I would try is to add WITH(NOLOCK) after each table in in your FROM clause. This means it will take the most recently committed value from the table. If the value is in the process of being updated, you will get the older value. It speeds up the retrieval process as it simply reading the values and not waiting for some other process to unlock the value.

Second thing to try is to add a parameter to the SELECT and then to populate it when you refresh the data...I would add handling for a NULL or a default case for when you don't need the filter. This way the database will be filtering the data and not CR.

HTH
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 13 Jun 2022 at 10:53am
First off, make sure that you have "Use Indexes or Server for Speed" turned on in the report options. If this doesn't speed up the report, are you using any Crystal formulas in the Select Expert? If so, that could be what's causing the data to be pulled into memory to be filtered in memory. If you'll post the formula from the Select Expert, I might be able to help you rewrite it to work more efficiently.

If you do have to move over to a Command, please see https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/ for more information about the best way to use commands.

-Dell
IP IP Logged
kmclaugh
Newbie
Newbie


Joined: 12 Aug 2021
Online Status: Offline
Posts: 4
Quote kmclaugh Replybullet Posted: 16 Jun 2022 at 11:12am
Thanks for the suggestions. I am using a SQL query to pull the results. I do have Use Indexes or Server for Speed turned on. I am not using any formulas. I simply want to return records for 1 value in 1 field but it really does seem like it is looking through every single record to obtain results. I am not familiary with the WITH(NOLOCK) option so I will give that a try.

I welcome any other suggestions. Thank you.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 21 Jun 2022 at 4:38am
I works with a DB that has millions of rows (Informix vendor supplied) and I have figured out a few ways that seems to help (trial and error).  Lots of luck.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.