Convert multiple SQL code with multiple subqueries into a single query

Question

I'm starting to handle an old database that was generated years ago with ACCESS. All the queries have been designed with the ACCESS query wizard and they seem to be very time consuming and I would like to improve their performance.

All queries depend on at least three subqueries and I would like to rewrite the SQL code to convert them into a single query.

Here you have an example of what I'm talking about:

This is the main query:

SELECT Subquery1.pid, Table4.SIB, Subquery1.event, Subquery1.event_date, Subquery2.GGG, Subquery3.status FROM Subquery1 LEFT JOIN ((Table4 LEFT JOIN Subquery2 ON Table4.SIB = Subquery2.SIB) LEFT JOIN Subquery3 ON Table4.SIB = Subquery3.SIB) ON Subquery1.pid = Table4.PID;

This main query depends on three subqueries:

Subquery1

SELECT Table2.id, Table2.pid, Table2.npid, Table3.event_date, Table3.event, Table3.notes, Table2.other FROM Table2 INNER JOIN Table3 ON Table2.id = Table3.subject_id WHERE (((Table2.pid) Is Not Null) AND ((Table3.event_date)>#XX/XX/XXXX#) AND ((Table3.event) Like "*AAAA" Or (Table3.event)="BBBB")) ORDER BY Table2.pid, Table3.event_date DESC;

Subquery2

SELECT Table1.SIB, IIf(Table1.GGG Like "AAA","BBB", IIf(Table1.GGG Like "CCC","BBB", IIf(Table1.GGG Like "DDD","DDD","EEE"))) AS GGG FROM Table1;

Subquery3

SELECT Table5.SIB, Table5.PID, IIf(Table5.field1 Like "1","ZZZ",IIf(Table5.field1 Like "2","ZZZ",IIf(Table5.field1 Like "3","ZZZ",IIf(Table5.field1 Like "4","HHH",IIf(Table5.field1 Like "5","HHH",IIf(Table5.field1 Like "6","HHH","UUU")))))) AS SSS FROM Table5;

Which would be the best way of improving the performance of this query and converting all the subqueries into a single statement.

I can handle each subquery, but I'm having a hard time joining them together.

Can someone help me a little bit with this code?

Thank you very much,

Best,

As far as merging them into one is concerned you should be able to simply replace the references in the main query's FROM with the actual subqueries aliased as their name; for example FROM Subquery1 .... becomes FROM (SELECT Table2.id ..... Table3.event_date DESC) AS Subquery1 .... Obviously, you'll need to translate all queries to the appropriate sql dialect as well; MySQL won't like IIf or #date#. — Uueerdo
That is the way I was trying to do it, but it always returns me errors — Yatrosin
Did you translate the queries used for the subqueries and make sure they run correctly on their own before trying to merge? — Uueerdo
Yes, I did, and I was missing one of the AS Subqueries statements. Now it works!! The next question would be: does it make any sense running the subqueries as Temporary tables or would the performance be exactly the same? — Yatrosin
Using temp tables is really dependent on too many factors to be able to give a definitive answer; however, there is a strong argument against even having the latter two subqueries as subqueries. It looks likely you can just join Table1 and Table5 directly to Table4, and move their subquery versions' SELECT field calculations into the main query's SELECT. — Uueerdo

SunKnight0 SunKnight0 · Accepted Answer · 2016-11-09T19:45:31

If this:

Table5.field1 Like "3"

is really how some of your subqueries are written (without actual wild characters) you can save a lot of time by changing it to

Table5.field1="3"

Convert multiple SQL code with multiple subqueries into a single query

1 Answers