SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


JOIN BIG and SMALL TABLEs


JOIN BIG and SMALL TABLEs

Author
Message
tony28
tony28
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 889
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







How to post data/code on a forum to get the best help: Option 1 / Option 2
Attachments
BigAndSmall.sqlplan (7 views, 189.00 KB)
tony28
tony28
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 889
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



How to post data/code on a forum to get the best help: Option 1 / Option 2
Attachments
BigAndSmallBeforeUpdate.sqlplan (6 views, 225.00 KB)
tony28
tony28
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 889
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



How to post data/code on a forum to get the best help: Option 1 / Option 2
tony28
tony28
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 889
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



How to post data/code on a forum to get the best help: Option 1 / Option 2
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15988 Visits: 18627
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.
Cool
tony28
tony28
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 889
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.
Cool


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.



How to post data/code on a forum to get the best help: Option 1 / Option 2
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15988 Visits: 18627
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.
Cool


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.
Cool
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15988 Visits: 18627
Quick thought, is it possible that the initial code was double pasted? Below is what I would guess it should be.
Cool

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;


tony28
tony28
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 889
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.
Cool

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;


there was with commit and without commit , i edited..



How to post data/code on a forum to get the best help: Option 1 / Option 2
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17118 Visits: 19557
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.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search