re: Optimizing Distributed Queries

Ian Boyd — Fri, 07 Apr 2006 17:11:03 GMT

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
)

re: Optimizing Distributed Queries

Ian Boyd — Fri, 07 Apr 2006 17:47:35 GMT

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)

Microsoft » Blog Archives » SSW SQL Auditor - Optimize your SQL Server performance

Sun, 01 Oct 2006 00:36:55 GMT

PingBack from http://chaespot.com/mssql/2006/09/30/ssw-sql-auditor-optimize-your-sql-server-performance-2/

Microsoft » Blog Archives » Tips, Tricks, and Advice from the SQL Server Query Optimization Team

Thu, 26 Apr 2007 03:48:31 GMT

PingBack from http://chaespot.com/mssql/2007/04/26/tips-tricks-and-advice-from-the-sql-server-query-optimization-team-3/

Website Scripts » Tips Tricks and Advice from the SQL Server Query Optimization Team …

Mon, 21 Jan 2008 11:32:53 GMT

PingBack from http://websitescripts.247blogging.info/tips-tricks-and-advice-from-the-sql-server-query-optimization-team/

Tips Tricks and Advice from the SQL Server Query Optimization Team | fix my credit

Wed, 17 Jun 2009 04:50:24 GMT

PingBack from http://fixmycrediteasily.info/story.php?id=6275