MSSQL Data Warehouse
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'' ')