Performance with SQL linked tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rotsey - 15 May 2007 06:05 GMT

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

Reply to this Message

Sylvain Lafontaine - 15 May 2007 06:43 GMT

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

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

> Hi,
>
[quoted text clipped - 27 lines]
>
> rotsey

Reply to this Message

Rotsey - 15 May 2007 07:12 GMT

thanks for reply

You say run sp_updatestats or redinex all

Not both??

rotsey

> Quick check: first update the statistics (sp_updatestats) or reindexe all
> the tables (DBREINDEX on all tables); after that, clear the procedure
[quoted text clipped - 31 lines]
>>
>> rotsey

Reply to this Message

Sylvain Lafontaine - 15 May 2007 14:59 GMT

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

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

> thanks for reply
>
[quoted text clipped - 39 lines]
>>>
>>> rotsey

Reply to this Message

Todos Menos [MSFT] - 15 May 2007 16:48 GMT

I'd just reccomend

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

On May 15, 6:59 am, "Sylvain Lafontaine" <sylvain aei ca (fill the
blanks, no spam please)> wrote:

> Unless you want a full scan for the statistics, reindexing a table will also
> updates its statistics.
[quoted text clipped - 54 lines]
>
> - Show quoted text -

Reply to this Message

Todos Menos [MSFT] - 15 May 2007 09:36 GMT

linked tables _ALWAYS_ gave me awful performance

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

On May 14, 10:05 pm, "Rotsey"
<malcolm_sm...@RemoveThis.optusnet.com.au> wrote:

> Hi,
>
[quoted text clipped - 25 lines]
>
> rotsey

Reply to this Message

Tony Toews [MVP] - 15 May 2007 17:42 GMT

"T o d o s M e no s [M S F T ]" <t o do s _ m e no s _ ms f t@hotmail.com> wrote:

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

Signature

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/

Reply to this Message

Todos Menos [MSFT] - 21 May 2007 21:54 GMT

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

> "T o d o s M e no s [M S F T ]" <t o do s _ m e no s _ ms f t...@hotmail.com> wrote:
>
[quoted text clipped - 11 lines]
> Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm
> Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

Reply to this Message