Question

Queries written by developers are not always optimized for performance. It is crucial to ensure that all queries are fully optimized in the database whenever possible so that they can execute optimally. There may be changes in code or the addition of indices to help with optimization.

Review six queries that require optimization (I am stuck on these 3).

1. SELECT Order.CustomerID, Product.ProductID, OrderDetail.OrderID

FROM Product, OrderDetail, Order

WHERE Order.OrderID = OrderDetail.OrderID AND OrderDetail.ProductID = Product.ProductID AND OrderDetail.Price = 50

a. Optimize query performance using appropriate strategies (that is, optimizer hints, additional indices, changing the query search arguments, or some combination thereof as appropriate).

b. Explain briefly why you chose the strategies that you employed.

2. SELECT Order.CustomerID, Product.ProductID, OrderDetail.OrderID

FROM Product, OrderDetail, Order

WHERE Order.OrderID = OrderDetail.OrderID AND OrderDetail.ProductID = Product.ProductID AND CustomerID < 100 AND Quantity = 1

a. Optimize query performance using appropriate strategies (that is, optimizer hints, additional indices, changing the query search arguments, or some combination thereof as appropriate).

b. Explain briefly why you chose the strategies that you employed.

3. SELECT Customer.FirstName, Customer.LastName, Product.ProductName, OrderDetail.Quantity, OrderDetail.Price

FROM Customer, Order, OrderDetail, Product

WHERE Customer.CustomerID = Order.CustomerID

AND Order.OrderID = OrderDetail.OrderID

AND OrderDetail.ProductID = Product.ProductID

AND ProductID NOT IN (1, 2, 3, 4, 5, 6, 7)

a. Optimize query performance using appropriate strategies (that is, optimizer hints, additional indices, changing the query search arguments, or some combination thereof as appropriate).

b. Explain briefly why you chose the strategies that you employed.

Answer 1

1. (a)

SELECT Order.CustomerID, Product.ProductID, OrderDetails.OrderID

FROM ( Order INNER JOIN OrderDetails ON Order.OrderID = OrderDetails.OrderID ) INNER JOIN Product ON OrderDetails.ProductID = Product.ProductID where OrderDetails.Price = 50;

2. (b)

SELECT Order.CustomerID, Product.ProductID, OrderDetail.OrderID

FROM ( Order INNER JOIN OrderDetails ON Order.OrderID = OrderDetails.OrderID ) INNER JOIN Product ON OrderDetails.ProductID = Product.ProductID

where OrderDetails.Price = 50 AND Order.CustomerID < 100 AND OrderDetails.Quantity = 1;

3.(a)

SELECT Customer.FirstName, Customer.LastName, Product.ProductName, OrderDetail.Quantity, OrderDetail.Price

FROM (Customer LEFT INNER JOIN Order ON Customer.CustomerID = Order.CustomerID )

INNER JOIN OrderDetails ON Order.OrderID = OrderDetails.OrderID ) INNER JOIN Product ON OrderDetails.ProductID = Product.ProductID

where Product.ProductID NOT IN (1, 2, 3, 4, 5, 6, 7);