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.


http://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.


http://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 (6)

  1. Ian Boyd says:

    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

    )

  2. Ian Boyd says:

    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)