How to know if that execution plan is suitable for this query?

• giribkm12

  Grasshopper

  Points: 16

  More actions

  March 31, 2018 at 9:04 am

  #403220

  This is the query.
  Declare
  @Pivv NVarchar(Max),
  @Query NVarchar(Max),
  @Pivv1 NVarchar(Max),
  @Columns NVarchar(Max)

  Select
  @Pivv = Coalesce(@Pivv + ',' , '') +
    Quotename(t.MatchType + '_' + '_Name'),
  @Pivv1 = Coalesce(@Pivv1 + ',' , '') +
    Quotename('HighestRunsAndWicketsIn' + t.MatchType),
  @Columns = Isnull(@Columns + ',', '') +
     'Max(' + (t.MatchType + '_' + '_Name') + ') As ' + (t.MatchType + '_' + '_Name') +
     ',' +
     'Max(' + ('HighestRunsAndWicketsIn' + t.MatchType) + ') As ' + ('HighestRunsAndWicketsIn' + t.MatchType)
From
  PlayersCareerBatting t Join PlayersBioDataNew a
On
  t.Id=a.Id
Group By
  t.MatchType

  Set @Query=
   'Select
    ''Batting'',
    ' + @Columns + '
   From (
     
        Select
          MatchType + ''_'' + ''_Name'' As Type1,
       FirstName + '' '' + MiddleName + '' '' + LastName As Runs1,
       ''HighestRunsAndWicketsIn'' + MatchType As Type,
       RunsScored As Runs
        From (
           Select
            s.Id,
            a.FirstName,
            a.MiddleName,
            a.LastName,
            s.MatchType,
            s.RunsScored,
            Row_Number() Over(Partition By MatchType Order By RunsScored Desc) rn
           From
            PlayersCareerBatting s Join PlayersBioDataNew a
           On s.Id=a.Id
          ) x
        Where rn=1
      
     ) x3
   Pivot
    (
      Max(Runs1) For Type1 In (' + @Pivv + ')
    ) x4
   Pivot
    (
      Max(Runs) For Type In (' + @Pivv1 + ')
    ) x5

     Union

     Select
    ''Bowling'',
    ' + @Columns + '
   From (
     
        Select
          MatchType + ''_'' + ''_Name'' As Type1,
       FirstName + '' '' + MiddleName + '' '' + LastName As Wickets1,
       ''HighestRunsAndWicketsIn'' + MatchType As Type,
       Wickets As Wickets
        From (
           Select
            s.Id,
            a.FirstName,
            a.MiddleName,
            a.LastName,
            s.MatchType,
            s.Wickets,
            Row_Number() Over(Partition By MatchType Order By Wickets Desc) rn
           From
            PlayersBowlingCareer s Join PlayersBioDataNew a
           On s.Id=a.Id
          ) x6
        Where rn=1
       
     ) x8
   Pivot
    (
      Max(Wickets1) For Type1 In (' + @Pivv + ')
    ) x9
   Pivot
    (
      Max(Wickets) For Type In (' + @Pivv1 + ')
    ) x10'

  Exec(@Query)

  Query contains 3 tables. Playersbiodatanew (5000 rows), players?careerbatting (20000 rows), playersbowlingcareer (20000 rows). And, i created pivot in that query.
  Now i want to know, is that query more suitable for the execution plan mentioned below?

  Link for execution plan, https://www.brentozar.com/pastetheplan/?id=SyJPRDs9M

  Sample Data

  For Table PlayersBioDataNew

  Id | Firstname | middlename | lastname
----------------------------------------
1 | Sachin  | Ramesh  | Tendulkar
----------------------------------------
2 | Suresh  | Kumar  | Raina
----------------------------------------

  For Table PlayersCareerBatting

  CareerId | Id | Matchtype | runsscored
---------------------------------------
1   | 1 | Test  | 15921
----------------------------------------
2   | 1 | ODI   | 18426
----------------------------------------
3   | 1 | T20I  | 10
----------------------------------------
4   | 1 | IPL   | 2334
----------------------------------------
5   | 2 | Test  | 768
----------------------------------------
6   | 2 | ODI   | 5568
----------------------------------------
7   | 2 | T20I  | 1498
----------------------------------------
8   | 2 | IPL   | 4540
----------------------------------------

  For Table PlayersBowlingCareer

  CareerId | Id | Matchtype | wickets
---------------------------------------
1   | 1 | Test  | 46
----------------------------------------
2   | 1 | ODI   | 154
----------------------------------------
3   | 1 | T20I  | 1
----------------------------------------
4   | 1 | IPL   | 0
----------------------------------------
5   | 2 | Test  | 13
----------------------------------------
6   | 2 | ODI   | 36
----------------------------------------
7   | 2 | T20I  | 13
----------------------------------------
8   | 2 | IPL   | 25
----------------------------------------

  Expected Output

  (No Column Name) | IPL__Name | HighestRunsAndWicketsInIPL | Test__Name | HighestRunsAndWicketsInTest | T20I__Name | HighestRunsAndWicketsInT20I | ODI__Name | HighestRunsAndWicketsInODI
----------------------------------------------------------------------------
Batting | Suresh Kumar Raina | 4540 | Sachin Ramesh Tendulkar | 15921 | Suresh Kumar Raina | 1498 | Sachin Ramesh Tendulkar | 18426
----------------------------------------------------------------------------
Bowling | Suresh Kumar Raina | 25 | Sachin Ramesh Tendulkar | 46 | Suresh Kumar Raina | 13 | Sachin Ramesh Tendulkar | 154
----------------------------------------------------------------------------

  I'm getting the output who got maximum runsscored and wickets for each matchtype.