Online Performance - Snapshot Viewer

[bluedolphin]

Hello All:

I have been brought onboard to help on a project that had some
performance problems last year. I have taken some steps to address
the issues in question, but a huge question mark remains.

Last year, all of the tables and reports were stored in Access. The
database was put online so that end users could access the reports
online using Snapshot Viewer. The reports were aggregated on the fly,
and the selection criteria available to the end users was passed as
text strings to the query/reports as the searchable criteria as
opposed to an indexed numeric value. (Example: Pass Year_Desc of
200-2003 as opposed to Year_ID, of 3.)

Hundreds of users were accessing this system. Fortunately not all at
the same time, but it must have been enough people since the system
came down a few times while people were accessing their reports.

This year, I have preaggregated all reports into a reporting table, so
that no aggregation is happening on the fly when people are pulling
their reports. The only work that will be done to pull up the reports
is to pass the selection criteria to the query that the report is
based on. ALL TABLES including the aggregated reporting tables have
been moved to SQL Server. No tables live in Access anymore. The
selection criteria in the SQL reporting tables all have indexed
numeric fields that will be used to choose the appropriate recordset.

I think these items represent a big improvement for this process,
making us less likely to come down this year. The theory is that,
Access is known to not handle multiple users and major load on it's
tables. I focused on the fact that everything was in Access and since
it is not a shining star for this type of multiple user/heavy load
scenario, we would be better off in SQL.

The argument has been put forward, however, that the bottleneck is the
actual instances of Access Opening and closing in order to put out the
Snapshot Viewer for each report. It is argued that the move from
Access to SQL will have little impact, because that was not the
bottleneck. The bottleneck, it is said, was the number of actual
instances that Access must "Open" in order for people to view the
reports.

Since I have little experience with Snapshots, the argument may in
fact be valid. I stand by the move to SQL, and I still believe that
everything living in Access contributed to the poor performance and
crashes. I cannot speak towards the Access Snapshot piece ansd am
thus still concerned for the success of this project.

My concern is that we may still be looking at a crash scenario despite
the SQL move and preaggregated data. Does anyone out there have
experience with snapshots and can verify or refute the performance
issues mentioned above and the approach we are taking?

Any input is greatly appreciated. Thanks.
BlueDolphin

[Tom van Stiphout]

On 12 Jul 2004 10:19:36 -0700, bc******@jeffco.k12.co.us (bluedolphin)
wrote:

Is this your scenario:
Users access the application via a web browser. A classic ASP
application takes the requests including report criteria, invokes an
instance of MsAccess to generate a report and exports it as a snapshot
file, and makes that snapshot file available to the requestor in the
form of a hyperlink. User clicks the hyperlink, the snapshot file
downloads and is viewed in the local viewer (or perhaps in-place in
the browser window).

If not, please describe your application architecture. What do you
mean by the term "online"?

-Tom.

Hello All:

I have been brought onboard to help on a project that had some
performance problems last year. I have taken some steps to address
the issues in question, but a huge question mark remains.

Last year, all of the tables and reports were stored in Access. The
database was put online so that end users could access the reports
online using Snapshot Viewer. The reports were aggregated on the fly,
and the selection criteria available to the end users was passed as
text strings to the query/reports as the searchable criteria as
opposed to an indexed numeric value. (Example: Pass Year_Desc of
200-2003 as opposed to Year_ID, of 3.)

Hundreds of users were accessing this system. Fortunately not all at
the same time, but it must have been enough people since the system
came down a few times while people were accessing their reports.

This year, I have preaggregated all reports into a reporting table, so
that no aggregation is happening on the fly when people are pulling
their reports. The only work that will be done to pull up the reports
is to pass the selection criteria to the query that the report is
based on. ALL TABLES including the aggregated reporting tables have
been moved to SQL Server. No tables live in Access anymore. The
selection criteria in the SQL reporting tables all have indexed
numeric fields that will be used to choose the appropriate recordset.

I think these items represent a big improvement for this process,
making us less likely to come down this year. The theory is that,
Access is known to not handle multiple users and major load on it's
tables. I focused on the fact that everything was in Access and since
it is not a shining star for this type of multiple user/heavy load
scenario, we would be better off in SQL.

The argument has been put forward, however, that the bottleneck is the
actual instances of Access Opening and closing in order to put out the
Snapshot Viewer for each report. It is argued that the move from
Access to SQL will have little impact, because that was not the
bottleneck. The bottleneck, it is said, was the number of actual
instances that Access must "Open" in order for people to view the
reports.

Since I have little experience with Snapshots, the argument may in
fact be valid. I stand by the move to SQL, and I still believe that
everything living in Access contributed to the poor performance and
crashes. I cannot speak towards the Access Snapshot piece ansd am
thus still concerned for the success of this project.

My concern is that we may still be looking at a crash scenario despite
the SQL move and preaggregated data. Does anyone out there have
experience with snapshots and can verify or refute the performance
issues mentioned above and the approach we are taking?

Any input is greatly appreciated. Thanks.
BlueDolphin

[david epsom dot com dot au]

The snapshot viewer is not Access. It is a separate program.
It runs on the client computer. It is a file viewer like
Acrobat Reader. Whatever problems you might have, they won't
be due to heavy use of the snapshot viewer.

Snapshot files are generated by Access. Access was never
intended to be a internet report server, no more than MS Word,
or MS Excel.
If you are using Access as a report server (like we do),
then you are pushing the acceptable boundaries, and you
should accept that the application will need monitoring.

On the other hand, you can run Report Services on IIS/
SQL Server, and there is a wizard that will convert simple
Access reports to IIS/SQL Server/Report Services. It
may require some work, but you will be placed right in
the centre of the target market for Report Services, rather
than off at the edge of the Access target market.

Access is known to not handle multiple users and major load

That is to say, Windows (File Services) is known to not
handle multiple users and major load, and IIS is known to
be unable to handle multi-threaded applications. Fortunately,
IIS is only ONE user, and Access/Jet can be configured to be
single threaded.
Still, MS has gone to the trouble of building Report Services
specifically for applications like yours, so it should be
at least considered.

(david)
news:39********************************@4ax.com... On 12 Jul 2004 10:19:36 -0700, bc******@jeffco.k12.co.us (bluedolphin)

Hello All:

I have been brought onboard to help on a project that had some
performance problems last year. I have taken some steps to address
the issues in question, but a huge question mark remains.

Last year, all of the tables and reports were stored in Access. The
database was put online so that end users could access the reports
online using Snapshot Viewer. The reports were aggregated on the fly,
and the selection criteria available to the end users was passed as
text strings to the query/reports as the searchable criteria as
opposed to an indexed numeric value. (Example: Pass Year_Desc of
200-2003 as opposed to Year_ID, of 3.)

Hundreds of users were accessing this system. Fortunately not all at
the same time, but it must have been enough people since the system
came down a few times while people were accessing their reports.

This year, I have preaggregated all reports into a reporting table, so
that no aggregation is happening on the fly when people are pulling
their reports. The only work that will be done to pull up the reports
is to pass the selection criteria to the query that the report is
based on. ALL TABLES including the aggregated reporting tables have
been moved to SQL Server. No tables live in Access anymore. The
selection criteria in the SQL reporting tables all have indexed
numeric fields that will be used to choose the appropriate recordset.

I think these items represent a big improvement for this process,
making us less likely to come down this year. The theory is that,
Access is known to not handle multiple users and major load on it's
tables. I focused on the fact that everything was in Access and since
it is not a shining star for this type of multiple user/heavy load
scenario, we would be better off in SQL.

The argument has been put forward, however, that the bottleneck is the
actual instances of Access Opening and closing in order to put out the
Snapshot Viewer for each report. It is argued that the move from
Access to SQL will have little impact, because that was not the
bottleneck. The bottleneck, it is said, was the number of actual
instances that Access must "Open" in order for people to view the
reports.

Since I have little experience with Snapshots, the argument may in
fact be valid. I stand by the move to SQL, and I still believe that
everything living in Access contributed to the poor performance and
crashes. I cannot speak towards the Access Snapshot piece ansd am
thus still concerned for the success of this project.

My concern is that we may still be looking at a crash scenario despite
the SQL move and preaggregated data. Does anyone out there have
experience with snapshots and can verify or refute the performance
issues mentioned above and the approach we are taking?

Any input is greatly appreciated. Thanks.
BlueDolphin

[bluedolphin]

