Access 2007/SQL 2005 ADP Performance Issues

[Vee Van Dyke]

Hi all. I have developed a SQL 2005 ADP that contains a LOT of Data. We are
currently experiencing some interesting performance issues over the network.
I am interested in finding solution for optimizing my DB and any other
suggestions that could help to improve the performance of the program.

We are running an ADP developed in Access 2007. Each workstation has a local
copy of the ADE on their machine. Some machines are using 2003, some 2007.

The issues are as follows:
1. In certain forms, the first 1-2 records that you add are extremely slow
when tabbing from field to field. after you have added several records, the
speed increases. This only occurs in workstations using Access 2007.

2. Certain reports have slowed WAAAAAY down and are crashing certain PC's.
They don't have THAT much data and the ones that are doing it are running
stored procedures. This occurs on both 2003 and 2007 workstations.

None of these issues occur if I am remoted into the SQL server. They DO
occur if someone else from the LAN is logged remotely into the SQL server.

Any advice is appreciated.
Thanks.
Vee Van Dyke

 

[Vadim Rapp]

VVD> We are currently experiencing some interesting performance issues over
VVD> the network.

You may find this thread useful:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3020500&SiteID=17

Vadim Rapp

 

[Vee Van Dyke]

Vadim-
Thank you for your prompt reply. The PC's that are experiencing issues are
not connecting via VPN or WAN. They are actually connected on the LAN, and
have mainly the reverse problem... the longer they stay connected, the better
the speed gets.

Any other ideas are welcomed.

Thank you.
V

 

[Sylvain Lafontaine]

Looks like a recompilation problem or a locking problem. The first thing to
do would be to update the statistics using the sp_updatestats stored
procedure and to free the procedure caches:

DBCC FLUSHPROCINDB
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

After that, adding the WITH RECOMPILE option to your stored procedures
and/or looking about the parameter sniffing problem (Seach Google on these
terms) will tell us if you have a recompilation problem. If you have a
locking problem, this will be harder to find.

In all cases, using (or learning to use) the SQL-Server Profiler to see
what's going on would be probably a very good idea.

A final possibility would be that you have an authentification issue. If
the peoples on the LAN are using the Windows authentification, then trying
with a SQL-Server account would be a good test to do.

 

[Vadim Rapp]

Hello, Vee!
You wrote on Sat, 7 Jun 2008 13:09:00 -0700:

VVD> Thank you for your prompt reply. The PC's that are experiencing issues
VVD> are not connecting via VPN or WAN. They are actually connected on the
VVD> LAN, and have mainly the reverse problem... the longer they stay
VVD> connected, the better the speed gets.

This is expected: first, sql server is probably configured to use dynamic
amount of memory, and once it sees increased usage, it grabs more and more
memory, increasing its performance; at the same time, more and more data,
indexes, and code are collected in the data and procedure cache.

I would probably spend some time using _very_ detailed profiling of sql
server, to see what extra is coming from Access 2007 compared to 2003 that
results in slowing down the reports.

Generally speaking, changing the product from the release 2003 that was
suppsoed to live to the release 2007 that is supposed to die (meaning ADP as
technology) is probably much closer to the term "downgrade" than to
"upgrade". Down to the grave, so to speak.

Vadim Rapp

 

[a a r o n . k e m p f]

bullshit you dipshit

nobody ever said ADP was supposed to die.



-Aaron

 

[a a r o n . k e m p f]

have you built enough indexes?

Seriously-- have you build enough indexes?
Select *
From Sys.dm_db_missing_index_details

ADP has a bright future!

-Aaron

 

[a a r o n . k e m p f]

have you put nolock on reporting queries?

 

[Tony Toews [MVP]]

ADP has a bright future!

ADP has not had any enhancements for at least the last two versions of
Access.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[a a r o n . k e m p f]

Tony;

I disagree.

What about SQL Server 2005 support?

What abotu SQL Server 2005 _ITSELF_?

What about the PIVOT keyword?
What about the UNPIVOT keyword?

ADP has had millions of improvements. Access MDB is a ****ing waste of
time and effort.

Seriously why do you post such mis-information?

-Aaron