Solved

Need help refining stored proc

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

920 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

16 Experts available now in Live!

Get 1:1 Help Now