I have created pivot query. How to reduce that query speed in SQL server 2005?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi,
This is my query. I want to reduce the query speed for this query. How to optimise that query?

SQL

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

Posted 26-Mar-18 3:47am

Giri Bkm

Updated 26-Mar-18 11:40am

Maciej Los

v2

Add a Solution

Comments

Manfred Rudolf Bihy 26-Mar-18 9:53am

Reducing query speed could be achieved by making the query more complex. Since you have not specified by what factor you'd like to reduce the speed it's kind of hard to say.
It may also be the case you just want to speed up your query, but you didn't ask for that. ;)

Maciej Los 26-Mar-18 13:44pm

Reduce the number of subqueries...

CHill60 26-Mar-18 13:56pm

A virtual 5!

Maciej Los 26-Mar-18 14:01pm

Thank you, Caroline. I thought to post it as an answer, but...

Giri Bkm 26-Mar-18 21:16pm

Oops! Sorry, this query is running slow right now. I want to run this query even more faster
Thanks in advance

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2018-03-26T11:42:00

Solution 1

In a short: i'd reduce the number of subqueries!

For further details, please see:
Query Performance[^]
SQL Performance Tuning: Top 5 Ways to Find Slow Queries[^]
The Seven Sins against TSQL Performance - Simple Talk[^]

Posted 26-Mar-18 11:42am

Maciej Los

Comments

Giri Bkm 27-Mar-18 3:42am

I'm very sorry. I don't want reduce. I want to increase the speed of that query. Pls give the solution for how can I increase the speed of that query. Thanks in advance

CHill60 27-Mar-18 4:37am

To increase the speed of the query, reduce the number of sub-queries. Your query is far too complex.
The construction of the dynamic sql is also messy for us to follow without any data - replace that

Exec(@Query)

with

Print @Query

and share the final query.
Alternatively share some sample data and expected results and describe what you are actually trying to do

Maciej Los 27-Mar-18 5:47am

Good advice!

Maciej Los 27-Mar-18 5:47am

Seems, you don't understand... A number of sub-queries affects on query performance.

Giri Bkm 27-Mar-18 6:12am

But i want that sub-queries, and also i dont know how to reduce the sub-queries

Giri Bkm 27-Mar-18 6:14am

Select
'Batting',
Max(IPL__Name) As IPL__Name,Max(HighestRunsAndWicketsInIPL) As HighestRunsAndWicketsInIPL,Max(Test__Name) As Test__Name,Max(HighestRunsAndWicketsInTest) As HighestRunsAndWicketsInTest,Max(T20I__Name) As T20I__Name,Max(HighestRunsAndWicketsInT20I) As HighestRunsAndWicketsInT20I,Max(ODI__Name) As ODI__Name,Max(HighestRunsAndWicketsInODI) As HighestRunsAndWicketsInODI
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,
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
) x2
) x3
Pivot
(
Max(Runs1) For Type1 In ([IPL__Name],[Test__Name],[T20I__Name],[ODI__Name])
) x4
Pivot
(
Max(Runs) For Type In ([HighestRunsAndWicketsInIPL],[HighestRunsAndWicketsInTest],[HighestRunsAndWicketsInT20I],[HighestRunsAndWicketsInODI])
) x5

Union

Select
'Bowling',
Max(IPL__Name) As IPL__Name,Max(HighestRunsAndWicketsInIPL) As HighestRunsAndWicketsInIPL,Max(Test__Name) As Test__Name,Max(HighestRunsAndWicketsInTest) As HighestRunsAndWicketsInTest,Max(T20I__Name) As T20I__Name,Max(HighestRunsAndWicketsInT20I) As HighestRunsAndWicketsInT20I,Max(ODI__Name) As ODI__Name,Max(HighestRunsAndWicketsInODI) As HighestRunsAndWicketsInODI
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,
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
) x7
) x8
Pivot
(
Max(Wickets1) For Type1 In ([IPL__Name],[Test__Name],[T20I__Name],[ODI__Name])
) x9
Pivot
(
Max(Wickets) For Type In ([HighestRunsAndWicketsInIPL],[HighestRunsAndWicketsInTest],[HighestRunsAndWicketsInT20I],[HighestRunsAndWicketsInODI])
) x10