CR Performance :((( 6

[Gennady]

I'm realy disappointed about Crystal Report Performance.

Look for a very simple example - report that shows employees whose birthday hits in selected period.

Report selection formula :
Month({?DateBegin})<=Month({domainEmployee.eBirthDate}) And Month({?DateEnd})>=Month({domainEmployee.eBirthDate})

Estimated time
about 100 records - very quickly.
762 records - receiving data from server - 1,5 minutes, forming report - 12 minutes !!!
3500 records - receiving data from server - 2,5 minutes, forming report - more than half hour (if Crystal survive)

My computer - PII-350Mhz, 128 Mb RAM.

I have many difficult reports. But with such performance I won't use it. May be I did somthing wrong or I can do something, I don't know ((

I need good advice.

 

[Snaggs]

Gennady,

Are you using ADO to base the report on? Typically what happens is when you use the SelectionCriteria of the report, the report still has to look through all the records before it comes up with the results. For example, if you have 1000 records in the database and only 10 of them match, Crystal still has to look at every record in the table. Then it weeds out the 990 that it doesn't need. You'll see it do this in the report preview window.

Typically what I do, is create an ADO recordset and prefilter the records. This puts all the filtering work on the SQL Engine so now I have ONLY the records I want to see. Then I pass the ADO recordset to the report all it does it format the data, but it doesn't have to do any filtering, just grouping or whatever you have it do.

My experience with Crystal is that it's pretty fast. I have 8000+ records in a database and takes about 10 - 15 seconds to retrieve and format all of them.

I haven't had much experience working with SQL Server or Oracle and Crystal. All the stuff I have done has been with MS-Access on a local machine or over a network.

What kind of database are you connecting to and how are you connecting to it?

Steve
tribesaddict@swbell.net

 

[MalcolmW]

Steve hit the nail on the head - the record selection process is primarily designed to be easy to use, not efficient. Because you are using a SCR function in your record selection (Month()), all the records are being pulled from your database into the report, then (I presume) 11/12 of them are discarded.
By filtering the dates in your database (as Steve suggests) you improve performance in two ways. First, the network traffic is reduced because fewer rows are returned to the report. Secondly, the report no longer has to process the records to determine which ones to discard - that has been done at the database level.
Whether ADO, Crystal Query, stored procedures, or views are the best answer for you, there are lots of ways to get the performance.
Malcolm
wynden@telus.net
November is &quot;be kind to dogs and programmers&quot; month. Or is that &quot;dogs or programmers&quot;?

 

[Gennady]

Steve and Malcolm,

Thank you both for advice. This repors are simlest part of project that I working for. I wanted use existing bisness objects through Crystal Data Objects for quick design some dozen of simple reports.
But I understand I must write some additional BO.
Crystal Reports formulas are useless

 

[KathrynC]

Dear Gennady: You have discovered very quickly what it takes many people a long time to discover. It is always better to use Crystal primarily as a display tool and let the database do the hard work. However, a few stored procedures and some very basic SQL can make all the difference in the world, no matter how complex the reports are. It's unfortunate that all that fancy formula stuff in Crystal is slow, but if you apply the same logic directly to the database and produce your result set, then pass that to Crystal, you will find that it outputs things very nicely and gives you lots of display flexibility.

 

[kridg]

We have more than 6,000,000 records on our AS/400 database, so I have to be very careful of my initial query criteria. One of the worst is a monthly report that originally used 800,000+ records yet only produced totals for 5,000. Once the query was refined, the 3 hour query took 20 minutes and the report takes about 10. Hopefully you'll look at taking some formal classes on Crystal report usage before giving up on it.

 

[mfrog]

If you are using page numbering and the such this can also slow down CR. try removing any &quot;Pretty Stuff&quot; and see if it will format faster.

 

[FowlerConsulting]

CR does all the record filtration at the client, and is a BIG performance HIT!

Why does the tool do this?
Because it was designed as a presentation layer for reporting not business rule processing (Though there has been some major improvement to this over the last three version). It was never really good at record filtration and aggregation.

That is why you want to use an OLAP datasource or move the demoralization and aggregation back to the DB server via stored procedure. Many organization cannot afford to configure and maintain a data warehouse so stored procedures are usually the way they go. In doing so your data is 99% the way you need to for final output.
Steven Fowler, Principal
steve.fowler@fowlerconsulting.com

http://www.fowlerconsulting.com

- Development, Training, and Consulting

 

[Ruairi]

Gennady,
What everyone has told you so far is true. However, it can still be very simple and quick to write these reports. I'm assuming you're using VB6 and CR8 with access. If you are using SQL Server instead, all the better but you do need CR8. Go ahead and make your report using Crystals Record selection formulas. When it is working(dont worry about how fast) cut and paste the reord selection formula from 'Show SQL Querey' on the 'Database' menu to notepad. Now remove all record selection formulae and parameter fields from the report and save it to a new filename. Now add the Crystal Reports RDC (report designer component) to a VB project and choose to use an existing report, importing the report you've created(without the paramaters). Rebuild the querey string in VB, substituting literal dates that you get from an input form (or numbers, strings, whatever) for the {?} parameter fields in the querey. Below is an example of building the string and setting it to the reports record selection formula. Wherever you see variables being added to the string is where Crystal Parameter fields once were. If you are usingf access these all need to be indexed. If you are using SQL i dont think they have to be, but it would of course speed things up on the DB end.

StartDateStr = "#" & StartDate & "#"
EndDateStr = "#" & EndDate & "#"
        
sql = "SELECT Production.`dag`, Production.`thickness`, Production.`width`, Production.`pieces`, "
sql = sql & "Production.`length`, Production.`shift`, Production.`volume`, grade.`description`, "
sql = sql & "endtrim.`description` "

If EPG = "NONE" Then
    frmDailyProd.Excluded = "NONE"
    sql = sql & "From ((`Production` Production INNER JOIN `endtrim` endtrim ON Production.`endtrimrec` = endtrim.`dosrec`) "
    sql = sql & "INNER JOIN `grade` grade ON Production.`graderec` = grade.`rec`) "
    sql = sql & "INNER JOIN `ProductionCenters` ProductionCenters ON Production.`productioncenterrec` = ProductionCenters.`dosrec` "
Else
    frmDailyProd.Excluded = EPG
    sql = sql & "From (((`Production` Production INNER JOIN `endtrim` endtrim ON Production.`endtrimrec` = endtrim.`dosrec`) "
    sql = sql & "INNER JOIN `grade` grade ON Production.`graderec` = grade.`rec`) "
    sql = sql & "INNER JOIN `groups` groups ON Production.`grouprec` = groups.`dosrec`) "
    sql = sql & "INNER JOIN `ProductionCenters` ProductionCenters ON Production.`productioncenterrec` = ProductionCenters.`dosrec` "
End If

sql = sql &amp; &quot;Where Production.`dag` >= &quot; &amp; StartDateStr &amp; &quot; AND Production.`dag` <= &quot; &amp; EndDateStr
sql = sql &amp; &quot; AND ProductionCenters.`description` = '&quot; &amp; ProdCenter &amp; &quot;' &quot;
If EPG <> &quot;NONE&quot; Then
    sql = sql &amp; &quot;AND groups.`description` <> '&quot; &amp; EPG &amp; &quot;'&quot;
End If
sql = sql &amp; &quot; Order By Production.`width` ASC, grade.`description` ASC&quot;
sql = sql &amp; Chr(0)

Report.UseIndexForSpeed = True
Report.SQLQueryString = sql
CRViewer1.ReportSource = Report 
CRViewer1.ViewReport

Ruairi
ruairi@logsoftware.com
Experienced with:

VB6, SQL Server, QBASIC, C(unix), MS Office VBA solutions

ALSO: Machine Control/Automation using GE and Omron PLC's and HMI(human machine interface) for industrial applications

 

[FowlerConsulting]

Ruairi,
What is the trade-off of performance vs LOE in maintenance for these reports should the schema change given this methodology? Steven Fowler, Principal
steve.fowler@fowlerconsulting.com

http://www.fowlerconsulting.com

- Development, Training, and Consulting

 

[Ruairi]

From my point of view it is just as easy to rework the vb code in the case of a schema change as it is to rework the stored procedures. All that would be involved would be making the changes to the report(necessary either way) and then redoing the steps i detailed in my post. Since the parameter input form would already be set up, this would be a 5 minute job. This is probably more of a personal prefrence, i'm a little more comfortable in vb code than in the world of DB's so this method works out well for me. Maybe i'm missing exactly how you're suggesting to implement the stored procedures. Your way may indeed be much superior to mine, but you certainly didn't post enough info to help this poor guy to a solution. Please elaborate on how you would do it, i might find it useful myself.
Thanks, Ruairi
ruairi@logsoftware.com
Experienced with:

VB6, SQL Server, QBASIC, C(unix), MS Office VBA solutions

ALSO: Machine Control/Automation using GE and Omron PLC's and HMI(human machine interface) for industrial applications

 

[MalcolmW]

Stored procs have the advantage of being usable for multiple reports. The SQL is easier to write/debug/maintain. However, it does boil down to what you are used to (and comfortable with) using, as productivity is also important. Malcolm
Remember, if it wasn't for electricity, we'd be surfing the net by candlelight.

 

[FowlerConsulting]

&quot;From my point of view it is just as easy to rework the vb code in the case of a schema change as it is to rework the stored procedures.&quot;

Where would the vb application live?
What would be the client side .DLL, etc required to distribute the report in this way?
What about distribution of report updates?
A SP can be used by multiple reports, would this not save on code re-writes in VB application?
Would there be potential version control issues with the vb application for a large installation base?

I’ll stop there…
Steven Fowler, Principal
steve.fowler@fowlerconsulting.com

http://www.fowlerconsulting.com

- Development, Training, and Consulting

 

[Gennady]

Dear Ruairi and Steven

I using now Crystal Reports for two projects. One of them - 3-tier application and I use now stored procedures and bisness object for preparing recordset for Crystal. Another project - ordinary desctop application and I use scheme like Ruairi wrote.

Both of you are right.
Thanks.

 

[marabou]

From what everyone has said, would I be right in thinking that if I used a stored procedure or a view as the datasource for a report and built in the parameters for the report within the VB source code rather than Cyrstal this would make increase the speed of performance?

 

[welshone]

Running the query on your SQL server through a SP or view will be quicker.
try using an ODBC connection for your data, also correct Indexing will inprove db performance

 

[marabou]

Thanks welshone, will try that