December 3, 2008 at 10:51 am
I have a stored procedure that creates a dynamic query, then executes it as follows:
Begin Tran
Execute (@Query)
Commit
The query itself populates 2 table variables, and then queries them to provide a result set. Normally all is fine, however, with a larger result set in the table variables, it takes much longer. It is not a massive set of data, but takes over a minute. However, if I remove the Begin Tran and Commit lines it works super fast.
No updates take place, so is there a good reason why it is performed as a transaction?
December 3, 2008 at 12:50 pm
Not intending to bump my post, but...
Just to clarify, I have inherited a product which makes extensive use of stored procedures. Would I be right in thinking that the original developer placed the Execute statement inside a transaction as the dynamic SQL does several Insert statements to populate the table variables? And, if so, is this mandatory?
December 3, 2008 at 1:49 pm
always try to create your tempdb objects outside of a transaction (locking on tempdb)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 3, 2008 at 2:06 pm
Thanks ALZDBA. Basically, the stored procedure builds up a string based on various criteria, then simply runs it as follows:
.
. code to build @Query string
.
Begin Tran
Execute (@Query)
Commit
Within the string, it creates 2 table variables to facilitate a subsequent Select statement which returns the results. There are inserts into these table variables as required.
But my question is, is it necessary for this to be executed as part of a transaction? There are no updates or deletes, just a few inserts into table variables. And the reason for the question is that it takes 1 minute 3 seconds to run as it is, but if I comment out the Begin & Commit statements, it takes a few seconds.
December 3, 2008 at 2:06 pm
Executing of dynamic SQL involves compiling of the SQL.
During compilation SQL Server locks system tables. And does not allow other compilation requests to pass through until the transaction is over.
Because you create temp objects you lock system tables in tempdb as well.
Place BEGIN/END TRANSACTION statements inside of dynamic SQL and only where it's really needed.
And get rid of dynamic SQL as much as possible.
_____________
Code for TallyGenerator
December 3, 2008 at 2:17 pm
Whether there is any need for the transaction depends on the query. You may be able to re-write the query so that you don't need the transaction, or you may have never needed it. The transactions are likely there (in my opinion) to ensure that the data cannot be changed while it is being worked with. That said, it may be completely unnecessary.
To start, ask yourself what would happen if someone updated one of the values you were working with while your stored procedure was in the middle of processing. Would that lead to incorrect output?
December 3, 2008 at 2:25 pm
The other question to ask is why dynamic SQL?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 3, 2008 at 2:35 pm
If the only thing the process is doing is inserting data into table variables and/or temporary tables, and then selecting from those tables - then there is no reason to wrap the process in a transaction.
Furthermore, table variables are not part of a transaction - so it doesn't really make sense to wrap the inserts into table variables in a transaction. However, temporary tables are part of the transaction and will be rolled back or committed.
It really depends on what is actually being done by the dynamic SQL. If the process is inserting/updating data in permanent tables where there are more than one insert/update operation occurring - then you probably need the transaction. Does not sound like that is what you are doing, so I would guess that you don't need this in a transaction.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 3, 2008 at 5:36 pm
Well thanks for all your replies, and I think Jeffrey's has hit the nail on the head. The only inserts are to table variables, no other updates or deletes. and then just a Select from these 2 table variables. Like I mentioned, I have inherited this project and this is just one of many problems. When I found an unrelated problem with this stored procedure, I assumed that I was missing something since it was wrapped up in a transaction. It may have originally been written for the predecessor of SQL Server 200, don't know if that helps explain why it was written so.
Also, a few people have asked why 'dynamic SQL'. Well, it is the nature of the beast, the generated query depends upon the role of the logged in user for this application, and the stored procedure isn't pretty, believe me. I'll post it tomorrow when I am back at work, along with the generated query when logged in as a particular role.
But once again, many thanks for all your replies, I have recently become a reluctant small database DBA and developer without any training, but have learnt so much thanks to this website and a book or two.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply