Is SqlTransaction Overhead ?
Oct 16, 2007hi,
Is it over head to use SqlTransaction(begin, commit, rollback) for a single transaction.
am not using application block or enterprise library.
only a single insert statement.
ADVERTISEMENT
About SqlTransaction
Nov 6, 2003updatestring = "Update [Flow] set received=1 where flowid=@flowid"
insertstring = "Insert into [Flow] (barcode, copyid, floworder, recvdate, recvuser, recvdepartment) "
insertstring += "values (@barcode, @copyid, @floworder, @recvdate, @recvuser, @recvdepartment)"
is it can't use sqltransaction in the same table ???
when i not apply the sqltransaction, then the about two queries can work success, but when i apply the sqltransaction, then have an error in following.
error :
Object reference not set to an instance of an object
Source Error:
Line 278: message.Text = "Recebido o documento " & barcodetx.Text & "!!"
Line 279: Catch ex As Exception
<red>Line 280: objtransaction.Rollback()</red>
Line 281: message.Text = "Não se conseguiu registar!!"
Line 282: Finally
The SqlTransaction
Apr 4, 2006Hi,
I am getting this error message
"This SqlTransaction has completed; it is no longer usable"
What's really strange is that I am getting this error from a page where I am not even using SqlTransaction.
How can I find the root cause and solve this problem?
Help Please!
SP Update Overhead
Feb 28, 2005As part of dealing with a locking problem I am fine tuning a stored procedure
that updates a table. The application updates a row by changing every single column except for the primary key, whether 1 or all of the columns have
been modified. Although easier to code, this strikes me as taking a sledge hammer to a nut.
Could anyone tell me if there is any benefit in attempting to break this down. That is, code the stored procedure so that only those columns being changed are modified. I am thinking this might reduce dramatically the overhead of writing to the transaction log and making the changes to the actual row.If the benefit is non existant (or insignificant) because of the way Sql Server updates a row it will obviously be a waste of time to generate
dynamic sql.
Thanks
Overhead With Tables
Aug 19, 2007Hey I need to know the overhead associated with SQL Server tables?
And what the overhead is comprised of ,would also be helpful.
Please
Encryption Overhead
Jun 7, 2007Does anyone have any information I can look at that may show me what resource overhead I may see by using encryption in SS2K5?
I am interested in memory consumption and other metrics of that nature when encrypting and decrypting data.
Especially when you have several hundred to several thousand connections.
Thanks ALL!
SqlTransaction Error Help
Mar 15, 2006Hi,
I use SqlTransaction to add record to the database. But I got a warning message on the "objTransact.Rollback()" that said "Variable 'objTransact' is used before it has been assigned a value, A null reference exception could result at runtime". what does this mean? how can I fix it? The following is the code. Thanks.
---------------------------------------------------------------------------------
Dim objTransact As SqlTransaction
Try
objConnect.Open()
objTransact = objConnect.BeginTransaction()
cmd.Connection = objConnect
cmd.Transaction = objTransact
-----do some sql query here............
objTransact.Commit()
Catch objError As Exception
objTransact.Rollback()
Finally
objConnect.Close()
End Try
SqlTransaction Error.
Oct 17, 2007Hi,
I am getting the below error when using the SqlTransaction's for doing DML operations for different tables,
"System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
Thanks in advance.
With regards,
Mani
SqlTransaction Rollback
Apr 4, 2007Hello,
I want to know, if you can use SqlTransaction even if you only execute one statement/query? I am particularly interested in Rollback(), which is called when the user presses the cancel button to abort a long file transfer.
Does rollback immediatley cancel the transfer and return control? Or, what is the best way of implementing a cancel option for transferring data?
Thank you
Overhead For Autoshrink, Autostats
Jun 6, 2001Does anyone have any benchmarks for the amount of overhead caused by autoshrink of the log and having autostats enabled? We have a customer that insists that turning off these options was necessary to eliminate a performance problem they were having (Query timeouts), but we are not convinced that these two options would have generated enough overhead to have been the root cause (they also rebuilt all their indexes and made some other unspecified changes that more likely solved the problem).
We are hestitant to have them continue with these options disabled because then we need to rely on them to keep the log file shrunk and the statistics updated and because of the data changes during the day, would prefer to have stats updated automatically rather than on a fixed schedule that may not be as appropriate.
Anyway, if anyone has any feedback on overhead generated and potential performance implications of having either of these options enabled, it would be greatly appreciated.
Thanks,
Ray Rankins
Temporary Table Overhead
Feb 10, 2005Hope somebody can give me some advice .
We are having some issues with temporary tables (with # prefixes) within Stored Procedures.
When running a profile trace on them, the stored proc quite happily creates the temp # table (in fact several of them) but whenever it hits the first statement inserting data into one of them (and it doesn't matter which one), there is a 5-6 second delay.
By commenting out one and moving to the next piece of code, the same thing happens.
Following which, the rest of the Stored Prco runs fine and subsequent inserts into the # temp tables also run efficiently.
Is the stored proc getting recompiled perhaps ?
Any advice woul;d be appreciated.
we are running SQL Server 7.0, dont know whether that helps ?
SQL Server Network Overhead
Mar 20, 2007Hi
I have been doing some testing with SQL Server 2000 using a packet sniffer, and have found that it is sending bytes with a value of 0x00 between each "valid" character. For example, if it was going to send "hello" over the network, it would be transmitted as "h.e.l.l.o".
Can anybody suggest why this might be. It happens regardless of what client is used - ADO.NET, osql.exe, etc. Is it something to do with the encoding used?
Everything does work fine and the data is received intact, but if these seemingly redundant bytes could be removed then it would increase performance by 100%.
Thanks
Using SqlTransaction Object Across Functions
Jun 27, 2006I have a class which has connection, transaction object and BeginTransaction of that method opens the transaction. This class has an array of objects of a base class which refers to their derived classes at runtime and passes the reference of connection and transaction object to the derived class. Now from the main class I call BeginTransaction and then from any of the array element some insert update will be called using the same connection and transaction which has been opened from parent class.
I get the following exception while executing the sql statement in the method of derived class
Unhandled Exception: System.InvalidOperationException: ExecuteNonQuery requiresthe command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized. at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
I tried using isolation level but transaction control does not work. I can not change the design...
C# SQLTransaction Or SQL BEGIN TRANSACTION
Dec 31, 2007I want to know if it's a good practice to use sql-transaction in both c# and sql procedures code.
View 4 Replies View RelatedSQLTransaction Locking Up Table
Jul 23, 2005Hi All,I am using a SQLTransaction to insert records into a table. At onetime, there are 5000 or more records to be inserted one by one. Ittakes some 20-25 mins for the entire process to run.Another application accesses the same table.As long as the insert process within the transaction isn't completed,the second application is not getting any response from the server. Ieven tried to run a SELECT on the table in SQL Query Analyzer while theinsert process was running and it also did not respond till the timethe insert process finished!Is this normal that a transaction is locking up a table? How do Iovercome it? I am using IsolationLevel.ReadUncommitted for thetransaction.If I do not run the process within a SQLTransaction, the second processor running the SELECT in Query Analyzer does not hang.Thanks,Sanjeev Mahajan
View 3 Replies View RelatedSQL 2012 :: CDC Overhead On Operational Database
Nov 6, 2015We are going to apply CDC on one database.
CDC is creating additional tables under System tables.
What is the performance overhead on the database by creating these tables?
I am going to access the CDC records through one ETL tool. Once read the data I am going to delete the records.
If frequency of changes are more once reading the data there may be few records will be added to the CDC. Is CDC is going to truncate the tables or only read records?
SQL Server Agent Overhead / Sysperfinfo
Jul 23, 2005I would like to capture about 20 rows from the sysperfinfo table every30 secs on a production server. I am thinking of ways I can reduce thedisk (not network) I/O overhead of this process. Instead of readingthe table from a local SQL Agent job and writing to a local table, I amwondering if I should create the job to capture the data from a remote(less critical) server. The servers are connecting via a gigabit lineand are in the same server room on the same switch. This way theproduction server would be required to do the reads but the otherserver could take care of the I/O of the writes to the capture table.Also, this job would run from 7am to 7pm non-stop. A waitfor delay of30 seconds would control the twice a min scheduling. Running this onthe remote server would free up a few SQL Agent CPU cycles as well.This would be one less job for the production server to worry about.Thanks
View 1 Replies View RelatedIs Full-Text Index Really That Much Overhead?
Oct 29, 2007I am a developer, and I have a disagreement with my DBA. He has convinced management, that SQL 2005 FullText Index is so much overhead on production, that it should NEVER be used under any circumstances. We have a Cold Fusion site, and somehow he convinced management that a bunch of Cold Fusion developers can create a more efficient full text indexing method than by using SQL 2005 Full Text Index. So now we have to come up with a method for doing this in Cold Fusion.
Is there any statistical data that could possible support or refute his statements?
Thanks
SQL 2005 And Encryption Performance Overhead
Jul 9, 2007Does anyone have any specific performance information using AES or other encryption schemes supported by SQL Server 2005?
- What method did you find works best?
- Did you encrypt any XML Data?
- Did you encrypt specific columns or whole tables?
Thanks,
Michael
SqlTransaction's And Auto Increment Fields
Mar 13, 2007Hi,
So I'm having an issue with a SqlTransaction and retrieving the auto-increment ID after an insert. Presumably this is because I have to commit the transaction before the ID's are generated, but I need the ID's before I commit the transaction (because their is another part of the transaction that requires them).
Is there any way to get these id's other than maybe getting the Max(ID) and auto-incrementing them myself in code to what they will be when the transaction is committed. If this is the best way how do I ensure no other connections can insert stuff while this transaction is going on?
Or is there a better way to do this whole thing? (i don't know really know how to do stored procedures, and presumably this would be the best way, but is there a good way to do it not Stored Procedures)
Thanks!
Error Explaination SqlTransaction Has Completed
Apr 4, 2008can anyone tell me what is the origin of this error "This SqlTransaction has completed; it is no longer usable".
View 2 Replies View RelatedUsing An Open SQLTransaction In Order To Lock A Record...
Jun 2, 2005Hello,I am trying to begin a transaction is ASP.NET that acts as a temporary lock on a record so that another web user can not gain access to that row of information. My problem is that when I begin the transaction, if the user then closes out of the browser completely, the temporary lock that is put on my table from the transaction does not go away. Further, I have no idea if it will or if there is a time range you can specifiy to do so.What I am wondering is if anyone has ever tried this before, or does anyone have a definite answer to the following question, "If I begin a transaction, and then I do NOT rollback or committ that transaction, will it ever rollback on its own and if so where can I set this time frame?"Thanks in advance for any help.
View 3 Replies View RelatedDoes It Locks Database/tables When Only Select Query Comes In SQLTransaction
Jul 12, 2006Hi,
I want to make SQLTransaction as global and use it checking the State.
But then where there are Only Select queries are going to fire, it will open transaction.
So, Does it locks database/tables when Only Select query comes in SQLTransaction.
If you have another successful way of doing this, Please suggest.
Thanking you.
tats
SQL Server 2008 :: Estimate / Calculate Performance Overhead From Replication?
Jun 10, 2015I've been asked to put together an estimation for the performance impact that replication would have on our database server during a particular operation. I know that this depends on a lot of different factors, including:
* Number of articles being replicated
* Types of articles being replicated
* Number of DML transactions that would result in delivery of replicated data
Any way to turn this into a meaningful metric?