MS SQL Server - Urgent - Slow join across SQL Linked Server to InterSystems Cache

Author

Urgent - Slow join across SQL Linked Server to InterSystems Cache

steven@ironcube.com

2006-02-03, 8:23 pm

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 parameterized pass through queries using
OPENQUERY and see excellent performance.

/*
SELECT * FROM
OPENQUERY(Cache_test
01, '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
*/

Looking more closely, SQL is pulling the entire table and then joining.
This is what I need to get worked out.

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

David Browne

2006-02-03, 8:23 pm

<steven@ironcube.com> wrote in message
news:1139001172.526969.271310@o13g2000cwo.googlegroups.com...
> 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.
>
> OPENQUERY and see excellent performance.
>
> /*
> SELECT * FROM
> OPENQUERY(Cache_test
01, '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
>

SQL Server linked servers are built on OleDb. Jet linked tables are built
on ODBC. You have only an ODBC driver, and are accessing Cache through the
OleDb provider for ODBC driver which is an extra layer of protocol mismatch
for SQL Server. This, I suspect, is why Jet does a better job here.

I suspect that SQL Server can't "see" the index through all the layers of
technology. Add to that the fact that for a relational data source (the
norm) "select *" is pretty cheap since rows in a table are physically stored
together. In a hierarchical database this is not the case, since rows are
physically stored under their parent key, and parent rows are physically
seperated by their children. This makes "select *" a very expensive
operation in those kinds of database systems. In essence any "select *"
causes a full scan of the whole database.

I would look for a workable solution using Jet to link together Cache and
SQL Server, or issue only "passthough" queries to Cache using OpenQuery.

David

steven@ironcube.com

2006-02-04, 8:23 pm

David, thank you SO MUCH for the feedback here. I thought the same
thing before I went looking online for help, and presented it to our
DBAs and they disagreed (but I know this has to be the issue). I setup
a linked server to the access file. The idea being, throught sql
server, i could use the linked server to the access file and then hit
the access linked table back to cache. However it turned out you can
only see local tables and nothing linked. So then i created access
queires hitting the access linked table. Although SQL could see the
queires, it couldn't run then.

I read at the Intersystems site that cache does support oledb. If i
could get oledb for cache installed on my sql server I could go through
that for the linked server and it would stand a better chance of not
loosing the indexes. This is a long shot but would you have any ideas
where i could get oledb for cache?

Sponsored Links