up vote 1 down vote favorite
1

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 for the query I have used 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
share|improve this question
Have you tried selecting the MAX from each of three tables first then using those values in the where clause? – Joshua Drake Apr 19 '12 at 13:43
No but I'll give it a go. Thanks – user114671 Apr 19 '12 at 16:28

2 Answers

up vote 1 down vote

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
share|improve this answer
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? – user114671 Apr 19 '12 at 16:35
Quite right. I overlooked that ref is the primary key. I've updated my answer. – Anthony Faull Apr 20 '12 at 9:15
Thanks Anthony, but it hasn't made the query any quicker. – user114671 Apr 21 '12 at 8:16
up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

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