Need help refining stored proc

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),

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)

      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',
            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,
            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,
From @AssociatedPlayers a, @RegisterInfo b, @MaxRegID c
Where c.playerID = a.playerID and
        c.registerID = b.registerID

Who is Participating?
BrandonGalderisiConnect With a Mentor Commented:
Try this:

Note: I changed your procedure name from sp_ to up_ (see:
CREATE PROCEDURE up_Online_Get_Players_From_Contact_Email
     @email varchar(125)
set nocount on
;with LeagueInfo as (
SELECT Row_Number() over (partition by playerid order by registerid desc) rn,
From Players P 
  INNER JOIN Contacts C
    on p.contactid = c.contactid
     and @email in (, 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
from LeagueInfo
where rn=1

Open in new window

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.
clintnashAuthor Commented:
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.

clintnashAuthor Commented:
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,
3 seconds.  You must have a lot of data or not enough indexes.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.