Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

JOIN BIG and SMALL TABLEs Expand / Collapse
Author
Message
Posted Monday, August 25, 2014 6:32 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:49 PM
Points: 188, Visits: 564
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





  Post Attachments 
BigAndSmall.sqlplan (3 views, 189.92 KB)
Post #1607302
Posted Monday, August 25, 2014 6:42 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:49 PM
Points: 188, Visits: 564
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


  Post Attachments 
BigAndSmallBeforeUpdate.sqlplan (2 views, 225.23 KB)
Post #1607304
Posted Monday, August 25, 2014 7:31 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:49 PM
Points: 188, Visits: 564
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.
Post #1607308
Posted Monday, August 25, 2014 7:51 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:49 PM
Points: 188, Visits: 564
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
Post #1607314
Posted Monday, August 25, 2014 10:16 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:12 PM
Points: 2,533, Visits: 7,105
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.
Post #1607325
Posted Monday, August 25, 2014 11:08 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:49 PM
Points: 188, Visits: 564
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.
Post #1607334
Posted Monday, August 25, 2014 11:50 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:12 PM
Points: 2,533, Visits: 7,105
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.
Post #1607339
Posted Tuesday, August 26, 2014 12:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:12 PM
Points: 2,533, Visits: 7,105
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;

Post #1607345
Posted Tuesday, August 26, 2014 2:00 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:49 PM
Points: 188, Visits: 564
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.


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..
Post #1607365
Posted Tuesday, August 26, 2014 2:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:43 AM
Points: 6,890, Visits: 14,254
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
Post #1607366
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse