Solved

Suggestions to improve performance

Posted on 2004-10-21
258 Views
Last Modified: 2012-08-13
The following stored procedure is used a lot in my ASP.Net application.
It's very slow and causes the CPU to max-out at times.

Could you please take a look at it and make some suggestions on how
to improve it?  Thanks in advance for your time.

--------------------------------------------------------------------------------------------------------
CREATE       PROCEDURE  dbo.dnReferralsGetByJPO
@EmployeeID Int
AS
SET NOCOUNT ON
declare @YouthLastJPO       varchar(250)

/* Step 1 */
--Get JPO's Referrals
SELECT      
      a.AssignSeqID,
      ISNULL(y.YthLastName,'') + ', ' + ISNULL(y.YthFirstName,'') + ' ' + ISNULL(y.YthMiddleName,'') AS 'Youth Name',
      CONVERT(varchar(12), a.MgrBeginDate, 101)AS 'Assigned Date',
      CONVERT(varchar(12), a.MgrEndDate, 101) AS 'End Date',
      a.ReferralID AS 'Referral ID',
      ct.CaseDescript AS 'Case Type',
      nrv.NbrRefs,
      ISNULL(s.LastName,'') + ', ' + ISNULL(s.FirstName,'') + ' ' + ISNULL(s.MiddleName,'') AS 'Assigned By',
      a.DJJID AS DJJID,
      a.LastEmployeeIDUpdate as AssignLastEmployeeIDUpdate,
      SharedWith =       (Select ISNULL(s.LastName,'') + ', ' + ISNULL(s.FirstName,'') + ' ' + ISNULL(s.MiddleName,'') + ' - ' + isnull(m.UnitTitle,'')
                  from tblstaff s
                  left join tblmgtunit m on s.mgtunitid = m.mgtunitid
                  where s.employeeid = (Select top 1 employeeid
                        from tblassigned
                        where referralid = a.referralid
                        and employeeid != @employeeid
                        and mgrenddate is null)and terminatdate is null),
      YouthLastJPO = @YouthLastJPO,
      t3.currlivzip1 as YouthZIP,
      a.MgtUnitID,
      a.CaseTypeID,
      JPOCarriesCaseType = CASE WHEN (a.casetypeid in (SELECT  casetypeid
                                           FROM        tblcaseload
                                           WHERE   employeeid = a.employeeid
                                           --AND     caseloadenddate is null
                                           AND        Active = 1))
                        THEN 'Yes'
                        ELSE 'No'
                  END

INTO      #temp

FROM
      tblAssigned a (NOLOCK)
      JOIN tblReferral r (NOlock) on a.ReferralID = r.ReferralID
      LEFT OUTER JOIN View_ReferralID_tblAssigned nrv (NOlock) ON r.ReferralID = nrv.ReferralID
      JOIN tblYouth y (NOlock) on y.DJJID = r.DJJID
      JOIN tblCaseType ct (NOlock) ON a.CaseTypeID = ct.CaseTypeID
      JOIN tblStaff s (NOlock) ON A.LastEmployeeIDUpdate = s.EmployeeID
      join tblYthMember t2  (NOlock) on y.djjid = t2.djjid and t2.RelTypeID=22 and t2.MbrEndDate IS NULL
      left outer join  tblYthCurrAddress t3  (NOlock) on t2.AddrSeqID = t3.AddrSeqID

WHERE      
      a.EmployeeID = @EmployeeID
and      a.MgrEndDate IS NULL
and      s.TerminatDate IS NULL

ORDER BY 2,3


/* Step 2 */
--Max AssignSeqID for all the above referrals closed Assigned records
SELECT       t1.[Referral ID],
      max(t2.Assignseqid) as AssignSeqID
INTO       #Temp3
FROM       #temp t1 (NOLOCK)
      JOIN tblAssigned t2 (NOLOCK) ON t1.[Referral ID] = t2.ReferralID
WHERE       t2.mgrEnddate is not null
group by t1.[Referral ID]



/* Step 3 */
--This gets the YouthLastJPO.
SELECT       t2.Referralid,
      isnull(isnull(UnitTitle,'') + ": " + isnull(LastName,'') + ", " + isnull(Firstname,'')
       + ": " + isnull(stuff( stuff( stuff( stuff( t3.staffphone,1 ,0, '('), 5, 0, ')'), 6, 0, ' '), 10, 0, '-'),''), '') as YouthLastJPO
