Speed & Crystal Reports

[bobmorane]

Hello
I have reports integrated in VB6, with over 2000 formulas/running totals, and refreshing, takes a long, long, long time !
is there a way to speed up all that ???
I read the faqs, and found that i had to leave the "use indexes or server for speed" option checked... but i have the error "-2147192179 (8004728d) : error detected by the database DLL", and on crystaldecisions.com they advice to uncheck it, that's what i did.
I also read to use selection formulas to use only the records i need, but the database is managed by another application (Cimplicity) that allow to keep only a certain number of days (in my case) or entries in the db. And i need 38 days (around 55000 entries), because i have to display all 31 days for a month, with 7-days averages (i keep 1-2 more days as a security)

So... any hope that my reports will run faster or may i buy some books; so as i have something to do during that long, long, long time ??? ;-)
regards,
bob

 

[kenhamady]

Do you mean there are literally 2K formulas/running totals in this report?

If so, your problem is not the typical processing problem. Usually a slow report spends most of the time getting the appropriate records from the database. The typical challenge is giving the database an efficient request.

I am afraid that your problem comes in the next phase, where Crystal has to calculate all of your formula columns and running totals. I think 2,000 is about as many as I have heard of. Doing that many calculations for each of 55K entries will take quite a bit of time. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[bobmorane]

lol yes at least 2000 formulas/running totals
when i create or delete one, sometimes i have to wait for 1 or 2 minutes (3'45 maximum, i calculated yesterday ) before i can do anything else (indeed it depends on the processor, this one is a P-III 733)
the .rpt file is about 3Mo
I don't know how to use subreports, i wonder if this would make smaller & faster reports ?

 

[bobmorane]

if possible i would have used cross tabs with days as columns, but when i tried i wasn't able to obtain a valid result

 

[Naith]

2000 is an incredible number of objects to have on any report - that they're all formulas is staggering.

To answer your question, I never met a report yet that was sped up by subreports. Subreports increase the hits on the database, as they kick off a separate query than is initially triggered by the main report.

In your case, where it sounds like hardly anything is dervied from the database at all, I would give serious consideration to using a stored procedure to source all this information, and running the report on that. A stored procedure would turn your processing time into a fraction of what you're experiencing now. Even if you were to create a view, and query that, eliminating the need for a great deal of your formulas, it would be preferable.

Alternatively, Stephen King has a couple of decent books out at the moment. May I recommend The Stand...

Naith

 

[kenhamady]

I am curious, and may not be able to shed any light..
But other than summarizing the 31 days, and providing a 7-day rolling average (a guess) what types of things does this report have to calculate? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dgillz]

You are not talking about 2000 records are you? or 200 record with 10 formulas each?

2000 formulas ina a report is mind boggling.

What calculations could possibly take 2000 formulas? Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[bobmorane]

"stored procedure" : does it mean that when i create a report, i have to select a .dsn file as database ?
the problem (i think) is that, once i have achieved the report, i will integrate it into a VB application, then send it to a plant. And there is no database manager there, so i have to set which db it will use before compiling the VB project.

 

[chelseatech]

Naith made a comment about subreports speeding up a process.

I have found a situation where that happened.

I and a competitor both support a local site. They wrote a Crystal SQL procesdure against a Progress DB. The SQL did a UNION join on a couple of big tables. Basicly, get a bunch of records from a large table, and then get some summary values from an even bigger history table. Took about 15 mins to run.

But decimal places were lost using that SQL tool, the competitor went on holiday, so I was called in to sort it out. I rewrote the report using a subreport and "Grouping on Server" to get the history summary, and it ran three times faster. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[Rooble]

If you have summary/aggregate functions in your formulas, I agree with Naith that Stored Proc. would be the best way to go. I hope you can do all your calculation of formulas in there....
As for the Sub-Report it all depends what you want to achieve by inserting a Sub-Report but definitely in your case it would make it slower as you have so many formulas/calculations/totals/subtotals.

Rooble

 

[synapsevampire]

Stored Procedures are always faster!

chelseatech: That's cause they didn't know what they were doing

Whenever I see the word UNION in a query I cringe, that's almost always a sign of a second rate SQL programmer.

Whatever SQL your subreport was generating could be in the SP, and it would be precompiled with an execution, so it would run faster.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[IdoMillet]

My personal belief and experience is that UNION statements can save you in many cases where no other SQL approach (other than tedious SP with temporary tables) would work.
However, you need to know what you are doing...

