Crystal Performance?

[nmmure]

Hi,

My Crystal Reports created using Stored Procedure. These reports are using Web application ( ASP.Net ). Reports are working fine and the reports are stored in server level.
Created one report using SQL command and used same business logic create Stored Procedure and then created another report.
I ran these 2 reports on Web and seen results and wondering Using SQl Command report is printed first and then Print Stored procedure report.

Both the reports stored in same path and executed in same client machine..
As of my knowledge Stored Procedure is recommanded for creating reports.

Pls guide me If I am wrong.

Thank you very much..
Mure

 

[AndrewBaines]

Depends upon the report and how well written the SP is.
If the data is very complex, or needs multiple passes, then SP is the way to go. If just linking a few tables, then you might as well let Crystal handle it.

Andrew Baines

http://www.pursuittechnology.co.uk

 

[smcnulty2000]

It also depends a lot on the DBA/Programmer who wrote the SP.

The experience of a DBA matters, the time the DBA has to devote to it, and the interest level he/she had when he wrote it.

An SP isn't automatically better.

A lot of times your command object, which is essentially a raw query, is something the optimizer in the system can sink its teeth into. An SP often is written with more procedure than SQL inside of it, and I've seen lots of SQL programmers who don't really understand SQL so they work around that limitation using for-next loops or Do loops never guessing their procedure would be optimized like crazy if they'd just do one or two more things in the SQL side. The database optimizer can't really do much with a Do loop.

I've seen SPs written that don't even touch an index causing a full table scan when it wasn't required. Others that try to force the database into an index scan when a full table scan is needed. Both of these cause extra performance hits.

I've also seen excellent SPs, and some that were great by accident as well.

There's also another factor. If the SP was written when a table it used had a few thousand records it might work well enough that the programmer wouldn't bother to rewrite it. Then after a year or two of usage the table grows to a half a million records and the SP isn't so hot anymore. At which point the SP could use a rewrite for performance purposes.

There is a sub-industry of DBAs whose sole role is performance tuning rewrites. Its kind of cool work if one is any good at it. But the sub-industry wouldn't even exist if an SP stayed good year in and year out.