Solved

Need help refining stored proc

Posted on 2008-10-13
5
140 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 500 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now