Help needed in join

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  May 12, 2014 at 7:57 am

  #1712681

  This might not be the best option for performance (as I haven't done real performance testing) but it's quite simple.

  SELECT c.IdCompany,

  c.CompanyName,

  ISNULL( p.CompanyCode, c.CompanyCode) CompanyCode

  FROM #Company c

  LEFT JOIN dbo.CompanyGroup cg ON c.IdCompany = cg.IdchildCompany

  LEFT JOIN dbo.Company p ON cg.IdParentCompany = p.IdCompany;

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 12, 2014 at 9:00 am

  #1712719

  Luis Cazares (5/12/2014)

  ---

  This might not be the best option for performance (as I haven't done real performance testing) but it's quite simple.

  SELECT c.IdCompany,

  c.CompanyName,

  ISNULL( p.CompanyCode, c.CompanyCode) CompanyCode

  FROM #Company c

  LEFT JOIN dbo.CompanyGroup cg ON c.IdCompany = cg.IdchildCompany

  LEFT JOIN dbo.Company p ON cg.IdParentCompany = p.IdCompany;

  Added your code and tested.

  print '********** Simple Outer Apply **********';

  print '';

  go

  set statistics io,time on;

  select

  c.IdCompany,

  c.CompanyName,

  isnull(oa.CompanyCode, c.CompanyCode) CompanyCode

  from

  dbo.Company c

  outer apply (select

  c1.CompanyCode

  from

  dbo.Company c1

  inner join dbo.CompanyGroup cg

  on c1.IdCompany = cg.IdParentCompany

  where

  c.IdCompany = cg.IdchildCompany)oa(CompanyCode)

  order by

  c.IdCompany;

  go

  set statistics io,time off;

  go

  print '';

  print '********** First Window Function **********';

  print '';

  go

  set statistics io,time on;

  SELECT

  Y.IdCompany

  ,Y.CompanyName

  ,Y.CompanyCode

  FROM

  (

  SELECT

  X.IdCompany

  ,X.CompanyName

  ,X.CompanyCode

  FROM

  (

  SELECT

  ROW_NUMBER() OVER

  (

  PARTITION BY CP.IdCompany

  ORDER BY (SELECT NULL)

  ) AS CID_RID

  ,CP.CompanyName

  ,CP.CompanyCode

  ,CP.IdCompany

  FROM dbo.Company CP

  /* Removed the join, simpler execution plan */

  WHERE CP.IdCompany IN (SELECT IdParentCompany FROM dbo.CompanyGroup)

  ) AS X WHERE X.CID_RID = 1

  UNION ALL

  SELECT

  CC.IdCompany

  ,CC.CompanyName

  ,CP.CompanyCode

  FROM dbo.Company CP

  INNER JOIN dbo.CompanyGroup CG

  ON CP.IdCompany = CG.IdParentCompany

  INNER JOIN dbo.Company CC

  ON CG.IdchildCompany = CC.IdCompany

  ) AS Y ORDER BY Y.CompanyCode,Y.IdCompany

  go

  set statistics io,time off;

  go

  print '';

  print '********** Second Window Function 2012/2014 option **********';

  print '';

  go

  set statistics io,time on;

  SELECT

  IdCompany

  ,CompanyName

  ,CompanyCode

  FROM

  (

  SELECT

  ROW_NUMBER() OVER

  (

  PARTITION BY CP.IdCompany

  ORDER BY (SELECT NULL)

  ) AS COMP_RID

  ,CP.IdCompany

  ,CP.CompanyName

  ,FIRST_VALUE(CP.CompanyCode) OVER

  (

  PARTITION BY CG.IdParentCompany

  ORDER BY (SELECT NULL)

  ) AS CompanyCode

  FROM dbo.CompanyGroup CG

  OUTER APPLY dbo.Company CP

  WHERE (CG.IdParentCompany = CP.IdCompany AND CG.IdchildCompany <> CP.IdCompany)

  OR (CG.IdParentCompany <> CP.IdCompany AND CG.IdchildCompany = CP.IdCompany)

  ) AS X WHERE X.COMP_RID = 1

  go

  set statistics io,time off;

  go

  print '';

  print '********** Multiple Join *********'

  print '';

  go

  set statistics io,time on;

  go

  SELECT c.IdCompany,

  c.CompanyName,

  ISNULL( p.CompanyCode, c.CompanyCode) CompanyCode

  FROM dbo.Company c

  LEFT JOIN dbo.CompanyGroup cg ON c.IdCompany = cg.IdchildCompany

  LEFT JOIN dbo.Company p ON cg.IdParentCompany = p.IdCompany;

  go

  set statistics io,time off;

  go

  Statistics:

  ********** Simple Outer Apply **********

  (9 row(s) affected)

  Table 'Company'. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  Table 'CompanyGroup'. Scan count 0, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 46 ms.

  SQL Server parse and compile time:

  CPU time = 0 ms, elapsed time = 0 ms.

  ********** First Window Function **********

  (9 row(s) affected)

  Table 'Company'. Scan count 1, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  Table 'CompanyGroup'. Scan count 2, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:

  CPU time = 16 ms, elapsed time = 47 ms.

  SQL Server parse and compile time:

  CPU time = 0 ms, elapsed time = 0 ms.

  ********** Second Window Function 2012/2014 option **********

  (9 row(s) affected)

  Table 'Worktable'. Scan count 6, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  Table 'CompanyGroup'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  Table 'Company'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 26 ms.

  SQL Server parse and compile time:

  CPU time = 0 ms, elapsed time = 0 ms.

  ********** Multiple Join *********

  SQL Server parse and compile time:

  CPU time = 0 ms, elapsed time = 0 ms.

  (9 row(s) affected)

  Table 'Company'. Scan count 2, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  Table 'CompanyGroup'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 38 ms.

  SQL Server parse and compile time:

  CPU time = 0 ms, elapsed time = 0 ms.

  Still need a million row test.

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  May 12, 2014 at 9:12 am

  #1712721

  Here is a simpler window function (2012/2014) version. Although simpler than the earlier 12/14 one, it performs similar, mainly because of an sort operator which is around 60% of the cost. An POC Index could and should eliminate the sort.

  😎

  SELECT

  CP.IdCompany

  ,CP.CompanyName

  ,FIRST_VALUE(CP.CompanyCode) OVER

  (

  PARTITION BY ISNULL(CG.IdParentCompany,CP.IdCompany)

  ORDER BY (SELECT NULL)

  ) AS CompanyCode

  FROM dbo.Company CP

  LEFT OUTER JOIN dbo.CompanyGroup CG

  ON CP.IdCompany = CG.IdchildCompany

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 12, 2014 at 9:51 am

  #1712741

  Eirikur Eiriksson (5/12/2014)

  ---

  Here is a simpler window function (2012/2014) version. Although simpler than the earlier 12/14 one, it performs similar, mainly because of an sort operator which is around 60% of the cost. An POC Index could and should eliminate the sort.

  😎

  SELECT

  CP.IdCompany

  ,CP.CompanyName

  ,FIRST_VALUE(CP.CompanyCode) OVER

  (

  PARTITION BY ISNULL(CG.IdParentCompany,CP.IdCompany)

  ORDER BY (SELECT NULL)

  ) AS CompanyCode

  FROM dbo.Company CP

  LEFT OUTER JOIN dbo.CompanyGroup CG

  ON CP.IdCompany = CG.IdchildCompany

  I have no idea what you mean by a POC Index, please explain. Also, please note the changes I did make on the sample data.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 12, 2014 at 9:56 am

  #1712747

  Not reposting all the code again, but here is the Statistics output after adding the latest code to the group:

  ********** Simple Outer Apply **********

  (9 row(s) affected)

  Table 'Company'. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  Table 'CompanyGroup'. Scan count 0, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 32 ms.

  SQL Server parse and compile time:

  CPU time = 0 ms, elapsed time = 0 ms.

  ********** First Window Function **********

  (9 row(s) affected)

  Table 'Company'. Scan count 1, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  Table 'CompanyGroup'. Scan count 2, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 38 ms.

  SQL Server parse and compile time:

  CPU time = 0 ms, elapsed time = 0 ms.

  ********** Second Window Function 2012/2014 option **********

  (9 row(s) affected)

  Table 'Worktable'. Scan count 6, logical reads 43, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  Table 'CompanyGroup'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  Table 'Company'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 26 ms.

  SQL Server parse and compile time:

  CPU time = 0 ms, elapsed time = 0 ms.

  ********** Multiple Join *********

  SQL Server parse and compile time:

  CPU time = 0 ms, elapsed time = 0 ms.

  (9 row(s) affected)

  Table 'Company'. Scan count 2, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  Table 'CompanyGroup'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 25 ms.

  SQL Server parse and compile time:

  CPU time = 0 ms, elapsed time = 0 ms.

  ********** Simpler Window Function query *********

  SQL Server parse and compile time:

  CPU time = 0 ms, elapsed time = 0 ms.

  (9 row(s) affected)

  Table 'Worktable'. Scan count 6, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  Table 'CompanyGroup'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  Table 'Company'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 28 ms.

  SQL Server parse and compile time:

  CPU time = 0 ms, elapsed time = 0 ms.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 12, 2014 at 10:02 am

  #1712753

  Here is the latest execution plans and spreadsheet with the information from SQL Sentry Plan Explorer results tab when using it to generate the actual execution plans.

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  May 12, 2014 at 10:55 am

  #1712784

  Lynn Pettis (5/12/2014)

  ---

  I have no idea what you mean by a POC Index, please explain. Also, please note the changes I did make on the sample data.

  The POC index or Partitioning, Ordering and Coverage is somewhat vital for the performance of the window functions. Itzik Ben-Gan has a very good 3 part peace on sqlmag.com on this and other aspects of using the window functions. If the POC index is missing, the window functions often perform rather poorly as the costly sort operator has to be introduced to the execution plan. In fact, more than 60% of the cost of the window function code we have been juggling today is simply due to the sort. It is clear that the window function can be hugely beneficial in some cases but sometimes not.

  😎

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  May 12, 2014 at 11:19 am

  #1712800

  Eirikur Eiriksson (5/12/2014)

  ---

  Lynn Pettis (5/12/2014)

  ---

  I have no idea what you mean by a POC Index, please explain. Also, please note the changes I did make on the sample data.

  The POC index or Partitioning, Ordering and Coverage is somewhat vital for the performance of the window functions. Itzik Ben-Gan has a very good 3 part peace on sqlmag.com on this and other aspects of using the window functions. If the POC index is missing, the window functions often perform rather poorly as the costly sort operator has to be introduced to the execution plan. In fact, more than 60% of the cost of the window function code we have been juggling today is simply due to the sort. It is clear that the window function can be hugely beneficial in some cases but sometimes not.

  😎

  Then why not post the DDL for the necessary indexes on the tables?

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  May 13, 2014 at 11:32 am

  #1713183

  Hi Lyn, it is super fast. i have tested on my. but have another query about on your logic, if need to bring the another column of child.

  Structure:

  Create Table dbo.Company(

  IdCompany int primary key,

  CompanyName varchar(50),

  CompanyCode varchar(50),

  CompanyEId varchar(30));

  Insert into Company

  values

  (1000,'IBM','CP100','E1000'),

  (1001,'Informix Corporation','CP101','E1001'),

  (1002,'Rational Software Corporation','CP102','E1002'),

  (1003,'Oracle','CP103','E1003'),

  (1004,'DataRaker','CP104','E1004'),

  (1005,'Ksplice','CP105','E1005'),

  (1006,'Microsoft','CP106','E1006'),

  (1007,'Hotmail','CP107','E1007'),

  (1008,'Flash Communications','CP108','E1008');

  Create table CompanyGroup(

  IdCompanyGroup int identity(1,1) primary key,

  IdParentCompany int,IdchildCompany int,

  FOREIGN KEY (IdParentCompany) REFERENCES Company(IdCompany),

  FOREIGN KEY (IdchildCompany) REFERENCES Company(IdCompany)

  );

  Insert into dbo.CompanyGroup

  values

  (1000,1001),

  (1000,1002),

  (1003,1004),

  (1003,1005),

  (1006,1007),

  (1006,1008);

  ExectedResult:

  IdCompanyCompanyNameCompanyCodeCompanyEid

  1000IBMCP100E1000

  1001Informix CorporationCP100E1001

  1002Rational Software CorporationCP100E1002

  1003OracleCP103E1003

  1004DataRakerCP103E1004

  1005KspliceCP103E1005

  1006MicrosoftCP106E1006

  1007HotmailCP106E1007

  1008Flash CommunicationsCP108E1008

  Any suggestions please

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  May 13, 2014 at 11:45 am

  #1713185

  If am not wrong this will be the query

  select

  c.IdCompany,

  c.CompanyName,

  case when oa.CompanyCode is null then c.CompanyCode else oa.CompanyCode end CompanyCode,

  c.CompanyEId

  from

  dbo.Company c

  outer apply (select

  c1.CompanyCode

  from

  dbo.Company c1

  inner join dbo.CompanyGroup cg

  on c1.IdCompany = cg.IdParentCompany

  where

  c.IdCompany = cg.IdchildCompany)oa(CompanyCode);

  Please correct me if am wrong.

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  May 13, 2014 at 11:47 am

  #1713186

  I tested. am correct. Sorry to bother.

  In terms of speed it is excellent. Thanks for the help. Outer apply logic is excellent and very fast