Optimize help for sql query

Question

We've got some SQL code I'm trying to optimize. In the code is a view that is rather expensive to run. For the sake of this question, let's call it ExpensiveView. On top of the view there is a query that joins the view to itself via a two sub-queries.

For example:

select v1.varCharCol1, v1.intCol, v2.intCol from (
    select someId, varCharCol1, intCol from ExpensiveView where rank=1
) as v1 inner join (
    select someId, intCol from ExpensiveView where rank=2
) as v2 on v1.someId = v2.someId

An example result set:

some random string, 5, 10
other random string, 15, 15

This works, but it's slow since I'm having to select from ExpensiveView twice. What I'd like to do is use a case statement to only select from ExpensiveView once.

For example:

select someId,
    case when rank = 1 then intCol else 0 end as rank1IntCol,
    case when rank = 2 then intCol else 0 end as rank2IntCol
from ExpensiveView where rank in (1,2)

I could then group the above results by someId and get almost the same thing as the first query:

select sum(rank1IntCol), sum(rank2Intcol) 
from ( *the above query* ) SubQueryData 
group by someId

The problem is the varCharCol1 that I need to get when the rank is 1. I can't use it in the group since that column will contain different values when rank is 1 than it does when rank is 2.

Does anyone have any solutions to optimize the query so it only selects from ExpensiveView once and still is able to get the varchar data?

Thanks in advance.

Answer 1

It's hard to guess since we don't see your view definition, but try this:

SELECT  MIN(CASE rank WHEN 1 THEN v1.varCharCol1 ELSE NULL END),
        SUM(CASE rank WHEN 1 THEN rank1IntCol ELSE 0 END),
        SUM(CASE rank WHEN 2 THEN rank2IntCol ELSE 0 END)
FROM    query
GROUP BY
        someId

Note that in most cases for the queries like this:

SELECT  *
FROM    mytable1 m1 
JOIN    mytable1 m2
ON      …

the SQL Server optimizer will just build an Eager Spool (a temporary index), which will later be used for searching for the JOIN condition, so probably these tricks are redundant.

Answer 2

select someId,
    case when rank = 1 then varCharCol1 else '_' as varCharCol1 
    case when rank = 1 then intCol else 0 end as rank1IntCol,
    case when rank = 2 then intCol else 0 end as rank2IntCol
from ExpensiveView where rank in (1,2)

then use min() or max in the enclosing query