Could anybody tell me or what is the preferred method when passing data to a Crystal Report 9 from a stored proc within SQL Server 2K.

Method 1: Summing data in SQL Server and passing the summed data to CR (no summing done in CR)

Method 2: Passing the resultset to CR and letting CR do the summing on the client PC

I am currently using method 2 though I am beginning to think that letting SQL do all the summing first may be quicker.

Any views appreciated

Regards
D

I would go for option 2 this way your DB doesn't slow down if 50 people suddenly request a report


http://sqlservercode.blogspot.com/

On the other hand, sending back a bunch of data to the client for the sole purpose of having the client aggregate it slows the whole network down with unnecessary traffic.

It's a tradeoff.

I generally prefer to have the server do the grouping and aggregation. It's better designed to do that, IMO.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

I do a lot of crystal with SQL. What you want to do for each report is figure what is the minimum level of detail you need on the report. Make sure all the filtering of data is done on the SQL side. For example if you filter on state, the result set from sql should only return one states data. Thus every row of data you send from the Stored Procedure is used and displayed on the crystal report (this assumes your not doing complex crystal drill down reports then you will likely have to do the most granular data). If the report is only doing an agregation with no detail. Agregate it on the SQL side not on Crystal. Crystal reports can take for ever if you send it more sql data than it needs. If the report requires detail and you also do agregation well you have to send the detail so you generally can't do the agregation on the SQL side. Sometimes I have writen stored procs that have a parameter to identify if the report as requested was agregated or detailed. Depending on the parameter the stored proc returns detailed rows or just agreagation. The catch here is the structure of your result set can not change between the two different options. Basically I am agreeing with Jeff Mason above but just giving you a little more details and thoughts. Hope this helps.

Thanks for a detailed answer, I have been reworking my query which originally took 47 seconds using method 2 (one big sql script in a SP), this was passing say around 50,000 records to just display less than a 1000 aggregated rows. A long time I know.

Therefore I started again, breaking the large resultset into smaller resultset (using temp tables)

In all the reworked version took 4 temp tables and one query to join them at the end (in one SP), using method 1. The new time is now 7 seconds!!! for the query to run and display the CR.

I hope you can understand my joy here more than my wife did, when I tried to explain my day to her. :-)

Many thanks for the pointers

No problem I have had to do the same type of things many times with Crystal/MS-SQL or Oracle. If your stored proce only returns the final 1000 agregated rows your going in the right dirction to streamline your query/process. SQL is infinitely better at quickly handeling data agrgations, complex joins, etc. than Crystal is.