INTO       #Temp4
FROM       #temp3 t1 (NOLOCK)
      JOIN tblAssigned t2 (NOLOCK) ON t1.AssignSeqID = t2.AssignSeqID
      JOIN tblStaff t3 (NOLOCK) ON t2.EmployeeID = t3.EmployeeID
      JOIN tblMgtUnit t4 (NOLOCK) ON t3.mgtUnitID = t4.mgtUnitID

/* Step 4 */
--This updates the YouthLastJPO.
update       #temp
set       YouthLastJPO = t2.YouthLastJPO
from       #temp t1
      join #temp4 t2 on t1.[referral id] = t2.referralid

/* Step 5 */
--This is the Current Employee.  This will also be the YouthLastJPO if there were no
--other JPO's that handled the referral.
SELECT       isnull(isnull(UnitTitle,'') + ": " + isnull(LastName,'') + ", " + isnull(Firstname,'')
       + ": " + isnull(stuff( stuff( stuff( stuff( t3.staffphone,1 ,0, '('), 5, 0, ')'), 6, 0, ' '), 10, 0, '-'),''), '') as YouthLastJPO
INTO       #Temp5
FROM       tblStaff t3 (NOLOCK)
      JOIN tblMgtUnit t4 (NOLOCK) ON t3.mgtUnitID = t4.mgtUnitID
WHERE      t3.employeeid = 32--@employeeid

/* Step 6 */
--This updates the remaining YouthLastJPO with the current JPO.
update #temp
set YouthLastJPO = #temp5.YouthLastJPO
from       #temp5
where      #temp.YouthLastJPO is null

