Enterprise ASP.NET question

[joels]

I'm in the preliminary reasearch stage of a large ASP.NET project; I'm
looking for some resources (e.g., articles, books).

I'm looking to determine the feasability of developing an ASP.NET
reporting application that will hit a 200+ GB database (SQL Server 2K
and Oracle 9i) and return on average 100,000 rows per report.

This application will be used by 10 - 15 concurrent users, each
returning between 50K and 100K rows.

I'm hoping to find an appropriate design pattern for such an app. I'm
currently investigating the following:

1) Asynchronous client notification
2) Asynchronous ADO.NET
3) Spawning multiple worker threads
4) Web farms

If anyone could highlight some resources, it would be greatly
appreciated.

Joel Scavone

 

[Eliyahu Goldin]

Natty Gur (http://weblogs.asp.net/NGur/) is the guy to ask.

Eliyahu

 

[David Browne]

I'm in the preliminary reasearch stage of a large ASP.NET project; I'm
looking for some resources (e.g., articles, books).

I'm looking to determine the feasability of developing an ASP.NET
reporting application that will hit a 200+ GB database (SQL Server 2K
and Oracle 9i) and return on average 100,000 rows per report.

This application will be used by 10 - 15 concurrent users, each
returning between 50K and 100K rows.

I'm hoping to find an appropriate design pattern for such an app. I'm
currently investigating the following:

1) Asynchronous client notification
2) Asynchronous ADO.NET
3) Spawning multiple worker threads
4) Web farms

A single CPU server running ASP.NET would never break a sweat churning out
these reports to only 10-15 concurrent users. But, if the reports take a
long time to run on the Database server, or a long time to prepare on the
web server you will want to use a background thread to prepare the report
instead of an ASP.NET worker thread.

The basic process is:

Client requests report.
Server spawns a thread to run the report and generates a "job ticket".
Server redirects client to a page where javascript polls every X seconds to
see if the job is done.
Background thread runs the query and prepares the report, and then stores it
in global scope (Application, cache etc) under the job ticket.
Client finds that the job is completed and redirects to the report output
page which sends the saved report to the client.

David

 

[Joel Scavone]

Excellent. That fills in a few gaps with regard to my thought process.

I believe I'll start a proof of concept.

js

 

[Guest]

You should investigate the Data Caching and Page Caching that comes with
ASP.Net.

I already have an application that generates reports of a similar or greater
size against an SQL DB. Once you get the DB tuned and bringing back the data
quickly, ASP.Net will have no trouble handling the concurrent users on a
decent web server. You can use Datagrid and the paging - DO NOT use the
default paging though or use a third party reporting tool.

 

[js]

Caching is not an option as we're looking at a 250 GB database. 10
different users may each return 100,000+ rows, and there may be
absolutely no overlap.

Additionally, custom most likely not an option as we'll be implementing
export functionality, and will need the entire set of data available to
the export process.

Thanks,
js

 

[Brian Pursley]

Joel,

Have you considered a reporting solution? Here are some that come to
mind...

SQL Server Reporting Services
http://www.microsoft.com/sql/reporting/default.asp

Crystal Enterprise
http://www.businessobjects.com/products/reporting/crystalenterprise/default.asp

Cognos ReportNet
http://www.cognos.com/products/busi...ucts//Business Intelligence//Cognos ReportNet

I'm sure there are others as well. One word of advice though, don't let
anyone convince that any of these will work perfectly out of the box.
Chances are you will still need to do a fair amount of customization to get
it working and looking how you want. But it does give you a good starting
point.

 

[js]

SQL Reporting Services is not an option as we're reporting of an Oracle
9i environment as well as MS SServer.

Crystal does not allow for true ad hoc reporting as it will not allow
for dynamic column generation-- you must build a report with predefined
columns.

js

 

[js]

SQL Server Reporting Services is not an option as we're reporting off of
Oracle as well.

Crystal Enterprise does not allow for dynamic column generation,
unfortunately, and we need true ad hoc reporting.

js