August 25, 2014 at 6:32 pm
I have one small table about 150rows and second bigger about 50000rows
If I join together and I selected just one item from bigger table I have this
(51 row(s) affected)
Table 'TB_CODE_ALC'. Scan count 0, logical reads 102, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_CODE_PRTFORM'. Scan count 101, logical reads 303, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_WORK_ORDERDTL_CPM01'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
If I selected all what I need I have
(53624 row(s) affected)
Table 'TB_CODE_ALC'. Scan count 0, logical reads 107248, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_CODE_PRTFORM'. Scan count 104750, logical reads 302940, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_WORK_ORDERDTL_CPM01'. Scan count 1, logical reads 879, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Why is possible that data from small data are still load to memory ?
Do you have any strategy for these joins ?
I uploaded sql plan
thanks
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( SELECT *,CASE WHEN BODY_NO like 'A6%' THEN 'GDe'
WHEN BODY_NO like'IT%' THEN 'EL'
WHEN BODY_NO like 'JC%' THEN 'JC'
END CAR_CODE FROM [dbo].[TB_WORK_ORDERDTL_CPM01] --with(nolock)
WHERE ORDER_DATE = '20140825'
---AND COMMIT_NO='0222'
) tB
LEFT JOIN TB_CODE_ALC tC
ON tC.ITEM_CODE = tB.ITEM_CODE
AND tC.CAR_CODE = tB.CAR_CODE
AND tC.ALC_CODE= tB.ALC_CODE
RIGHT JOIN [TB_CODE_PRTFORM] tA
ON tC.CAR_CODE= tA.CAR_CODE
and tA.PRT_ID='CPM-PRT-01'
AND tC.ITEM_CODE = tA.ITEM_CODE
WHERE ORDER_DATE IS NOT NULL
AND tA.CAR_CODE IN ('EL','JC','GDe')
AND PRT_ID='CPM-PRT-01'
ORDER BY ORDER_DATE, COMMIT_NO, BODY_NO , ITEM_CODE
August 25, 2014 at 6:42 pm
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
August 25, 2014 at 7:31 pm
interesting is that with data which were updatet statistics I have very good results--about 56000 rows
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_WORK_ORDERDTL_CPM01'. Scan count 1, logical reads 995, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_CODE_ALC'. Scan count 130, logical reads 389, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_CODE_PRTFORM'. Scan count 3, logical reads 11, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
and data without update I have little noreal values --
TB_CODE_ALC has 31234rows update sometimes
TB_WORK_ORDERDTL_ has about 60000rows inserted almost one per minutes
TB_CODE_PRTFORM has 1165 rows , updated sometimes ...
i dont understand how can I get these values below
Table 'TB_CODE_ALC'. Scan count 0, logical reads 111980, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_CODE_PRTFORM'. Scan count 109374, logical reads 316032, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_WORK_ORDERDTL_CPM01'. Scan count 1, logical reads 921, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.:-P
August 25, 2014 at 7:51 pm
so what do you think, is any soulution than update statistics ? because with these high values I cannot use this procedure, this is very heavy for server if somebody will stil exec..
And I can update stats just in saturday because we work 24/5
August 25, 2014 at 10:16 pm
tony28 (8/25/2014)
so what do you think, is any soulution than update statistics ? because with these high values I cannot use this procedure, this is very heavy for server if somebody will stil exec..And I can update stats just in saturday because we work 24/5
Outdated statistics will never help, do you have "Automatically Update Statistics" enabled?
Quick question, can you post the DDL for the tables and some sample data, makes it a lot easier to help;-)
My first thought is to look into the query, looks like some room for improvement exists there.
😎
August 25, 2014 at 11:08 pm
Eirikur Eiriksson (8/25/2014)
tony28 (8/25/2014)
so what do you think, is any soulution than update statistics ? because with these high values I cannot use this procedure, this is very heavy for server if somebody will stil exec..And I can update stats just in saturday because we work 24/5
Outdated statistics will never help, do you have "Automatically Update Statistics" enabled?
Quick question, can you post the DDL for the tables and some sample data, makes it a lot easier to help;-)
My first thought is to look into the query, looks like some room for improvement exists there.
😎
yes I have enabled, but what I know there is some % after will be automatical updated and this is not possible...
And I checked on updated data and it is ok, on notupdated there is problem..
I can post DDL, and next , but this problem is just with lot of data, if I select for example few rows it is ok...
I tried select just one row with from smaller tables and IO logical was 2, so maybe if there is 50000rows , io will be *2 ....
EDIT this table has little more rows, because this 50000rows is from one day and there is about 3months.
August 25, 2014 at 11:50 pm
tony28 (8/25/2014)
Eirikur Eiriksson (8/25/2014)
tony28 (8/25/2014)
so what do you think, is any soulution than update statistics ? because with these high values I cannot use this procedure, this is very heavy for server if somebody will stil exec..And I can update stats just in saturday because we work 24/5
Outdated statistics will never help, do you have "Automatically Update Statistics" enabled?
Quick question, can you post the DDL for the tables and some sample data, makes it a lot easier to help;-)
My first thought is to look into the query, looks like some room for improvement exists there.
😎
yes I have enabled, but what I know there is some % after will be automatical updated and this is not possible...
And I checked on updated data and it is ok, on notupdated there is problem..
I can post DDL, and next , but this problem is just with lot of data, if I select for example few rows it is ok...
I tried select just one row with from smaller tables and IO logical was 2, so maybe if there is 50000rows , io will be *2 ....
Post the DDL and just enough data to test the logic.
😎
August 26, 2014 at 12:43 am
Quick thought, is it possible that the initial code was double pasted? Below is what I would guess it should be.
😎
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
(
SELECT
*
,CASE
WHEN BODY_NO like 'A6%' THEN 'GDe'
WHEN BODY_NO like 'IT%' THEN 'EL'
WHEN BODY_NO like 'JC%' THEN 'JC'
END CAR_CODE
FROM [dbo].[TB_WORK_ORDERDTL_CPM01]
WHERE ORDER_DATE = '20140825'
AND COMMIT_NO = '0222'
) tB
LEFT JOIN TB_CODE_ALC tC
ON tC.ITEM_CODE = tB.ITEM_CODE
AND tC.CAR_CODE = tB.CAR_CODE
AND tC.ALC_CODE = tB.ALC_CODE
RIGHT JOIN [TB_CODE_PRTFORM] tA
ON tC.CAR_CODE = tA.CAR_CODE
and tA.PRT_ID ='CPM-PRT-01'
AND tC.ITEM_CODE = tA.ITEM_CODE
WHERE ORDER_DATE IS NOT NULL
AND tA.CAR_CODE IN ('EL','JC','GDe')
AND SUBSTRING(PRT_ID,1,3) = SUBSTRING('CPM01',1,3)
AND ( tA.PRT_POS >= 1 AND tA.PRT_POS <= 60 )
AND PRT_ID='CPM-PRT-01'
ORDER BY ORDER_DATE, COMMIT_NO, BODY_NO , ITEM_CODE;
August 26, 2014 at 2:00 am
Eirikur Eiriksson (8/26/2014)
Quick thought, is it possible that the initial code was double pasted? Below is what I would guess it should be.😎
SELECTtB.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
(
SELECT
*
,CASE
WHEN BODY_NO like 'A6%' THEN 'GDe'
WHEN BODY_NO like 'IT%' THEN 'EL'
WHEN BODY_NO like 'JC%' THEN 'JC'
END CAR_CODE
FROM [dbo].[TB_WORK_ORDERDTL_CPM01]
WHERE ORDER_DATE = '20140825'
AND COMMIT_NO = '0222'
) tB
LEFT JOIN TB_CODE_ALC tC
ON tC.ITEM_CODE = tB.ITEM_CODE
AND tC.CAR_CODE = tB.CAR_CODE
AND tC.ALC_CODE = tB.ALC_CODE
RIGHT JOIN [TB_CODE_PRTFORM] tA
ON tC.CAR_CODE = tA.CAR_CODE
and tA.PRT_ID ='CPM-PRT-01'
AND tC.ITEM_CODE = tA.ITEM_CODE
WHERE ORDER_DATE IS NOT NULL
AND tA.CAR_CODE IN ('EL','JC','GDe')
AND SUBSTRING(PRT_ID,1,3) = SUBSTRING('CPM01',1,3)
AND ( tA.PRT_POS >= 1 AND tA.PRT_POS <= 60 )
AND PRT_ID='CPM-PRT-01'
ORDER BY ORDER_DATE, COMMIT_NO, BODY_NO , ITEM_CODE;
there was with commit and without commit , i edited..
August 26, 2014 at 2:03 am
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
Don't you mean AFTER you updated statistics? If it were BEFORE, then the plan would be the same as your first post in this thread.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 26, 2014 at 2:21 am
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.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 26, 2014 at 4:29 am
Nice Chris, you took away my two comments, on the RIGHT JOIN getting turned into an INNER JOIN and that awful string manipulation against a column. Those were my two big worries in the query itself.
Other than that, you only need to worry about the automatic statistics maintenance not working if you find that the sampled statistics are radically different than statistics that you create yourself using FULL SCAN. Depending on the data distribution, in most circumstances, the differences will be fairly small. If they are not, you might need to augment the automatic updates with updates of your own. But, remember, when you rebuild an index, you get a FULL SCAN on the statistics then too. That may satisfy in additional maintenance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 26, 2014 at 6:04 am
Grant Fritchey (8/26/2014)
Nice Chris, you took away my two comments, on the RIGHT JOIN getting turned into an INNER JOIN and that awful string manipulation against a column. Those were my two big worries in the query itself.
Thanks Grant. I've used this reference extensively (first ed., not second). It has your name on the cover - one day, I hope it will have your name on the inside cover too 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 26, 2014 at 10:38 am
ChrisM@Work (8/26/2014)
Grant Fritchey (8/26/2014)
Nice Chris, you took away my two comments, on the RIGHT JOIN getting turned into an INNER JOIN and that awful string manipulation against a column. Those were my two big worries in the query itself.Thanks Grant. I've used this reference extensively (first ed., not second). It has your name on the cover - one day, I hope it will have your name on the inside cover too 😉
Oh, get the second edition. The guy who wrote the first one was an idiot. Of course, after I finish the third one, the guy who wrote the second will be an idiot.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 26, 2014 at 3:31 pm
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:
SELECTtB.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
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply