Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Suggestions to improve performance

Posted on 2004-10-21
14
Medium Priority
?
270 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
Comment
Question by:JeffDrummond
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
14 Comments
 
LVL 12

Expert Comment

by:kselvia
ID: 12371826
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
ID: 12371980
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
ID: 12374981
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 6

Assisted Solution

by:Duane Lawrence
Duane Lawrence earned 800 total points
ID: 12375994
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
ID: 12380381
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
ID: 12381031
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
ID: 12381618
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
ID: 12383464
Blow away that index and recreate it.  Then try it again as a whole and in pieces.

Duane
0
 
LVL 12

Accepted Solution

by:
kselvia earned 1200 total points
ID: 12383491
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
ID: 12384714
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
ID: 12385175
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
ID: 12385495
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
ID: 12443845
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
ID: 12443982
Glad to help.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

636 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