Linked server to SQL Azure very slow

Question

I can use the Management studio to connect directly to my Azure SQL database and execute this query:

select * from DB.dbo.DSeries where instrument='name1' 

and it works really fast. But if I create a linked server (called "WF") in my local server that connects to Azure and try to execute the same query,

select * from WF.DB.dbo.DSeries where instrument='name1' 

The query is really slow. Why could that be happening?

• Nestor: However, SELECT * FROM OPENQUERY(WF, 'select * from DB.dbo.DSeries where instrument=''name1'' ') seems to work fast !?

Answer 1

Try setting the "Collation Compatible" to True on the Linked Server definition.

If you set it to False, it will pull the entire table down and do the compare on the receiving end. Consider the scenario where the local server is case insensitive, and the remote server is case sensitive, the results will differ based on where the compare is done.

• Nestor: Yessss... that solved it. Care to explain the solution?