Slow but simple query, how to make it faster?
I have a 6 GB database with many tables, but small queries seem to have the most problems and you want to know what can be done to optimize them, for example there is a table of stocks, items and orders.
A stock exchange table is an item in a warehouse that stores approximately 100,000 records with 25 fields that store ProductCode, Price, and other inventory data.
The Elements table stores information about items with over 2,000,000 of them, with over 50 fields storing item names and other information about the item or product in question.
The Orders table stores the orders for the items that were specified when the order was placed, plus the price sold and has about 50,000 records.
Here is a query from that database:
SELECT Stock.SKU, Items.Name, Stock.ProductCode FROM Stock
INNER JOIN Order ON Order.OrderID = Stock.OrderID
INNER JOIN Items ON Stock.ProductCode = Items.ProductCode
WHERE (Stock.Status = 1 OR Stock.Status = 2) AND Order.Customer = 12345
ORDER BY Order.OrderDate DESC;
Given the information on what can be done to improve this query, there are other similar alternatives that exist. However, the nature of the data and database cannot be detailed further, so if generic optmisation methods and techniques are provided, it would be fine or anything that would normally apply to databases.
Database - MS SQL 2000 on Windows Server 2003 with the latest service packs for each. Database upgrade / OS upgrade are no longer options.
Edit
The indices are Stock.SKU, Items.ProductCode and Orders.OrderID in the mentioned tables.
Execution plan is 13-16 seconds for a request such as 75% of the time spent in the warehouse
Thanks for all the answers so far. Indexing seems to be the problem, all the various examples given were helpful - spell out a few errors with the query, but it helped me solve many of these queries faster, but when combined with the suggestions of the index, I think I might be on the right way - thanks for the quick answers - really helped me and made me consider something that I hadn't thought of and didn't know about before!
Indices. My problem was added to the Foriegn key with orders (by Customer) and this improved performance at the expense of minimal execution time!
Looks like I got a tunnel vision and focused on the question - I have been working with DB for a few years now, but it was very helpful. However, thanks for all the query examples, they are combinations and features that I have not covered can also be helpful!
a source to share
Is your code correct ??? I'm sure you are missing something
INNER JOIN Batch ON Order.OrderID = Orders.OrderID
and you have a) in your code ...
you can always check some options against the execution plan tool like
SELECT
s.SKU, i.Name, s.ProductCode
FROM
Stock s, Orders o, Batch b, Items i
WHERE
b.OrderID = o.OrderID AND
s.ProductCode = i.ProductCode AND
s.Status IN (1, 2) AND
o.Customer = 12345
ORDER BY
o.OrderDate DESC;
and you should only return a part, like TOP 10 ... it will take a few milliseconds for the TOP 10, but you will save a lot of time by tying it to your application.
a source to share
A tabular index will certainly help, as Cătălin Pitiş suggested.
Another trick is to reduce the size of the join strings, either by brute-force selection or for more extreme temporary tables. For example, instead of joining the entire Orders table, join
(SELECT * FROM Orders WHERE Customer = 12345)
also don't join directly to the stock table join on
(SELECT * FROM Stock WHERE Status = 1 OR Status = 2)
a source to share
Setting up the correct indexes on tables usually makes the biggest difference in performance.
In Management Studio (or Query Analyzer for earlier versions), you can choose to view the execution plan for a query when it runs. In the execution plan, you can see what the database is doing to get the result, and which parts are working the most. There are some things to look for in there, like table scan, which is usually the most expensive part of the query.
The primary key of a table usually has an index, but you must make sure it does. Then you probably want indexes for the fields you use to find records and the fields you use to sort.
After you've added the index, you can rerun the query and see in the execution plan if it actually uses the index. (You may have to wait a while after creating the database index to create the index before it can use it.)
a source to share
Could you let him go?
SELECT Stock.SKU, Items.Name, Stock.ProductCode FROM Stock
INNER JOIN Order ON Order.OrderID = Stock.OrderID AND (Order.Customer = 12345) AND (Stock.Status = 1 OR Stock.Status = 2))
INNER JOIN Items ON Stock.ProductCode = Items.ProductCode
ORDER BY Order.OrderDate DESC;
a source to share
Developing what Cătălin Pitiş said: in your request
SELECT Stock.SKU, Items.Name, Stock.ProductCode
FROM Stock
INNER JOIN Order ON Order.OrderID = Stock.OrderID
INNER JOIN Items ON Stock.ProductCode = Items.ProductCode
WHERE (Stock.Status = 1 OR Stock.Status = 2) AND Order.Customer = 12345
ORDER BY Order.OrderDate DESC;
the criterion Order.Customer = 12345 looks very specific, while (Stock.Status = 1 OR Stock.Status = 2) sounds non-specific. If correct, an effective query consists of
1) first find orders that belong to a specific customer,
2), then find the matching Stock rows (with the same OrderID) filtering out those with Status (1, 2),
3) and finally find items with the same ProductCode as Stock strings in 2)
For 1) you need a pointer to Customer for the Order table, for 2) an index for OrderID for the Stock table, and for 3) an index for ProductCode for table items.
Until your query becomes much more complex (like a subquery in a larger query, or that the product, order, and items are just views, not tables), the query optimizer should be able to find this plan from your query. ... Otherwise, you will have to do what kuoson suggests (but the second suggestion does not help if Status in (1, 2) is not very specific and / or the status is not indexed in the Status table). But also remember that when storing indexes there is an ongoing performance cost if you are doing a lot of nesting / updates to the table.