December 9, 2002 at 1:46 pm
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.
December 9, 2002 at 2:25 pm
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.
December 9, 2002 at 2:34 pm
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?
December 9, 2002 at 2:48 pm
I just found my answer.
LinkedServerName...TableName
Very cool
54 seconds before...now we are down to 45 seconds.
December 10, 2002 at 9:36 am
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
December 10, 2002 at 1:09 pm
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