If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Linked SQL query performance after upgrade 97 to 2003
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?! |
#2
|
|||
|
|||
Linked SQL query performance after upgrade 97 to 2003
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/ -- Steve Clark, Former Access MVP FMS, Inc http://www.fmsinc.com/consulting "SusieN" wrote: 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?! |
#3
|
|||
|
|||
Linked SQL query performance after upgrade 97 to 2003
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" wrote: 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/ -- Steve Clark, Former Access MVP FMS, Inc http://www.fmsinc.com/consulting "SusieN" wrote: 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?! |
#4
|
|||
|
|||
Linked SQL query performance after upgrade 97 to 2003
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 "SusieN" wrote: 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" wrote: 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/ -- Steve Clark, Former Access MVP FMS, Inc http://www.fmsinc.com/consulting "SusieN" wrote: 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?! |
#5
|
|||
|
|||
Linked SQL query performance after upgrade 97 to 2003
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) "SusieN" wrote in message ... 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?! |
#6
|
|||
|
|||
Linked SQL query performance after upgrade 97 to 2003
SusieN wrote:
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). -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com |
#7
|
|||
|
|||
Linked SQL query performance after upgrade 97 to 2003
S.Clark wrote:
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/ |
Thread Tools | |
Display Modes | |
|
|