Written by

Question Scott Hawkins · Oct 30, 2018

MSSQL Data Warehouse

#Caché #ODBC #Microsoft Windows Server #SQL

Hello,

I have been trying to pull data through a linked server in SSMS from an InterSystems Cache Database for a while, this is to enable us to join the data to other source systems in our Data Warehouse.

I have set up an ODBC connection and a linked server to the database and can execute queries through OPENQUERY in Management studio, but the data is huge (> 100million rows). So when I execute a SQL query with a WHERE clause the query just spins.

If I SELECT TOP 100 with no where clause the query returns data, so I know that all the connections are correct. Any guidance would be greatly appreciated!

Example of the query below:

SELECT *

FROM OPENQUERY (InterSystems_Cache ,
   'SELECT    pt.Column1,
               pt.Column2,
               pt.Column3,
               pt.Column4,
               pt.Column5,
               pt.Column6,
               pt.Column7,
               pt.Column8,
               pt.Column9,
               pt.Column10,
               pt.Column11,
               tr.Column12,
               tr.Column13,
               tr.Column14,
               te.Column15,
               te.Column16,
               te.Column17,
               te.Column18,
               te.Column19,
               te.Column20,
               rs.Column21,
               rs.Column22,
               rs.Column23,
               re.Column24,
               re.Column25,
               re.Column26,
               tr.Column27,
               tr.Column28,
               re.Column29
       FROM Database1.Table1 tr
           LEFT JOIN Database1.Table2 te on te.Column16 = tr.Column13
           LEFT JOIN Database1.Table3 rs on rs.Column23 = tr.Column28
           LEFT JOIN Database1.Table4 re on re.Column25 = rs.Column22
           LEFT JOIN Database1.Table5 pt on pt.Column6 = re.Column26
       WHERE pt.Column10 = ''2018-10-30'' ')

Discussion (2)0

Add reply

Comments

Bharathsimha Reddy Jakka · Oct 30, 2018

Hi,

Not sure if this could help - Just adding here to see if this was tried -

Hope you have the SQL Gateway connections setup from Management portal.

In STUDIO -> Create a class and then try executing the following inside a Class Method / method

ClassMethod DataWarehouseFetch() As %Status

{

Set Status=$$$OK

Set SQL= 7

Set SQL(1)= "SELECT pt.Column1, pt.Column2, pt.Column3, pt.Column4, pt.Column5, pt.Column6, pt.Column7, pt.Column8, pt.Column9, pt.Column10, pt.Column11, tr.Column12, tr.Column13, tr.Column14, te.Column15, te.Column16, te.Column17, te.Column18, te.Column19, te.Column20, rs.Column21, rs.Column22, rs.Column23, re.Column24, re.Column25, re.Column26, tr.Column27, tr.Column28, re.Column29 "

Set SQL(2)= "FROM Database1.Table1 tr "

Set SQL(3)= "LEFT JOIN Database1.Table2 te on te.Column16 = tr.Column13"

Set SQL(4)= "LEFT JOIN Database1.Table3 rs on rs.Column23 = tr.Column28"

Set SQL(5)= "LEFT JOIN Database1.Table4 re on re.Column25 = rs.Column22"

Set SQL(6)= "LEFT JOIN Database1.Table5 pt on pt.Column6 = re.Column26"

Set SQL(7)= "WHERE pt.Column10 = '2018-10-30'"

Set Status= Statement.%Prepare(.SQL)
If $$$ISERR(Status) $$$ThrowOnError(" PREPARE issue")
Set tResults=Statement.%Execute()

While (tResults.%Next()){

//w tResults.Column1,!

//w tResults.%Get("Column2"),!

//w tResults.Get("Column2"),!

}

Quit Status

}

0 0

Scott Hawkins Nov 1, 2018 to Bharathsimha Reddy Jakka

I only have access to Microsoft's SQL Server Management Studio, I have been informed that my OBDC connection looks correct.

I have been told that

"Typically there is an index 'table' that you have to join to for optimization. It isn't always obvious or exposed by the vendor, but they should be able to provide you direction."

Does this sound correct?

Thank you!!

0 0