sql server programming: Hi all,

I need to run large reports off a SQL server using an application like
Crystal Reports. Unfortunately Crystal Reports for VS.NET runs into
memory problems if the result set is >50,000 rows. As such, I need to do
all my calculation, collating, etc on the server side and then send a
nicely formatted dataset to Crystal.

Typically how does one go about building queries that calculate data
without cursors? There wouldn't happen to be a book Building Report
Queries for Dummies?

For example, I have a table which has a bunch of records as follows:

Date Value
01/01/2004 10:55:00AM 1
01/01/2004 11:56:00AM 2
01/01/2004 13:24:00AM 4
01/01/2004 12:34:00AM 7
01/01/2004 11:20:00AM 9

And I need to report on 1/2 hours, Daily, Monthly, Yearly, etc.

If I use the middle tier to calcualte such data, I would need large
amounts of memory to replicate the tables while I do the calculations...
wouldn't using cursors in SQL server be more effecient if all I'm doing
is 90% read operations and 10% writes (writes only occur when the
reporting DB is syncronizing with the production DB)?

In anycase, how does one tackle report queries? Are there any tutorials
online with a set of templates or examples for different style of
calculation type queries?

Thanks.
--
Lucas Tam (REMOVEnntp@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.

Hi,

I gave up on CR for this type of reporting a long time ago. It was only
capable then of the Big Bang approach to report writing: 1 query feeds the
whole report.

For our own statement runs this was a big PIA issue as it used to take > 20
minutes just to run the query to feed the report, then take a few more
minutes for the report to start coming out and in the process use near a gig
of memory then produce a massive spool file. We needed a special machine
just to run the statements then! It was also prone to break for reasons I
never understood so we invariably split statements into "Batches". This
report was the only heavy user of the database concerned!

The method is now (without CR): a simple query feeds the report program the
List of statements to print. This takes about 12 seconds to complete. The
statement program then isses a query (SP call actually) to fetch the data
for each individual statement which it prints with Page N of M numbering
too. These SP calls take 28ms or so each, so the first page now comes out in
around 12 seconds. It still takes yonks to print the report, but the
statement run produces around 10,000 pages at EOM, finishes the print to the
spool file in about 2 minutes 20 seconds, uses minimal memory, does not load
the server heavily, can be rerun at whim, and we can now selectively reprint
any part of the statement print (we can reprint all statements online
anyway).

So, as you can see I am not a fan of the big bang approach. SQL Server
Reporting Services supports this type of report writing - I had this going
during beta, just for testing on "large" reports.

So: Query 1 ==> list of statements to print, then during iteration, SP1
returns the data for each statement using the data from query 1 as its
parameters for the call.

The odd thing I find is that 10,000 pages is bound to be tiny for many
installations -100,000+ pages would not be uncommon, so it really begs how
others do similar things.

- Tim



[quoted text, click to view]