Optimizing Distributed Queries

I saw a post in one of the newsgroups today that referenced a piece of information Microsoft published on how the Optimizer makes decisions about remoting certain operations.

https://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1a_6oxf.asp

It's slightly out-of-date, and I'll work on trying to get it updated.  Specifically, SQL 2005 will remote uniqueidentifiers (and it will support statistics over them as well).  We'll remote queries from SQL 2005 to SQL 2000 that contain filters comparing columns to constant uniqueidentifier values as well.

We published a research paper last year on how the Distributed Query feature works in more detail.  While it does not cover every practical detail of the implementation, you may find it as an interesting reference if you use distributed queries.

https://citeseer.ist.psu.edu/732761.html

If you have other remoting questions/problems, please post comments on them and we'll see if we can get them answered for you.

Thanks,

Conor

Comments

• Anonymous
  April 07, 2006
  It's time for everyone's favorite game: What should the remote query be?
  
  Get ready. Go!
  
  NOTES
  1. These can be solved by inspection. No knowledge of DDL, indexes or
  statistics is required.
  
  2. You must login to this site to post comments; makes adding comments difficult.
  
  
  Question#1: Querying a linked server
  SELECT *
  FROM servertest.CMSArchiveTraining.dbo.Transactions_90
  WHERE TransactionGUID = '9B2D0E34-F91C-45E8-A904-1BDA0E72802B'
  
  Answer#1:
  QUERY:(
     SELECT *
     FROM "CMSArchiveTraining"."dbo"."Transactions_90"
     WHERE TransactionGUID = '9B2D0E34-F91C-45E8-A904-1BDA0E72802B'
  )
  
  
  
  Question#2: Querying a linked server through a derived table
  SELECT *
  FROM (SELECT * FROM servertest.CMSArchiveTraining.dbo.Transactions_90)
  CMSArchiveTransactions
  WHERE TransactionGUID = '9B2D0E34-F91C-45E8-A904-1BDA0E72802B'
  
  Answer#2:
  QUERY:(
     SELECT *
     FROM "CMSArchiveTraining"."dbo"."Transactions_90"
     WHERE TransactionGUID = '9B2D0E34-F91C-45E8-A904-1BDA0E72802B'
  )
  
  
  
  Question#3: Querying a linked server through a view
  CREATE VIEW CMSArchiveTransactions AS
  SELECT *
  FROM servertest.CMSArchiveTraining.dbo.Transactions_90
  
  SELECT *
  FROM CMSArchiveTransactions
  WHERE TransactionGUID = '9B2D0E34-F91C-45E8-A904-1BDA0E72802B'
  
  Answer#3:
  QUERY:(
     SELECT
     FROM "CMSArchiveTraining"."dbo"."Transactions_90"
     WHERE TransactionGUID = '9B2D0E34-F91C-45E8-A904-1BDA0E72802B'
  )
  
  
  
  Question#4: Joining to a linked server table
  SELECT CMSArchiveTransactions.
  FROM CMSArchiveTransactions t
     INNER JOIN Customers c
     ON t.CustomerID = c.CustomerID
  
  Answer#4:
  QUERY:(
     SELECT
     FROM "CMSArchiveTraining"."dbo"."Transactions_90"
  )
  
  
  
  Question#5: Joining to a linked server with join criteria
  SELECT CMSArchiveTransactions.
  FROM CMSArchiveTransactions t
     INNER JOIN Customers c
     ON t.CustomerID = c.CustomerID
  WHERE c.CustomerID = 4463
  
  Answer#5:
  QUERY:(
     SELECT
     FROM "CMSArchiveTraining"."dbo"."Transactions_90"
     WHERE CustomerID = 4463
  )
  
  
  
  Question#6: Joining to a linked server with join criteria
  SELECT CMSArchiveTransactions.
  FROM CMSArchiveTransactions t
     INNER JOIN Customers c
     ON t.CustomerID = c.CustomerID
  WHERE t.TransactionAmount > 1000
  
  Answer#6:
  QUERY:(
     SELECT
     FROM "CMSArchiveTraining"."dbo"."Transactions_90"
     WHERE TransactionAmount > 1000
  )
  
  
  
  Question#7: Joining to a linked server with join criteria
  SELECT CMSArchiveTransactions.
  FROM CMSArchiveTransactions t
     INNER JOIN Customers c
     ON t.CustomerID = c.CustomerID
  WHERE t.TransactionAmount > 1000
  AND c.CustomerID = 4463
  
  Answer#7:
  QUERY:(
     SELECT *
     FROM "CMSArchiveTraining"."dbo"."Transactions_90"
     WHERE TransactionAmount > 1000
     AND CustomerID = 4463
  )
  
  
  
  Question#8 Joining to a linked server with join criteria
  SELECT c.CustomerName, t.TransactionAmount
  FROM CMSArchiveTransactions t
     INNER JOIN Customers c
     ON t.CustomerID = c.CustomerID
  WHERE c.CustomerID = 4463
  
  Answer#8:
  QUERY:(
     SELECT CustomerID, TransactionAmount
     FROM "CMSArchiveTraining"."dbo"."Transactions_90"
     WHERE CustomerID = 4463
  )
  
  
  
  Question#9: Joining to a linked server with join criteria
  SELECT c.CustomerName, t.TransactionAmount
  FROM CMSArchiveTransactions t
     INNER JOIN Customers c
     ON t.CustomerID = c.CustomerID
  WHERE c.CustomerID >= 4000
  AND c.CustomerID <= 5000
  
  Answer#9:
  QUERY:(
     SELECT CustomerID, TransactionAmount
     FROM "CMSArchiveTraining"."dbo"."Transactions_90"
     WHERE CustomerID >= 4000
     AND CustomerID <= 5000
  )
  
  
  
  Question#10: Joining to a linked server with aggregates
  ELECT c.CustomerID, c.CustomerName,
  LastCustomerTransactions.LastTransactionDate
  FROM Customers c
     LEFT JOIN (
         SELECT CustomerID, MAX(TransactionDate) AS LastTransactionDate
         FROM CMSArchiveTransactions
         GROUP BY CustomerID) AS LastCustomerTransactions
     ON c.CustomerID = LastCustomerTransactions.CustomerID
  
  Answer#10:
  QUERY:(
     SELECT CustomerID, MAX(TransactionDate) Col1042
     FROM "CMSArchiveTraining"."dbo"."Transactions_90"
  )
  
  
  Question#11 Joining to a linked server with aggregates and join criteria
  SELECT c.CustomerID, c.CustomerName,
  LastCustomerTransactions.LastTransactionDate
  FROM Customers c
     LEFT JOIN (
         SELECT CustomerID, MAX(TransactionDate) AS LastTransactionDate
         FROM CMSArchiveTransactions
         GROUP BY CustomerID) AS LastCustomerTransactions
     ON c.CustomerID = LastCustomerTransactions.CustomerID
  WHERE c.CustomerID = 4463
  
  Answer#11:
  QUERY:(
     SELECT CustomerID, MAX(TransactionDate) Col1042
     FROM "CMSArchiveTraining"."dbo"."Transactions_90"
     WHERE CustomerID = 4463
  )
  
  
  
  Question#12: Joining to a linked server with aggregates
  SELECT c.CustomerID, c.CustomerName,
  LastCustomerTransactions.LastTransactionDate
  FROM Customers c
     LEFT JOIN (
         SELECT CustomerID, MAX(TransactionDate) AS LastTransactionDate
         FROM CMSArchiveTransactions
         GROUP BY CustomerID) AS LastCustomerTransactions
     ON c.CustomerID = LastCustomerTransactions.CustomerID
  WHERE c.CustomerName = 'Ian Boyd'
  
  Answer#12:
  QUERY:(
     SELECT CustomerID, MAX(TransactionDate) Col1042
     FROM "CMSArchiveTraining"."dbo"."Transactions_90"
  )
  
  
  
  Question#13: Joining to a linked server with aggregates
  SELECT c.CustomerName, SUM(CMSArchiveTransactions.TransactionAmount)
  FROM CMSArchiveTransactions t
     INNER JOIN Customers c
     ON t.CustomerID = c.CustomerID
  GROUP BY c.CustomerName
  
  Answer#13:
  QUERY:(
     SELECT CustomerID, SUM(TransactionAmount) Col1042
     FROM "CMSArchiveTraining"."dbo"."Transactions_90"
     GROUP BY CustomerID
  )
  
  
  
  Question#14: Joining to a linked server with aggregates
  SELECT c.CustomerName, SUM(CMSArchiveTransactions.TransactionAmount)
  FROM CMSArchiveTransactions t
     INNER JOIN Customers c
     ON t.CustomerID = c.CustomerID
  WHERE CustomerName = 'Ian Boyd'
  GROUP BY c.CustomerName
  
  Answer#14:
  QUERY:(
     SELECT CustomerID, SUM(TransactionAmount) Col1042
     FROM "CMSArchiveTraining"."dbo"."Transactions_90"
     GROUP BY CustomerID
  )
  
  
  
  Question#15: Joining to a linked server with aggregates
  SELECT c.CustomerName, SUM(CMSArchiveTransactions.TransactionAmount)
  FROM CMSArchiveTransactions t
     INNER JOIN Customers c
     ON t.CustomerID = c.CustomerID
  WHERE c.CustomerName LIKE '%ian%'
  GROUP BY c.CustomerName
  
  Answer#15:
  QUERY:(
     SELECT CustomerID, SUM(TransactionAmount) Col1042
     FROM "CMSArchiveTraining"."dbo"."Transactions_90"
     GROUP BY CustomerID
  )
  
  
  
  Question#16: Joining to a linked server with aggregates
  SELECT c.CustomerName, SUM(CMSArchiveTransactions.TransactionAmount)
  FROM CMSArchiveTransactions t
     INNER JOIN Customers c
     ON t.CustomerID = c.CustomerID
  WHERE CustomerName LIKE '%ian%'
  AND c.CustomerID >= 4000
  AND c.CustomerID <= 5000
  GROUP BY c.CustomerName
  
  Answer#16:
  QUERY:(
     SELECT CustomerID, SUM(TransactionAmount) Col1042
     FROM "CMSArchiveTraining"."dbo"."Transactions_90"
     WHERE CustomerID >= 4000
     AND CustomerID <= 5000
     GROUP BY CustomerID
  )
  
  
  
  Question#17: Right join to linked table
  SELECT *
  FROM Customers c
     RIGHT OUTER JOIN CMSArchiveTransactions t
     ON c.CustomerID = t.CustomerID
  
  Answer#17:
  QUERY: (
     SELECT *
     FROM "CMSArchiveTraining"."dbo"."Transactions_90"
  )
  
  Question#18: Right join to linked table with left table filtering
  SELECT *
  FROM Customers c
     RIGHT OUTER JOIN CMSArchiveTransactions t
     ON c.CustomerID = t.CustomerID
  WHERE c.CustomerID = 4463
  
  Answer#18:
  QUERY: (
     SELECT *
     FROM "CMSArchiveTraining"."dbo"."Transactions_90"
  )
  
  Question#19: Right join to linked table with right table filtering
  SELECT *
  FROM Customers c
     RIGHT OUTER JOIN CMSArchiveTransactions t
     ON c.CustomerID = t.CustomerID
  WHERE t.TransactionAmount >= 1000
  
  Answer#19:
  QUERY: (
     SELECT *
     FROM "CMSArchiveTraining"."dbo"."Transactions_90"
     WHERE TransactionAmount >= 1000
  )
  
  
  Question#20: Right join to linked table with both table filtering
  SELECT *
  FROM Customers c
     RIGHT OUTER JOIN CMSArchiveTransactions t
     ON c.CustomerID = t.CustomerID
  WHERE c.CustomerID = 4463
  AND t.TransactionAmount >= 1000
  
  Answer#20:
  QUERY: (
     SELECT *
     FROM "CMSArchiveTraining"."dbo"."Transactions_90"
     WHERE TransactionAmount >= 1000
  )
  
  
