Strange query performance

[nymble]

OK it's strange to me but maybe that's my lack of understanding. With the
query below when I SELECT GroupID, BSV.ValueAmount without any grouping I get
back 25 rows instantly
WHEN I GROUP ON the GroupID field AND SUM the ValueAmount as ishown below it
then takes 6 seconds.

I do not understand how if SQL can find all 25 rows instantly how can it
take a further 6 seconds to group the 25 rows into the 2 GroupID values with
the sum values.

Obviously I can use a temp table stage to get the answer I am looking for
instantly too but I don’t understand why SQL performance for this query is so
bad?

Any comments apreciated, Cheers

SELECT
GroupID
, SUM(BSV.ValueAmount) AS ValueAmount
FROM
All_OnBalanceSheet BS
JOIN All_OnBalanceSheetValues BSV
ON (BS.id = BSV.OnBsID)
LEFT JOIN All_OnBalanceSheetMapping BSM
ON ((BS.Category1 = BSM.Category1) OR (BSM.Category1 IS NULL))
AND ((BS.Category2 = BSM.Category2) OR (BSM.Category2 IS NULL))
AND ((BS.Category3 = BSM.Category3) OR (BSM.Category3 IS NULL))
AND ((BS.Category4 = BSM.Category4) OR (BSM.Category4 IS NULL))
AND (BSV.Valuetype = BSM.Valuetype)
AND GroupID IS NOT NULL
WHERE
BSM.Suppress = 0
AND BS.DataDate = '2010-08-23'
AND GroupID IS NOT NULL
GROUP BY
GroupID

[Erland Sommarskog]

nymble (nym...@discussions.microsoft.com) writes:
> OK it's strange to me but maybe that's my lack of understanding. With
> the query below when I SELECT GroupID, BSV.ValueAmount without any
> grouping I get back 25 rows instantly WHEN I GROUP ON the GroupID field
> AND SUM the ValueAmount as ishown below it then takes 6 seconds.
>
> I do not understand how if SQL can find all 25 rows instantly how can it
> take a further 6 seconds to group the 25 rows into the 2 GroupID values
> with the sum values.

SQL Server, like all other modern RDBMS:s, has a cost-based optimizer,
which from statistics sampled about the data, makes estimate which is
the best way to evaluate the query.

In your case, SQL Server obviously finds two different ways to this, and
you can verify this if you look at the query plan. (Enabled this through the
Query menu in SSMS.)

> LEFT JOIN All_OnBalanceSheetMapping BSM
> ON ((BS.Category1 = BSM.Category1) OR (BSM.Category1 IS NULL))
> AND ((BS.Category2 = BSM.Category2) OR (BSM.Category2 IS NULL))
> AND ((BS.Category3 = BSM.Category3) OR (BSM.Category3 IS NULL))
> AND ((BS.Category4 = BSM.Category4) OR (BSM.Category4 IS NULL))
> AND (BSV.Valuetype = BSM.Valuetype)
> AND GroupID IS NOT NULL

I don't know the data model or the business rules but these "OR Category
IS NULL" looks funny. Are you sure that they are correct?

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Gert-Jan Strik]

I agree with Erlands comments. The optimizer does not come up with the
optimal query plan for your grouped query, but your query is quite
unusual with the Left Outer Join criteria.

Maybe you need a full outer join instead of a left outer join, and maybe
the optimizer is not smart enough to figure this out itself.

Also, some general question apply here:
- Have you installed the latest service packs?
- What version and edition of SQL Server are you running?

--
Gert-Jan