Very Slow Access Response

[Blaze]

I have a DB that was created in Access. I have developed with Access 2003,
SQL 2000 w/ SP4, Sharepoint Services V2 SP1.

I use access to do Sharepoint Queries for linking tables inside the Access
database. These queries work quite well, but I have 2 queries/reports that
run unbearilbly slow.

The Query takes anywhere from 6-10 seconds to execute and the Report takes
anywhere from 8-15 seconds to execute before it will ask for the information
required (called BID).

I have been running performance monitor against the machine and see that I
have plenty of memory and processor. I have a quad xeon with 2Gb memory, w/
~400mb free. When the query/report runs, you see all 4 processors "spike"
and "plateau" until the (BID) is asked. The report then completes within a
second or 2 depending on the amount of data on the page.

These delays can also be seen when editing the report by either moving a
label, text box, or text field. But I do not see the issue when either
changing font options (Bold, Underline, Italic), or font size.

Any ideas out there, because it is painful to design reports at 1 move per
15-20 seconds.

A compact does help but doesn't fix the issue?

Thank you,

Blaze

 

[Tom Wickerath]

Hi Blaze,

Here are some things to try. These suggestions come from a presentation that
Armen Stein gave to the Pacific NW Access Developer's Group last year. Armen
is the past President of the PNWADG, owner of JStreetTechnology, Inc., and
one of the co-authors of an Access 2003 book by Wrox on learning Access VBA.

Add SQL Server TimeStamp fields to every table
Access row locking normally compares EVERY field to the last read value to
see if anyone else has changed the record.
Not only is this slow, but for some floating point fields, the comparison
fails and Access always thinks the record has been changed by “another userâ€.
Use a TS field in every table. Access will use it to make the comparison.

Common performance bottlenecks:
Updateable detail screens using large recordsets (use an index screen to
open one record at a time)
Combo boxes on large rowsources
Reports with complex queries
Index screens with complex queries
Data lookup functions (Dlookup, Dsum, Dcount, etc.)


Use Passthrough Queries
To build: Query..SQL Specific..Passthrough
Very fast, but not updateable
Use SQL Server syntax (% instead of *, etc.)
Can be altered in code using the QueryDef.SQL property
Can be based on views or stored procedures for even more speed and complexity
Don’t work for subforms/subreports with master/child fields
Cheat! Use the Access or SQL Designer to get the syntax, then paste it into
the passthrough


Use NOLOCK
Even for non-updateable index forms, SQL Server will sometimes place locks
anyway. These can cause updates to fail and poor performance.

To prevent these, add (NOLOCK) to your passthrough queries.

It goes right after the FROM clause, like this:
Select * from Products (NOLOCK)


Bonus Trick 1
Performance issue with complex passthroughs
Access forms and reports are not supposed to evaluate the recordset until
AFTER the On Open event. However, we have seen that it does, probably to get
the column definitions of the recordset. This can cause a serious delay.
To avoid this, set the Where Clause of the passthrough query to “WHERE 1 =
0†when the form or report closes.
The next time, the object will open much faster. Then set the Where clause
normally.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I have a DB that was created in Access. I have developed with Access 2003,
SQL 2000 w/ SP4, Sharepoint Services V2 SP1.

I use access to do Sharepoint Queries for linking tables inside the Access
database. These queries work quite well, but I have 2 queries/reports that
run unbearilbly slow.

The Query takes anywhere from 6-10 seconds to execute and the Report takes
anywhere from 8-15 seconds to execute before it will ask for the information
required (called BID).

I have been running performance monitor against the machine and see that I
have plenty of memory and processor. I have a quad xeon with 2Gb memory, w/
~400mb free. When the query/report runs, you see all 4 processors "spike"
and "plateau" until the (BID) is asked. The report then completes within a
second or 2 depending on the amount of data on the page.

These delays can also be seen when editing the report by either moving a
label, text box, or text field. But I do not see the issue when either
changing font options (Bold, Underline, Italic), or font size.

