CODEWITHSUNDEEP
databasejoin
naeemgik
naeemgik

Reputation: 2272

Order By Clause take too much time in SQL

SELECT     dteRun,                 
CASE WHEN coalesce(nPriorityCode,0) <= 0 THEN 3
               ELSE nPriorityCode
               END AS nPriorityCode,
               CASE WHEN sCommand IN ('DiaryWF','XC_Reminder') THEN '*'
               ELSE ''
               END as Alert,
               sParentRef,
               nWorkflowTypeCode,
               sSubjectName,
               sDescription,
               sUniqueRef,
               sUserInfo,
               sUserInfo2   
FROM    AuroraTasksDiaryView ad
    INNER JOIN   UserAuthority
         ON UserAuthority.UserName = ad.sOwningUser
         AND ad.sOwningUser  = 'ammonsd'  AND  ad. nErrorCode = -1
         AND ad.sExcludedUser <>   ad.sOwningUser
         AND UserAuthority.FunctionCode = ad.sFunctionCode
        AND ( (UserAuthority.LowerBound <=  ad.nTaskValue
        AND UserAuthority.UpperBound >=  ad.nTaskValue)
        OR ad.sFunctionCode = 'RTS')
        AND  RowNum <= 100
        ORDER BY dteRun

When I omit "Order By dteRun" query runs in milliseconds however with Order By Clause it take more than minute. Whats problem with Order By Clause ?

Upvotes: 1

Views: 1032

Answers (2)

Jahan Zinedine
Jahan Zinedine

Reputation: 14874

It needs an Index on columns in your Order By clause preferably in the same order.

Upvotes: 1

Ernest Friedman-Hill
Ernest Friedman-Hill

Reputation: 81684

There's presumably no index on the dteRun column. If you want to do fast ORDER BY on a column, it needs an index.

Upvotes: 4

Related Questions