0
\$\begingroup\$

I have the following query which works as it should, but I'd like to know how I can make it run faster.

For info, tables cl, jo and mi all have column ref as the auto_inc primary key and ID as the main identification number. Therefore, I use the refs to keep a history of changes for ID. So, I use SELECT MAX(ref) FROM [xx] GROUP BY ID to get the most recent cl jo and mi.

SELECT mi.ref, jo.ID, mi.ID AS mID, mt.mi_type, jo.ref, jt.jo_type, cl.cl_name 
FROM mi 
INNER JOIN mt ON mt.ref = mi.mi_type
INNER JOIN jo ON jo.ID = mi.job
INNER JOIN cl ON cl.ID = jo.cl
INNER JOIN jt ON jt.ref = jo.jo_type
WHERE mi.ref IN (SELECT MAX(ref) 
                    FROM mi 
                    GROUP BY ID)
AND jo.ref IN (SELECT MAX(ref)
                    FROM jo
                    GROUP BY ID)
AND cl.ref IN (SELECT MAX(ref)
                    FROM cl
                    GROUP BY ID)
ORDER BY cl.cl_name, jt.jo_type, mt.mi_type ASC
\$\endgroup\$

closed as unclear what you're asking by Jamal Dec 2 '16 at 4:16

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • \$\begingroup\$ Have you tried selecting the MAX from each of three tables first then using those values in the where clause? \$\endgroup\$ – Joshua Drake Apr 19 '12 at 13:43
  • \$\begingroup\$ No but I'll give it a go. Thanks \$\endgroup\$ – user417627 Apr 19 '12 at 16:28
1
\$\begingroup\$

Moving your subqueries to the from clause may improve performance.

Additional indexes may help too. Add a clustered index on ID or (ID, ref) for tables mi, jo and cl to speed up the group by and max operations. You may also benefit from a clustered index on each of your order by columns.

SELECT mi.ref, jo.ID, mi.ID AS mID, mt.mi_type, jo.ref, jt.jo_type, cl.cl_name 
FROM mi 
INNER JOIN mt ON mt.ref = mi.mi_type
INNER JOIN jo ON jo.ID = mi.job
INNER JOIN cl ON cl.ID = jo.cl
INNER JOIN jt ON jt.ref = jo.jo_type
INNER JOIN (SELECT MAX(ref) AS ref FROM mi GROUP BY ID) mi2 ON mi2.ref = mi.ref
INNER JOIN (SELECT MAX(ref) AS ref FROM jo GROUP BY ID) jo2 ON jo2.ref = jo.ref
INNER JOIN (SELECT MAX(ref) AS ref FROM cl GROUP BY ID) cl2 ON cl2.ref = cl.ref
ORDER BY cl.cl_name, jt.jo_type, mt.mi_type
\$\endgroup\$
  • \$\begingroup\$ Thanks Anthony. Unfortunately it doesn't appear to be any quicker. Can I ask you to explain why matching on ID as well as on ref (the primary key) improves the performance? \$\endgroup\$ – user417627 Apr 19 '12 at 16:35
  • \$\begingroup\$ Quite right. I overlooked that ref is the primary key. I've updated my answer. \$\endgroup\$ – Anthony Faull Apr 20 '12 at 9:15
  • \$\begingroup\$ Thanks Anthony, but it hasn't made the query any quicker. \$\endgroup\$ – user417627 Apr 21 '12 at 8:16
1
\$\begingroup\$

Thanks to Anthony for pointing me in the right direction, I then realised that I had unnecessary joins so here's the much quicker final version, for reference:

SELECT mi2.ref, jo2.ID, mi2.ID AS mID, mt.mi_type, jo2.ref, jt.jo_type, cl2.cl_name
FROM (SELECT ID, MAX(ref) AS ref, mi_type, job FROM mi GROUP BY ID) mi2
INNER JOIN mt ON mt.ref = mi2.mi_type
INNER JOIN (SELECT ID, MAX(ref) AS ref, cl, jo_type FROM jo GROUP BY ID) jo2 ON jo2.ID = mi2.job
INNER JOIN (SELECT ID, MAX(ref) AS ref, cl_name FROM cl GROUP BY ID) cl2 ON cl2.ID = jo2.cl
INNER JOIN jt ON jt.ref = jo2.jo_type
ORDER BY cl2.cl_name, jt.jo_type, mt.mi_type ASC

Hope this helps someone else.

\$\endgroup\$

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