Partager via

Optimizing Distributed Queries

  • Article

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