Linked Server Tables and Performance

  • I have a SQL Server 2000 database with a linked server to an access 2002 database. The query uses tables from a SQL Server database and the access database.

    Phase 1: Access database had linked tables and query runs via access and takes approx 6 minutes to run. - Yesterday

    Phase 2: I created a stored proc using the access tables via a linked server. I specifically used OpenRowset in my 'From' claus. The time was reduced to 54 seconds. A very significant increase. - Today

    Phase 3: Upsize Access database to SQL Server---Planned for the future

    Question: Is using OpenRowset the most efficeient method to talk to tables via a linked server.

    The access table have approx 20-30K records (3 tables)

    One of the SQL Server tables has approx 3 million records.

    I hope I've given enough information, if not just ask.

    Thanks in advance.

  • I think you want to use OPENROWSET only when you CAN'T make your server a linked server for some reason. You can use 4-part names to access a linked server and my experience has been this is the best.

    Access is not a true DBMS so it's hard to compare times when you are talking about it. All querys always return all data and then the engine filters out rows. Not very efficient compared to retrieving just the data you wanted in the first place.

  • Thanks for the info. I didn't want to use the OpenRowset to begin with, but I couldn't figure out how to use a table from a linked server in the from claus. Is there some trick to referencing an access database table?

  • I just found my answer.

    LinkedServerName...TableName

    Very cool

    54 seconds before...now we are down to 45 seconds.

  • If you are filtering the data returned using OPENROWSET might be better as this can pass the query to Access to filter. Depends on the link to the Access database, might be quicker to get all the data and filter on the SQL box.

    Other thing to note is OPENROWSET and OPENQUERY have static costs applied to them whatever is being run, this can result in some very bad queries if you then join to the this rowset.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • I would add another reason to performing all the actions on the SQL box. When I faced this issue last summer not only did I see a very large drop in processing time but the speed at scrolling between report pages went from 25 seconds to not an issue.



    Everett Wilson
    ewilson10@yahoo.com

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply