Performance Bottleneck in ASP.NET

[Glenn]

I have a performance issue that needs resolving, and am
not sure which options we have come up with are the best.
Let me explain.

Our site has a report designer that allows users to create
dynamic report content. The output for these reports is
HTML, but they can be exported to a number of formats for
download (ie Excel). The contents of the exported report
must be identical to the original report, thus cannot read
from the DB as data is volatile.

To overcome this we persist the original HTML report (can
be exported too), the report options XML document, and the
dataset used to create the report (XML) to the file
system. If a user wants to export to another format then
we load these files and create the necessary report type.

Having to write to the file system before returning the
generated report is causing a performance bottleneck when
the server is under load.

Here are the options we have come up with:
* Store the xml options and dataset in session state
(properties of a wrapper class).
* Write files asynchronously to file system after the data
is retrieved from SQL, whilst still continuing report
generation processing.
* Is there a better way to structure this? Could caching
be used here?

The dataset has the potential to be quite large (up to
5000 rows in some cases), and will contain multiple data
tables. This may be a problem for session state usage.

Haven't use asych processing very much so am a bit
hesitant to use, but if it is the most efficient solution
then that's the way I'll move forward.

Any advice or recommendations here would be greately
apprecieated...

Glenn.

[Ken Cox [Microsoft MVP]]

Hi Glenn,

I wonder if there isn't a way to get a "snapshot" of the data into a unique
temporary table in SQL Server. That would preserve the contents until the
user decides what formats in needs.

You'd have to identify that particular table as the datasource for the
export format about to be requested, but adding a Session ID field to the
table might get around that.

This would offload the slow file system portions onto the database server.

BTW, I have a process that does write to XML to the file system and then
processes into PDFs. It also uses XSL Transformations to generate HTML for
the Web and Excel spreadsheets. It confirm that it certainly can be slow.

Ken

"Glenn" <an*******@discussions.microsoft.com> wrote in message
news:04****************************@phx.gbl...

I have a performance issue that needs resolving, and am
not sure which options we have come up with are the best.
Let me explain.

Our site has a report designer that allows users to create
dynamic report content. The output for these reports is
HTML, but they can be exported to a number of formats for
download (ie Excel). The contents of the exported report
must be identical to the original report, thus cannot read
from the DB as data is volatile.

To overcome this we persist the original HTML report (can
be exported too), the report options XML document, and the
dataset used to create the report (XML) to the file
system. If a user wants to export to another format then
we load these files and create the necessary report type.

Having to write to the file system before returning the
generated report is causing a performance bottleneck when
the server is under load.

Here are the options we have come up with:
* Store the xml options and dataset in session state
(properties of a wrapper class).
* Write files asynchronously to file system after the data
is retrieved from SQL, whilst still continuing report
generation processing.
* Is there a better way to structure this? Could caching
be used here?

The dataset has the potential to be quite large (up to
5000 rows in some cases), and will contain multiple data
tables. This may be a problem for session state usage.

Haven't use asych processing very much so am a bit
hesitant to use, but if it is the most efficient solution
then that's the way I'll move forward.

Any advice or recommendations here would be greately
apprecieated...

Glenn.

[Glenn.]

Thanks for that Ken.

Unfortuately the dataset usually contains cross-tabbed
data, so the table schema is different for a vast majority
of instances.

I had toyed with the idea of creating a report table that
contained the sessionID, name of a separate report data
table, and date created. I could then schedule a stored
proc to run periodically to clean up any "old" report data
tables.

If we had a 64 bit server I would just store it using
InProc session state, but we don't!

-----Original Message-----
Hi Glenn,

I wonder if there isn't a way to get a "snapshot" of the data into a uniquetemporary table in SQL Server. That would preserve the contents until theuser decides what formats in needs.

You'd have to identify that particular table as the datasource for theexport format about to be requested, but adding a Session ID field to thetable might get around that.

This would offload the slow file system portions onto the database server.
BTW, I have a process that does write to XML to the file system and thenprocesses into PDFs. It also uses XSL Transformations to generate HTML forthe Web and Excel spreadsheets. It confirm that it certainly can be slow.
Ken

"Glenn" <an*******@discussions.microsoft.com> wrote in messagenews:04****************************@phx.gbl...

I have a performance issue that needs resolving, and am
not sure which options we have come up with are the best. Let me explain.

Our site has a report designer that allows users to create dynamic report content. The output for these reports is
HTML, but they can be exported to a number of formats for download (ie Excel). The contents of the exported report must be identical to the original report, thus cannot read from the DB as data is volatile.

To overcome this we persist the original HTML report (can be exported too), the report options XML document, and the dataset used to create the report (XML) to the file
system. If a user wants to export to another format then
we load these files and create the necessary report type.
Having to write to the file system before returning the
generated report is causing a performance bottleneck when the server is under load.

Here are the options we have come up with:
* Store the xml options and dataset in session state
(properties of a wrapper class).
* Write files asynchronously to file system after the data is retrieved from SQL, whilst still continuing report
generation processing.
* Is there a better way to structure this? Could caching
be used here?

The dataset has the potential to be quite large (up to
5000 rows in some cases), and will contain multiple data
tables. This may be a problem for session state usage.

Haven't use asych processing very much so am a bit
hesitant to use, but if it is the most efficient solution then that's the way I'll move forward.

Any advice or recommendations here would be greately
apprecieated...

Glenn.

.

[aspnet dev]

You certainly don't have to persist the data to disk to
enable this kind of functionality, but it is one possible
solution.

I understand your datasets can be large, but the bottom
line is you HAVE to persist them somewhere right? So...
are you memory constrained? If so, a few options come to
mind. One is persisting to disk, as you are currently
doing. Another is using session state but perhaps
offloading the session storage to another machine using
asp.net state service or SQL session state. While you have
some perf hit introduced w/ serialization/deserialization
and moving the data across the network, my guess is it is
not as high as the disk I/O hit (especially if its a
single spindle). These methods also give you the added
functionality of being able to survive a worker process
recycle on the web server end.

If you're not memory constrained, store in session. I'd
highly recommend quantifying your expected load and stress
testing to that level to determine if you are, in fact,
memory constrained.

In any case, if the user chooses to export to excel or
what have you, set the mime type and stream to the client.

Hope that is helpful in some way.

-----Original Message-----
I have a performance issue that needs resolving, and am
not sure which options we have come up with are the best.
Let me explain.

Our site has a report designer that allows users to createdynamic report content. The output for these reports is
HTML, but they can be exported to a number of formats for
download (ie Excel). The contents of the exported report
must be identical to the original report, thus cannot readfrom the DB as data is volatile.

To overcome this we persist the original HTML report (can
be exported too), the report options XML document, and thedataset used to create the report (XML) to the file
system. If a user wants to export to another format then
we load these files and create the necessary report type.

Having to write to the file system before returning the
generated report is causing a performance bottleneck when
the server is under load.

Here are the options we have come up with:
* Store the xml options and dataset in session state
(properties of a wrapper class).
* Write files asynchronously to file system after the datais retrieved from SQL, whilst still continuing report
generation processing.
* Is there a better way to structure this? Could caching
be used here?

The dataset has the potential to be quite large (up to
5000 rows in some cases), and will contain multiple data
tables. This may be a problem for session state usage.

Haven't use asych processing very much so am a bit
hesitant to use, but if it is the most efficient solution
then that's the way I'll move forward.

Any advice or recommendations here would be greately
apprecieated...

Glenn.
.