I need a slow query on AdventureWorks (SQL 2005)

Question

As an exercise (read:interview question) in index optimisation, I need a query which is slow on the standard AdventureWorks database in SQL2005. All the queries I've tried take about 1 second and I would prefer to have a query which takes multiple seconds so that it can be optimised effectively.

Can anyone here create such a query or give me pointers to how to create a slow query? I just can't seem to make my queries non-performant :)

Answer 1

Here you have list of database tables with the most rows:

Tables - Rows count
Sales.SalesOrderDetail - 121317
Production.TransactionHistory - 113443
Production.TransactionHistoryArchive - 89253
Production.WorkOrder - 72591
Production.WorkOrderRouting - 67131
Sales.SalesOrderHeader - 31465
Sales.SalesOrderHeaderSalesReason - 27647
Person.Contact - 19972
Person.Address - 19614
Sales.CustomerAddress - 19220
Sales.Customer - 19185
Sales.ContactCreditCard - 19118
Sales.CreditCard - 19118
Sales.Individual - 18484
Sales.CurrencyRate - 13532

You can try different variations using that tables. For example this query:

SELECT * FROM Sales.SalesOrderDetail s
INNER JOIN Production.Product p ON s.ProductID = p.ProductID

runs for 9 seconds at my computer.

You can run this:

SELECT * FROM Production.TransactionHistory th
INNER JOIN Production.TransactionHistoryArchive tha ON th.Quantity = tha.Quantity

Inner join on unindexed tables. Very artificial example, but for now it takes over 2 minutes to roll on my machine. Now - over 20 minutes. Now - 1h 20 minutes.