For example, if you use UNION when you should be using UNION ALL you could be taking a huge performance hit due to an expensive (and ferquently unnecessary) step of searching for and removing duplicate records.

Similarly, the source of the problem in the case described by chelseatech is that instead of using UNION to return raw records, that portion (or perhaps both) of the UNION statement should have returned a single summary record using a GROUP BY SELECT statement.

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

I agree, Ido, Unions *can* be useful, but they're punishingly slow, and many people use them when they shouldn't. For instance, I use them to concatenate massive dynamic SQL statements that I don't want the local hack DBA to muck up...

A common Union scenario is when people want to return a derived value based on the condition of a field.

SQL Server and Oracle supports CASE statements in the select, and even older Oracle databases support the decode function, which can handle this sort of thing for them.

An example in SQL Server is:

select
case Isvalid
when 1 then 'Hi, I'm 1'
when 2 then 'Hi, I'm 2'
else 'I'm neither 1 nor 2'
end as 'WhoAmI'
from SomeTable

I see SQL coders use Unions to do this sort of thing constantly, and the performance is obviously horrendous and the code is unappetizing.

And then there's the equivalent to a group by with rollup which is often poorly imitated by the use of Unions.

I rarely see a Union that doesn't deserve to be rewritten.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[SirSmoother]

I must agree with Ken: 2K formulas is where the problem lies, not in the quantity of data. Not sure why you would need that many formulas when a fraction of that is the "norm", but until this is solved, there isn't much I could suggest.

Rick

 

[fareopagita]

Haven't tried that yet, 2000 formulas/running totals... But I have reports that read that much data entries. A sales report which gives a comparative of two years of data (gross sales,returns, net sold, percentage inc/dec, etc.).

At first, I did the report using the database tables and all. The generation time was very very long.

So I decided to create a stored procedure on the data I needed.

The stored procedure really helped and that why I recommend that you use it too.

Freddie Areopagita
Business Systems Specialist

 

[bobmorane]

Hello !
I see that my 2000+ formulas/running totals were source of amazement
If you guys want to see it, i can upload my report (3,2 Mo lol) so as you can see it by yourselves...
However, i'm french, so maybe you will not understand the names/labels, furthermore you will not have the database, but it's just to show you how i'm far from being the best in CR ;-)
If you want some explanations on what it's supposed to do, just ask me !!
Later,
Bob

 

[bobmorane]

fareopagita you posted while I was writing

If anyone could give me a french translation for "stored procedure", or could tell me how to create one, that would be useful thanks

---no report uploaded yet, i'm modifying some £µ^£@## bug CR did yesterday, i'll certainly ask how to resolve it in another topic---

 

[bobmorane]

hum... cannot upload the .rpt...
seems that the file is too big
if anyone interested let me know and please could you tell me a "good" web space provider (free of course )
Later,
Bob

 

[Naith]

ChelseaTech,

If your competitor thought that performing a union against two large tables resulting in performance that was slower than a subreport was the best way to go, then he deserved to lose the client.

Idomillet's touched on what I think is the key point to consider when using unions. The union function is generally going to expose the user to some lag time because the temp tablespace generally gets called in to faff around with sorting. At least, this is the case with Oracle.

Kudos for edging him out with a subreport, but I suspect you'd have gotten even better performance (and saved yourself a lot more work) had you used a Union All instead.

Bob - I don't know if you have a resource who's able to write the necessary stored procedures for your record breaking report, but your first port of call would be to speak to your dba or a developer to find out if what you're doing can be dumped into a stored procedure. To give you a very high level definition, a stored procedure is simply a script which processes all the SQL and applies functions and aggregations you would otherwise end up working out in your report. The idea is that in connecting to the stored procedure instead of to the tables in the database, the report doesn't have to process nearly as much information, and so it returns the data much more quickly.

I hope all our two-pences have been helpful,

Naith

 

[IdoMillet]

Naith,

My comment about UNION ALL was more of a general nature.
IN the case described by ChelseaTech, I suspect it wouldn't have been the solution. His description of the situation indicated that the user needed to get SUMMARY info from a large table and combine it with other records, probably to produce a combined summary.

It is very probable that the previous consultant decided to simply use a raw SELECT from the 1st table and UNION it with a raw SELECT from the 2nd one and then produce totals on the combined result set.

A MUCH better approach is to SELECT summary data (GROUP BY) in each of the SELECT statements so that instead of a huge result set, you get only summary records to work on. ChelseaTech's subreport approach "simulated" that because his subreport used an aggregate query returning only SUMMARY data.

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com