SQL Server Performance Forum – Threads Archive

Tuning this sp

This sp is taking over 5 to 6 seconds .Is further optimization possible in it.
The timespan of this sp is causing timeouts for other requests in the queue from the frontend application.
here is the sp
CREATE PROCEDURE P_ORDER (
@DHWOIDBIGINT,
@DHLOANchar(15),
@DHDEPTchar(10),
@DHBNKGRPchar(3),
@DHJOBchar(4)= NULL,
@DHVDRPTDTDECIMAL(8,0) = NULL ) AS
SET NOCOUNT ON
DECLARE @PROCESS_ID INT IF @SOURCE is NULL
BEGIN INSERT INTO
DBO.ORDER_HDR_INTFC
(
DHWOID,
DHLOAN,
DHDEPT,
DHBNKGRP,
DHJOB,
DHVDRPTDT )
VALUES
(
@DHWOID,
@DHLOAN,
@DHDEPT,
@DHBNKGRP,
@DHJOB,
@DHVDRPTDT )
RETURN
END
IF @SOURCE = 1
BEGIN–IF (@DHJOB IS NULL ) AND (@SOURCE = 1) DECLARE @ERROR INT
DECLARE @PIMSORDERTYPECHAR(2) SET @PIMSORDERTYPE = @DHFORM IF @DHLTYPE = ‘1’ SET @DHLTYPE=’FHA’
IF @DHLTYPE = ‘2’ SET @DHLTYPE=’VA’
IF @DHLTYPE = ‘3’ SET @DHLTYPE=’CONV’
IF @DHLTYPE = ‘4’ SET @DHLTYPE=’UNIN’ IF (@DHJOB IS NULL) OR (@DHJOB = ”)
BEGIN
SET @DHJOB =(SELECT ISNULL(MAX(JOB_NBR),0) + 1 FROM ORDERS(NOLOCK) WHERE WORK_ORDER_ID [email protected])
END SET @PROCESS_ID = @DHWOID + @DHCOID
INSERT INTO
DBO.ORDER_HDR_INTFC
(
DHWOID,
DHLOAN,
DHDEPT,
DHBNKGRP,
DHJOB,
DHVDRPTDT
)
VALUES
(
@DHWOID,
@DHLOAN,
@DHDEPT,
@DHBNKGRP,
@DHJOB,
@DHVDRPTDT )
RETURN
END GO

---

Do you have an index on work_order_id or job_nbr?

---

also try running SQLProfiler while calling this proc, log all sp<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />tmt complete and see which ones are taking the longest (this could include trigger code if the inserts invoke triggers too…<br /><br />Cheers<br />Twan

---

Thanks Twan
it did worked

---

Why have you specified the NOLOCK hint here? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

---

Frank
This is a hint to the query optimizer so that it dosent puts any lock on the table.
Thanks

---

Frank knows that very well. Is there any reason of using that in your case? Madhivanan Failing to plan is Planning to fail

---

Yes, NOLOCK doesn’t care about any locks on a table. Therefore it’s *really* fast. However, NOLOCK is equal to READ UNCOMMITTED. So it reads and returns data that probably will never get committed. I would be careful using this hint. Consider this:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=12779 —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

---

]]>