better performance on sql query

Question

I've been trying to rewrite the following query, because it's taking too long. I tried to rewrite it using Common Table Expressions, but still taking long or records are not equal.

This is the query I need to make it better performance.

SELECT 
  DISTINCT A_OMSCHRIJVING, 
  LokaalAll.LK_CODE, 
  LokaalAll.LK_KAMER_FK 
FROM 
  Lokaal LokaalOri 
  inner join lokaal LokaalAll on (
    LokaalAll.LK_KAMER_FK = LokaalOri.LK_KAMER_FK
  ) 
  inner join agendalesdata on (ALD_LOKAAL_FK = LokaalAll.LK_ID) 
  Inner join agendapunt on (APU_agendalesdata_FK = ALD_ID) 
  inner join agendaitems on (AIT_AGENDAPUNT_FK = APU_ID) 
  inner join agenda on (
    AIT_AGENDA_FK = A_ID 
    and A_TYPEAGENDA = 6
  ) 
WHERE 
  (
    LokaalOri.LK_ID in (
      11, 13, 15, 16, 180, 183, 184, 185, 186, 
      189, 190, 191, 192, 195, 196, 198, 199, 
      200, 202, 206, 210, 211, 212, 213, 278, 
      282, 286, 287, 290, 291, 293, 298, 302, 
      303, 309, 310, 346, 367, 368, 382, 387, 
      540, 542, 543, 549, 551, 554, 555
    )
  )  
  AND (APU_TOT >= '2023-04-27 14:45:00') 
  AND (APU_VAN < '2023-04-27 15:35:00') 
ORDER BY 
 AIT_ID

I think the performance is hit on this logic.

  Lokaal LokaalOri 
  inner join lokaal LokaalAll on (
    LokaalAll.LK_KAMER_FK = LokaalOri.LK_KAMER_FK
  ) 

I tried the following query

WITH t1 as ( select
  LK_ID, 
  LK_CODE, 
  LK_KAMER_FK 
FROM 
  Lokaal
  WHERE 
  (
    LK_ID in (
      11, 13, 15, 16, 180, 183, 184, 185, 186, 
      189, 190, 191, 192, 195, 196, 198, 199, 
      200, 202, 206, 210, 211, 212, 213, 278, 
      282, 286, 287, 290, 291, 293, 298, 302, 
      303, 309, 310, 346, 367, 368, 382, 387, 
      540, 542, 543, 549, 551, 554, 555
    )
  ) 
)
 select  A_OMSCHRIJVING, 
  t1.LK_CODE, 
  t1.LK_KAMER_FK from t1
  inner join lokaal LokaalAll on (
    LokaalAll.LK_KAMER_FK = t1.LK_KAMER_FK
  ) 
    inner join agendalesdata on (ALD_LOKAAL_FK = t1.LK_ID) 
      Inner join agendapunt on (APU_agendalesdata_FK = ALD_ID)     
  inner join agendaitems on (AIT_AGENDAPUNT_FK = APU_ID) 
  inner join agenda on (
    AIT_AGENDA_FK = A_ID 
    and A_TYPEAGENDA = 6
  ) 
  WHERE 

   (APU_TOT >= '2023-04-27 14:45:00') 
  AND (APU_VAN < '2023-04-27 15:35:00')  
ORDER BY 
  AIT_ID

But not same records are returned.

PS. Common Table Expressions are not obligated to be used.

Answer 1

First, you should always qualify the columns in your query by doing table.column or alias.column so users know which columns come from where.

Next, edit your existing post and list the table structures. We have no idea which foreign key joins to the other tables primary key. Having said that, I just forced aliases to the tables and probably applied the incorrect alias.column in the JOIN clauses which will make the query fail.

Having said that, I can offer more after getting the table structures (which tables have which columns) when you re-edit your post.

Now back to the query. You are using the Lokaal table twice, joining on itself on the LK_KAMER_FK which is probably killing your query with a Cartesian product. Take a look at the following sample data.

Lokaal
ID   LK_KAMER_FK  SomeOtherField
1    A            B
2    B            B
3    A            X
4    A            Y
5    B            W
6    A            Z
7    B            R

Even though you have 7 records in the above sample, by joining on the LK_KAMER_FK, you are actually getting

LokaalOri ID    LokaalOri LK_KAMER_FK     LokaalAll ID   LokaalAll LK_KAMER_FK   LokaalAll SomeOtherField
1               A                         1              A                       B
1               A                         3              A                       X
1               A                         4              A                       Y
1               A                         6              A                       Z
Now, the next iteration on the LokaalOri ID = 3 (next in line for LK_KAMER_FK) now results with
3               A                         1              A                       B
3               A                         3              A                       X
3               A                         4              A                       Y
3               A                         6              A                       Z
and again moving to ID #4
4               A                         1              A                       B
4               A                         3              A                       X
4               A                         4              A                       Y
4               A                         6              A                       Z
and 6 
6               A                         1              A                       B
6               A                         3              A                       X
6               A                         4              A                       Y
6               A                         6              A                       Z

even before you get to the "B" values of LK_KAMER_FK. This is known as a Cartesian result -- very bad.

Now, consider if you have 500 records with an LK_KAMER_FK of "A". You have just killed your machine's memory. So, get rid of it. Just use the one and join from that directly.

SELECT DISTINCT 
        A_OMSCHRIJVING, 
        L.LK_CODE, 
        L.LK_KAMER_FK 
    FROM 
        Lokaal L 
            inner join agendalesdata Ag
                on L.LK_ID = Ag.ALD_LOKAAL_FK
                Inner join agendapunt Ap
                    on Ag.ALD_ID = Ap.APU_agendalesdata_FK
                    inner join agendaitems Ai
                        on Ap.APU_ID = Ai.AIT_AGENDAPUNT_FK
                        inner join agenda Ag2
                            on Ai.AIT_AGENDA_FK = Ag2.A_ID 
                            and Ag2.A_TYPEAGENDA = 6
    WHERE 
            L.LK_ID in 
            (
                11, 13, 15, 16, 180, 183, 184, 185, 186, 
                189, 190, 191, 192, 195, 196, 198, 199, 
                200, 202, 206, 210, 211, 212, 213, 278, 
                282, 286, 287, 290, 291, 293, 298, 302, 
                303, 309, 310, 346, 367, 368, 382, 387, 
                540, 542, 543, 549, 551, 554, 555
            )
        AND Ap.APU_TOT >= '2023-04-27 14:45:00'
        AND Ap.APU_VAN < '2023-04-27 15:35:00'
    ORDER BY 
        Ai.AIT_ID

Again, the above will fail as we have no idea which columns are really associated with which table. And having proper indexes after your edit can add additional improvement.