Linked Server Vs OPENQUERY
-
When I have to query the other database, I ofter use OPENQUERY to get the data. My new boss insists me using linked server. I proved to him, using OPENQUERY is faster than linked server but he would not hear it.
I want to know if OPENQUERY and Linked server, which one is better?
-
Here is my penny.
For the security: OPENQUERY is better.
For performance: I cannot see why necessarity of using OPENQUERY is faster than using linked server. Their only difference is the first one runs your queries on the remote site, another runs on the server which holds your linked server.
-
Maybe Loner means she uses OPENROWSET?
Greg
Greg
-
Yes, thanks Greg, it is OPENROWSET. My former DBA refused to use linked server because of security reason. One time my co-worker asked me how to query a different database so I told him to use OPENQUERY.
Now I mixed up in this thread. In my current company, they use linked server in almost every procedure. When the table has 6 millions rows, it takes forever to run. So I suggested to use OPENROWSET to retrieve the necessary data from the table in the database at once and put it in a temp table. Then I used temp table to join to the query. the query runs faster than joining a linked server database table to the query.
-
If you copied the data through a linked server to a temp table like you did with OPENROWSET, the speed would be the same.
The problem with OPENROWSET is that the UserID and Password must be hardcoded... that put's in in clear text to anyone who can read the proc. If the security is correct on that proc, that doesn't pose a big problem... what does pose a big problem is if you change the password on the target server for that user (according to PCI requirements, should occur every 3 months or less)... once that happens, you have to go and change all those passwords in code every where you used it. Good luck with that
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
We used window authentication, so there is no need to put user id and password in the procedure.
-
Doh!
My bad... made an assumption
Thanks for the feedback. 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply