Hi,
This is my query. I want to reduce the query speed for this query. How to optimise that 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)
Thanks in advance
What I have tried:
Actually I checked some points given in the internet but it doesn't use for me