• Anonymous
  April 07, 2006
  Extra credit questions.
  
  
  Question#21:
  CREATE VIEW AllTransactions AS
     SELECT t., 'local' AS SourceTable
     FROM LocalTransactions t
  
     UNION ALL    
  
     SELECT t., 'remote' AS SourceTable
     FROM servertest.CMSArchiveTraining.dbo.Transactions_90
  
  SELECT *
  FROM AllTransactions
  WHERE Source = 'local'
  
  Answer#21
  no remote query
  
  
  Question#22
  SELECT *
  FROM AllTransactions
  WHERE Source <> 'remote'
  
  Answer#22
  no remote query
  
  
  Question#23
  SELECT MAX(TranasctionDate)
  FROM AllTransactions
  
  Answer#23
  QUERY(
     SELECT MAX(TransactionDate) Col1402
     FROM CMSArchiveTraining.dbo.Transactions_90)
  
  Bonus points:
     AGGREGATE
         CONCAT
             LOCALQUERY(
                 SELECT MAX(TransactionDate) Col1402
                 FROM LocalTransactions)
             REMOTEQUERY(
                 SELECT MAX(TransactionDate) Col1402
                 FROM CMSArchiveTraining.dbo.Transactions_90)
  
  
  Question#24
  SELECT c.CustomerID, MAX(TransactionDate)
  FROM Customers c
     INNER JOIN AllTransactions t
  GROUP BY c.CustomerID
  
  Answer#24
  QUERY(
     SELECT CustomerID, MAX(TranasctionDate)
     FROM CMSArchiveTraining.dbo.Transactions_90
     GROUP BY CustomerID)
  
  Bonus points:
  
  INNER JOIN
     Customers
     AGGREGATE
         CONCAT
             REMOTEQUERY(
                 SELECT CustomerID, MAX(TranasctionDate)
                 FROM CMSArchiveTraining.dbo.Transactions_90
                 GROUP BY CustomerID)
             LOCALQUERY(
                 SELECT CustomerID, MAX(TransactionDate)
                 FROM LocalTransactions
             GROUP BY CustomerID)
• Anonymous
  September 30, 2006
  PingBack from http://chaespot.com/mssql/2006/09/30/ssw-sql-auditor-optimize-your-sql-server-performance-2/
• Anonymous
  April 25, 2007
  The comment has been removed
• Anonymous
  January 20, 2008
  The comment has been removed
• Anonymous
  June 16, 2009
  PingBack from http://fixmycrediteasily.info/story.php?id=6275