Any ideas out there, because it is painful to design reports at 1 move per
15-20 seconds.

A compact does help but doesn't fix the issue?

Thank you,

Blaze

 

[Blaze]

Thank you for your input...

I don't exactly understand what you said, but I think I can work on it to
find what you mean. I am pretty green at Access and only started because it
was an easy way to interface sharepoint services. I have found for the
query, that one thing speeds it up a 20-fold.

The query "can" tie to the BID, but the BID is not required. The BID ties
to an Inmate Record ALWAYS. If the data has a link to a BID, it then pulls
the last updated information from the Inmate Record. If I take both the
Inmate Record table and the BID table from the query it runs like the rest.

Now with that said, the only thing I could come up is to have 2 different
queries. 1 to run against all the records from "Date" to "Date". This pulls
all reports for a date span. From there, if there was data with a reference
to the BID, the user would have to run that seperatly.

Okay... with that said. Is there any way to evaluate which report to run
for which record, without all the delay by running the initial "Full" report?
The biggest reason is that I don't see the user keeping track as to when
they had data with the BID reference. This isn't a HUGE deal, but it was a
great addon because it allowed us to compare known information and unknown
information.

Thank you for the reply as this was burdening the project!

Blaze

 

[Tom Wickerath]

Hi Blaze,

I don't exactly understand what you said, but I think I can work on it to
find what you mean.

Well, for example, fire up Enterprise Manager and add a TimeStamp field to
every table. This is a data type available to SQL Server. You don't need to
worry about adding any data to this field. SQL Server will take care of that
automatically. Also, have you indexed all fields that are used for criteria
or sorting in queries?

I realize that I forgot to add a very critical point: You should delete any
linked tables, compact the database, and re-establish the link from scratch
after making design changes to the back-end tables (ie. adding/removing
fields or indexes).

Okay... with that said. Is there any way to evaluate which report to run
for which record, without all the delay by running the initial "Full" report?

I don't know your database well enough to offer any constructive advice
here. Perhaps if you posted the structure of the tables involved, and the SQL
statements, then someone might be able to assist.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Thank you for your input...

I don't exactly understand what you said, but I think I can work on it to
find what you mean. I am pretty green at Access and only started because it
was an easy way to interface sharepoint services. I have found for the
query, that one thing speeds it up a 20-fold.

The query "can" tie to the BID, but the BID is not required. The BID ties
to an Inmate Record ALWAYS. If the data has a link to a BID, it then pulls
the last updated information from the Inmate Record. If I take both the
Inmate Record table and the BID table from the query it runs like the rest.

Now with that said, the only thing I could come up is to have 2 different
queries. 1 to run against all the records from "Date" to "Date". This pulls
all reports for a date span. From there, if there was data with a reference
to the BID, the user would have to run that seperatly.

Okay... with that said. Is there any way to evaluate which report to run
for which record, without all the delay by running the initial "Full" report?
The biggest reason is that I don't see the user keeping track as to when
they had data with the BID reference. This isn't a HUGE deal, but it was a
great addon because it allowed us to compare known information and unknown
information.

Thank you for the reply as this was burdening the project!

Blaze
__________________________________________

:

Hi Blaze,

Here are some things to try. These suggestions come from a presentation that
Armen Stein gave to the Pacific NW Access Developer's Group last year. Armen
is the past President of the PNWADG, owner of JStreetTechnology, Inc., and
one of the co-authors of an Access 2003 book by Wrox on learning Access VBA.

Add SQL Server TimeStamp fields to every table
Access row locking normally compares EVERY field to the last read value to
see if anyone else has changed the record.
Not only is this slow, but for some floating point fields, the comparison
fails and Access always thinks the record has been changed by “another userâ€.
Use a TS field in every table. Access will use it to make the comparison.

Common performance bottlenecks:
Updateable detail screens using large recordsets (use an index screen to
open one record at a time)
Combo boxes on large rowsources
Reports with complex queries
Index screens with complex queries
Data lookup functions (Dlookup, Dsum, Dcount, etc.)


