slow adp and linked tables

[Ben]

Access 2000 -> sql server 2000

I'm finding access is slow querying data but the same query in enterprise
manager or query analyzer is fast. slowness also occurs when you are
specifying the tables/cols for a combo box to be based upon.

client net tools is set to use tcp/ip on :1433

What could be the issue ?

 

[Sylvain Lafontaine]

The most often explanation would be a bad query plan (EM or QA won't
necessarily use the same query plans as ADP because of possible changes in
the connection options (not the same user, etc.)).

The first thing to do would be to update the stats or to reindexe the table
with DBCC DBREINDEX. You can also try with adding WITH RECOMPILE option to
your SP. For reindexing all tables in a database, use something like:

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX (@TableName, ' ', 90) With NO_INFOMSGS
-- or: DBCC DBREINDEX (@TableName, ' ') With NO_INFOMSGS

FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor
GO