?
Solved

Need help refining stored proc

Posted on 2008-10-13
5
Medium Priority
?
156 Views
Last Modified: 2012-05-05
I have the following stored proc, which takes up to a minute to run.  I know that I have taken the LONG road to get the results, but I don't have enough experience to know how to clean it up.

Thanks for any advice
CREATE PROCEDURE sp_Online_Get_Players_From_Contact_Email
(
 @email varchar(125),
 )
AS

DECLARE @AssociatedPlayers TABLE (
      RowNumber int,
      LastName varchar(30),
      FirstName varchar(30),
      email varchar(120),
      email2 varchar(120),
      playerID int
)

DECLARE @RegisterInfo TABLE (
      PlayerID int,
      RegisterID int,
      season varchar(35),
      sportname varchar(35),
      league varchar(35),
      teamname varchar(35),
      c_lastname varchar(35),
      c_firstname varchar(35)
      )

DECLARE @MaxRegID TABLE (
      PlayerID int,
      RegisterID int
)

--First Get the Players Associated with the email address.
Insert Into @AssociatedPlayers (RowNumber,LastName,FirstName,email,email2,playerID)
            SELECT  ROW_NUMBER() OVER(ORDER BY PlayerID ASC) AS 'RowNumber',
            P.LastName,
            P.FirstName,
            C.Email,
            c.ct2email,
            MAX(P.PlayerID) as PlayerID
            FROM Players P INNER JOIN Contacts C
            ON C.Email = @email AND
         P.ContactID = C.ContactID OR
            C.ct2email = @email AND
         P.ContactID = C.ContactID      
            GROUP BY PlayerID, P.LastName, P.FirstName, C.Email,c.ct2email

--Now get the registrations associated with the Players
Insert into @RegisterInfo(PlayerID,RegisterID,season,sportname,league,teamname,c_lastname,c_firstname)
            SELECT ap.PlayerID,
            r.registerID,
            s.season,
            sp.sportname,
            l.league,
            t.teamname,
            c.c_lastname,
            c.c_firstname
            FROM @AssociatedPlayers ap, register r, seasons s, sports sp, leagues l, teams t, coaches c
            Where r.playerID = ap.PlayerID and
                  r.seasonID = s.seasonID and
                  r.sportID = sp.sportID and
                  r.leagueID = l.leagueID and
                  r.teamID = t.teamID and
                  t.coachID = c.coachID
            
--Finally Insert the Last RegisterID for each playerID into @MaxRegID
Insert Into @MaxRegID (PlayerID, RegisterID)
      Select PlayerID, Max(RegisterID) from Register
      Group by PlayerID
      having PlayerID in
            (select PlayerID from @AssociatedPlayers )

SELECT       a.RowNumber,
      a.PlayerID,
      a.Lastname,
      a.Firstname,
      a.email,
      a.email2,
      b.season,
      b.sportname,
      b.league,
      b.teamname,
      b.c_lastname,
      b.c_firstname,
      b.RegisterID
From @AssociatedPlayers a, @RegisterInfo b, @MaxRegID c
Where c.playerID = a.playerID and
        c.registerID = b.registerID


0
Comment
Question by:clintnash
  • 2
  • 2
5 Comments
 
LVL 1

Expert Comment

by:crumber
ID: 22707591
Firstly, I am not sure why you would be creating temporary tables and then selecting the data from the physical tables in the database.  This is one of the reasons why the sp is taking so long.
You could probably get away with either creating one select statement inside the sp that linked all of the tables and gave you your results, or you could create view objects inside the database and then have the stored procedure select from the view in order to obtain your result set.
One thing to keep in mind.  Do you have primary keys on each of these tables?  Indexing these tables will also speed up the results when queried.
Let me know if this is what you are looking for.
0
 
LVL 1

Author Comment

by:clintnash
ID: 22707653
The reason that I created the temporary tables is I was unable to figure out how to get it into a single statement.  I would very much like to have this in a single select statement, for two reasons first I believe it would make this run faster and second to learn how to limit the results (Max(RegisterID) within a select statement (and it work...).

Yes, there are primary keys defined and indexes on the tables used.

Thanks,
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 22707812
Try this:

Note: I changed your procedure name from sp_ to up_ (see: http://www.sqlservercentral.com/articles/Performance+Tuning/sp_performance/850/)
CREATE PROCEDURE up_Online_Get_Players_From_Contact_Email
     @email varchar(125)
AS
set nocount on
 
;with LeagueInfo as (
SELECT Row_Number() over (partition by playerid order by registerid desc) rn,
      p.PlayerID,
      p.Lastname,
      p.Firstname,
      c.email,
      c.email2,
      
      s.season,
      r.sportname,
      l.league,
      t.teamname,
      c.c_lastname,
      c.c_firstname,
      r.RegisterID
From Players P 
  INNER JOIN Contacts C
    on p.contactid = c.contactid
     and @email in (c.email, c.ct2email)
 
  inner join register r
    on p.playerid = r.playerid
  inner join seasons s
    on r.seasonid = s.seasonid
  inner join sports sp
    on r.sportid = sp.sportid
  inner join league l
    on r.leagueid = l.leagueid
  inner join teams t
    on r.teamid = t.teamid
  inner join coaches c
    on t.coachid = c.coachid
)
select row_number() over (order by playerid) rownumber
     ,playerID,lastname,firstname,email,email2,season
     ,sportsname,league,teamname,c_lastname,c_firstname,registerid
from LeagueInfo
where rn=1

Open in new window

0
 
LVL 1

Author Closing Comment

by:clintnash
ID: 31505726
Brilliant, that is right on the money and with no surprise reduced the response time to 3 seconds.  Thank you for taking time to give me hand with this, also I appreciate the note on the sp_ versus up_ .

thanks again,
Clint...
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22712782
3 seconds.  You must have a lot of data or not enough indexes.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question