T-SQL Help on same table join

• vineet_dubey1975

  SSCrazy

  Points: 2306

  More actions

  October 15, 2017 at 1:47 pm

  #339896

  Hi  SQL View  

  SELECT  a.Name , Q0.Value, Q.1.Value,Q2.Value
  FROM A with (nolock) 
  Left Outer join B AS Q0 ON Q0.FK_ID = A.ID   AND Q0.Type =1
  Left Outer join B AS Q1 ON Q1.FK_ID = A.ID    AND Q1.Type =2
  Left Outer join B AS Q2 ON Q2.FK_ID = A.ID    AND Q2.Type =3

  How can I optimize the above query statement Into single query statement as My 'B' table is very huge and really doing bad in terms 
  of the performance.
  Please help or guide me ..
  Thankx !!!
  VD

• Thom A

  SSC Guru

  Points: 99133

  More actions

  October 15, 2017 at 2:36 pm

  #1963738

  Why are joining onto Table B 3 times on the same value? You could return the same column 3 times with 1 JOIN, not use 3 to the same value and use 3 joins.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• vineet_dubey1975

  SSCrazy

  Points: 2306

  More actions

  October 15, 2017 at 2:54 pm

  #1963739

  Thom A - Sunday, October 15, 2017 2:36 PM

  Why are joining onto Table B 3 times on the same value? You could return the same column 3 times with 1 JOIN, not use 3 to the same value and use 3 joins.

  Sorry Now i have corrected / Updated the statements ..

• Phil Parkin

  SSC Guru

  Points: 246985

  More actions

  October 15, 2017 at 4:39 pm

  #1963742

  This query gets you part of the way there, I think:
  SELECT a.Name,
  Type1 = IIF(Q0.Type = 1, Q0.Value, 0),
  Type2 = IIF(Q0.Type = 2, Q0.Value, 0),
  Type3 = IIF(Q0.Type = 3, Q0.Value, 0)
FROM A
  LEFT JOIN B Q0
   ON Q0.FK_ID = a.ID
WHERE Q0.Type BETWEEN 1 AND 3;

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Thom A

  SSC Guru

  Points: 99133

  More actions

  October 16, 2017 at 1:05 am

  #1963748

  If the JOIN is still really slow, then I imagine your Indexes have been poorly chosen. Providing DDL with the indexes would help.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• TheCTEGuy

  Ten Centuries

  Points: 1032

  More actions

  October 16, 2017 at 4:35 am

  #1963779

  Phil Parkin - Sunday, October 15, 2017 4:39 PM

  This query gets you part of the way there, I think:
  SELECT a.Name,
  Type1 = IIF(Q0.Type = 1, Q0.Value, 0),
  Type2 = IIF(Q0.Type = 2, Q0.Value, 0),
  Type3 = IIF(Q0.Type = 3, Q0.Value, 0)
FROM A
  LEFT JOIN B Q0
   ON Q0.FK_ID = a.ID
WHERE Q0.Type BETWEEN 1 AND 3;

  Agreed..
  @Phil could the below code be considered better, as the table is huge.
  
;With cte as (
Select type,fk_id from B
)

  SELECT a.Name,
Type1 = IIF(Q0.Type = 1, Q0.Value, 0),
Type2 = IIF(Q0.Type = 2, Q0.Value, 0),
Type3 = IIF(Q0.Type = 3, Q0.Value, 0)
FROM A
LEFT JOIN cte Q0
  ON Q0.FK_ID = a.ID
WHERE Q0.Type BETWEEN 1 AND 3;

  First solve the problem then write the code !

• Thom A

  SSC Guru

  Points: 99133

  More actions

  October 16, 2017 at 4:43 am

  #1963781

  TheCTEGuy - Monday, October 16, 2017 4:35 AM

  Phil Parkin - Sunday, October 15, 2017 4:39 PM

  This query gets you part of the way there, I think:
  SELECT a.Name,
  Type1 = IIF(Q0.Type = 1, Q0.Value, 0),
  Type2 = IIF(Q0.Type = 2, Q0.Value, 0),
  Type3 = IIF(Q0.Type = 3, Q0.Value, 0)
FROM A
  LEFT JOIN B Q0
   ON Q0.FK_ID = a.ID
WHERE Q0.Type BETWEEN 1 AND 3;

  Agreed..
  @Phil could the below code be considered better, as the table is huge.
  
;With cte as (
Select type,fk_id from B
)

  SELECT a.Name,
Type1 = IIF(Q0.Type = 1, Q0.Value, 0),
Type2 = IIF(Q0.Type = 2, Q0.Value, 0),
Type3 = IIF(Q0.Type = 3, Q0.Value, 0)
FROM A
LEFT JOIN cte Q0
  ON Q0.FK_ID = a.ID
WHERE Q0.Type BETWEEN 1 AND 3;

  It would be no different. The CTE is converted into an expression and then run as part of the batch. In your SQL referencing cte Q0 would be identical to not using the CTE and referencing dbo.B Q0 (as Phil has done).

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• TheCTEGuy

  Ten Centuries

  Points: 1032

  More actions

  October 16, 2017 at 4:47 am

  #1963782

  But the CTE has restricted to 2 cols only, as the table is BIG; retricting extra columns would be beneficial. Right ?

  First solve the problem then write the code !

