Linked SQL query performance after upgrade 97 to 2003

[SusieN]

Following upgrade of a database from Access 97 to 2003 we are experiencing
noticeably worse performance. We have a Find button (using the Access in
built Find box) on a linked SQL table of 30,000 records, using the
'reference' as the lookup. 'Reference' is one field of the primary key and
is also defined as an index on the SQL table - but scanning these records for
a valid reference is taking 30secs + where it used to be instant in Access
97. We have changed the SubDataSheet property (as per other posts along
these lines) but this has made no difference at all. Any suggestions,
please?!

 

[S.Clark]

Was the A97 version linked tables to SQL also?
Post the SQL.

Other Idears:
-Remove any *'s,
-Ensure the criteria fields are indexed
-Configure all database front-end computers to maintain an open connection
to the back-end database
-http://support.microsoft.com/kb/208858/

 

[SusieN]

Thanks Steve
A97 was also linked - all we did was upgrade the database to 2003 and
refresh the link tables. The point where performance is poor is on the
Access Find which is looking up on an indexed field. Only thing is that the
field is indexed in SQL, but does not show as index in Access - despite
refreshing the link. But field is also part of primary key (tho there is a
second field required for uniqueness, but only max of 2 records per
reference).
Will check the KB article for keeping the connection open.

Thanks

 

[S.Clark]

That kbb article doesn't show the keeping connection open concept, but it
does discuss the use of the MSysConf Table. My guess is that A97 Jet would
return & dispaly a smaller # of records, giving the illusion of faster
performance. With the MSysConf Table, I think you can fine tune this feature
to behave the same.

See, if you can tell Access to return only a small number of records and
display them, then keep working in the background to get the rest of the
records, then the user is faked out by the smoke and mirrors.

Checkout Tony Taves performance FAQ
http://www.granite.ab.ca/Access/performancefaq.htm

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

 

[david]

SubDataSheet property applies to MDB tables.

SQL Server tables, you may need to update your ODBC driver.
Are you using a DSN? or DSN-less connections?

(david)

 

[Rick Brandt]

Thanks Steve
A97 was also linked - all we did was upgrade the database to 2003 and
refresh the link tables. The point where performance is poor is on
the Access Find which is looking up on an indexed field. Only thing
is that the field is indexed in SQL, but does not show as index in
Access - despite refreshing the link. But field is also part of
primary key (tho there is a second field required for uniqueness, but
only max of 2 records per reference).
Will check the KB article for keeping the connection open.

In my experience "Find" is never a good thing to use against an ODBC link.
You end up with a full table scan. Use Filters instead as those are passed
back to the server for processing (most of the time).

 

[Tony Toews [MVP]]

Checkout Tony Taves performance FAQ

That's how it's pronounced but then we've met. <smile> It's spelled
Toews.

And there's nothing there on Access to SQL Server performance.

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/