Pass through query efficiency

[jondow]

Hi All,

Im just just wondering which method is more efficent and the pros and cons of each of the following two:

Pass-through query
Select * from table1
Inner Join Openquery('links','Select * from table2') ls on table1.id = ls.id

And

Select * from table1
Inner Join Server.Database.dbo.table2 ls on table1.id = ls.id

Also, what is the name latter method?

Thanks for your advice!

 

[gmmastros]

The second query should be more efficient. It's called a "Linked Server". To use the second method, you must first create it (done just once).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jondow]

OK, thanks George.

 

[rajeevnandanmishra]

I agree, the linked server query (with 3 dot notation) will be more efficient, but only if you are having the link server pointing to some native databases (e.g. MS SQL Server).

But if the link server is pointing to a ODBC DSN, then the pass through query will be more efficient.

I am not sure about the reason. It is just my experience when my link server is pointing to progress, text Data source.