Is this your scenario:
Users access the application via a web browser. A classic ASP
application takes the requests including report criteria, invokes an
instance of MsAccess to generate a report and exports it as a snapshot
file, and makes that snapshot file available to the requestor in the
form of a hyperlink. User clicks the hyperlink, the snapshot file
downloads and is viewed in the local viewer (or perhaps in-place in
the browser window).
Exactly! This is the precisely the scenario.

[Simple]

I read your post. If you are worried about the opening and closing of
Access, the following article shows a good way of doing it:
http://www.4guysfromrolla.com/webtec...1.update.shtml

If you have been able to run your Reporting ASP code on Windows 2003
(with the default IIS that ships with W2K3), please tell me your
IIS/Component Services settings because I have been unable to run it
on W2K3. I can run it fine on IIS 5.x / Win XP.

Thanks.

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message news:<40***********************@news.syd.swiftdsl. com.au>...

The snapshot viewer is not Access. It is a separate program.
It runs on the client computer. It is a file viewer like
Acrobat Reader. Whatever problems you might have, they won't
be due to heavy use of the snapshot viewer.

Snapshot files are generated by Access. Access was never
intended to be a internet report server, no more than MS Word,
or MS Excel.
If you are using Access as a report server (like we do),
then you are pushing the acceptable boundaries, and you
should accept that the application will need monitoring.

On the other hand, you can run Report Services on IIS/
SQL Server, and there is a wizard that will convert simple
Access reports to IIS/SQL Server/Report Services. It
may require some work, but you will be placed right in
the centre of the target market for Report Services, rather
than off at the edge of the Access target market.

Access is known to not handle multiple users and major load

That is to say, Windows (File Services) is known to not
handle multiple users and major load, and IIS is known to
be unable to handle multi-threaded applications. Fortunately,
IIS is only ONE user, and Access/Jet can be configured to be
single threaded.
Still, MS has gone to the trouble of building Report Services
specifically for applications like yours, so it should be
at least considered.

(david)
news:39********************************@4ax.com...

On 12 Jul 2004 10:19:36 -0700, bc******@jeffco.k12.co.us (bluedolphin)

Hello All:

I have been brought onboard to help on a project that had some
performance problems last year. I have taken some steps to address
the issues in question, but a huge question mark remains.

Last year, all of the tables and reports were stored in Access. The
database was put online so that end users could access the reports
online using Snapshot Viewer. The reports were aggregated on the fly,
and the selection criteria available to the end users was passed as
text strings to the query/reports as the searchable criteria as
opposed to an indexed numeric value. (Example: Pass Year_Desc of
200-2003 as opposed to Year_ID, of 3.)

Hundreds of users were accessing this system. Fortunately not all at
the same time, but it must have been enough people since the system
came down a few times while people were accessing their reports.

This year, I have preaggregated all reports into a reporting table, so
that no aggregation is happening on the fly when people are pulling
their reports. The only work that will be done to pull up the reports
is to pass the selection criteria to the query that the report is
based on. ALL TABLES including the aggregated reporting tables have
been moved to SQL Server. No tables live in Access anymore. The
selection criteria in the SQL reporting tables all have indexed
numeric fields that will be used to choose the appropriate recordset.

I think these items represent a big improvement for this process,
making us less likely to come down this year. The theory is that,
Access is known to not handle multiple users and major load on it's
tables. I focused on the fact that everything was in Access and since
it is not a shining star for this type of multiple user/heavy load
scenario, we would be better off in SQL.

The argument has been put forward, however, that the bottleneck is the
actual instances of Access Opening and closing in order to put out the
Snapshot Viewer for each report. It is argued that the move from
Access to SQL will have little impact, because that was not the
bottleneck. The bottleneck, it is said, was the number of actual
instances that Access must "Open" in order for people to view the
reports.

Since I have little experience with Snapshots, the argument may in
fact be valid. I stand by the move to SQL, and I still believe that
everything living in Access contributed to the poor performance and
crashes. I cannot speak towards the Access Snapshot piece ansd am
thus still concerned for the success of this project.

My concern is that we may still be looking at a crash scenario despite
the SQL move and preaggregated data. Does anyone out there have
experience with snapshots and can verify or refute the performance
issues mentioned above and the approach we are taking?

Any input is greatly appreciated. Thanks.
BlueDolphin

[Tom van Stiphout]

On 13 Jul 2004 09:23:14 -0700, bc******@jeffco.k12.co.us (bluedolphin)
wrote:

OK, why didn't you say so :-)
No, just kidding.
But as others have pointed out, this is the most likely bottleneck.
You're creating Access.Application objects left and right. They are
very fat, and don't scale.
SQL Server Reporting Services is a much better solution. Bite the
bullet and migrate your reports.

-Tom.

Is this your scenario:
Users access the application via a web browser. A classic ASP
application takes the requests including report criteria, invokes an
instance of MsAccess to generate a report and exports it as a snapshot
file, and makes that snapshot file available to the requestor in the
form of a hyperlink. User clicks the hyperlink, the snapshot file
downloads and is viewed in the local viewer (or perhaps in-place in
the browser window).
Exactly! This is the precisely the scenario.

[Beth Capone-Trembath]

Thank you for the input. Yes I agree we need to migrate to SQl
reporting or Crystal or something else. We have someone here who has
created MANY Access reports and is unwilling to give them up until they
find another easy to use tool with strong graphing functionality. We
will definately move to another tool with time. At the moment it is too
much to recreate the over 100 reports that she currently has
functioning. I will definately look into the reporting suggestion made
above.

However, in the meantime, what I'm reading is that we will still have
the same bottleneck despite the SQL move. Will we get ANY PERFORMANCE
IMPROVEMENT AT ALL as a result of the move?

Thanks for your input everyone.
BlueDolphin
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[david epsom dot com dot au]

> the same bottleneck despite the SQL move. Will we get ANY PERFORMANCE

IMPROVEMENT AT ALL as a result of the move?
No

In fact, it's just one more thing to break.
FWIW, the Report Services report builder is kinda similar to the
Access Report Builder, so it can be learned.

And, depending on your reports, the wizard might be able to do
most of the conversion.

I don't know what you are using for 'graphing'. Most people
don't like MS Graph in Access, and are willing to switch
across to Report Services graphs.

(david)
"Beth Capone-Trembath" <bc******@jeffco.k12.co.us> wrote in message
news:40**********************@news.newsgroups.ws.. . Thank you for the input. Yes I agree we need to migrate to SQl
reporting or Crystal or something else. We have someone here who has
created MANY Access reports and is unwilling to give them up until they
find another easy to use tool with strong graphing functionality. We
will definately move to another tool with time. At the moment it is too
much to recreate the over 100 reports that she currently has
functioning. I will definately look into the reporting suggestion made
above.

However, in the meantime, what I'm reading is that we will still have
the same bottleneck despite the SQL move. Will we get ANY PERFORMANCE
IMPROVEMENT AT ALL as a result of the move?

Thanks for your input everyone.
BlueDolphin
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Tom van Stiphout]

On 14 Jul 2004 19:05:43 GMT, Beth Capone-Trembath
<bc******@jeffco.k12.co.us> wrote:

With that number of reports, it may make sense to invest in a
conversion tool beyond the one built into SQL Sever Reporting Services
(RS). After all, a RS report is just an XML text file, which syntax
can be understood. Access reports can be opened in design view and
queried programmatically. Thus the road to a conversion tool that does
90% of the work is open.

-Tom.

Thank you for the input. Yes I agree we need to migrate to SQl
reporting or Crystal or something else. We have someone here who has
created MANY Access reports and is unwilling to give them up until they
find another easy to use tool with strong graphing functionality. We
will definately move to another tool with time. At the moment it is too
much to recreate the over 100 reports that she currently has
functioning. I will definately look into the reporting suggestion made
above.

However, in the meantime, what I'm reading is that we will still have
the same bottleneck despite the SQL move. Will we get ANY PERFORMANCE
IMPROVEMENT AT ALL as a result of the move?

Thanks for your input everyone.
BlueDolphin
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[david epsom dot com dot au]

> can be understood. Access reports can be opened in design view and

Or saved as text. They look kind of like VB form files.

(david)

"Tom van Stiphout" <no*************@cox.net> wrote in message
news:37********************************@4ax.com...

On 14 Jul 2004 19:05:43 GMT, Beth Capone-Trembath
<bc******@jeffco.k12.co.us> wrote:

With that number of reports, it may make sense to invest in a
conversion tool beyond the one built into SQL Sever Reporting Services
(RS). After all, a RS report is just an XML text file, which syntax
can be understood. Access reports can be opened in design view and
queried programmatically. Thus the road to a conversion tool that does
90% of the work is open.

-Tom.