SQL Server view optimization help (repeated subqueries, case when, and so on...)

I need some help optimizing a MSSQL view that is, honestly, a little too much complex for my knowledge.

The view is working good but I would like to rewrite it using less subqueries or a better structure in order to simplify it and use less server resources.

The main issue is a CASE WHEN with the same subquery repeated 4 times... I tried to understand if I can put it in a variable and use it for the CASE instead of repeating the query each time but it seems not possible to me...

Here's the query

SELECT     dbo.MACCHINE.id_macchina, [...] dbo.VIEW_CANTIERI.indirizzo,

(SELECT     TOP (1) data_fine
FROM          dbo.MANUTENZIONI
WHERE      (id_macchina = dbo.MACCHINE.id_macchina)
ORDER BY data_fine DESC)
AS ultima_manutenzione, 

DATEDIFF(day,
    (SELECT     TOP (1) data_fine
    FROM          dbo.MANUTENZIONI AS MANUTENZIONI_1
    WHERE      (id_macchina = dbo.MACCHINE.id_macchina)
    ORDER BY data_fine DESC), GETDATE())
AS data_diff, 

(CASE WHEN 
    stato = 0 
    THEN 'GREY' 
WHEN stato = 2 
    THEN 'BLACK' 
WHEN stato = 1 
    THEN
        (CASE WHEN 
            DATEDIFF(day,
            (SELECT     TOP (1) data_fine
            FROM          dbo.MANUTENZIONI AS MANUTENZIONI_1
            WHERE      (id_macchina = dbo.MACCHINE.id_macchina)
            ORDER BY data_fine DESC), GETDATE()) >= 90 
        THEN 'RED'

        WHEN 
            DATEDIFF(day,
            (SELECT     TOP (1) data_fine
            FROM          dbo.MANUTENZIONI AS MANUTENZIONI_1
            WHERE      (id_macchina = dbo.MACCHINE.id_macchina)
            ORDER BY data_fine DESC), GETDATE()) >= 80 
        THEN 'ORANGE' 

        WHEN
            DATEDIFF(day,
            (SELECT     TOP (1) data_fine
            FROM          dbo.MANUTENZIONI AS MANUTENZIONI_1
            WHERE      (id_macchina = dbo.MACCHINE.id_macchina)
            ORDER BY data_fine DESC), GETDATE()) >= 60 
        THEN 'YELLOW'

        WHEN
            (SELECT     TOP (1) data_fine
            FROM          dbo.MANUTENZIONI AS MANUTENZIONI_1
            WHERE      (id_macchina = dbo.MACCHINE.id_macchina)
            ORDER BY data_fine DESC) IS NULL
        THEN 'RED' ELSE 'GREEN' 

        END) 

END) 
AS colore FROM         dbo.MACCHINE INNER JOIN
                  dbo.MACCHINE_MODELLI ON dbo.MACCHINE.id_modello = dbo.MACCHINE_MODELLI.id_modello INNER JOIN
                  dbo.MACCHINE_TIPOLOGIE ON dbo.MACCHINE_MODELLI.id_tipologia = dbo.MACCHINE_TIPOLOGIE.id_tipologia INNER JOIN
                  dbo.VIEW_CANTIERI ON dbo.MACCHINE.id_cantiere = dbo.VIEW_CANTIERI.id_cantiere INNER JOIN
                  dbo.MACCHINE_PRODUTTORI ON dbo.MACCHINE_MODELLI.id_produttore = dbo.MACCHINE_PRODUTTORI.id_produttore INNER JOIN
                  dbo.CLIENTI ON dbo.VIEW_CANTIERI.id_cliente = dbo.CLIENTI.id_cliente WHERE     (dbo.MACCHINE._del = 'N')

Any suggestion is really appreciated, if开发者_StackOverflow you need more information about the db I will try to provide it...

---

I notice you are using TOP(1), so this must be 2005 or above. You can keep the result of the datediff in an OUTER APPLY subquery so that it is only evaluated once.

SELECT
    M.id_macchina,
    [...],
    V.indirizzo,
    MA.data_fine AS ultima_manutenzione, 
    MA.data_diff, 
    CASE
    WHEN stato = 0 THEN 'GREY' 
    WHEN stato = 2 THEN 'BLACK' 
    WHEN stato = 1 THEN
        CASE
        WHEN MA.data_diff >= 90   THEN 'RED'
        WHEN MA.data_diff >= 80   THEN 'ORANGE' 
        WHEN MA.data_diff >= 60   THEN 'YELLOW'
        WHEN MA.data_fine IS NULL THEN 'RED'
        ELSE 'GREEN' 
        END
    END AS colore
FROM dbo.MACCHINE M
INNER JOIN dbo.MACCHINE_MODELLI I ON M.id_modello = I.id_modello
INNER JOIN dbo.MACCHINE_TIPOLOGIE T ON I.id_tipologia = T.id_tipologia
INNER JOIN dbo.VIEW_CANTIERI V ON M.id_cantiere = V.id_cantiere
INNER JOIN dbo.MACCHINE_PRODUTTORI P ON I.id_produttore = P.id_produttore
INNER JOIN dbo.CLIENTI C ON V.id_cliente = C.id_cliente
OUTER APPLY (SELECT TOP (1)
                MA.data_fine,
                DATEDIFF(day, MA.data_fine, GETDATE()) AS data_diff
            FROM    dbo.MANUTENZIONI AS MA
            WHERE   MA.id_macchina = M.id_macchina
            ORDER BY MA.data_fine DESC) MA
WHERE M._del = 'N'

---

Apologies if you've done this already, but I would suggest using SHOWPLAN (ctrl+L), or performance tools if you have them. Confirm that it's really these CASE statements that are causing the issue - the bottleneck could be one of the joins for instance, a missing index, or stale statistics promoting a bad execution plan.

If the view is read from far more often than it is written to, or it doesn't need to have exactly up to date data (i.e. you could cache the results each day and read the view from last night's refresh), consider using an indexed view. This will cache the view as if it were a table, so the the operations (including both the CASE and the JOINs) are not performed every time you read from the table (having an index also speeds usage). Alternatively, if only recent data changes, you could try partitioning an indexed view on date.