• Thom A

  SSC Guru

  Points: 99133

  More actions

  October 16, 2017 at 4:53 am

  #1963784

  TheCTEGuy - Monday, October 16, 2017 4:47 AM

  But the CTE has restricted to 2 cols only, as the table is BIG; retricting extra columns would be beneficial. Right ?

  No, as the SELECT part of the statement isn't done until later in the query. Have a look at the below; as you can see SELECT is one of the last things done. Long after the FROM, ON and JOIN parts.

  This explains why, for example, you can use an expression to derive a column in your SELECT statement, and reference it in your ORDER BY; as the value and column has been determined prior to the ORDER BY. You could not, however, reference it in your WHERE clause, as the SELECT portion has not been processed yet.

  Logical Processing Order of the SELECT statement

  The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. The actual physical execution of the statement is determined by the query processor and the order may vary from this list.

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. WITH CUBE or WITH ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. TOP

  https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• TheCTEGuy

  Ten Centuries

  Points: 1032

  More actions

  October 16, 2017 at 4:58 am

  #1963785

  Thom A - Monday, October 16, 2017 4:53 AM

  TheCTEGuy - Monday, October 16, 2017 4:47 AM

  But the CTE has restricted to 2 cols only, as the table is BIG; retricting extra columns would be beneficial. Right ?

  No, as the SELECT part of the statement isn't done until later in the query. Have a look at the below; as you can see SELECT is one of the last things done. Long after the FROM, ON and JOIN parts.

  Logical Processing Order of the SELECT statement

  The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. The actual physical execution of the statement is determined by the query processor and the order may vary from this list.

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. WITH CUBE or WITH ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. TOP

  https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql

  Thanks for that. But the code will be expressed as below right ? If yes, then it makes sense to limit the cols i guess.
  
SELECT a.Name,
Type1 = IIF(Q0.Type = 1, Q0.Value, 0),
Type2 = IIF(Q0.Type = 2, Q0.Value, 0),
Type3 = IIF(Q0.Type = 3, Q0.Value, 0)
FROM A
LEFT JOIN (Select type,fk_id from B) Q0
ON Q0.FK_ID = a.ID
WHERE Q0.Type BETWEEN 1 AND 3;


  First solve the problem then write the code !

• Thom A

  SSC Guru

  Points: 99133

  More actions

  October 16, 2017 at 5:07 am

  #1963787

  TheCTEGuy - Monday, October 16, 2017 4:58 AM

  Thanks for that. But the code will be expressed as below right ? If yes, then it makes sense to limit the cols i guess.
  
SELECT a.Name,
Type1 = IIF(Q0.Type = 1, Q0.Value, 0),
Type2 = IIF(Q0.Type = 2, Q0.Value, 0),
Type3 = IIF(Q0.Type = 3, Q0.Value, 0)
FROM A
LEFT JOIN (Select type,fk_id from B) Q0
ON Q0.FK_ID = a.ID
WHERE Q0.Type BETWEEN 1 AND 3;


  Limiting the columns returned in your JOIN is not going to give you any benefit.

  You could have a table 256 columns wide and 10M rows deep, but if you only reference one column, then that is the only column SQL Server is going to use (along with the Clustered Index column(s)). It doesn't matter that there are 255 other columns, as SQL Server, and you, don't need them. 

  Much like if you had an Excel Sheet that went up to column AIG, and contained 1,000,000 rows. If you were asked to provide all the data in column K where column BL had the value "7" would you bother looking at any other columns apart from K and BL? No. SQL Server is doing a similar thing.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  October 18, 2017 at 7:17 am

  #1964201

  TheCTEGuy - Monday, October 16, 2017 4:58 AM

  Thom A - Monday, October 16, 2017 4:53 AM

  TheCTEGuy - Monday, October 16, 2017 4:47 AM

  But the CTE has restricted to 2 cols only, as the table is BIG; retricting extra columns would be beneficial. Right ?

  No, as the SELECT part of the statement isn't done until later in the query. Have a look at the below; as you can see SELECT is one of the last things done. Long after the FROM, ON and JOIN parts.

  Logical Processing Order of the SELECT statement

  The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. The actual physical execution of the statement is determined by the query processor and the order may vary from this list.

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  5. GROUP BY
  6. WITH CUBE or WITH ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  10. ORDER BY
  11. TOP

  https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql

  Thanks for that. But the code will be expressed as below right ? If yes, then it makes sense to limit the cols i guess.
  
SELECT a.Name,
Type1 = IIF(Q0.Type = 1, Q0.Value, 0),
Type2 = IIF(Q0.Type = 2, Q0.Value, 0),
Type3 = IIF(Q0.Type = 3, Q0.Value, 0)
FROM A
LEFT JOIN (Select type,fk_id from B) Q0
ON Q0.FK_ID = a.ID
WHERE Q0.Type BETWEEN 1 AND 3;


  Additionally, your code wouldn't even run.   The Q0 table alias has no means to reference the Value field, as you didn't even select it.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)