Optimize SQL Server Aggregation Query

Question

I'm looking for ideas on how to optimize this query. I've evaluated the Execution Plan but it does not offer any ideas for a missing index so just curious if writing the query better (different tactics) would result in a faster/lighter query.

SELECT [Place], COUNT([Place]) 
FROM (
    SELECT scoresid, REPLACE(REPLACE(EventPlace1,'T', ''),'*','') [Place] 
        FROM [MS.Prod]..mso_scores UNION
    SELECT scoresid, REPLACE(REPLACE(EventPlace2,'T', ''),'*','') 
        FROM [MSO.Prod]..mso_scores UNION
    SELECT scoresid, REPLACE(REPLACE(EventPlace3,'T', ''),'*','') 
        FROM [MSO.Prod]..mso_scores UNION
    SELECT scoresid, REPLACE(REPLACE(EventPlace4,'T', ''),'*','') 
        FROM [MSO.Prod]..mso_scores UNION
    SELECT scoresid, REPLACE(REPLACE(EventPlace5,'T', ''),'*','') 
        FROM [MSO.Prod]..mso_scores UNION
    SELECT scoresid, REPLACE(REPLACE(EventPlace6,'T', ''),'*','') 
        FROM [MSO.Prod]..mso_scores UNION
    SELECT scoresid, REPLACE(REPLACE(AAPlace,'T', ''),'*','') 
        FROM [MSO.Prod]..mso_scores
) data1 
JOIN [MSO.Prod]..mso_scores scores ON scores.scoresid = data1.scoresid
    AND scores.usagnum = '274246' 
    AND scores.TeamResult='N'
WHERE data1.Place IN ('1', '2', '3')
GROUP BY Place

So a quick explanation: there are 6 event place fields. The data in these fields looks like "1", "2", "1T", "3", "5T"; where "T" is a tie. I only care about the number, 1,2,3 so am parsing out the "T" or the "*" from the place and then grouping the query for a count.

How many 1st places do they have, how many 2nd places, and so on..

Answer 1

Try this one (for 2008 and higher) -

SELECT [Place], COUNT(1)
FROM (
    SELECT [Place] = REPLACE(REPLACE(t.[Place], 'T', ''), '*', '')  
    FROM dbo.mso_scores r
    OUTER APPLY (
        VALUES 
            (EventPlace1),
            (EventPlace2),
            (EventPlace3),
            (EventPlace4),
            (EventPlace5),
            (EventPlace6),
            (AAPlace)
    ) t([Place])
    WHERE r.usagnum = '274246' 
        AND r.TeamResult = 'N'
) d
WHERE d.Place IN ('1', '2', '3')
GROUP BY d.Place

For additional information read this topic: Tips for SQL Query Optimization by Analyzing Query Plan