Reputation: 435
Techniques to simplify complicated Select query
Recently, I've noticed one of my stored procedures taking a lot longer to run.
I feel my question is a bit too specific to ask out right so I thought I'd ask for a solution to my problem and advice on how to simplify complicated statements.
I have a "Results" table in my SSMS database. The select query I have, generates rankings based on results.
The rankings are generated depending on the date provided as a parameter, and calculates from results from the past four years.
Results within the last year get 100% of the points they received, between a year and 2 years ago get 75%, between 2 and 3 years ago get 50%, and between 3 and 4 years ago get 25%.
I also get another calculated column called "Rank Change", this is the change of rank since the last result before the provided date.
The query is below.
As it is a complicated query and it may be difficult to simplify, how would you simplify this query, if possible?
And what techniques do you use to go about simplifying a query like this, if there are any?
IF (@Date < '2000-01-01')
BEGIN
SET @Date = '2000-01-01'
END
DECLARE @LatestContestDate DATE
SET @LatestContestDate = ( SELECT TOP 1 Date
FROM Results
WHERE Date < @Date
ORDER BY Date DESC
)
SET @LatestContestDate = DATEADD(DD, -1, @LatestContestDate)
SELECT Band.Name ,
Area.Name ,
( ISNULL(( SELECT SUM(R2.Points) /*Calculates points*/
FROM Results R2
WHERE R2.Date > DATEADD(YY, -1, @Date)
AND R2.Date < DATEADD(DD, 1, @Date)
AND R2.BandID = R1.BandID
), 0)
+ ISNULL(( SELECT ( SUM(R2.Points) * 0.75 )
FROM Results R2
WHERE R2.Date < DATEADD(YY, -1, @Date)
AND R2.Date > DATEADD(YY, -2, @Date)
AND R2.BandID = R1.BandID
), 0)
+ ISNULL(( SELECT ( SUM(R2.Points) * 0.5 )
FROM Results R2
WHERE R2.Date < DATEADD(YY, -2, @Date)
AND R2.Date > DATEADD(YY, -3, @Date)
AND R2.BandID = R1.BandID
), 0)
+ ISNULL(( SELECT ( SUM(R2.Points) * 0.25 )
FROM Results R2
WHERE R2.Date < DATEADD(YY, -3, @Date)
AND R2.Date > DATEADD(YY, -4, @Date)
AND R2.BandID = R1.BandID
), 0) ) AS PointsTotal , /*Below Calculates rank change*/
( ( ROW_NUMBER() OVER ( ORDER BY ( ISNULL(( SELECT
SUM(R2.Points)
FROM Results R2
WHERE R2.Date > DATEADD(YY,
-1,
@LatestContestDate)
AND R2.Date < DATEADD(DD,
1,
@LatestContestDate)
AND R2.BandID = R1.BandID
), 0)
+ ISNULL(( SELECT
( SUM(R2.Points)
* 0.75 )
FROM
Results R2
WHERE
R2.Date < DATEADD(YY,
-1,
@LatestContestDate)
AND R2.Date > DATEADD(YY,
-2,
@LatestContestDate)
AND R2.BandID = R1.BandID
), 0)
+ ISNULL(( SELECT
( SUM(R2.Points)
* 0.5 )
FROM
Results R2
WHERE
R2.Date < DATEADD(YY,
-2,
@LatestContestDate)
AND R2.Date > DATEADD(YY,
-3,
@LatestContestDate)
AND R2.BandID = R1.BandID
), 0)
+ ISNULL(( SELECT
( SUM(R2.Points)
* 0.25 )
FROM
Results R2
WHERE
R2.Date < DATEADD(YY,
-3,
@LatestContestDate)
AND R2.Date > DATEADD(YY,
-4,
@LatestContestDate)
AND R2.BandID = R1.BandID
), 0) ) DESC ) )
- ( ROW_NUMBER() OVER ( ORDER BY ( ISNULL(( SELECT
SUM(R2.Points)
FROM
Results R2
WHERE
R2.Date > DATEADD(YY,
-1, @Date)
AND R2.Date < DATEADD(DD,
1, @Date)
AND R2.BandID = R1.BandID
), 0)
+ ISNULL(( SELECT
( SUM(R2.Points)
* 0.75 )
FROM
Results R2
WHERE
R2.Date < DATEADD(YY,
-1, @Date)
AND R2.Date > DATEADD(YY,
-2, @Date)
AND R2.BandID = R1.BandID
), 0)
+ ISNULL(( SELECT
( SUM(R2.Points)
* 0.5 )
FROM
Results R2
WHERE
R2.Date < DATEADD(YY,
-2, @Date)
AND R2.Date > DATEADD(YY,
-3, @Date)
AND R2.BandID = R1.BandID
), 0)
+ ISNULL(( SELECT
( SUM(R2.Points)
* 0.25 )
FROM
Results R2
WHERE
R2.Date < DATEADD(YY,
-3, @Date)
AND R2.Date > DATEADD(YY,
-4, @Date)
AND R2.BandID = R1.BandID
), 0) ) DESC ) ) ) AS RankChange
FROM Results R1
INNER JOIN Band ON R1.BandID = Band.ID
INNER JOIN Area ON Band.AreaID = Area.ID
WHERE ( ISNULL(( SELECT SUM(R2.Points)
FROM Results R2
WHERE R2.Date > DATEADD(YY, -1, @Date)
AND R2.Date < DATEADD(DD, 1, @Date)
AND R2.BandID = R1.BandID
), 0)
+ ISNULL(( SELECT ( SUM(R2.Points) * 0.75 )
FROM Results R2
WHERE R2.Date < DATEADD(YY, -1, @Date)
AND R2.Date > DATEADD(YY, -2, @Date)
AND R2.BandID = R1.BandID
), 0)
+ ISNULL(( SELECT ( SUM(R2.Points) * 0.5 )
FROM Results R2
WHERE R2.Date < DATEADD(YY, -2, @Date)
AND R2.Date > DATEADD(YY, -3, @Date)
AND R2.BandID = R1.BandID
), 0)
+ ISNULL(( SELECT ( SUM(R2.Points) * 0.25 )
FROM Results R2
WHERE R2.Date < DATEADD(YY, -3, @Date)
AND R2.Date > DATEADD(YY, -4, @Date)
AND R2.BandID = R1.BandID
), 0) ) > 0
GROUP BY Band.Name ,
Area.Name,
BandID
ORDER BY PointsTotal DESC
Upvotes: 0
Views: 81
Answers (2)
Reputation: 1269663
Use conditional aggregation for the points calculation. Something like:
select (sum(case when r2.Date > DATEADD(year, -1, @Date) and r2.Date < DATEADD(day, 1, @Date)
then r2.points else 0
end) +
sum(case when r2.Date < DATEADD(year, -1, @Date) and r2.Date > DATEADD(year, -2, @Date)
then r2.points else 0
end) * 0.75 +
. . .
)
from results r2
where r2.BandID = r1.BandID and
r2.Date >= dateadd(year, -4, @Date)
Then put this in a subquery, along with a similar expression for the similar query used for the row_number. Then reference the expression from the subquery in the SELECT, ROW_NUMBER() and WHERE. This will reduce the number of subqueries from something like 16 to 1 or 2.
You can optimize this, with an index on results(BandId, Date).
In SQL Server 2012+, you might be able to do what you want with cumulative sums.
Upvotes: 3
Reputation: 3563
Its hard to say exactly how to modify your select statement without knowing how the underlying schema and table look like. That said I would start with looking at the execution plan in sql server management studio and seeing what is bottlenecking the query: Missing Indexes, Sargability, IO, Missing Stats etc.
Here is a really good link that could get you started to quickly performance tune the query:
Upvotes: 0
Related Questions
- Simplifying SELECT statement
- Converting my SELECT query to a less complex one
- how to streamline a select query
- How to simplify following SQL query?
- Simplifying a repetitive SQL Server query
- Simplify SQL select statement
- Simplifying/fixing a long query
- Simplify T-SQL Query
- Is there a way to shorten this query?
- How to simplify this Sql query