We had a view in SQL server (2008) that consisted of a simple SELECT with some fields from a table that was on another database server (an Oracle database even). Performance was not a problem, the query took less than a second to complete. Until we had to add a left join to another table on that same remote server. The view suddenly took minutes to open. Of course, we first suspected the join and looked at the indices, but they were ok. Furthermore, running the same query on the remote Oracle database directly, performed great.

The query was defined like this (pseudo):

SELECT fldA, fldB FROM tblA

It was then changed to

SELECT fldA, fldB, fldC FROM tblA LEFT JOIN tblB ON (keyA=keyB)

where fldC was from tblB. But this was really slow, despite doing nothing really complicated here.

What turned out to be the problem? Because the second table was added later and field names were distinct, we had not prefixed table names for the field names. As soon as we changed the query to

SELECT tblA.fldA, tblA.fldB, tblB.fldC FROM tblA LEFT JOIN tblB ON (tblA.keyA=tblB.keyB)

performance was back to the regular level.

It appears that a field name lookup over a linked server connection from SQL Server to Oracle is really expensive. When using linked servers, I now take extra care to prefix tablenames to my fieldnames in a SELECT-statement.