Newbie question on SQL code best practice

Team
I wrote following code
Create PROCEDURE asp_nykl_Full_Update_605ProcStat
--@.sku_barcode varchar(12)
AS
--declare @.Err1 int
--begin transaction
UPDATE pix_tran
SET proc_stat_code = 90
FROM ITEM_MASTER
WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
--AND sku_brcd=@.sku_barcode
AND TRAN_TYPE = '605'
AND proc_stat_code = 10
I have been advised that I must put
1. begin and end transaction
2. Must have SELECT ... (UPDLOCK) before update statement
3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
Is it always advisable to do so?
Thanks
D Goyal (goyald@.gmail.com) writes:
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
As long as you only have a single update statement, that's a bit
of overkill - as long as you can be dead sure that the code is running
with implicit_transactions off. This setting is indeed off by default,
but if the procedure is invoked remotely, this is not so. So BEGIN/END
would make it a little safer.

> 2. Must have SELECT ... (UPDLOCK) before update statement
I don't really see the point with this here.

> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is indeed recommendable, as without setting SQL Server
produces a rowcount about affected rows which clients more often does
not care about than they do. In fact, our load tool automatically inserts
a SET NOCOUNT ON in all our stored procedures.
SET LOCK_TIMEOUT I can't really comment on, as this is more tied to
business rules. It prevents the procedure from being locked forever,
but then again what should you do if you time out?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||D Goyal wrote:
> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
You can, but it's not always necessary. SQL Server will run that single
statement in a transaction for you if you leave off the begin
tran/commit. Autocommit mode is the default, but if you have standards
in place, you can start a transaction and check @.@.ERROR after each DML
statement and subsequently commit or rollback. It will save you some
headaches should you add a second DML statement to the procedure. In
autocommit mode (without a begin tran) if the first succeeds and the
second statement fails, the first statement still commits.

> 2. Must have SELECT ... (UPDLOCK) before update statement
It's not needed. But if I look at the next item for LOCK_TIMEOUT, I
think I see why it might have been proposed. If you set a lock timeout
to say 5 seconds and try and select the rows with an escalated lock
(would need to be in a transaction), and other processes have locks on
the required pages, the SELECT will abort. But you can do the same
without the SELECT and just leave it to the UPDATE to time out if there
is lock contention.

> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
SET NOCOUNT ON is a highly advisable addition to every stored procedure
(first line). I don't generally use a lock timeout unless I'm running
something that I need to make sure doesn't sit there forever in the case
of someone hold extended locks on the required pages.

> Is it always advisable to do so?
> Thanks
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||(1) Single statment like this does not require explicit transactions.
(2) If you are planning for any updates after a read and want to insure that
the data did not change between reads, then you need to add UPDLOCK hint in
your SELECT. If not, I do not see any need.
(3) SET NOCOUNT ON does not have any performance impact. No matter how you
set this option, the @.@.ROWCOUNT value will be affected. It simply does not
send the count as part of the result to the client.
(4) Unless you know how much time you want to wait for a blocked resource, I
would not recommend to change LOCK_TIMEOUT. Remember that this setting change
is for your connection.
"D Goyal" wrote:

> Team
> I wrote following code
> Create PROCEDURE asp_nykl_Full_Update_605ProcStat
> --@.sku_barcode varchar(12)
> AS
> --declare @.Err1 int
> --begin transaction
> UPDATE pix_tran
> SET proc_stat_code = 90
> FROM ITEM_MASTER
> WHERE ITEM_MASTER.sku_id = pix_tran.sku_id
> --AND sku_brcd=@.sku_barcode
> AND TRAN_TYPE = '605'
> AND proc_stat_code = 10
> I have been advised that I must put
> 1. begin and end transaction
> 2. Must have SELECT ... (UPDLOCK) before update statement
> 3. Should include "SET NOCOUNT ON" and "SET LOCK_TIMEOUT"
> Is it always advisable to do so?
> Thanks
>
|||satheeshks wrote:
> (3) SET NOCOUNT ON does not have any performance impact. No matter
> how you set this option, the @.@.ROWCOUNT value will be affected. It
> simply does not send the count as part of the result to the client.
I have disagree with # 3.
Using SET NOCOUNT ON can cause a improvement in some queries (batches)
as well as prevent some ADO issues caused by the rowcount information
being returned to the client.
On a test I just performed that inserts 1000 rows into a table in a
loop, the CPU and Reads were the same, but the Duration dropped from an
average of 550ms to 450ms (a 19% improvement in speed).
This test was on local SQL Server box using Query Analyzer. Results
might vary when running on a network or when ignoring the row count
results (which are displayed on screen in QA).
But I would urge the OP to set NOCOUNT ON at the very top of every
stored procedure and also as the first command after connecting should
any embedded SQL be executed from the app.
David Gugick
Quest Software
www.imceda.com
www.quest.com