Distributed query vs OPENQUERY


Distributed query (Four-part name) and OPENQUERY


OpenQuery Distributed Query
速度 一般來說 OPENQUERY較快一點 也很快
Query optimizer 遠端產生執行計畫 本地產生執行計畫 產生local query與remote query
連線數量 只會產生1條連線到遠端取回資料 會產生2條連線
第1條連線先取回統計資訊
第2條連線再取回資料
優點 可以在一個Query裡面JOIN多個不同SQL Server的Table
可能出現issue 如果太多連線使用Distributed Query,則會造成大量等待SOSHOST_MUTEX wait
缺點 雖然有WHERE條件,但SQL Server可能會送出SELECT * FROM the remote table,然後等資料回到本地端才進行filter
權限 只需要設定SELECT資料表的權限 為了產生最佳執行計畫,remote login account必須有以下權限,才能取得完整的統計資訊,若沒有以下權限,則查詢效能則會比較差 To create the best query plans the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server. https://msdn.microsoft.com/en-us/library/ms175537.aspx 但SQL 2012開始則不需要此權限

Best Performer: Distributed query (Four-part) or OPENQUERY when executing linked server queries in SQL Server
https://blogs.msdn.microsoft.com/sqlsakthi/2011/05/08/best-performer-distributed-query-four-part-or-openquery-when-executing-linked-server-queries-in-sql-server/
Security for Linked Servers
http://msdn.microsoft.com/en-us/library/ms175537.aspx
Guidelines for Using Distributed Queries
https://msdn.microsoft.com/en-us/library/ms175129.aspx
OPENQUERY (Transact-SQL)
https://msdn.microsoft.com/en-us/library/ms188427.aspx
Optimizing Distributed Queries
https://technet.microsoft.com/en-us/library/ms180972(v=sql.105).aspx
Improving the Performance of Distributed Queries
http://sqlmag.com/database-performance-tuning/improving-performance-distributed-queries

 

Comments (0)

Skip to main content