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 2005 Forums
 Transact-SQL (2005)
 Using Transaction in Select Statements
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sridhar3004
Starting Member

34 Posts

Posted - 05/10/2013 :  09:44:47  Show Profile  Reply with Quote
Hi,

We've a query that is frequently used in the application
The query is a select statement query involving joins to multiple tables and is enclosed in a TRANSACTION block. The syntax is correct

Since the query is used often, by the end of the day, we get timeout error.

Is it possible that because the query uses the TRANSACTION keyword this could happen??? Otherwise the query fetches the result in less than a second


Warm Regards
Sridhar

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 05/10/2013 :  10:11:51  Show Profile  Reply with Quote

It's possible that it is the transaction that is causing the timeouts. When you do a select a row from a table, it acquires a shared lock on the row (unless you specify otherwise). That would prevent any updates to that row (unless row versioning is enabled, which is not by default), because an update requires an exclusive lock, which is incompatible with everything else. If it is a readonly database (i.e., there are no updates to the tables involved in the selects), the transaction should not have an impact (unless you are acquiring update locks deliberately).

First thing to look at might be to see if it is a set of select statements, do you really need the transaction. I am not saying you don't - just inspect whether you really need it.

You might also consider enabling row versioning: http://msdn.microsoft.com/en-us/library/ms175095(v=sql.90).aspx However, that should be done with care because it can adversely affect the tempdb performance and space usage.


Go to Top of Page

sridhar3004
Starting Member

34 Posts

Posted - 05/11/2013 :  03:06:34  Show Profile  Reply with Quote
Thank you very much for your reply.
Let me inspect a few things before I take a final call
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/11/2013 :  03:17:22  Show Profile  Visit russell's Homepage  Reply with Quote
Tough to imagine a SELECT statement that requires or could benefit from an explicit transaction.

If it is a single SELECT, no matter how many joins, then 100% sure it is unnecessary and likely causing performance problems.

If there are multiple SELECT statements in a stored proc or batch (but only SELECTs) certainly you can and should remove the transaction. Though the proc may need to be re-written.

In short, the explicit transaction is probably causing performance problems.

Edited by - russell on 05/11/2013 03:20:11
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 05/11/2013 :  16:52:39  Show Profile  Reply with Quote
Russell, I agree with everything you said - I assumed that there were multiple statements (even though OP said "a select statement"). Then, I stretched my imagination to think that there might be tablock or updlock or something similar on some of the select statements.

On the other hand, if it is a single select statement preceded by a begin tran and succeeded by a commit, wouldn't that be effectively equivalent to the the implicit transaction that SQL Server would have done anyway?
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.09 seconds. Powered By: Snitz Forums 2000