February 3, 2006 at 10:29 am
Hi Everyone,
I am a newbie to this discussion forum. If I’m bending/breaking any of the rules, please let me know.
I need some major help with a linked server I setup. I have a cache database that we can only access through ODBC (no mumps). I have a SQL database, db01, which is storing data related to a table in one of the cache databases. I setup a linked server on our SQL Server pointing to the cache database using the MS OLE DB Provider for ODBC driver, referencing our ODBC data source.
From SQL Server I can do pass through queries using OPENQUERY and see excellent performance when looking for specific IDs.
/*
SELECT * FROM
OPENQUERY(Cache_test01, 'SELECT * FROM Table01 WHERE ID=22') AS X
*/
Works great. However, whenever I do a simple join between my SQL database and Cache across the link it crawls.
/*
--This returns 600 records but takes 12 minutes. It should take a few seconds top.
SELECT A.*, X.*
FROM
tblTable01AddOn AS A INNER JOIN Cache_test01..SQLUser.Table01 AS X ON A.ID = X.ID
--I also tried.
SELECT A.*, X.*
FROM Cache_test01..SQLUser.Table01 AS X, tblTable01AddOn A
Where X.ID = A.ID
*/
I have no access to the Cache server. I cannot make any modifications to any settings on that server. Our vendor reassured me that ID is indexed in Cache. ID is indexed on tblTable and Collation Compatible is checked on the SQL Linked Server pointing to Cache (Cache_test01).
I know little about Cache but I am stuck with making this work. At first glance I would imagine the performance issue has something to do with a setting on the Cache server. But I’m seeing the following which strongly leads me to believe that it is SQL.
I somewhat replicated this entire situation in MS Access and it is smoking fast. I created a second ODBC data source pointing to my SQL database. I then created an Access 2000 database and created two linked tables using the ODBC data sources (one to tblTable01AddOn in the SQL database and the other to Table01 in the cache database [using the same DSN I referenced when setting up the Linked Server in SQL]).I then put together this query:
/*
SELECT dbo_tblTable01AddOn.*, SQLUser_Table01.*
FROM dbo_tblTable01AddOn INNER JOIN SQLUser_Table01 ON dbo_tblTable01AddOn.ID = SQLUser_Table01.ID;
*/
It returns the data, all 600 records, in two seconds. So the providers are capable of working in this manner. When I take the SQL linked server out of the equation it is fast. The linked table in Access uses the same ODBC data source I used to create the linked server, so I don’t think it’s ODBC driver or the DSN either.
I should be able to see this performance in SQL. What is it that Access/Jet is doing that SQL isn’t? Can someone please help me workout the issue with my linked server? There is something about how the linked server is pulling the data that Jet is not doing. I have to get this to work or I’m going to fall weeks back on this project. With a new born in the house, our first, and working 60 hours a week at work, I can’t afford to loose that time.
Thank you for any help you can provide.
BTW, I also tried
/*
Select A.*, C.ID
From
tblTable01AddOn AS A,
(SELECT ID
FROM Cache_test01..SQLUser.Table01
WHERE ID IN (SELECT ID FROM tblTable01AddOn)) AS C
*/
Thinking I would drive this as a table and rejoin it to my table in SQL, but it crawled. SQL still pulled the entire table back and then looked for my data.
So why is MS Access Kicking my SQL Server 2000’s backside? ANY help would be IMMENSELY appreciated. Thank you in advance.
Steven
February 3, 2006 at 10:46 am
Welcome to the forum
Some guesses:
1) if you "link" the intercache table in Access, is there a primary key/unique index?
2) How fast does
SET NOCOUNT ON
SELECT A.ID, X.ID
FROM tblTable01AddOn AS A
INNER JOIN Cache_test01..SQLUser.Table01 AS X ON A.ID = X.ID
return?
3) Does the linked server has the 'lazy schema validation on?'
When creating a distributed partitioned view in SQL Server 2000, set the "lazy schema validation" option true for each linked server participating in your distributed partitioned views. This acts to optimize performance by ensuring that the query processor does not request meta data for any of the linked tables until the data is actually needed from the remote member table, reducing overhead. For example, to set this option to true, run this command for at each linked server:
sp_serveroption 'server_name', 'lazy schema validation', true
where 'server_name' is the name of the linked server.
http://www.sql-server-performance.com/federated_databases.asp
February 3, 2006 at 2:49 pm
@ Welcome to the forum
Thank you. 🙂
@ 1) if you "link" the intercache table in Access, is there a primary key/unique index?
Yes
@ 2) How fast does . . .
Faster, quite a bit (about 2 minutes). If I explicitly specify the field names (restricting what I bring back) it does a lot better. Unfortunately it’s still too slow for this project. And I’m still puzzled about what Access is doing to bring back all the fields from both tables in seconds.
@ 3) Does the linked server has the 'lazy schema validation on?'
I set it to true to see if it might help but I’m seeing the same thing. Darn. Thank you for the ideas though. If any other ideas cross your mind please let me know. I really need the help.
I even went so far as to set up a linked server to the Access file. Then from SQL I referenced the linked server to the files and tried hitting the tables that way. But SQL can’t see the linked tables. So then I tried creating access quires in the access file pointing to the linked access tables. Although SQL could see the queries in the Access file, they would crash (I guess because it still couldn’t see the tables). 🙁
February 4, 2006 at 6:33 am
Have you checked that collation compatible option is on for the linked server?
September 2, 2015 at 10:09 am
Hey Steven,
That child of yours must be big by now ;-). I am facing some similar issues and would e grateful if you have any updates on this ?
Ernest
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply