SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Begin...Commit Tran in select statement		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

debug
Starting Member

India
29 Posts
Posted - 06/30/2003 :  00:17:30  Show Profile  Reply with Quote
Hi All,

Please let me know if the following script can pose an overhead or extra cost in an OLTP enviornment :

CREATE PROC Select_Grid_Refresh
@vCloasID varchar(8) AS
BEGIN TRAN
SELECT DISTINCT
TControl.TxnID,
TControl.UserID,
TControl.TxnDate,
TControl.CoID,
TControl.TxnKey,
TControl.TxnAmount,
TControl.TxnCalcPrem,
TStatus.TableID,
TStatus.StatusID
FROM TControl, TStatus
WHERE TControl.CloasID = @vCloasID AND TControl.TxnID = TStatus.TxnID
ORDER BY TControl.TxnDate, TControl.TxnID, TStatus.TableID
COMMIT TRAN

Also, please let me know what i can do to optimise it.




DU

nr
SQLTeam MVY

United Kingdom
12543 Posts
Posted - 06/30/2003 :  00:57:50  Show Profile  Visit nr's Homepage  Reply with Quote
a begin/commit tran doesn't do anything for a single statement as every statement is atomic.
It doesn't do anything for a select only updates (unless you include locking hints).

Depends on the system whether or not this could cause problems. How much data is it selecting? Are there indexes to support the query?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000