Pfthb

Here the problem:

• First I have a nested query, for which I need to improve the execution time and make it faster. Imagine 47 seconds at first time and second time was 46 / 45 second. Second was running by cache (the second execution always faster than first time but not this).




• Second that query uses SUM, CAST, CASE in each sub select. Can't dodge that




• Third I read some another solution from internet using OPTION (FAST 500).

Here the example :

SELECT [cutomerSection],
 SUM(DEBIT) AS DEBIT,
 SUM(CREDIT) AS CREDIT,
 SUM([OTHERS]) AS 'OTHERS'
FROM
 (-- SECTOR A
 SELECT cb.paymentGrp AS 'cutomerSection',
 CASE
 WHEN j.paymentStat = 'DEBIT' THEN SUM(CAST(ISNULL(isnull(qty, 1)*(100-disc)*d.adm/100, 0) AS INT)) + SUM(CAST(ISNULL(isnull(qty, 1)*(100-disc)*(d.fee-d.adm)/100, 0) AS INT))
 ELSE 0
 END AS 'DEBIT',
 CASE
 WHEN j.paymentStat = 'CREDIT' THEN SUM(CAST(ISNULL(isnull(qty, 1)*(100-disc)*d.adm/100, 0) AS INT)) + SUM(CAST(ISNULL(isnull(qty, 1)*(100-disc)*(d.fee-d.adm)/100, 0) AS INT))
 ELSE 0
 END AS 'CREDIT',
 CASE
 WHEN (j.paymentStat = ''
 OR j.paymentStat IS NULL) THEN SUM(CAST(ISNULL(isnull(qty, 1)*(100-disc)*d.adm/100, 0) AS INT)) + SUM(CAST(ISNULL(isnull(qty, 1)*(100-disc)*(d.fee-d.adm)/100, 0) AS INT))
 ELSE 0
 END AS 'OTHERS'
 FROM tbactDetail d WITH(NOLOCK)
 LEFT JOIN tbsectorSection j WITH(NOLOCK) ON d.tranCode = j.tranCode
 LEFT JOIN tbact t ON (d.actCode=t.actCode
 AND t.sectorCode = j.sectorCode)
 LEFT JOIN tbCustomer c WITH(NOLOCK) ON c.custCode=j.custCode
 LEFT JOIN tbfrontDesk a WITH(NOLOCK) ON a.struckNo=j.struk
 LEFT JOIN tbpaymentList cb WITH(NOLOCK) ON cb.paymentK = a.paymentK
 LEFT JOIN tbleading dc ON dc.leadCode = j.leadCode
 LEFT JOIN tbsector p ON j.sectorCode = p.sectorCode
 WHERE (j.deleted IS NULL
 OR j.deleted = 0)
 AND a.status = 'FINISH'
 AND (j.tranDate BETWEEN '2018-11-01 00:00:00' AND '2018-11-01 23:59:59')
 GROUP BY cb.paymentGrp,
 j.paymentStat
 UNION ALL -- SECTOR PARK
SELECT cb.paymentGrp AS 'cutomerSection',
 CASE
 WHEN t.paymentStat = 'DEBIT' THEN SUM(CAST(isnull(d.rate*d.qty*(100-disc)/100, 0) AS INT))
 ELSE 0
 END AS 'DEBIT',
 CASE
 WHEN t.paymentStat = 'CREDIT' THEN SUM(CAST(isnull(d.rate*d.qty*(100-disc)/100, 0) AS INT))
 ELSE 0
 END AS 'CREDIT',
 CASE
 WHEN (t.paymentStat = ''
 OR t.paymentStat IS NULL) THEN SUM(CAST(isnull(d.rate*d.qty*(100-disc)/100, 0) AS INT))
 ELSE 0
 END AS 'OTHERS'
 FROM tbparkDetail d
 LEFT JOIN tbTranPark t ON d.tranCode = t.tranCode
 JOIN tbsectorSection j ON t.strukPoli = j.struk
 LEFT JOIN tbCustomer c WITH(NOLOCK) ON c.custCode=j.custCode
 LEFT JOIN tbfrontDesk a WITH(NOLOCK) ON a.struckNo=j.struk
 JOIN tbpaymentList cb ON a.paymentK = cb.paymentK
 LEFT JOIN tbleading dc ON dc.leadCode=j.leadCode
 LEFT JOIN tbsector p ON j.sectorCode=p.sectorCode
 WHERE (j.deleted IS NULL
 OR j.deleted=0)
 AND (t.strukPoli IS NOT NULL
 OR t.strukPoli <> ''
 OR t.strukPoli <> '--')
 AND a.status = 'FINISH'
 AND (j.tranDate BETWEEN '2018-11-01 00:00:00' AND '2018-11-01 23:59:59')
 GROUP BY cb.paymentGrp,
 t.paymentStat
 UNION ALL -- SECTOR FRONT
SELECT cb.paymentGrp AS 'cutomerSection',
 CASE
 WHEN t.paymentStat = 'DEBIT' THEN SUM(CAST(isnull(d.rate*(100-disc)/100, 0) AS INT))
 ELSE 0
 END AS 'DEBIT',
 CASE
 WHEN t.paymentStat = 'CREDIT' THEN SUM(CAST(isnull(d.rate*(100-disc)/100, 0) AS INT))
 ELSE 0
 END AS 'CREDIT',
 CASE
 WHEN (t.paymentStat = ''
 OR t.paymentStat IS NULL) THEN SUM(CAST(isnull(d.rate*(100-disc)/100, 0) AS INT))
 ELSE 0
 END AS 'OTHERS'
 FROM tbDetailRadioterapi d
 LEFT JOIN tbtranFront t ON d.tranCode = t.tranCode
 LEFT JOIN tbsectorSection j ON t.strukPoli = j.struk
 LEFT JOIN tbCustomer c WITH(NOLOCK) ON c.custCode=j.custCode
 LEFT JOIN tbfrontDesk a WITH(NOLOCK) ON a.struckNo = j.struk
 LEFT JOIN tbpaymentList cb ON a.paymentK = cb.paymentK
 LEFT JOIN tbleading dc ON dc.leadCode=j.leadCode
 LEFT JOIN tbsector p ON j.sectorCode=p.sectorCode
 WHERE (t.deleted IS NULL
 OR t.deleted=0)
 AND (strukPoli IS NOT NULL
 OR strukPoli <>''
 OR strukPoli <>'--')
 AND a.status='FINISH'
 AND (j.tranDate BETWEEN '2018-11-01 00:00:00' AND '2018-11-01 23:59:59')
 GROUP BY cb.paymentGrp,
 t.paymentStat) AS a
GROUP BY [cutomerSection]

Third using option (fast 500), I don't know why but always got error :

Incorect Syntax near OPTION

PS : Sorry for my bad English