CODEWITHSUNDEEP
ms-access
Jossy
Jossy

Reputation: 1021

How do I improve the performance of my sub query?

I have the following two separate queries:

SELECT qry_tbl_G_ov_uni_atp.ID, Max(qry_tbl_G_ov_uni_atp.nElo_Ov) AS MaxOfnElo_Ov
FROM qry_tbl_G_ov_uni_atp
GROUP BY qry_tbl_G_ov_uni_atp.ID;

And:

SELECT qry_tbl_G_ov_uni_atp.ID, Max(qry_tbl_G_ov_uni_atp.nElo_Sur) AS MaxOfnElo_Sur1
FROM qry_tbl_G_ov_uni_atp
WHERE qry_tbl_G_ov_uni_atp.ID_C = 1
GROUP BY qry_tbl_G_ov_uni_atp.ID;

Both run fine and within a second or two. I want to combine them into one query so I have ID, MaxOfnElo_Ov and MaxOfnElo_Sur1 in the same output.

I know I need to use a sub query but my attempts take absolutely ages to display anything and then are barely useable as any attempt at scrolling locks Access up for an age again. I'm clearly not doing something right. Here's my sub query code:

SELECT qry_tbl_G_ov_uni_atp.ID, Max(qry_tbl_G_ov_uni_atp.nElo_Ov) AS MaxOfnElo_Ov, (SELECT Max(tt.nElo_Sur)
FROM qry_tbl_G_ov_uni_atp as tt
WHERE tt.ID_C = 1
AND tt.ID = qry_tbl_G_ov_uni_atp.ID) AS MaxOfnElo_Sur1
FROM qry_tbl_G_ov_uni_atp
GROUP BY qry_tbl_G_ov_uni_atp.ID;

Upvotes: 0

Views: 20

Answers (1)

Martin
Martin

Reputation: 16453

You can use a subquery to achieve this as you have indicated. By using a sub-query in the JOIN as well you will get all results from a (your first query) and matching results from b (your second query):

SELECT  a.ID,
        a.MaxOfnElo_Ov,
        b.MaxOfnElo_Sur1
  FROM  (
        SELECT  qry_tbl_G_ov_uni_atp.ID,
                Max(qry_tbl_G_ov_uni_atp.nElo_Ov) AS MaxOfnElo_Ov
          FROM  qry_tbl_G_ov_uni_atp
          GROUP BY qry_tbl_G_ov_uni_atp.ID
        ) a
    LEFT JOIN (
              SELECT  qry_tbl_G_ov_uni_atp.ID,
                      Max(qry_tbl_G_ov_uni_atp.nElo_Sur) AS MaxOfnElo_Sur1
                FROM  qry_tbl_G_ov_uni_atp
                WHERE qry_tbl_G_ov_uni_atp.ID_C = 1
                GROUP BY qry_tbl_G_ov_uni_atp.ID
              ) b ON b.ID = a.ID

Note that this is untested, and assumes the ID is the same in both a and b (which I believe it is).

Upvotes: 1

Related Questions