Results take too long...
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=23005
Printed Date: 28 Apr 2024 at 8:51pm
Topic: Results take too long...
Posted By: kmclaugh
Subject: Results take too long...
Date 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.
|
Replies:
Posted By: lockwelle
Date 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
|
Posted By: hilfy
Date 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
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: kmclaugh
Date 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.
|
Posted By: kevlray
Date 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.
|
|