Open Query vs "Linked" Query, what is the difference?
MS SQLHey everyone, I ran in to an interesting issue today trying to query an Oracle database I have set up as a linked server from my MS SQL box. The issue is the amount of time it takes a "linked" query to run vs. and Open Query. I call it a "linked" query becuase I don't know the actual name of the type of query. But I know the other is called an open query. The same query took 25 seconds when ran as linked vs 2 second when ran as an open query. That is a 92% difference, huge!!
So my question to you all is why? Where I can't share the table and column names with you I can share the general format of the queries below.
"Linked" Query: 25 seconds
T4.Column1,
T4.Column2
FROM LinkedServerName..Owner.Table1 T1,
LinkedServerName..Owner.Table2 T2,
LinkedServerName..Owner.Table3 T3,
LinkedServerName..Owner.Table4 T4
WHERE T1.Column = T2.Column AND
T2.Column = T3.Column AND
T3.Column = T4.Column AND
T1.Column = 100002800 AND
T4.Column = 'L' AND
(T4.Column is NULL OR T4.Column > getDate())
Open Query: 2 seconds
A.Column2
FROM OPENQUERY (
LinkedServerName,
'
SELECT DISTINCT
T4.Column1 AS Column1,
T4.Column2 AS Column2
FROM Owner.Table1 T1,
Owner.Table2 T2,
Owner.Table3 T3,
Owner.Table4 T4
WHERE T1.Column = T2.Column AND
T2.Column = T3.Column AND
T2.Column = T4.Column AND
T1.Column = 100002800 AND
T4.Column = ''L'' AND
(T4.Column is NULL OR T4.Column > Current_Date)
'
)A
Ok, so they are basically the same query, just different ways of calling the same data. Any MSSQL DBAs out there that can shed some light on this issue?
Good Day!
Ryan
Add Comment
Loading....