1
\$\begingroup\$

database relations can be found here http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-h_v2.18.0.pdf

SELECT l_orderkey, sum(l_extendedpriced * (1 - l_discount)) as revenue, o_orderdate, o_shippriority
FROM customer, orders, lineitem
WHERE c_mktsegment = 'BUILDING' -- '[SEGMENT]' 
    and c_custkey = o_custkey 
    and l_orderkey = o_orderkey 
    and o_orderdate < "1995-03-15" -- '[DATE]' 
    and l_shipdate > "1995-03-15" -- '[DATE]'
GROUP BY l_orderkey, o_orderdate, o_shippriority
ORDER BY revenue desc, o_orderdate;

I was planning on optimizing this query by using joins like so

SELECT l_orderkey, sum(l_extendedpriced * (1 - l_discount)) as revenue, o_orderdate, o_shippriority
FROM customer
JOIN orders ON c_custkey = o_custkey
JOIN lineitem ON o_orderkey = l_orderkey
WHERE c_mktsegment = 'BUILDING' --'[SEGMENT]'
AND o_orderdate < "1995-03-15" -- '[DATE]'
AND l_shipdate > "1995-03-15" -- '[DATE]'
GROUP BY l_orderkey, o_orderdate, o_shippriority
ORDER BY revenue desc, o_orderdate;

Now all I have to do is create indexes for the tables. For this problem, a maximum of 3 indexes per table can be created with its primary key being 1 of them.

The indexes that I came up with are (these are the columns used in the GROUP BY and ORDER BY clauses)

CREATE INDEX idx_l_orderkey ON lineitem(l_orderkey);
CREATE INDEX idx_o_orderdate_shippriority ON orders(o_orderdate, o_shippriority);

Are there any more indexes that I can put in to further optimize this query? I feel like I need to make another index for the customer table for the c_mktsegment. Also, if there's any other strategies that I forgot, could you please enlighten me. Thank you.

\$\endgroup\$
1
\$\begingroup\$

You can ask SQL Server to recommend the indexes that might improve performance of your query by selecting the query and clicking on this Display Estimated Execution Plan button on the taskbar (or simply Ctrl+L):

enter image description here

This will analyze the query and it will show you the query cost of the operations required to retrieve the data, and eventually propose which indexes might be useful.

Btw, using the joins (as on your 2nd example) should be more efficient indeed.

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.