stored proc performanace

Question

stored proc performanace

jim.rasmussen

SSC Eights!

Points: 999
More actions
February 5, 2010 at 10:29 am

#215385

This may be a simple question. I have procs that selects from a table that has about 2,000,000 records. The process of the select statement takes a long time to return, about 60 sec. What can I do to improve this performance.
my proc is:
SELECT DISTINCT WO.HMY,
CASE RTRIM(WO.SBRIEFDESC) WHEN 'Boiler Check' THEN RTRIM(WO.SBRIEFDESC) + ' - ' + SUBSTRING(P.SADDR2,1,CHARINDEX(' ',P.SADDR2)) ELSE RTRIM(WO.SBRIEFDESC) END SBRIEFDESC,
DTCALL,
CASE LEFT(WO.SBRIEFDESC,1) WHEN '!' THEN '!' + P.SCODE + '|' + ISNULL(U.SCODE,'') ELSE P.SCODE+'|'+ISNULL(U.SCODE,'') END AS LOCATION,
DATEDIFF(DAY,WO.DTCALL,GETDATE()) AS OUTSTANDING
FROM
MM2WO WO (NOLOCK)
LEFT OUTER JOIN MM2WODET WOD (NOLOCK) ON WO.HMY = WOD.HWO
JOIN PROPERTY P ON WO.HPROPERTY = P.HMY
LEFT OUTER JOIN UNIT U ON WO.HUNIT = U.HMY
WHERE
WO.SUSER1 = @SCODE
AND WO.SSTATUS NOT IN ('WORK COMPLETED', 'CANCELED' )
AND WO.SCODE <= 'A'
AND WO.DTCALL < GETDATE()+ 1
ORDER BY
OUTSTANDING DESC
MM2WODET has the 2,000,000 records and MM2WO has about 750,000
Thanks
Jim

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

Jim,

There are a lot of things that can affect performance. As a result, we need more information. Take a look at the article in the 2nd link in my signature below for what those items are. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jim.rasmussen SSC Eights! Points: 999 More actions · Answer 2

Here is the information. Pleae let me know if there is anything else.

Thanks fir looking into this.

Jim