SQL server views performing really bad when referencing a linked server

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.

Advertenties

Over Shiftkey
I am Maarten, owner of and chief software developer for Shiftkey software development from The Netherlands. I will be writing mainly about things I run into when programming in C# or Delphi.

Geef een reactie

Vul je gegevens in of klik op een icoon om in te loggen.

WordPress.com logo

Je reageert onder je WordPress.com account. Log uit / Bijwerken )

Twitter-afbeelding

Je reageert onder je Twitter account. Log uit / Bijwerken )

Facebook foto

Je reageert onder je Facebook account. Log uit / Bijwerken )

Google+ photo

Je reageert onder je Google+ account. Log uit / Bijwerken )

Verbinden met %s

%d bloggers liken dit: