Nested select query optimization - slow execution

Question

I have a query that looks like the following:

SELECT 
    ROUND(SUM(AGLR      * BlokInsideAreaFactor), 2) AS AGLRSum,
    ROUND(SUM(Vaarsaed  * BlokInsideAreaFactor), 2) AS VaarsaedSum,
    ROUND(SUM(Vintsaed  * BlokInsideAreaFactor), 2) AS VintsaedSum,
    ROUND(SUM(Oliefroe  * BlokInsideAreaFactor), 2) AS OliefroeSum,
    ROUND(SUM(Baelgsaed * BlokInsideAreaFactor), 2) AS BaelgsaedSum
    .... (+ 10 more columns)
FROM
(
    SELECT
        AGLR,         
        Vaarsaed,     
        Vintsaed,     
        Oliefroe,     
        Baelgsaed, 
        .... (+ 10 more columns)
        Round((CASE WHEN bloktema.AREAL > 0 THEN 
        omraade.Geom.STIntersection(bloktema.Geom).STArea() / bloktema.AREAL ELSE 0 END), 2) 
            AS BlokInsideAreaFactor
    FROM [CTtoolsData].dbo.BlokAfgroedeGrp blokAfgroed
    INNER JOIN [CTtoolsTema].dbo.bloktema2012 bloktema
        ON (bloktema.bloknr = blokAfgroed.bloknr)
    INNER JOIN [CTtoolsTema].dbo.Area omraade 
        ON omraade.Geom.STIntersects(bloktema.GEOM) = 1
    where   omraade.Id = 296
            AND blokAfgroed.[Year] = 2012
) AS Q1

The reason why I have done a nested select is because I have to calculate the "BlokInsideAreaFactor" before multiplying it to the other column values in the outer select.

My initial thought was that I would optimize the query this way because the "BlokInsideAreaFactor" is only calculated once for each row instead of fifteen times per row (once per column). The thing is that the query gets very very slow doing it like this. The query takes about 15 min containing about 4000 rows. Unfortunately we have ageing hardware and are running the query on SQLServer 2012 Express.

I have looked at indexes and can't seem to optimize further that way. Why does a query looking like this gets so slow and most importantly is there a way to optimize it?

UPDATE:

The tables involved look as follows:

BlokAfgroedeGrp:

• Columns: Id (Primary key, identity), BlokNr, Year, AGLR, Vaarsaed, Vintsaed...etc.
• Indexes: Clustered on Id, Unique Non-Clustered on BlokNr + Year

Bloktema2012:

• Columns: Id (Primary key, identity), BlokNr, Geom (geometry) + others (not important)
• Indexes: Clustered on Id, Spatial on Geom, Non-Unique - Non Clustered on Id + BlokNr, Non-Unique - Non Clustered on BlokNr alone.

Area:

• Columns: Id (Primary key, identity), Geom (geometry) + others (not important)
• Indexes: Clustered on Id, Spatial on Geom

I have made sure that there are no fragmentation on any on the indexes.

Answer 1

I recently came back to this question after learning about temp tables. I've been able to optimize the query to this:

DECLARE @TempTable TABLE (AGLR float,         
    Vaarsaed float,     
    Vintsaed float,     
    Oliefroe float,     
    Baelgsaed float, 
    BlokInsideAreaFactor float)

INSERT INTO @TempTable (AGLR, Vaarsaed, Vintsaed, Oliefroe, Baelgsaed, BlokInsideAreaFactor)

SELECT
    AGLR,         
    Vaarsaed,     
    Vintsaed,     
    Oliefroe,     
    Baelgsaed,
    Round((CASE WHEN bloktema.AREAL > 0 THEN 
    omraade.Geom.STIntersection(bloktema.Geom).STArea() / bloktema.AREAL ELSE 0 END), 2) 
        AS BlokInsideAreaFactor
FROM [CTtoolsData].dbo.BlokAfgroedeGrp blokAfgroed
INNER JOIN [CTtoolsTema].dbo.bloktema2012 bloktema
    ON (bloktema.bloknr = blokAfgroed.bloknr)
INNER JOIN [CTtoolsTema].dbo.Area omraade 
    ON omraade.Geom.STIntersects(bloktema.GEOM) = 1
where   omraade.Id = 296
        AND blokAfgroed.[Year] = 2012


SELECT 
ROUND(SUM(AGLR      * BlokInsideAreaFactor), 2) AS AGLRSum,
ROUND(SUM(Vaarsaed  * BlokInsideAreaFactor), 2) AS VaarsaedSum,
ROUND(SUM(Vintsaed  * BlokInsideAreaFactor), 2) AS VintsaedSum,
ROUND(SUM(Oliefroe  * BlokInsideAreaFactor), 2) AS OliefroeSum,
ROUND(SUM(Baelgsaed * BlokInsideAreaFactor), 2) AS BaelgsaedSum
FROM @TempTable

...so now the query takes about 11 sec, instead of 15 min.

Hope it helps someone else!

Answer 2

Why don't you declare a variable, put the dataset or value you need into the variable, and then reference the variable to do all of the calculations? Then you only need to find that value once.

If you don't want to do that, you could create a CTE (Common Table Expression) table, so you can reference and join to that table instead of doing anything in the where clause.

If you're not using SQL Server then you can look into using temp tables.