0

We have a series of programs that are designed to move data from one SQL Server database into another using OPENQUERY and database linked servers.

We are noticing that queries such as the following are taking orders magnitude long than anticipated.

DELETE OPENQUERY(server, 'SELECT * FROM table WHERE pkf1 = ''v1'' AND pkf2 = ''v2''')

For example, if the above was rewritten as:

exec('DELETE * FROM table WHERE pkf1 = ''v1'' and pkf2 = ''v2''') at server

or

DELETE FROM server.schema.table WHERE pkf1 = 'v1' and pkf2 = 'v2'

the performance is only a few seconds compared to minutes using OPENQUERY.

Can someone explain why the OPENQUERY approach is so inefficient by comparison? Does it not use the primary key indexes?

Wyatt Shipman
  • 1,669
  • 1
  • 10
  • 22
Naros
  • 19,928
  • 3
  • 41
  • 71
  • you can take a look here https://support.microsoft.com/en-us/kb/309182 – jthalliens Mar 28 '16 at 20:20
  • " taking orders magnitude long than anticipated". Your anticipation is misguided. I guess the slow query transfers the records locally before deleting and the fast ones do not. OPENQUERY returns a resultset locally and the other methods do not. I wasn't familiar with the `at server` syntax though. Interesting. Surely that tells you that operations occur at the server not the client. – Nick.Mc Mar 28 '16 at 22:55

1 Answers1

0

Open query has a better handler than simple query like DELETE, and query is resolve within remote server

Oscar Pereyra Astorga
  • 1
  • 3