Begin and Commit Transactions in Stored Procs

[nicatt]

The last place I worked the DBA said that using the Begin and Commit transactions when selecting into #temp tables helped speed execution of the proc. Therefore it became a standard to do so.

I presently work for a company that says it does nothing since, which is rightly noted, #temp tables are not logged. So, using the Begin/Commit statements do not add nor take away from the query. Using these statements have become a habit with me. Is there any value in continuing this practice? It is my choice if I wish to.

Thanks in advance.

ps. I am interested in finding more 'tuning' tips. I would appreciate direction to a site or a good book. I work almost exclusively with stored procs, Crystal 8 and Visual Basic.

Bob

 

[qyllr]

hello,

yes, there is great value in your practice... i work in a production environment... for me this is good practice when i have to update tables... especially when i am running updates scripts on thousands of rows... it gives me a chance to validate my updates before i commit...

i think it's good to be a bit paranoid when working with databases... data is crucial...

so i always do the following:

backup
bcp out
begin tran

cheers,
q.

 

[Tim1]

Hi Bob,

Where I work, even on the smallest transactions, and with temp tables, it is our production standard that we begin and commit either side of every transaction. This is especially important when working on live tables, as it means that logs and locks are cleared down at every transaction.

I personally think that it is a good idea always to begin and commit either side of any DML............

As to tuning tips, check the Sybase web site (

http://www.sybase.com),

they have some excellent on-line books, including tuning tips for each different version of Sybase.

Tim