Hi there,
I have this stored procedure I am trying to optimize:
CREATE PROCEDURE uspLeaderboardStateResults
@.StateID int
AS
BEGIN
CREATE TABLE #ResultsOutput (
rowID int not null identity(1,1),
Rank int,
MemberID int,
MemberCode varchar(50),
State varchar(5),
MemberName varchar(510),
StateID int,
TotalScore int,
TotalEvents int,
PRIMARY KEY (TotalScore, MemberID, rowID),
UNIQUE(TotalScore, MemberID, rowID)
)
CREATE TABLE #TotalScoreCount (
rowID int not null identity(1,1),
TotalScore int,
TotalScoreCount int
PRIMARY KEY (TotalScoreCount, TotalScore, rowID),
UNIQUE (TotalScoreCount, TotalScore, rowID)
)
INSERT INTO #ResultsOutput
SELECT 0, --rank. gets updated later.
Results.MemberID,
Members.MemberCode,
Members.State,
Members.FirstName + ' ' + Members.Surname,
State.StateID,
SUM(Results.Score) AS TotalScore,
COUNT(Results.ResultsID) AS TotalEvents
FROM Members
INNER JOIN Results ON Members.MemberID = Results.MemberID
INNER JOIN Event ON Results.EventID = Event.EventID
INNER JOIN Venue ON Event.VenueID = Venue.VenueID
INNER JOIN Region ON Venue.RegionID = Region.RegionID
INNER JOIN State ON Region.StateID = State.StateID
WHERE Results.Valid = 1 AND Event.EventType = 0 AND State.StateID =
@.StateID
GROUP BY Results.MemberID,
Members.MemberCode,
Members.State,
Members.FirstName,
Members.Surname,
State.StateID
ORDER BY TotalScore DESC, Results.MemberID ASC
INSERT INTO #TotalScoreCount
SELECT TotalScore,
COUNT(TotalScore) AS TotalScoreCount
FROM #ResultsOutput
GROUP BY TotalScore
ORDER BY TotalScore DESC
UPDATE #ResultsOutput
SET Rank = (SELECT ISNULL(SUM(TSC.TotalScoreCount), 0) FROM
#TotalScoreCount TSC WHERE TSC.TotalScore > RO.TotalScore) + 1
FROM #ResultsOutput RO
SELECT * FROM #ResultsOutput ORDER BY TotalScore DESC, MemberID ASC
DROP table #ResultsOutput
DROP table #TotalScoreCount
END
I did a SQL Execution plan and saw no table scans on my selects/updates
(Only on my inserts ofcourse)
The query still runs poorly it takes up to 7 seconds to run!
Members has 44621 rows
Results has 121158
Venue has 336
Event has 3298
Region has 58
I am quite new and poor at SQL so any help would be appreciated.
Thanks!to be perfectly honest... its not 'that' bad already.
You could try using table variable (sql2k +) instead of temp table and also
remove the order by on the insert as you are ordering the select after
anyway.
<robert@.relate.com.au> wrote in message
news:1147350229.650320.155550@.g10g2000cwb.googlegroups.com...
> Hi there,
> I have this stored procedure I am trying to optimize:
>
> CREATE PROCEDURE uspLeaderboardStateResults
> @.StateID int
> AS
> BEGIN
> CREATE TABLE #ResultsOutput (
> rowID int not null identity(1,1),
> Rank int,
> MemberID int,
> MemberCode varchar(50),
> State varchar(5),
> MemberName varchar(510),
> StateID int,
> TotalScore int,
> TotalEvents int,
> PRIMARY KEY (TotalScore, MemberID, rowID),
> UNIQUE(TotalScore, MemberID, rowID)
> )
> CREATE TABLE #TotalScoreCount (
> rowID int not null identity(1,1),
> TotalScore int,
> TotalScoreCount int
> PRIMARY KEY (TotalScoreCount, TotalScore, rowID),
> UNIQUE (TotalScoreCount, TotalScore, rowID)
> )
>
> INSERT INTO #ResultsOutput
> SELECT 0, --rank. gets updated later.
> Results.MemberID,
> Members.MemberCode,
> Members.State,
> Members.FirstName + ' ' + Members.Surname,
> State.StateID,
> SUM(Results.Score) AS TotalScore,
> COUNT(Results.ResultsID) AS TotalEvents
> FROM Members
> INNER JOIN Results ON Members.MemberID = Results.MemberID
> INNER JOIN Event ON Results.EventID = Event.EventID
> INNER JOIN Venue ON Event.VenueID = Venue.VenueID
> INNER JOIN Region ON Venue.RegionID = Region.RegionID
> INNER JOIN State ON Region.StateID = State.StateID
> WHERE Results.Valid = 1 AND Event.EventType = 0 AND State.StateID =
> @.StateID
> GROUP BY Results.MemberID,
> Members.MemberCode,
> Members.State,
> Members.FirstName,
> Members.Surname,
> State.StateID
> ORDER BY TotalScore DESC, Results.MemberID ASC
>
> INSERT INTO #TotalScoreCount
> SELECT TotalScore,
> COUNT(TotalScore) AS TotalScoreCount
> FROM #ResultsOutput
> GROUP BY TotalScore
> ORDER BY TotalScore DESC
> UPDATE #ResultsOutput
> SET Rank = (SELECT ISNULL(SUM(TSC.TotalScoreCount), 0) FROM
> #TotalScoreCount TSC WHERE TSC.TotalScore > RO.TotalScore) + 1
> FROM #ResultsOutput RO
>
> SELECT * FROM #ResultsOutput ORDER BY TotalScore DESC, MemberID ASC
> DROP table #ResultsOutput
> DROP table #TotalScoreCount
> END
>
> I did a SQL Execution plan and saw no table scans on my selects/updates
> (Only on my inserts ofcourse)
> The query still runs poorly it takes up to 7 seconds to run!
> Members has 44621 rows
> Results has 121158
> Venue has 336
> Event has 3298
> Region has 58
> I am quite new and poor at SQL so any help would be appreciated.
> Thanks!
>|||Well, 7 seconds is hardly poor.
Anyway, make sure you have the following indexes:
- on Members(MemberID)
- on Results(EventID, MemberID, Valid)
- on Results(MemberID, EventID, Valid)
- on Event(EventID, VenueID, EventType)
- on Event(VenueID, EventID, EventType)
- on Venue(VenueID, RegionID)
- on Venue(RegionID, VenueID)
- on Region(RegionID, StateID)
- on Region(StateID, RegionID)
- on State(StateID)
and then try the SP below. Of course, you can remove the indexes that
you created above if they are not used in the query below.
CREATE PROCEDURE uspLeaderboardStateResults
@.StateID int
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #ResultsOutput
(MemberID int
,MemberCode varchar(50)
,State varchar(5)
,MemberName varchar(510)
,StateID int
,TotalScore int
,TotalEvents int
)
INSERT INTO #ResultsOutput (MemberID, MemberCode, State, MemberName,
StateID, TotalScore, TotalEvents)
SELECT Results.MemberID
, MAX(Members.MemberCode)
, MAX(Members.State)
, MAX(Members.FirstName + ' ' + Members.Surname)
, MAX(State.StateID)
, SUM(Results.Score)
, COUNT(Results.ResultsID)
FROM Members
INNER JOIN Results ON Results.MemberID = Members.MemberID
INNER JOIN Event ON Event.EventID = Results.EventID
INNER JOIN Venue ON Venue.VenueID = Event.VenueID
INNER JOIN Region ON Region.RegionID = Venue.RegionID
INNER JOIN State ON State.StateID = Region.StateID
WHERE Results.Valid = 1
AND Event.EventType = 0
AND State.StateID = @.StateID
GROUP BY Results.MemberID
Declare @.SQL varchar(1000)
Set @.SQL='CREATE NONCLUSTERED INDEX IX_ResultOutput_spid'+CAST(@.@.spid
as varchar(12))+' ON #ResultsOutput(TotalScore)'
EXEC (@.SQL)
SELECT
ISNULL((
SELECT COUNT(*)
FROM #ResultsOutput R2
WHERE R2.TotalScore > R1.TotalScore
),0)+1 AS Rank
,MemberID
,MemberCode
,State
,MemberName
,StateID
,TotalScore
,TotalEvents
FROM #ResultsOutput R1
ORDER BY TotalScore DESC, MemberID ASC
DROP TABLE #ResultsOutput
END
Hope this helps,
Gert-Jan
"robert@.relate.com.au" wrote:
> Hi there,
> I have this stored procedure I am trying to optimize:
> CREATE PROCEDURE uspLeaderboardStateResults
> @.StateID int
> AS
> BEGIN
> CREATE TABLE #ResultsOutput (
> rowID int not null identity(1,1),
> Rank int,
> MemberID int,
> MemberCode varchar(50),
> State varchar(5),
> MemberName varchar(510),
> StateID int,
> TotalScore int,
> TotalEvents int,
> PRIMARY KEY (TotalScore, MemberID, rowID),
> UNIQUE(TotalScore, MemberID, rowID)
> )
> CREATE TABLE #TotalScoreCount (
> rowID int not null identity(1,1),
> TotalScore int,
> TotalScoreCount int
> PRIMARY KEY (TotalScoreCount, TotalScore, rowID),
> UNIQUE (TotalScoreCount, TotalScore, rowID)
> )
> INSERT INTO #ResultsOutput
> SELECT 0, --rank. gets updated later.
> Results.MemberID,
> Members.MemberCode,
> Members.State,
> Members.FirstName + ' ' + Members.Surname,
> State.StateID,
> SUM(Results.Score) AS TotalScore,
> COUNT(Results.ResultsID) AS TotalEvents
> FROM Members
> INNER JOIN Results ON Members.MemberID = R
esults.MemberID
> INNER JOIN Event ON Results.EventI
D = Event.EventID
> INNER JOIN Venue ON Event.
VenueID = Venue.VenueID
> INNER JOIN Region
ON Venue.RegionID = Region.RegionID
> INNER JOIN
State ON Region.StateID = State.StateID
> WHERE Results.Valid = 1 AND Event.EventType = 0
AND State.StateID =
> @.StateID
> GROUP BY Results.MemberID,
> Members.MemberCode,
> Members.State,
> Members.FirstName,
> Members.Surname,
> State.StateID
> ORDER BY TotalScore DESC, Results.MemberID ASC
> INSERT INTO #TotalScoreCount
> SELECT TotalScore,
> COUNT(TotalScore) AS TotalScoreCount
> FROM #ResultsOutput
> GROUP BY TotalScore
> ORDER BY TotalScore DESC
> UPDATE #ResultsOutput
> SET Rank = (SELECT ISNULL(SUM(TSC.TotalScoreCount), 0) FRO
M
> #TotalScoreCount TSC WHERE TSC.TotalScore > RO.TotalScore) + 1
> FROM #ResultsOutput RO
> SELECT * FROM #ResultsOutput ORDER BY TotalScore DESC, MemberID AS
C
> DROP table #ResultsOutput
> DROP table #TotalScoreCount
> END
> I did a SQL Execution plan and saw no table scans on my selects/updates
> (Only on my inserts ofcourse)
> The query still runs poorly it takes up to 7 seconds to run!
> Members has 44621 rows
> Results has 121158
> Venue has 336
> Event has 3298
> Region has 58
> I am quite new and poor at SQL so any help would be appreciated.
> Thanks!|||Try moving the filters from the where clause because they get applied
after building the set of rows from the JOIN statements. Also, the
JOIN to States doesn't appear to be needed because the StateID can be
filltered directly from Results.
This may be faster:
INSERT INTO #ResultsOutput
SELECT 0, --rank. gets updated later.
Results.MemberID,
Members.MemberCode,
Members.State,
Members.FirstName + ' ' +
Members.Surname,
State.StateID,
SUM(Results.Score) AS TotalScore,
COUNT(Results.ResultsID) AS TotalEvents
FROM Members
INNER JOIN Results ON Members.MemberID =
Results.MemberID AND Results.Valid = 1
INNER JOIN Event ON Results.EventID = Event.EventID AND
Event.EventType = 0
INNER JOIN Venue ON Event.VenueID = Venue.VenueID
INNER JOIN Region ON Venue.RegionID = Region.RegionID
AND Region.StateID = @.StateID
GROUP BY Results.MemberID,
Members.MemberCode,
Members.State,
Members.FirstName,
Members.Surname,
State.StateID|||On Thu, 11 May 2006 20:56:02 +0200, Gert-Jan Strik wrote:
> Declare @.SQL varchar(1000)
> Set @.SQL='CREATE NONCLUSTERED INDEX IX_ResultOutput_spid'+CAST(@.@.spid
>as varchar(12))+' ON #ResultsOutput(TotalScore)'
> EXEC (@.SQL)
Hi Gert-Jan,
Why are you addind the spid to the index name here? I have always been
under the assumption that you only have to prefix indexes on temp tables
with the # symbol, just like you do with temp tables.
In fact, I just tested this. I executed the code below from two
different connections in SSMS and didn't get an error:
create table #test (a int)
go
create nonclustered index #ind on #test(a)
go
Hugo Kornelis, SQL Server MVP|||Thanks for the tip. It was about time that I learnt something new :-)
Gert-Jan
Hugo Kornelis wrote:
> On Thu, 11 May 2006 20:56:02 +0200, Gert-Jan Strik wrote:
>
> Hi Gert-Jan,
> Why are you addind the spid to the index name here? I have always been
> under the assumption that you only have to prefix indexes on temp tables
> with the # symbol, just like you do with temp tables.
> In fact, I just tested this. I executed the code below from two
> different connections in SSMS and didn't get an error:
> create table #test (a int)
> go
> create nonclustered index #ind on #test(a)
> go
> --
> Hugo Kornelis, SQL Server MVP|||Thanks for everyone's help everyone. Immy, i originally was using table
variables but was gettign table scans because of lack of indexing.
I guess i'm going to have to setup a scheduled task to run a stored
proc that will run every half an hour to do that 1 query and select it
all into a table with every field in one row and already sorted, that
way when my website application goes to run it only has to do a simple
select.
Thanks again all for confirming nothings majorly wrong.
Amarpreet wrote:
