Skip to Content

Crystal Reports 2008 -> Performance Improvement Techniques

Following are the steps to be taken care of while designing the crystal report to improve the performance of the designed crystal report:

1) Using a command Object/Stored Procedure as the data source will be faster than using crystal report to link tables or views.

2) Remove unused tables, unused formulas and unused running totals from the report.

3) Whenever possible, limit records through selection, not suppression, meaning use conditional formulas to return a desired field result, instead of using suppression to eliminate unwanted records.

4) If the database is of large size try to use a selection formula within the report to only return those records that you need for the report, rather than having crystal read all the records.

5) If a selection formula is used in the report, make sure that no reference to any crystal functions (i.e. totext, cDate etc) or any other formula is there. If it is referencing crystal functions or report formulas, the database will return all records because it does not understand the crystal functions on the database side, hence filter at database level if possible or use SQL Expression to achieve it, as SQL Expressions are always handled on the database whereas formulas are handled on the report level. Use the Crystal functions in specific formulas rather than the selection formula.

6) Using Indexes for faster data retrieval also improves the performance of the reports. This can be achieved by using the option under menu File|Report Options, “Use Indexes or server for speed”.

7) The fewer sub reports in the report, the faster the report will run. Each sub report will hit the database again to gather data. Preferably use the “On Demand Sub Reports” instead of normal sub reports. Since using the normal sub reports will be a cost factor to retrieve the data from the data source. Also, Linked sub reports will always perform faster than unlinked sub reports, because they are being run on a subset of data being passed from the main report, rather than returning all records.

8) While using text objects, avoid inserting database or formula fields inside them.

9) If some sections are not required to be displayed on the report, try and suppress sections (no drill-down) if you don’t need to see drill-down information. This will speed up report performance, as hiding a section which will calculate and save drill-down information takes longer than a section that is suppressed (no drill-down).

10) The more OLE objects you have inserted, the slower the report will run. The bigger or more complex the OLE object is, the slower the report will run.

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Shawn harmon

    I have lots of data. There are 15++ million records in some of my database files. But I need to drill into the data and get counts for each field.

    I need to know how many times each data field (row) is populated in a database file. For example,  how many of my records have last name? how many have first name? date of birth? etc.  I need a simple report made using SAP “Crystal Reports 2011” Workstation type edition that SUMS each data  row into a single column output like csv.

    My database sizes can range from 1,000 to 18 million records that are in fixed field asci format with 1888 bytes and 256 rows per record!  The report  needs to SUM each data field or, in the case of a data field with a minimum of twelve tables, SUM each value of a table. The output should be in a single column (except for   table values) csv/excel format using our xls layout.

    We hired a CR programmer who was very good but she could not get CR to work on our HOMONGO SIZED data…can anyone here help? Please read her comments below which are presented in the order she progressed or skip to the end to see her conclusion..
              

         
     
    Question 1.     The data in the text file is not delimited by anything specific (i.e.: the fields are not separated by a common character like a TAB). They just have a bunch of spaces and the spaces are not even consistent. Therefore, when Crystal reports reads your file, it will read the record as 1 large data field. Crystal reports reads this as a memo field which does not allow you to do anything with it. Only display it. Therefore, in order to do any summations on your data, you will need to fix up the txt file so that the fields are separated by a common value like a TAB. How does the data get into the TXT format? Does it get exported from another program? Does someone email it to you? Or are you manually typing out all the information? The reason I ask this is that there may be an easier way to report off the data or to get the data into the right output.

    REPLY it is fixed field, notice on the file layout every field length is defined (fixed) and unchanging. if I have to resort to delimiting it will be extra steps. The field lengths and start/stop positions are on the layout.

    Questions  2.     The only rows that are to be displayed in the Crystal Report are rows 1-259 and columns A-C, correct? There is other information in your example file (row 104, col E-N and Rows 270+). I just want to make sure that I understand the correct output.

    REPLY yes the column name, the SUM and the table value SUMS

    Question 3.     Column C may be a bit tricky. I played with the data for a bit and I believe I have a way that we can do this without having to return all 18 million records. This is going to be key. The report will take way too long and could stop working if we try to output all 18 million records. However, I created some custom SQL which should work and will only return the records we need.

    REPLY As noted above I believe C is table values and they can be created at the bottom of the 259 fields if that is easier. You should know that I want a nice vertical column of counts so that I can place the next data update beside it and compare the delta between the two updates  of data. Also, political party, Field #25 has over 80 numerical party codes that would need to be SUMMED indiviudally but values with null would not have to be retuned as is illustrated in the picture down below.
     

    Question 4 This report is definitely not a simple report. Column B is a somewhat simple report and won’t take too long to create. However, column C is going to need some custom formulas and custom sql. I would estimate that this will take me 2-3 days to put together. If you would just like Column B, I could do this for less. Of course this is all dependant on the txt file being set up in a way that Crystal can read the fields as individual fields.

    REPLY I think you know what you are doing but poke around some more because I do not want to have to revise fees or downgrade expectations, what is the next step re downplay, time frame and maybe some bigger data and of course handling fixed field format if possible….maybe we should wrap things up by phone tomorrow and I can tell the others that the deal is closed.
              
    Question 5 Is the Excel file a good one to use for the entire list of field names and the exact position they are in within the txt file? I will use this as a guide for the schema.ini file. Do you have a list that also includes the fixed field length or should I manually count the spaces within the text file?
    REPLY I thought you had two excel layouts already, I have attached the one which has field lengths and start/stops. Notice that the actual data does not have every field populated some some SUMS will be null value. See e- Merges Master File Layout for Voters, Hunters & Fishers and CCW April 12, 2011 no color code.xls
    Question 6 If I do come up with a better way to do things and it will take much more time, I will bring them up and we can discuss whether you would like me to go forward with them or not. However, I meant that I am willing to settle on $1000 for the scope that we have outlined below. Just to make sure you understand, Crystal cannot maintain the previous values run. So when you do your comparison in Excel, you will need to save the old data in excel and compare it to the new data in Crystal. I just want to make sure you understand the limitations of Crystal. I know I said this before in another email, but just want to make sure it made sense to you.
    REPLY – I run the data through Crystal, it generates the counts, I export it to Excel or CSV and I store it outside Crystal right? As you know I have not yet bought Crystal. To be clear, I do not need any other SAP license to use Crystal right. I just buy the $500 or so seat and that is all I need right? You will make a deliverable that is plug and play, I simply import it to Crystal and I am good to go. You will provide detailed how to in a “book” or mini user manual how to get up and generating reports. Your “book” will be inclusive of the details of what you did so that conceivably I could ‘copy cat’ it and implement it with another layout I need reports for. Finally, you will get an FTP from me to access restricted and confidential data that you agree to  protect and destroy when done. The sample voter data will be about six million or more zipped records (I expect the report tool to work on files as large as 25 million) which you will run through the new report tool and benchmark the time to process. I have WIN7 with 10gb memory so testing on something comparable will be best.
    Question 7 If all the fields are a fixed width, then we need a schema.ini file in order for crystal reports to know what field length each field is. Do you have a schema file, would you be creating a schema file, or would you like me to create it?
    REPLY – I do not know how to do it maybe if you do it and in the “book” user manual on how to operate your new program you include some  details for dummies. 
    Question 8 If you are going to be placing the next run data, I am assuming you will be exporting the crystal report into Excel and saving the information, then copying the next run data into excel? Crystal reports cannot hold onto data that was run in the past.
    REPLY – This is true, we want each update column  (Jan 2011, July 2011 for instance) side by side and then I will use a little Excel to compare the two columns and for instance, flag any row that exceeds the other by like 5%.
    Question 9 If you are open to the display of column C, it may make it a bit easier, however you will still need the custom SQL in order to only return the counts rather than the entire dataset. I would still quote that this will take 2-3 days, and therefore I am willing to settle on fee even if I go over.

    REPLY -Well, do not abandon any good ideas that might between now just to stay under the salary cap—contact me while in progress  and we can discuss. Although we want to shake the branches as much as possible now and since we both know our stuff, it is unlikely something may occur after we come to terms but let’s have an open mind.
    Question 10.     We need a schema.ini file in order to define the fields. However, Schema.ini files have a limitation of 255 columns. Therefore, Crystal reports cannot report on your 259 columns from a text file. Are you able to remove 4 columns? If not, you may need to move your data into a database that can handle 259 columns.
    REPLY – To be clear: the output has to be in one column, not rows. The output must appear in Excel in the same way as the file layout looks.  As a imperfect workaround,  discard the final 4 rows.  
    Question 11.     The text file name will need to remain the same each time you run the report. I noticed that the file you sent me had a DATE in the title of the txt file. Please note that the name of the text file needs to be set in the schema.ini file, therefore changing the name of your data file will mess up the schema.ini file. I’m sure this isn’t a big deal for you, just wanted to let it be known.
    REPLY – Please use this file name: master1report . I will rename each data to run it through the application. 
    Question 12     The custom SQL to only return the COUNTS (i.e.: only return 1 record as opposed to 2.3 million), does not appear to work well against a text file. It did not improve performance like it does when reporting off databases like SQL Server, Oracle, etc… This means that I cannot get the report to speed up. On my machine, the report takes 10 minutes to run (against the file you sent me with 2.3 million records) and note that I am not finished all the values (only 15 of 259)  so it could take longer. Therefore, when you try and run this against 80 million records, it could theoretically take a couple hours to run. I have seen reports take this long to run before when there is a lot of data. It is not uncommon and most customers just hit the refresh button and go get a coffee. Or they have the Enterprise addition of Business Objects which allows you to schedule a report to run overnight so that it is ready for you in the morning. But, if this is a show stopper for you, then you might want to consider other options….. Possibly another product can run text files quicker, or getting your data into a beefier database like SQL Server so that you can run stored procedures off the data to return only the counts. These are much more expensive options though.
    Here is a little screenshot of the report so far (only 15of the 256 rows) so that you can see the numbers are coming together:
     
    If you are still wanting to go with Crystal Reports and are able to remove 4 of the fields (there are 4 blank fields at the end), please let me know and I will continue on with the report that I have started.
    REPLY May I suggest you consider adding some calculations for fields like phone. I want SUM but also I need SUM field length seven and SUM of length 10 values.

    Crystal Reports Fail:

    My file is 14.1 gb  as well. However, when run through Crystal Reports, it
    only returns 2,274,876 records. Therefore, I am going to assume that there
    is a Record Limit for Text files of this. I searched through all the
    registry settings and there is nothing that appears to be limiting it. I
    went online for a while to see if there was any information on it, and just
    like when I used to work there, there was nothing concrete. Business Objects
    does not physically test this large of data retrieval, therefore they don’t
    know the exact record limits.

    I’m not sure what to suggest, but I don’t think Crystal Reports is going to
    work for you and I wouldn’t have known that from the beginning without
    testing your data. I’m 99% sure there is a refund policy on the software, so
    you shouldn’t have any issues returning it.

    Since I only specialize in Crystal Reports, I’m not sure what other software
    can handle this amount of data. Possibly Cognos, but you may want to find
    out from them or do some research.

    If your data were in a database that can run stored procedures (like SQL
    Server or Oracle) then you could write SQL to only return the counts. Then
    when Crystal connected to the stored procedure, it should work, but I can’t
    say for sure without testing it. And I don’t have SQL Server or Oracle
    installed on my machine. I believe MYSQL (the free version) has a limit to
    the number of records it can hold and I’m sure 8 million would be over the
    limit. Also, I’m not sure if it supports stored procedures. I have used it
    once before and found it extremely cumbersome and limited. I think SQL
    Server and Oracle are quite pricey, so I don’t think that this will even be
    an option for you.

    Again, no charge for this as I am not giving you anything. I am sorry I
    don’t have better news for you or even better suggestions on where to go. I
    will keep thinking about it and let you know if I come up with any great
    ideas. But in the meantime, please do start to research other options.

    Let me know if you have any thoughts or comments.

    (0) 
    1. Mahesh Sudarsanan
      When we talk about text file,its always better to use some scripting languages like Perl which can process millions of records with in few seconds.Now you can use Win32::OLE::CrystalRuntime::Application package with in Perl to call the crystal reports.All the data processing that needs to be done can be achieved through the Perl code before feeding the text file as input to Crystal reports.
      (0) 
      1. Shawn harmon
        I have WIN7 with 8 processor and 10gb RAM.

        The files are always fixed field asci 1888 byte 259 fields.

        Do you know who I can hire to do this?

        (0) 

Leave a Reply