Optimizing calculation query In a legacy project I've the following query:

SELECT 
    concat(SR_PO, '-', SR_RlsSequence) as Order,
    count(*) as Todo,
    sum(isnull(SR_Enabled,0)) as ToLoad,
    sum(isnull(SR_Done,0)) as Loaded,
    sum(isnull(SR_Enabled,0))-sum(isnull(SR_Done,0))-sum(isnull(Rejected,0)) as Missing,
    count(EndTime3) as Quot,
    sum(isnull(Rejected,0)) as Scrapped
FROM [Edgebanding]
WHERE concat(SR_PO, '-', SR_RlsSequence) IN ('100063-100', '100063-101', '100063-103', '100063-104')
GROUP BY by concat(SR_PO, '-', SR_RlsSequence)


But its too expensive, especially when the IN parameters are lots. Any ideas concerning an optimization? please consider that I cannot use a stored procedure.

Estimated Query plan is uploaded here.
You could try applying the filter 

(WHERE concat(SR_PO, '-', SR_RlsSequence) IN ('100063-100', '100063-101', '100063-103', '100063-104'))

Directly on the index instead of it being implemented as a filter further in the execution plan.


You can do this by not applying functions on columns in your where clause and adding an index. The group by is also changed to work without the function. 

Query adaptation:

SELECT 
    concat(SR_PO, '-', SR_RlsSequence) as [Order],
    count(*) as Todo,
    sum(isnull(SR_Enabled,0)) as ToLoad,
    sum(isnull(SR_Done,0)) as Loaded,
    sum(isnull(SR_Enabled,0))-sum(isnull(SR_Done,0))-sum(isnull(Rejected,0)) as Missing,
    count(EndTime3) as Quot,
    sum(isnull(Rejected,0)) as Scrapped
FROM [Edgebanding]
WHERE SR_PO = '100063' AND SR_RlsSequence IN ('100','101','103','104')
GROUP BY SR_PO, SR_RlsSequence;


And index:

CREATE INDEX IX_SR_PO_SR_RlsSequence
ON [dbo].[Edgebanding](SR_PO,SR_RlsSequence)
INCLUDE(SR_Enabled,SR_Done,Rejected,EndTime3);