CODEWITHSUNDEEP
sqlperformancesql-server-2008
Mahdi Bagheri
Mahdi Bagheri

Reputation: 23

query Time optimization in sql

I use the following query:

   select 
    MAX(CASE WHEN hrm.vwPayPersCostCenter.ElmntRef = 21 THEN CAST(hrm.vwPayPersCostCenter.val AS INT) END) AS NEWS,
 MAX(CASE WHEN hrm.vwPayPersCostCenter.ElmntRef = 4 THEN CAST(hrm.vwPayPersCostCenter.val AS INT) END) AS NEWS2,
 MAX(CASE WHEN hrm.vwPayPersCostCenter.ElmntRef = 120 THEN CAST(hrm.vwPayPersCostCenter.val AS INT) END) AS NEWS3
    FROM hrm.vwPayPersCostCenter

But its execution time is very long(3 Minutes)

Upvotes: 0

Views: 52

Answers (2)

Igor
Igor

Reputation: 62213

2 Things to improve the performance.

  1. Use a WHERE clause for filtering
  2. Use a group by on ElmntRef and then take the MAX hrm.vwPayPersCostCenter.val. Really it would be better if you did the casting to int outside the max but I am not sure what type of data this is.

Outside of the query make sure you have an index on ElmntRef that INCLUDEs column hrm.vwPayPersCostCenter.val. Because you are casting the type it probably does not matter if it is an actual part of the index or just an included column but that again depends on the current data type of that column.

CREATE NONCLUSTERED INDEX [IX_PayPersCostCenter_ElmntRef] 
  ON hrm.vwPayPersCostCenter(ElmntRef ASC) INCLUDE(val)

This will produce 3 rows instead of 3 additional columns, basically pivoting the results if you compare it to your current output.

SELECT hrm.vwPayPersCostCenter.ElmntRef, 
    MAX(CAST(hrm.vwPayPersCostCenter.val AS INT)) AS News
FROM hrm.vwPayPersCostCenter
WHERE hrm.vwPayPersCostCenter.ElmntRef IN (21, 4, 120)
GROUP BY hrm.vwPayPersCostCenter.ElmntRef

Upvotes: 2

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

Put the condition in the where clause to speed up the query:

select MAX(case when ElmntRef = 21 then CAST(val as int) end) as NEWS,
    MAX(case when ElmntRef = 4 then CAST(val as int) end) as NEWS2,
    MAX(case when ElmntRef = 120 then CAST(val as int) end) as NEWS3
from hrm.vwPayPersCostCenter
where ElmntRef in (4, 21, 120);

The where can speed up the row finding process if there is an index to use and the aggregation is performed only on the rows with ElmntRef 4, 21, 120 instead of all the rows as was happening in the original case.

The query would perform better if you had index on ElmntRef and val

create index idx_PayPersCostCenter on hrm.vwPayPersCostCenter(ElmntRef, val);

Upvotes: 2

Related Questions