| Author |
Message |
kmclaugh
Newbie
Joined: 12 Aug 2021
Online Status: Offline
Posts: 4
|
   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 Logged |
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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 Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
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 Logged |
|
kmclaugh
Newbie
Joined: 12 Aug 2021
Online Status: Offline
Posts: 4
|
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 Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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 Logged |
|
|
|
