SQL Server 2005 – How to Determine if an Execution Plan is Suitable for a Query

sql serversql-server-2005

This is my query.

Declare
    @Pivv NVarchar(Max),
    @Query NVarchar(Max),
    @Pivv1 NVarchar(Max),
    @Columns NVarchar(Max)

Select
    @Pivv = Coalesce(@Pivv + ',' , '') + 
            Quotename(MatchType + '_' + '_Name'),
    @Pivv1 = Coalesce(@Pivv1 + ',' , '') + 
             Quotename('HighestRunsAndWicketsIn' + MatchType),
    @Columns = Isnull(@Columns + ',', '') +
               'Max(' + (MatchType + '_' + '_Name') + ') As ' + (MatchType + '_' + '_Name') +
               ',' +
               'Max(' + ('HighestRunsAndWicketsIn' + MatchType) + ') As ' + ('HighestRunsAndWicketsIn' + MatchType)
From
    PlayersCareerBatting t Join PlayersBioDataNew a
On
    t.Id=a.Id
Group By
    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 
                            FirstName, 
                            MiddleName,
                            LastName, 
                            MatchType, 
                            RunsScored
                        From (
                                Select 
                                    s.Id, 
                                    FirstName, 
                                    MiddleName,
                                    LastName, 
                                    MatchType, 
                                    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
                     ) x2
             ) x3
        Pivot 
            (
                Max(Runs1) For Type1 In (' + @Pivv + ')
            ) x4
        Pivot
            (
                Max(Runs) For Type In (' + @Pivv1 + ')
            ) x5

        Union All

        Select
            ''Bowling'',
            ' + @Columns + '
        From (
                Select
                    MatchType + ''_'' + ''_Name'' As Type1,
                    FirstName + '' '' + MiddleName + '' '' + LastName As Wickets1,
                    ''HighestRunsAndWicketsIn'' + MatchType As Type,
                    Wickets As Wickets
                From ( 
                        Select 
                            FirstName, 
                            MiddleName,
                            LastName,
                            MatchType, 
                            Wickets
                        From (
                                Select 
                                    s.Id, 
                                    FirstName, 
                                    MiddleName,
                                    LastName, 
                                    MatchType, 
                                    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
                     ) x7
             ) 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

execution plan

execution plan

execution plan

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.

Best Answer

how many rows in final output ?

From your script one cannot make out which column belong to which table.

you should prefix table alias along with column

Alternate Solution,

Since you are using `PlayersBioDataNew` in 3 places.Why not insert them in temp table and use temp table instead

create table #tmp (id int, rn int,matchtype int, wickets int)

insert into #tmp
select id, Row_Number() Over(Partition By MatchType Order By Wickets Desc) rn
,MatchType,wickets
 from  dbo.PlayersBioDataNew
 -- where apply other filter
 -- may be apply rn=1 here itself if it help in all 3 places

 --so depeding upon final script we can create index on #tmp table

 Declare
    @Pivv NVarchar(Max),
    @Query NVarchar(Max),
    @Pivv1 NVarchar(Max),
    @Columns NVarchar(Max)

Select
    @Pivv = Coalesce(@Pivv + ',' , '') + 
            Quotename(MatchType + '_' + '_Name'),
    @Pivv1 = Coalesce(@Pivv1 + ',' , '') + 
             Quotename('HighestRunsAndWicketsIn' + MatchType),
    @Columns = Isnull(@Columns + ',', '') +
               'Max(' + (MatchType + '_' + '_Name') + ') As ' + (MatchType + '_' + '_Name') +
               ',' +
               'Max(' + ('HighestRunsAndWicketsIn' + MatchType) + ') As ' + ('HighestRunsAndWicketsIn' + MatchType)
From
    PlayersCareerBatting t Join #tmp a
On
    t.Id=a.Id
Group By
    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 
                                    FirstName, 
                                    MiddleName,
                                    LastName, 
                                    MatchType, 
                                    RunsScored
                                From 
                                    PlayersCareerBatting s Join #tmp a 
                                On s.Id=a.Id
                                Where rn=1


                     ) x2
             ) x3
        Pivot 
            (
                Max(Runs1) For Type1 In (' + @Pivv + ')
            ) x4
        Pivot
            (
                Max(Runs) For Type In (' + @Pivv1 + ')
            ) x5

        Union All

        Select
            ''Bowling'',
            ' + @Columns + '
        From (
                Select
                    MatchType + ''_'' + ''_Name'' As Type1,
                    FirstName + '' '' + MiddleName + '' '' + LastName As Wickets1,
                    ''HighestRunsAndWicketsIn'' + MatchType As Type,
                    Wickets As Wickets
                From ( 

                                Select 
                                    FirstName, 
                                    MiddleName,
                                    LastName,
                                    MatchType, 
                                    Wickets
                                From 
                                    PlayersBowlingCareer s Join #tmp a 
                                On s.Id=a.Id
                                Where rn=1


                     ) x7
             ) x8 
        Pivot 
            (
                Max(Wickets1) For Type1 In (' + @Pivv + ')
            ) x9
        Pivot
            (
                Max(Wickets) For Type In (' + @Pivv1 + ')
            ) x10'

Exec(@Query)

use sp_executesql in any case

Related Question