DeleteMyAccount February 2016

Build Best index for this Query

I have this query. The query takes too much time to return results. I made almost any possible combination of the WHERE, FUNCS(), as index. The index in this table, or similar tables are 10 times in space than data. If data is 1GB, then indexex are 10GB.

I believe that with proper index, I could return the result instantly. When same field repeats itself, like money7, it means that it is the same field in the search more times.

Looking for your help & suggestions.

SELECT TOP 100 
    tinyint3, money1, money2, money3, money4,
    COUNT(date1), 
    (SUM(money7) - (SUM(smallint1) * moneyConstVal)) / COUNT(date1),
    SUM(smallint1), (SUM(money7) - (SUM(smallint1) * moneyConstVal)) / SUM(smallint1),
    SUM(money7) - (SUM(smallint1) * moneyConstVal)  colResultName
FROM
    SSum1 WITH(NOLOCK)
WHERE 
    Day <= '2008-10-31' AND ProfitProtect <= 100
GROUP BY 
    tinyint3, money1, money2, money3, money4
ORDER BY 
    colResultName DESC, money1, money2 DESC, money3 DESC, money4

Answers


rdn87 February 2016

for you case try this index:

CREATE NONCLUSTERED INDEX IX_SSum1_SEARCH 
ON SSum1  (Day,ProfitProtect)

usually indexes are created with the columns included in the where

Post Status

Asked in February 2016
Viewed 3,108 times
Voted 4
Answered 1 times

Search




Leave an answer