tony28 (8/26/2014)

---

ChrisM@Work (8/26/2014)

---

tony28 (8/25/2014)

---

I added second sql plan with query with condition before update statistics..

is this so big different between before and after ?

here looks almost ok

Query 2 works very differently from the way it looks. Examination of the execution plan and the code results in this:

SELECT

tB.ORDER_DATE,

tB.COMMIT_NO,

tA.PRT_ID,

tA.PRT_POS,

tA.PRT_TITLE, tA.ITEM_CODE,

isnull(tB.ALC_CODE,'') ALC_CODE,

isnull(tC.PRINT_SCRIP,'') PRINT_SCRIP,

CASE

WHEN tC.PRINT_SCRIP = '' THEN tB.ALC_CODE

WHEN tC.PRINT_SCRIP <> '' THEN tC.PRINT_SCRIP

ELSE tB.ALC_CODE END ALC_RESULT

FROM [dbo].[TB_WORK_ORDERDTL_CPM01] tb

inner JOIN TB_CODE_ALC tC -- INNER JOIN to [TB_CODE_PRTFORM] tA turns this to an INNER JOIN

ON tC.ITEM_CODE = tB.ITEM_CODE

AND tC.ALC_CODE= tB.ALC_CODE

AND tC.CAR_CODE = CASE

WHEN tb.BODY_NO like 'A6%' THEN 'GDe'

WHEN tb.BODY_NO like'IT%' THEN 'EL'

WHEN tb.BODY_NO like 'JC%' THEN 'JC'

ELSE NULL END -- tB.CAR_CODE

inner JOIN [TB_CODE_PRTFORM] ta -- changed to INNER JOIN

ON tC.CAR_CODE= tA.CAR_CODE

AND tC.ITEM_CODE = tA.ITEM_CODE

--and tA.PRT_ID='CPM-PRT-01' REPEATED IN where CLAUSE

WHERE tb.ORDER_DATE = '20140822'

AND tA.CAR_CODE IN ('EL','JC','GDe')

-- AND SUBSTRING(ta.PRT_ID,1,3) = SUBSTRING('CPM01',1,3) -- unnecessary: ta.PRT_ID='CPM-PRT-01'

AND (tA.PRT_POS BETWEEN 1 AND 60) -- turns RIGHT JOIN [TB_CODE_PRTFORM] tA into an INNER JOIN

aND ta.PRT_ID='CPM-PRT-01'

ORDER BY tb.ORDER_DATE, tb.COMMIT_NO, BODY_NO, ta.ITEM_CODE

Test it: if the results are the same as your original query 2, then use it.

Looks good, after write this topics I little modify - remove string operation and etc. I dont use Inner join, but in few things was very better than Left or Right join, so I have to learn to use normally

IO is almost same, and Ex plan looks more better, according to subthree cost, but memory grant is worst.

and time

SQL Server Execution Times:

CPU time = 1595 ms, elapsed time = 1427 ms.

SQL Server Execution Times:

CPU time = 734 ms, elapsed time = 1577 ms.

Not updated stats 20140626

Your

(45389 row(s) affected)

Table 'TB_CODE_ALC'. Scan count 0, logical reads 90778, physical

Table 'TB_CODE_PRTFORM'. Scan count 88673, logical reads 256594,

Table 'TB_WORK_ORDERDTL_CPM01'. Scan count 1, logical reads 750,

My

(45389 row(s) affected)

Table 'TB_CODE_ALC'. Scan count 0, logical reads 90778, physical

Table 'TB_CODE_PRTFORM'. Scan count 88673, logical reads 256594,

Table 'TB_WORK_ORDERDTL_CPM01'. Scan count 1, logical reads 750,

Updated stats 20140622

Your(62740 row(s) affected)

Table 'TB_CODE_PRTFORM'. Scan count 3, logical reads 11,

Table 'TB_CODE_ALC'. Scan count 9, logical reads 568,

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0

Table 'TB_WORK_ORDERDTL_CPM01'. Scan count 9, logical reads 1132,

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0

My

(62740 row(s) affected)

Table 'Worktable'. Scan count 4, logical reads 33, physical reads

Table 'TB_WORK_ORDERDTL_CPM01'. Scan count 1, logical reads 995,

Table 'TB_CODE_PRTFORM'. Scan count 1, logical reads 16, physical

Table 'TB_CODE_ALC'. Scan count 1, logical reads 155, physical rea