Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sridhar3004
Starting Member
34 Posts |
 Posted - 2013-05-10 : 09:44:47
|
| Hi,We've a query that is frequently used in the applicationThe 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 secondWarm RegardsSridhar |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-10 : 10:11:51
|
| 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. |
 |
|
|
sridhar3004
Starting Member
34 Posts |
Posted - 2013-05-11 : 03:06:34
|
| Thank you very much for your reply.Let me inspect a few things before I take a final call |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-11 : 03:17:22
|
| 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. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-11 : 16:52:39
|
| 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? |
|
|
|
|
|
|
|
|
Using Transaction in Select Statements