/* Step 7 */
--return everything
SELECT * FROM #temp
0
Question by:JeffDrummond
    14 Comments
     
    LVL 12

    Expert Comment

    by:kselvia
    Can you run each step individually in query analyzer (preferably with set statistics profile on) and identify the part(s) that need improvement?  Show us the query plan(s) for the slow parts.
    0
     
    LVL 6

    Expert Comment

    by:Duane Lawrence
    1. Check to see if there are indexes on the where parameters, for example.

    tblstaff.mgtunitid
    tblstaff.employeeid
    tblmgtunit.mgtunitid

    WHERE   a.EmployeeID = @EmployeeID
    and     a.MgrEndDate IS NULL
    and     s.TerminatDate IS NULL

    2. Find out if table scans are going on.

    3. Find out if the execution plans are failing to be created, if so update your statistics.

    4. Do you have maintainance plans running?  You should.  Are they succeeding?

    D


    0
     

    Author Comment

    by:JeffDrummond
    I've rewritten the procedure to create temp tables first and then use INSERT INTO
    instead of SELECT INTO.  I'm not sure yet if that will make a difference but I've read that
    is more efficient.

    KSelvia:

    Do you mean execution plan instead of query plan?  I'm not sure what a query plan is.

    duanelawrence:

    1.  Yes, there are indexes on the columns in the where clause.
    2.  When I run the execution plan, it does show table scans occurring
         on every step.
    3.  DBA says the execution plans are being created and statistics are updated.
    4.  Maintenance plans are run regularly.

    THIS IS THE LATEST VERSION:

    alter       PROCEDURE  dbo.dnReferralsGetByJPO1021
    @EmployeeID Int
    AS
    SET NOCOUNT ON
    declare @YouthLastJPO       varchar(250)
    declare @SharedWith      varchar(250)

    /* Step 1 */
    --Get the active casetypes assigned to the JPO
    CREATE TABLE #tdnReferralsGetByJPO1 (
          CASETYPEID      INT)

    CREATE INDEX IDXCASETYPEID ON #tdnReferralsGetByJPO1(CASETYPEID)

    INSERT INTO #tdnReferralsGetByJPO1
    SELECT      casetypeid
    FROM      tblcaseload (NOLOCK)
    WHERE   employeeid = @EmployeeID
    AND       Active = 1

    --get the referrals assigned to the JPO
    CREATE TABLE #tdnReferralsGetByJPO2 (
          ASSIGNSEQID                  INT,
          [YOUTH NAME]                  VARCHAR(100),
          ASSIGNEDDATE                  VARCHAR(12),
          ENDDATE                        VARCHAR(12),
          REFERRALID                  INT,
          CASETYPE                  VARCHAR(100),
          NBRREFS                        INT,
          ASSIGNEDBY                  VARCHAR(250),
          DJJID                        INT,
          ASSIGNLASTEMPLOYEEIDUPDATE      INT,
          SHAREDWITH                  VARCHAR(250),
          YOUTHLASTJPO                  VARCHAR(250),
          YOUTHZIP                  VARCHAR(15),
          MGTUNITID                  INT,
          CASETYPEID                  INT,
          JPOCARRIESCASETYPE            VARCHAR(5))

    CREATE INDEX IDXREFERRALID ON #tdnReferralsGetByJPO2(REFERRALID)
          
    INSERT INTO #tdnReferralsGetByJPO2
    SELECT      a.AssignSeqID,
          ISNULL(y.YthLastName,'') + ', ' + ISNULL(y.YthFirstName,'') + ' ' + ISNULL(y.YthMiddleName,''),
          CONVERT(varchar(12), a.MgrBeginDate, 101),
          CONVERT(varchar(12), a.MgrEndDate, 101),
          a.ReferralID,
          ct.CaseDescript,
          nrv.NbrRefs,
          ISNULL(s.LastName,'') + ', ' + ISNULL(s.FirstName,'') + ' ' + ISNULL(s.MiddleName,''),
          a.DJJID AS DJJID,
          a.LastEmployeeIDUpdate as AssignLastEmployeeIDUpdate,
          SharedWith = @SharedWith,      
          YouthLastJPO = @YouthLastJPO,
          t3.currlivzip1 as YouthZIP,
          a.MgtUnitID,
          a.CaseTypeID,
          JPOCarriesCaseType = CASE WHEN (a.casetypeid in (SELECT casetypeid FROM #tdnReferralsGetByJPO1))
                            THEN 'Yes'
                            ELSE 'No'
                      END

    FROM       tblAssigned a (NOLOCK)
          JOIN tblReferral r (NOLOCK) on a.ReferralID = r.ReferralID
          LEFT OUTER JOIN View_ReferralID_tblAssigned nrv (NOLOCK) ON r.ReferralID = nrv.ReferralID
          JOIN tblYouth y (NOLOCK) on y.DJJID = r.DJJID
          JOIN tblCaseType ct (NOLOCK) ON a.CaseTypeID = ct.CaseTypeID
          JOIN tblStaff s (NOLOCK) ON A.LastEmployeeIDUpdate = s.EmployeeID
          join tblYthMember t2  (NOLOCK) on y.djjid = t2.djjid and t2.RelTypeID=22 and t2.MbrEndDate IS NULL
          left outer join  tblYthCurrAddress t3  (NOLOCK) on t2.AddrSeqID = t3.AddrSeqID

    WHERE      
          a.EmployeeID = @EmployeeID
    and      a.MgrEndDate IS NULL
    and      s.TerminatDate IS NULL

    ORDER BY 2,3


    /* Step 2 */
    --Max AssignSeqID for all the above referrals closed Assigned records
    CREATE TABLE #tdnReferralsGetByJPO3 (
          REFERRALID      INT,
          ASSIGNSEQID      INT)

    CREATE INDEX IDXREFERRALID ON #tdnReferralsGetByJPO3(REFERRALID)

    INSERT INTO #tdnReferralsGetByJPO3
    SELECT       t1.ReferralID,
          max(t2.Assignseqid)
    FROM       #tdnReferralsGetByJPO2 t1 (NOLOCK)
          JOIN tblAssigned t2 (NOLOCK) ON t1.ReferralID = t2.ReferralID
    WHERE       t2.mgrEnddate is not null
    group by t1.ReferralID



    /* Step 3 */
    --This gets the YouthLastJPO.
    CREATE TABLE #tdnReferralsGetByJPO4 (
          REFERRALID            INT,
          YOUTHLASTJPO            VARCHAR(250))

    CREATE INDEX IDXREFERRALID ON #tdnReferralsGetByJPO4(REFERRALID)

    INSERT INTO #tdnReferralsGetByJPO4
    SELECT       t2.Referralid,
          isnull(isnull(UnitTitle,'') + ": " + isnull(LastName,'') + ", " + isnull(Firstname,'')
           + ": " + isnull(stuff( stuff( stuff( stuff( t3.staffphone,1 ,0, '('), 5, 0, ')'), 6, 0, ' '), 10, 0, '-'),''), '')
    FROM       #tdnReferralsGetByJPO3 t1 (NOLOCK)
          JOIN tblAssigned t2 (NOLOCK) ON t1.AssignSeqID = t2.AssignSeqID
          JOIN tblStaff t3 (NOLOCK) ON t2.EmployeeID = t3.EmployeeID
          JOIN tblMgtUnit t4 (NOLOCK) ON t3.mgtUnitID = t4.mgtUnitID


    /* Step 4 */
    --This updates the YouthLastJPO.
    update       #tdnReferralsGetByJPO2
    set       YouthLastJPO = t2.YouthLastJPO
    from       #tdnReferralsGetByJPO2 t1 (NOLOCK)
          join #tdnReferralsGetByJPO4 t2 (NOLOCK) on t1.referralid = t2.referralid

    /* Step 5 */
    --This is the Current Employee.  This will also be the YouthLastJPO if there were no
    --other JPO's that handled the referral.
    CREATE TABLE #tdnReferralsGetByJPO5 (
          YOUTHLASTJPO      VARCHAR(250))

    CREATE INDEX IDXYOUTHLASTJPO ON #tdnReferralsGetByJPO5(YOUTHLASTJPO)

    INSERT INTO #tdnReferralsGetByJPO5
    SELECT       isnull(isnull(UnitTitle,'') + ": " + isnull(LastName,'') + ", " + isnull(Firstname,'')
           + ": " + isnull(stuff( stuff( stuff( stuff( t3.staffphone,1 ,0, '('), 5, 0, ')'), 6, 0, ' '), 10, 0, '-'),''), '')
    FROM       tblStaff t3 (NOLOCK)
          JOIN tblMgtUnit t4 (NOLOCK) ON t3.mgtUnitID = t4.mgtUnitID
    WHERE      t3.employeeid = @employeeid

    /* Step 6 */
    --This updates the remaining YouthLastJPO with the current JPO.
    update #tdnReferralsGetByJPO2
    set YouthLastJPO = #tdnReferralsGetByJPO5.YouthLastJPO
    from       #tdnReferralsGetByJPO5 (NOLOCK)
    where      #tdnReferralsGetByJPO2.YouthLastJPO is null

    /* Step 7 */
    --get the REFERRALS THAT ARE SHARED

    CREATE TABLE #tdnReferralsGetByJPO6 (
          REFERRALID      INT,
          EMPLOYEEID      INT,
          MGTUNITID      INT,
          NAMEUNIT      VARCHAR(200))

    CREATE INDEX IDXREFERRALID ON #tdnReferralsGetByJPO6(REFERRALID)
    --GET THE REFERRALS FROM #tdnReferralsGetByJPO2
    INSERT INTO #tdnReferralsGetByJPO6
    SELECT       A.REFERRALID,
                A.EMPLOYEEID,
                A.MGTUNITID,      
                @SHAREDWITH      
    FROM      tblAssigned A (NOLOCK)
          JOIN #tdnReferralsGetByJPO2 B on A.REFERRALID = B.REFERRALID
    WHERE      A.EMPLOYEEID <> @EMPLOYEEID
    AND       A.MGRENDDATE IS NULL

    UPDATE #tdnReferralsGetByJPO6
    SET NAMEUNIT = (Select ISNULL(s.LastName,'') + ', ' + ISNULL(s.FirstName,'') + ' ' + ISNULL(s.MiddleName,'') + ' - ' + isnull(m.UnitTitle,'')
                      from tblstaff s (NOLOCK)
                      left join tblmgtunit m (NOLOCK) on s.mgtunitid = m.mgtunitid
                      WHERE S.EMPLOYEEID = #tdnReferralsGetByJPO6.EMPLOYEEID)


    /* Step 8 */
    UPDATE       #tdnReferralsGetByJPO2
    SET      SHAREDWITH = #tdnReferralsGetByJPO6.NAMEUNIT
    FROM       #tdnReferralsGetByJPO2
          JOIN #tdnReferralsGetByJPO6 ON #tdnReferralsGetByJPO2.REFERRALID = #tdnReferralsGetByJPO6.REFERRALID

    /* Step 9 */
    --return everything
    SELECT * FROM #tdnReferralsGetByJPO2
     

    /* Step 8 */
    --cleanup
    DROP TABLE #tdnReferralsGetByJPO1
    DROP TABLE #tdnReferralsGetByJPO2
    DROP TABLE #tdnReferralsGetByJPO3
    DROP TABLE #tdnReferralsGetByJPO4
    DROP TABLE #tdnReferralsGetByJPO5
    0
     
    LVL 6

    Assisted Solution

    by:Duane Lawrence
    Yes, I ment execution plan.

    I think you missed a very good suggestion from kselvia, I knew that, I smacked my forehead when I read it, to easy.

    Open query analyser and copy the below line.

    set statistics profile on

    Now run your proc in pieces.  Then post the execution plan here.


    0
     

    Author Comment

    by:JeffDrummond
    There's so much information in the statistics profile.  How do you read this stuff?  What should I be looking for?
    0
     

    Author Comment

    by:JeffDrummond
    When I run the entire procedure, there is one point where
    an INDEX SCAN is performed on the largest table in the database, with 560,000 rows.

    When I run the procedure in pieces, the same portion that does the INDEX SCAN
    instead perfoms an INDEX SEEK that affects only 220 rows.  



    0
     

    Author Comment

    by:JeffDrummond
    When I run the procedure as a whole, there is one point where it performs
    an INDEX SCAN on the largest table in the database, tblYouth, with
    estimated row count at 558,000.  From the execution plan is says
    it is using this argument:
     
    OBJECT:([COMREG].[DBO].[TBLYOUTH].[YTHDJJIDNDX] ON [Y])
    ORDERED FORWARD
     
    When I run the procedure in pieces, at the same point it performs an
    INDEX SEEK on the same table with estimated row count 220, which
    is the correct result set.  From the execution plan is says
    it is using this argument:
     
    OBJECT:([COMREG].[DBO].[TBLYOUTH].[YTHDJJIDNDX] ON [Y]),
    SEEK ([Y].[DJJID]) = [R].[DJJID] ORDERED FORWARD
    0
     
    LVL 6

    Expert Comment

    by:Duane Lawrence
    Blow away that index and recreate it.  Then try it again as a whole and in pieces.

    Duane
    0
     
    LVL 12

    Accepted Solution

    by:
    An index scan on 560,000 rows is not necessarily worse than a seek for 220 rows. It depends on how many times that is done. The statistics plan will show that.  Post the first 3 columns of that output here. (Those will be rows, executes, and plan)  (Save it and import fixed width into Excel or use a text editor with column-cut-paste if you have one)

    Multiply rows*executes and find the highest entries.  See how to reduce those numbers by changing joins, search args etc.

    Also run "set statistics io on" and look for high physical io, then for high logical io. Reduce those counts by creating (or sometimes removing) temp tables. Show us that output too.

    0
     

    Author Comment

    by:JeffDrummond
    This is the first three columns from the statistics for the section doing the heaviest lifting.
    The other sections seem to be irrelevant.

    220      1      INSERT INTO #tdnReferralsGetByJPO2  SELECT a.AssignSeqID,   YOUTHNAME = (SELECT YOUTHNAME FROM #tdnReferralsGetByJPO7 where djjid = a.djjid),   --ISNULL(y.YthLastName,'') + ', ' + ISNULL(y.YthFirstName,'') + ' ' + ISNULL(y.YthMiddleName,''),   CONVERT(varc
    220      1        |--Index Insert(OBJECT:([tempdb].[dbo].[#tdnReferralsGetByJPO2]), SET:([IdxBmk1030]=[Bmk1021], [REFERRALID1031]=[#tdnReferralsGetByJPO2].[REFERRALID]))
    220      1             |--Sort(ORDER BY:([#tdnReferralsGetByJPO2].[REFERRALID] ASC, [Bmk1021] ASC))
    220      1                  |--Table Insert(OBJECT:([tempdb].[dbo].[#tdnReferralsGetByJPO2]), SET:([#tdnReferralsGetByJPO2].[CASETYPEID]=[a].[CaseTypeID], [#tdnReferralsGetByJPO2].[MGTUNITID]=[a].[MgtUnitID], [#tdnReferralsGetByJPO2].[ASSIGNLASTEMPLOYEEIDUPDATE]=[a].[Last
    220      1                       |--Top(ROWCOUNT est 0)
    220      1                            |--Parallelism(Gather Streams, ORDER BY:([Expr1023] ASC, [Expr1012] ASC))
    220      4                                 |--Sort(ORDER BY:([Expr1023] ASC, [Expr1012] ASC))
    220      4                                      |--Compute Scalar(DEFINE:([Expr1023]=[Expr1023], [Expr1012]=Convert([a].[MgrBeginDate]), [Expr1013]=Convert([a].[MgrEndDate]), [Expr1014]=Convert([ct].[CaseDescript]), [Expr1015]=Convert([Expr1002]), [Expr1016]=Convert(isnul
    220      4                                           |--Nested Loops(Left Semi Join, OUTER REFERENCES:([a].[CaseTypeID]), DEFINE:([Expr1024] = [PROBE VALUE]))
    220      4                                                |--Sort(ORDER BY:([a].[CaseTypeID] ASC))
    220      4                                                |    |--Parallelism(Repartition Streams, PARTITION COLUMNS:([a].[CaseTypeID]))
    220      4                                                |         |--Nested Loops(Left Outer Join, OUTER REFERENCES:([a].[DJJID]))
    220      4                                                |              |--Parallelism(Repartition Streams, PARTITION COLUMNS:([a].[DJJID]))
    220      4                                                |              |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([r].[ReferralID]))
    220      4                                                |              |         |--Parallelism(Repartition Streams, PARTITION COLUMNS:([r].[ReferralID]))
    220      4                                                |              |         |    |--Merge Join(Inner Join, MERGE:([y].[DJJID])=([r].[DJJID]), RESIDUAL:([y].[DJJID]=[r].[DJJID]))
    333      4                                                |              |         |         |--Parallelism(Repartition Streams, PARTITION COLUMNS:([y].[DJJID]), ORDER BY:([y].[DJJID] ASC), WHERE:(PROBE([Bitmap1037])=TRUE))
    558570      4                                                |              |         |         |    |--Clustered Index Scan(OBJECT:([Comreg].[dbo].[tblYouth].[PK_tblYouth] AS [y]), ORDERED FORWARD)
    220      4                                                |              |         |         |--Sort(ORDER BY:([r].[DJJID] ASC))
    220      4                                                |              |         |              |--Bitmap(HASH:([r].[DJJID]), DEFINE:([Bitmap1037]))
    220      4                                                |              |         |                   |--Parallelism(Repartition Streams, PARTITION COLUMNS:([r].[DJJID]))
    220      4                                                |              |         |                        |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[ReferralID]) WITH PREFETCH)
    220      4                                                |              |         |                             |--Hash Match(Inner Join, HASH:([ct].[CaseTypeID])=([a].[CaseTypeID]), RESIDUAL:([a].[CaseTypeID]=[ct].[CaseTypeID]))
    120      4                                                |              |         |                             |    |--Parallelism(Broadcast)
    30      1                                                |              |         |                             |    |    |--Clustered Index Scan(OBJECT:([Comreg].[dbo].[tblCaseType].[PK_tblCaseType] AS [ct]))
    220      4                                                |              |         |                             |    |--Merge Join(Inner Join, MERGE:([s].[EmployeeID])=([a].[LastEmployeeIDUpdate]), RESIDUAL:([a].[LastEmployeeIDUpdate]=[s].[EmployeeID]))
    3      4                                                |              |         |                             |         |--Parallelism(Repartition Streams, PARTITION COLUMNS:([s].[EmployeeID]), ORDER BY:([s].[EmployeeID] ASC), WHERE:(PROBE([Bitmap1036])=TRUE))
    6065      4                                                |              |         |                             |         |    |--Clustered Index Scan(OBJECT:([Comreg].[dbo].[tblStaff].[PK_tblStaff] AS [s]),  WHERE:([s].[TerminatDate]=NULL) ORDERED FORWARD)
    220      4                                                |              |         |                             |         |--Sort(ORDER BY:([a].[LastEmployeeIDUpdate] ASC))
    220      4                                                |              |         |                             |              |--Bitmap(HASH:([a].[LastEmployeeIDUpdate]), DEFINE:([Bitmap1036]))
    220      4                                                |              |         |                             |                   |--Parallelism(Repartition Streams, PARTITION COLUMNS:([a].[LastEmployeeIDUpdate]))
    220      4                                                |              |         |                             |                        |--Bookmark Lookup(BOOKMARK:([Bmk1004]), OBJECT:([Comreg].[dbo].[tblAssigned] AS [a]))
    220      4                                                |              |         |                             |                             |--Index Seek(OBJECT:([Comreg].[dbo].[tblAssigned].[AssignedEmpID_Ndx] AS [a]), SEEK:([a].[EmployeeID]=[@EmployeeID] AND [a].[Mgr
    220      220                                                |              |         |                             |--Clustered Index Seek(OBJECT:([Comreg].[dbo].[tblReferral].[PK_tblReferral] AS [r]), SEEK:([r].[ReferralID]=[a].[ReferralID]) ORDERED FORWARD)
    220      220                                                |              |         |--Hash Match(Cache, HASH:([r].[ReferralID]), RESIDUAL:([r].[ReferralID]=[r].[ReferralID]))
    217      217                                                |              |              |--Stream Aggregate(DEFINE:([Expr1002]=Count(*)))
    224      217                                                |              |                   |--Index Seek(OBJECT:([Comreg].[dbo].[tblAssigned].[AssignedReferral_Ndx]), SEEK:([tblAssigned].[ReferralID]=[r].[ReferralID] AND [tblAssigned].[MgrEndDate]=NULL) ORDERED FORWARD)
    220      220                                                |              |--Hash Match(Cache, HASH:([a].[DJJID]), RESIDUAL:([a].[DJJID]=[a].[DJJID]))
    100      100                                                |                   |--Assert(WHERE:(If ([Expr1022]>1) then 0 else NULL))
    100      100                                                |                        |--Stream Aggregate(DEFINE:([Expr1022]=Count(*), [Expr1023]=ANY([#tdnReferralsGetByJPO7].[YOUTHNAME])))
    100      100                                                |                             |--Index Spool(SEEK:([#tdnReferralsGetByJPO7].[DJJID]=[a].[DJJID]))
    100      1                                                |                                  |--Table Scan(OBJECT:([tempdb].[dbo].[#tdnReferralsGetByJPO7]))
    211      220                                                |--Row Count Spool
    6      8                                                     |--Index Seek(OBJECT:([tempdb].[dbo].[#tdnReferralsGetByJPO1]), SEEK:([#tdnReferralsGetByJPO1].[CASETYPEID]=[a].[CaseTypeID]) ORDERED FORWARD)
    0
     
    LVL 12

    Expert Comment

    by:kselvia
    I hate to say it but that looks pretty good to me. I doubt the entire thing takes over 3 seconds.  Maybe it degrades when combined with other parts of the query.

    Can we see that output for the entire procedure?
    0
     

    Author Comment

    by:JeffDrummond
    What I posted last time is the output for the stored procedure as I have
    re-written it.  I went back and ran the original stored procedure with
    statistics on and there is a significant difference in the amount of processing.

    I've got it now running now in about 2.5 seconds and the DBA thinks it's
    in good shape.  We'll see after the testers get at it :-)

    0
     

    Author Comment

    by:JeffDrummond
    kselvia, you were spot on in your observation.  Under stress testing the procedure is returning consistently in 3 seconds.  Thanks to you and duanelaurence for pointing me in the right direction.
    0
     
    LVL 12

    Expert Comment

    by:kselvia
    Glad to help.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Prepare to Pass the CompTIA A+ 900 Series Exam

    CompTIA aims to adapt its A+ Certification to reflect the most current knowledge and skills needed by today's IT professionals--and this year's 2016 exam is harder than ever. This certification is one of the most highly-respected and sought after in IT.

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    860 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

    15 Experts available now in Live!

    Get 1:1 Help Now