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.

What are the table structures involved? What kind of indexes exist on those tables? — marc_s
– marc_s, Commented Nov 9, 2012 at 7:28
"The query takes about 15 min containing about 4000 rows" - you running that on a 486? — Mitch Wheat
– Mitch Wheat, Commented Nov 9, 2012 at 7:33

Claus_L · Accepted Answer · 2013-03-22 10:52:51Z

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!

Ellie K · Accepted Answer · 2012-11-14 00:10:00Z

1

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.

edited Nov 14, 2012 at 0:10

Ellie K

9061 gold badge19 silver badges37 bronze badges

answered Nov 13, 2012 at 23:24

Merr Leader

7472 gold badges10 silver badges13 bronze badges

Collectives™ on Stack Overflow

Nested select query optimization - slow execution

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related