I have a sql linked server pointed to a cache database. I created it
using the oledb for odbc and pointed it to an odbc data source I
created using the intersystems odbc driver install. I can openquery the
database fine if i need to pull back specific record fine. But if i try
to join data between my sql database and cache it crawls. I created a
simple join between two tables, one in my sql database, the other in
the cache databse. I joined on a common indexed field and it too 12
minutes to pull back 600 records. i repeate the process in ms access,
which uses straigh odbc for its linked tables, and it returned the data
in 2 seconds. The sql execution planed showed that sql server is
pulling back all the records from cache and then comparing the data.
The cache table is huge and pulling all of it is why sql is going so
slow.
I tried changing some of the linked server setting like collation and
so on, but now changes. Does anyone have any ideas how to address this
issue (sql pulling the entire table from cache over and then comparing
the data) or does anyone know where I can get the OLEDB driver for
cache. Intersystems says there is one but I can't find it at their
site.
Thank you for any ideas. :-)
.Hi
It is not clear if you are joining with OPENQUERY or a linked server e.g
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM
OPENQUERY ( loopback, 'select au_id, au_fname, au_lname from pubs..authors') a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
or
SELECT a.au_id, t.title_id, a.au_fname, a.au_lname, t.title
FROM loopback.pubs.dbo.authors a
JOIN pubs..titleauthor ta on a.au_id = ta.au_id
JOIN pubs..titles t on t.title_id = ta.title_id
But looking at your other posts it is the latter you are using. For an OLEDB
drive you should contact CACHE.
John
"steven@.ironcube.com" wrote:
> I have a sql linked server pointed to a cache database. I created it
> using the oledb for odbc and pointed it to an odbc data source I
> created using the intersystems odbc driver install. I can openquery the
> database fine if i need to pull back specific record fine. But if i try
> to join data between my sql database and cache it crawls. I created a
> simple join between two tables, one in my sql database, the other in
> the cache databse. I joined on a common indexed field and it too 12
> minutes to pull back 600 records. i repeate the process in ms access,
> which uses straigh odbc for its linked tables, and it returned the data
> in 2 seconds. The sql execution planed showed that sql server is
> pulling back all the records from cache and then comparing the data.
> The cache table is huge and pulling all of it is why sql is going so
> slow.
> I tried changing some of the linked server setting like collation and
> so on, but now changes. Does anyone have any ideas how to address this
> issue (sql pulling the entire table from cache over and then comparing
> the data) or does anyone know where I can get the OLEDB driver for
> cache. Intersystems says there is one but I can't find it at their
> site.
> Thank you for any ideas. :-)
> ..
>|||Hi John,
I got posts all over this group this week as well as the cache. Sorry,
I didn't see that anyone got back to me on this one. I actually have
tried both syntax you mentioned above. I saw the same from both of
them. Very slow.
I contacted cache over the weekend and I'm hoping to see an email from
them soon in my inbox. If I do and the OLEdb driver helps (or doesn't)
I'm going to post back just so other in the same boat know what
happened.
Thanks for taking the time to post. I appreciate it. Have a good one.
:-)|||Hi
I did not see an example of the first query in your previous post!
You may also want to specify only the column names required instead of (*)
which will reduce the size of the data being retrieved
SELECT X.ID, X.Col1, X.Col2
FROM OPENQUERY(Cache_test01, 'SELECT ID, col1, col2 FROM Table01 WHERE
ID=22 ') AS X
JOIN dbo.tblTable01AddOn A ON X.ID = A.ID
Have you tried using a temporary table to store the data from the openquery?
If the openquery can be restricted that should be better, although it may
mean resorting to dynamic SQL.
John
"steven@.ironcube.com" wrote:
> Hi John,
> I got posts all over this group this week as well as the cache. Sorry,
> I didn't see that anyone got back to me on this one. I actually have
> tried both syntax you mentioned above. I saw the same from both of
> them. Very slow.
> I contacted cache over the weekend and I'm hoping to see an email from
> them soon in my inbox. If I do and the OLEdb driver helps (or doesn't)
> I'm going to post back just so other in the same boat know what
> happened.
> Thanks for taking the time to post. I appreciate it. Have a good one.
> :-)
>
