SQL Slow Query Execution

Question

SELECT s.supplier_id, s.supplier_name, o.order_date
FROM suppliers s, orders o
WHERE s.supplier_id = o.supplier_id
AND o.cust_reference = 9
ORDER BY o.order_date;

This query was supposedly executing slowly and was taking close to 10 minutes to execute how could query time be improved?

The reason for taking longer time is the "join " operation in where clause. You can add a sub query here to improve the performance — Nipun Alahakoon, Nov 10 '14 at 05:12
Have a look at this explanation: http://stackoverflow.com/a/129410/3063884 — CJBS, Nov 10 '14 at 05:13
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style should **no longer be used** and instead it is recommended to use the **proper ANSI JOIN** syntax introduced with the ANSI-**92** SQL Standard (more than **20 years** ago) — marc_s, Nov 10 '14 at 07:31

score 2 · Answer 1 · answered Nov 10 '14 at 05:13

Check the execution plan. Look for table scans. Make sure that the relevant indexes exist on the tables. And change from the 'FROM X,Y' with the expensive WHERE clause syntax to an INNER JOIN

SELECT s.supplier_id, s.supplier_name, o.order_date
FROM suppliers s
INNER JOIN orders o ON s.supplier_id = o.supplier_id
WHERE o.cust_reference = 9
ORDER BY o.order_date;

score 1 · Answer 2 · answered Nov 10 '14 at 05:18

Try Below Query because o.cust_reference = 9 Also use in join so its execution is fast because join occur only on that rows which have cust_reference = 9

SELECT s.supplier_id, s.supplier_name, o.order_date
FROM suppliers s
INNER JOIN orders o ON s.supplier_id = o.supplier_id
AND o.cust_reference = 9
ORDER BY o.order_date;

SQL Slow Query Execution

2 Answers2