Use Passthrough Queries
To build: Query..SQL Specific..Passthrough
Very fast, but not updateable
Use SQL Server syntax (% instead of *, etc.)
Can be altered in code using the QueryDef.SQL property
Can be based on views or stored procedures for even more speed and complexity
Don’t work for subforms/subreports with master/child fields
Cheat! Use the Access or SQL Designer to get the syntax, then paste it into
the passthrough


Use NOLOCK
Even for non-updateable index forms, SQL Server will sometimes place locks
anyway. These can cause updates to fail and poor performance.

To prevent these, add (NOLOCK) to your passthrough queries.

It goes right after the FROM clause, like this:
Select * from Products (NOLOCK)


Bonus Trick 1
Performance issue with complex passthroughs
Access forms and reports are not supposed to evaluate the recordset until
AFTER the On Open event. However, we have seen that it does, probably to get
the column definitions of the recordset. This can cause a serious delay.
To avoid this, set the Where Clause of the passthrough query to “WHERE 1 =
0†when the form or report closes.
The next time, the object will open much faster. Then set the Where clause
normally.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I have a DB that was created in Access. I have developed with Access 2003,
SQL 2000 w/ SP4, Sharepoint Services V2 SP1.

I use access to do Sharepoint Queries for linking tables inside the Access
database. These queries work quite well, but I have 2 queries/reports that
run unbearilbly slow.

The Query takes anywhere from 6-10 seconds to execute and the Report takes
anywhere from 8-15 seconds to execute before it will ask for the information
required (called BID).

I have been running performance monitor against the machine and see that I
have plenty of memory and processor. I have a quad xeon with 2Gb memory, w/
~400mb free. When the query/report runs, you see all 4 processors "spike"
and "plateau" until the (BID) is asked. The report then completes within a
second or 2 depending on the amount of data on the page.

These delays can also be seen when editing the report by either moving a
label, text box, or text field. But I do not see the issue when either
changing font options (Bold, Underline, Italic), or font size.

Any ideas out there, because it is painful to design reports at 1 move per
15-20 seconds.

A compact does help but doesn't fix the issue?

Thank you,

Blaze

 

[david epsom dot com dot au]

These delays can also be seen when editing the report by either

label, text box, or text field. But I do not see the issue when

http://www.granite.ab.ca/access/performancefaq.htm

Work through the suggestions. (FWIW, It looks like
"autocorrect" to me).

(david)

 

[aaron.kempf]

blaze

did you work for lernout and hauspie back in the day?

i personalyl think that sharepoint is the root of all evil; im sorry it
runs slow

maybe you can link to it directly via SQL from the sharepoint
repository insead?

you're just trying to display the data, not update it irght?

 

[Blaze]

No I didn't work for l&h... Did you know a Blaze there?

As for the direct SQL interface, I have NO IDEA on how to do this. For some
reason MS has left that who chapter out of anywhere. Is there an easy way to
directly access the SQL database? I do just want to read data. Posting is
not required. That is why I run sharepoint for a easy interface to data
entry.
Since Access has a way to link tables from the interface easily, I was able
to figure it out. I'm not a huge access user and my skills are limited, but
I have found this to work for what I need and it works quite efficiently.
If you know somewhere that references the SQL directly, it would be greatly
appreciated if you would post the links/reference materials!

Thank you,

Blaze

 

[Blaze]

Will adding this TimeStamp field cause any issues with Sharepoint? I can't
afford the time lost if it does The way MS stores Sharepoint data is
rediculous, but it's they way they do it and it works. Just doesn't seem to
be built the right way to me though.

I will work on getting my database design posted so that people can
learn/teach from it. I'm really surprised that more people aren't leveraging
the power of Sharepoint with the reporting power of Access. It seems there
are no references to it on the web and people either don't know about it or
are staying away for unknown reasons to me.

Thanks for the input!
Blaze

 

[aaron.kempf]

yeah.. the easy way to do Access against SQL is to use Access Data
Projects.

