Performance with SQL linked tables

  • Thread starter Thread starter Rotsey
  • Start date Start date
R

Rotsey

Hi,

My question is a application performance issue so maybe the wrong forum.

I have a SQL Server 2005 Express database and connected Access 2003 clients.

The performance seems to have degraded since moving from SQL 7.

The users are seeing delays when clicking on menu optiosns that have open
bound forms in Access.

So I have been using the Profiler to have a look at things.

I am filtering events SQL:Batchcompleted and RPC:Completed with duration >
10ms.

I found some issues with the forms loading without filerting rows so i have
changed it
so it does not load any rows on opening.

Anyhow some users say that they are still experiencing delays from the menu.

So my question finally is if I see a delay on my menu but no corresponiding
spike in the profiler can i assume that the problem is with Access and not
SQL.

Any help would be great.

rotsey
 
Quick check: first update the statistics (sp_updatestats) or reindexe all
the tables (DBREINDEX on all tables); after that, clear the procedure cache
(DBCC FREEPROCCACHE, DBCC DROPCLEANBUFFERS).
 
linked tables _ALWAYS_ gave me awful performance

that's why I started using access data projects a long long long time
ago
 
Unless you want a full scan for the statistics, reindexing a table will also
updates its statistics.
 
I'd just reccomend

a) proper indexing
b) simpler architecture using Access Data Projects
 
T o d o s M e no s said:
linked tables _ALWAYS_ gave me awful performance

that's why I started using access data projects a long long long time
ago
Click to expand...


Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.
--
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/
 
note that tony is neither

MOST
VALUABLE
or a
PROFESSIONAL


anyone with a clue would have started using SQL Server a decade ago;
it is a completely superior platform in all regards-- with no
negatives

only a retard that can't learn a real database would ever use Access
MDB
I mean get real-- move to ADP or real VB-- either way, Access MDB is
dead and it has been for a decade
 
Back
Top