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

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

Share this:

Vind ik leuk:

Gerelateerd

Over ShiftkeyI 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.