Performance with SQL linked tables

[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

 

[Sylvain Lafontaine]

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).

 

[Rotsey]

thanks for reply

You say run sp_updatestats or redinex all

Not both??

rotsey

 

[Todos Menos [MSFT]]

linked tables _ALWAYS_ gave me awful performance

that's why I started using access data projects a long long long time
ago

 

[Sylvain Lafontaine]

Unless you want a full scan for the statistics, reindexing a table will also
updates its statistics.

 

[Todos Menos [MSFT]]

I'd just reccomend

a) proper indexing
b) simpler architecture using Access Data Projects

 

[Tony Toews [MVP]]

linked tables _ALWAYS_ gave me awful performance

that's why I started using access data projects a long long long time
ago

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/

 

[Todos Menos [MSFT]]

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