then you don't horse around with locking and DSNs; it's a lot more
manageable.

yeah-- blaze-- i used to work for L&H with 9 other people.. I was by
far, the WORST vb developer out of the crew.

A year later-- in 2001-- I was the only person from that group that was
still in the computer industry... Do you know why? i was the worst
programmer; and i was the only one that was programming.

BECAUSE I GAVE A CRAP ABOUT DATABASES.

 

[aaron.kempf]

and just for the record, blaze.. my professional opinion of sharepoint?

it's for Excel dorks.

you can't UPLOAD OR DOWNLOAD AN MDB OR ADP?

why the hell NOT microsoft

I dislike that you can't use real HTML inside of sharepoint-- i should
be able to make a description that includes <B><HR>

whatever i want-- i mean.. it is webbased so what gives MS?

and I think that sharepoint is a disease-- i mean-- from what i
understand 'frontpage screws up sharepoint pages' so it sounds to me
like it's just not ready for production use anywhere lol.

 

[Tom Wickerath]

Hi Blaze,

Will adding this TimeStamp field cause any issues with Sharepoint?

Not to my knowledge. Why are you even involving Sharepoint?

I'm really surprised that more people aren't leveraging the power of
Sharepoint with the reporting power of Access. It seems there are no
references to it on the web and people either don't know about it or are
staying away for unknown reasons to me.

I could be wrong, but my limited understanding of Sharepoint is that you
cannot enforce referential integrity between tables. That alone is enough to
keep me from even wanting to investigate any further as far as databases go.
My suggestion is to obtain a copy of the book titled "Microsoft Access
Developer's Guide to SQL Server", written by Mary Chipman and Andy Baron
(SAMS Publishing), and then re-write the data access portions of your
application, as required.

Here is a link to the book at Amazon.com:

http://www.amazon.com/exec/obidos/t...103-5399559-8631817?v=glance&s=books&n=507846

Early in the book (page 6) the authors write:

<Begin Quote>
"Many people think that upsizing from the Jet database engine to SQL Server
is a universal panacea for whatever is ailing their Access databases. It's
not. In fact, just the opposite is usually true. If your Access application
is a dog, then most likely it will still be a dog after you upsize it to SQL
Server--perhaps an even bigger, uglier, shaggier dog! Even a well-designed
Jet database often won't run any faster after the tables are upsized to SQL
Server if you are using the same data access methods you used for your Access
database. In order to successfully convert your Access database, you have to
be clear about why it needs to be upsized, and you need to understand how to
take advantage of the strengths of SQL Server by reworking the data access
elements of your Access application."
</End Quote>


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Will adding this TimeStamp field cause any issues with Sharepoint? I can't
afford the time lost if it does The way MS stores Sharepoint data is
rediculous, but it's they way they do it and it works. Just doesn't seem to
be built the right way to me though.

I will work on getting my database design posted so that people can
learn/teach from it. I'm really surprised that more people aren't leveraging
the power of Sharepoint with the reporting power of Access. It seems there
are no references to it on the web and people either don't know about it or
are staying away for unknown reasons to me.

Thanks for the input!
Blaze
__________________________________________

:

Hi Blaze,

I don't exactly understand what you said, but I think I can work on it to
find what you mean.

Well, for example, fire up Enterprise Manager and add a TimeStamp field to
every table. This is a data type available to SQL Server. You don't need to
worry about adding any data to this field. SQL Server will take care of that
automatically. Also, have you indexed all fields that are used for criteria
or sorting in queries?

I realize that I forgot to add a very critical point: You should delete any
linked tables, compact the database, and re-establish the link from scratch
after making design changes to the back-end tables (ie. adding/removing
fields or indexes).

Okay... with that said. Is there any way to evaluate which report to run
for which record, without all the delay by running the initial "Full" report?

I don't know your database well enough to offer any constructive advice
here. Perhaps if you posted the structure of the tables involved, and the SQL
statements, then someone might be able to assist.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Thank you for your input...

