Query performance of this Select Where each Column is a Select I would like to know how to improve the performance of the following query:

SELECT          Kunde.Nachname, 
                Kunde.Vorname, 
                Kunde.Debitorennummer, 
                sum(Abmeldestatus) AS Abmeldestatus,
                Ab1.Fruehstuck, 
                Best1.Menu1, 
                Best2.Menu2, 
                Ab2.Vesper, 
                Ab3.Abendbrot

FROM            Kunde 
LEFT OUTER JOIN
                (   SELECT      EssenTyp AS Fruehstuck, 
                                RefKundeId
                     FROM       Abbestellungen
                     WHERE      ([date] =@Datum) 
                     AND        (EssenTyp = 0)
                ) AS Ab1 ON Ab1.RefKundeId = Kunde.KundeId 

LEFT OUTER JOIN
                (   SELECT      BestellDetails.Anzahl AS Menu1, 
                                Bestellung.RefKundeId
                    FROM        Bestellung 
                    INNER JOIN  BestellDetails ON Bestellung.BestellId = BestellDetails.RefBestellId
                    WHERE       (BestellDetails.Datum = @Datum) 
                    AND         (BestellDetails.SpaltenNr = 0)
                ) AS Best1 ON Best1.RefKundeId = Kunde.KundeId 

LEFT OUTER JOIN 
                (   SELECT      BestellDetails.Anzahl AS Menu2, Bestellung.RefKundeId
                    FROM        Bestellung 
                    INNER JOIN  BestellDetails ON Bestellung.BestellId = BestellDetails.RefBestellId
                    WHERE       (BestellDetails.Datum = @Datum) 
                    AND         (BestellDetails.SpaltenNr = 1)
                ) AS Best2 ON Best2.RefKundeId = Kunde.KundeId

LEFT OUTER JOIN
                (   SELECT      EssenTyp AS Vesper, 
                                RefKundeId
                    FROM        Abbestellungen
                     WHERE      ([date] =@Datum) 
                    AND         (EssenTyp = 1)
                ) AS Ab2 ON Ab2.RefKundeId = Kunde.KundeId 

LEFT OUTER JOIN 
                (   SELECT      EssenTyp AS Abendbrot, 
                                RefKundeId
                    FROM        Abbestellungen
                     WHERE      ([date] =@Datum) 
                    AND         (EssenTyp = 2)
                ) AS Ab3 ON Ab3.RefKundeId = Kunde.KundeId

LEFT OUTER JOIN 
                (   SELECT      AbmeldeId AS Abmeldestatus, 
                                RefKundenId 
                    FROM        Abmelden
                     WHERE      (StartDate &lt;= @Datum) 
                    AND         (EndDate IS NULL OR EndDate &gt;= @Datum)
                ) AS Abm ON Abm.RefKundenId  = Kunde.KundeId

INNER JOIN  dbo.iter_intlist_to_tbl(@Kunden) AS i ON Kunde.KundeId = i.number

GROUP BY    Kunde.Nachname, 
            Kunde.Vorname, 
            Kunde.Debitorennummer, 
            Ab1.Fruehstuck, 
            Best1.Menu1, 
            Best2.Menu2, 
            Ab2.Vesper, 
            Ab3.Abendbrot


The query above takes 13sec to execute for 100 Kunden which is not acceptable.

My function iter_intlist_to_tbl takes a list of numbers (IDs) and creates a table with them.

I build my SELECT based on multiple SELECTs which are OUTER JOIN to return what I expect.

The result looks like:

Nachname | Vormane | Debitorennummer | Fruehstuck | Menu1 | Menu2 | Vesper | Abendbrot
--------------------------------------------------------------------------------------
Schmiedt | Lee     | 123456789       | NULL        |   1  |  NULL |   1    |    1
Müller   | Marie   | 123456700       |     1       | NULL |  NULL |  NULL  |    NULL


Execution plan:
You could try to use a PIVOT statement in order to convert values of several rows into columns. I show it for Abbestellungen.EssenTyp here. You will have to combine it with the rest of your query.

SELECT RefKundeId, [0] AS Fruehstuck, [1] AS Vesper, [2] AS Abendbrot
FROM (SELECT RefKundeId, EssenTyp FROM Abbestellungen WHERE [date] = @Datum) a
PIVOT
(
    MAX (EssenTyp)
    FOR EssenTyp IN ([0], [1], [2])
) AS pvt;


I am not sure which value you want to return for the EssenTyp columns. Your query returns the EssenTyp id, but your example result set returns 1. So may be you want a count? Then you would have to replace MAX(EssenTyp) by SUM (1) or COUNT(EssenTyp). If you are storing a number of meals say in a column Anzahl (german 'number'), then write SUM(Anzahl). In any case you need an aggregate function for the pivot operation.

Will it be faster? I don't know. Make experiments. Instead of making two independent pivot queries for Abbestellungen and Bestellung, you could combine the two in a UNION query and pivot them together:

SELECT
    RefKundeId,
    [0] AS Fruehstuck, [1] AS Vesper, [2] AS Abendbrot, [10] AS Menu1, [11] AS Menu2
FROM (
    SELECT RefKundeId, EssenTyp AS nr, 1 AS value
    FROM Abbestellungen
    WHERE [date] = @Datum

    UNION ALL

    SELECT B.RefKundeId, D.SpaltenNr + 10 AS nr, D.Anzahl AS value
    FROM
        Bestellung B
        INNER JOIN BestellDetails D
            ON B.BestellId = D.RefBestellId
    WHERE D.Datum = @Datum
) A
PIVOT
(
    SUM (value)
    FOR nr IN ([0], [1], [2], [10], [11])
) AS pvt;