🚀 go-pugleaf

Hi Tiedo!

I cannot tell why your query takes that much longer, but in general I 
think using COUNT in a subquery can be slow and I've never even tried 
using a GROUP BY within a subquery. I think your example can be written 
a bit differently:

SELECT DISTINCT G.GerechtigdeID, G.AdminNrPensioen
   FROM G_Gerecht G
   JOIN T_SelectStanden T ON G.GerechtigdeID = T.GerechtigdeID
   JOIN G_RekenBasis B ON B.GerechtigdeID = G.GerechtigdeID
WHERE B.RekenBasisCode = 50
   AND EXISTS(SELECT *
     FROM G_Gerecht Gr
     JOIN T_SelectStanden Tr ON Gr.GerechtigdeID = Tr.GerechtigdeID
     JOIN G_RekenBasis Br ON Br.GerechtigdeID = Gr.GerechtigdeID
     WHERE Br.RekenBasisCode = 50
       AND B.RekenBasisID = Br.RekenBasisID
       AND NOT EXISTS(SELECT *
         FROM G_RekenBasis Br2
         WHERE Br2.GerechtigdeID = Gr.GerechtigdeID
           AND Br2.RekenBasisCode = 50
           AND Br.RekenBasisID < Br2.RekenBasisID))
ORDER BY G.AdminNrPensioen

I'm a bit too tired just now to check whether this is right or if it can 
be simplified, but HTH,
Set

Tiedo Kruisselbrink wrote:
> I am using IB 2007
> 
> In my small testdatabase this query:
> SELECT DISTINCT G.GerechtigdeID, G.AdminNrPensioen
>   FROM G_Gerecht G
>   JOIN T_SelectStanden T ON G.GerechtigdeID = T.GerechtigdeID
>   JOIN G_RekenBasis B ON B.GerechtigdeID = G.GerechtigdeID
> WHERE B.RekenBasisCode = 50
> takes 0.05 sec to fetch 6 rows
> 
> this query:
> SELECT MAX(Br.RekenBasisID)
>   FROM G_Gerecht Gr
>   JOIN T_SelectStanden Tr ON Gr.GerechtigdeID = Tr.GerechtigdeID
>   JOIN G_RekenBasis Br ON Br.GerechtigdeID = Gr.GerechtigdeID
> WHERE Br.RekenBasisCode = 50
> GROUP BY Br.RekenbasisCode, Br.GerechtigdeID
> takes 0.02 sec to fetch 6 rows
> (in this simple testcase this are the same 6 rows)
> 
> Now I use the second query nested in the first:
> SELECT DISTINCT G.GerechtigdeID, G.AdminNrPensioen
>   FROM G_Gerecht G
>   JOIN T_SelectStanden T ON G.GerechtigdeID = T.GerechtigdeID
>   JOIN G_RekenBasis B ON B.GerechtigdeID = G.GerechtigdeID
> WHERE B.RekenBasisCode = 50
>   AND B.RekenBasisID IN
>     (
>       SELECT MAX(Br.RekenBasisID)
>         FROM G_Gerecht Gr
>         JOIN T_SelectStanden Tr ON Gr.GerechtigdeID = Tr.GerechtigdeID
>         JOIN G_RekenBasis Br ON Br.GerechtigdeID = Gr.GerechtigdeID
>       WHERE Br.RekenBasisCode = 50
>       GROUP BY Br.RekenbasisCode, Br.GerechtigdeID
>     )
> ORDER BY G.AdminNrPensioen
> This time it takes 4.39 sec. to fetch the same 6 records.
> 
> Since both queries can be executed independent, I expected 0.05 + 0.02
> = 0.07 sec.
> Even when IB executes the inner query again for each row of the outer
> query, I would expect only 0.05 + (6 * 0.02) = 0.17 sec.
> Why does it take this extreme long? What can I do about it?
> 
> (when I try this in SQL Server, everything is going as fast as I
> expected)
> 
> Thanks, Tiedo