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?

However, SELECT * FROM OPENQUERY(WF, 'select * from DB.dbo.DSeries where instrument=''name1'' ') seems to work fast !? — Nestor, Nov 21 '11 at 06:47

score 5 · Accepted Answer · edited Dec 01 '11 at 21:35

5

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.

edited Dec 01 '11 at 21:35

Nick Chammas

14,670
17
75
121

answered Nov 21 '11 at 18:07

SqlACID

2,218
16
16

3

Yessss... that solved it. Care to explain the solution? – Nestor Nov 21 '11 at 18:32

Linked server to SQL Azure very slow

1 Answers1

Linked