I don't exactly understand what you said, but I think I can work on it to
find what you mean. I am pretty green at Access and only started because it
was an easy way to interface sharepoint services. I have found for the
query, that one thing speeds it up a 20-fold.

The query "can" tie to the BID, but the BID is not required. The BID ties
to an Inmate Record ALWAYS. If the data has a link to a BID, it then pulls
the last updated information from the Inmate Record. If I take both the
Inmate Record table and the BID table from the query it runs like the rest.

Now with that said, the only thing I could come up is to have 2 different
queries. 1 to run against all the records from "Date" to "Date". This pulls
all reports for a date span. From there, if there was data with a reference
to the BID, the user would have to run that seperatly.

Okay... with that said. Is there any way to evaluate which report to run
for which record, without all the delay by running the initial "Full" report?
The biggest reason is that I don't see the user keeping track as to when
they had data with the BID reference. This isn't a HUGE deal, but it was a
great addon because it allowed us to compare known information and unknown
information.

Thank you for the reply as this was burdening the project!

Blaze
__________________________________________

:

Hi Blaze,

Here are some things to try. These suggestions come from a presentation that
Armen Stein gave to the Pacific NW Access Developer's Group last year. Armen
is the past President of the PNWADG, owner of JStreetTechnology, Inc., and
one of the co-authors of an Access 2003 book by Wrox on learning Access VBA.

Add SQL Server TimeStamp fields to every table
Access row locking normally compares EVERY field to the last read value to
see if anyone else has changed the record.
Not only is this slow, but for some floating point fields, the comparison
fails and Access always thinks the record has been changed by “another userâ€.
Use a TS field in every table. Access will use it to make the comparison.

Common performance bottlenecks:
Updateable detail screens using large recordsets (use an index screen to
open one record at a time)
Combo boxes on large rowsources
Reports with complex queries
Index screens with complex queries
Data lookup functions (Dlookup, Dsum, Dcount, etc.)


Use Passthrough Queries
To build: Query..SQL Specific..Passthrough
Very fast, but not updateable
Use SQL Server syntax (% instead of *, etc.)
Can be altered in code using the QueryDef.SQL property
Can be based on views or stored procedures for even more speed and complexity
Don’t work for subforms/subreports with master/child fields
Cheat! Use the Access or SQL Designer to get the syntax, then paste it into
the passthrough


Use NOLOCK
Even for non-updateable index forms, SQL Server will sometimes place locks
anyway. These can cause updates to fail and poor performance.

To prevent these, add (NOLOCK) to your passthrough queries.

It goes right after the FROM clause, like this:
Select * from Products (NOLOCK)


Bonus Trick 1
Performance issue with complex passthroughs
Access forms and reports are not supposed to evaluate the recordset until
AFTER the On Open event. However, we have seen that it does, probably to get
the column definitions of the recordset. This can cause a serious delay.
To avoid this, set the Where Clause of the passthrough query to “WHERE 1 =
0†when the form or report closes.
The next time, the object will open much faster. Then set the Where clause
normally.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I have a DB that was created in Access. I have developed with Access 2003,
SQL 2000 w/ SP4, Sharepoint Services V2 SP1.

I use access to do Sharepoint Queries for linking tables inside the Access
database. These queries work quite well, but I have 2 queries/reports that
run unbearilbly slow.

The Query takes anywhere from 6-10 seconds to execute and the Report takes
anywhere from 8-15 seconds to execute before it will ask for the information
required (called BID).

I have been running performance monitor against the machine and see that I
have plenty of memory and processor. I have a quad xeon with 2Gb memory, w/
~400mb free. When the query/report runs, you see all 4 processors "spike"
and "plateau" until the (BID) is asked. The report then completes within a
second or 2 depending on the amount of data on the page.

These delays can also be seen when editing the report by either moving a
label, text box, or text field. But I do not see the issue when either
changing font options (Bold, Underline, Italic), or font size.

Any ideas out there, because it is painful to design reports at 1 move per
15-20 seconds.

A compact does help but doesn't fix the issue?

Thank you,

Blaze