SQL Transactions - Torn apart in old code
-
Hey guys, while i was doing some performance tests on my software i came across a fascinating result. A lot of my DB query execution code uses transactions since if there is an error i have to undo the canges on the database. So now i found that the transaction creation and commit takes roughly 2 the cost of the actual executeQuery(). I also have a isDBAvailable check before every DB query gets fired to cope with connection problems. Now i saw i used to do the available check also with a transaction, which is in fact quite an overload for just checking a SELECT 0 executing as scalar. So now im not sure if i should stick to the transaction there, any pros or cons? Should i just execute the scalar without using a transaction, what would you guys do? I mean it actually costs ~20ms so far using a transaction and currently im not sure if i need it or not. Little bit stuck right here cause i don'T know what i was thinking back then :) Discuss? [ed. Moved to Database discussion forum] Edit: Thanks for moving, i see that the term technical discussion was a bit overshot in this!
if(this.signature != "")
{
MessageBox.Show("This is my signature: " + Environment.NewLine + signature);
}
else
{
MessageBox.Show("404-Signature not found");
} -
Hey guys, while i was doing some performance tests on my software i came across a fascinating result. A lot of my DB query execution code uses transactions since if there is an error i have to undo the canges on the database. So now i found that the transaction creation and commit takes roughly 2 the cost of the actual executeQuery(). I also have a isDBAvailable check before every DB query gets fired to cope with connection problems. Now i saw i used to do the available check also with a transaction, which is in fact quite an overload for just checking a SELECT 0 executing as scalar. So now im not sure if i should stick to the transaction there, any pros or cons? Should i just execute the scalar without using a transaction, what would you guys do? I mean it actually costs ~20ms so far using a transaction and currently im not sure if i need it or not. Little bit stuck right here cause i don'T know what i was thinking back then :) Discuss? [ed. Moved to Database discussion forum] Edit: Thanks for moving, i see that the term technical discussion was a bit overshot in this!
if(this.signature != "")
{
MessageBox.Show("This is my signature: " + Environment.NewLine + signature);
}
else
{
MessageBox.Show("404-Signature not found");
}HobbyProggy wrote:
A lot of my DB query execution code uses transactions
Maybe I have a big gaping hole in my understanding of DB's, but why do you need to create a transaction if you're "just" doing a query? Are you writing to some temp tables to perform the query? Marc
Imperative to Functional Programming Succinctly Contributors Wanted for Higher Order Programming Project! Learning to code with python is like learning to swim with those little arm floaties. It gives you undeserved confidence and will eventually drown you. - DangerBunny
-
HobbyProggy wrote:
A lot of my DB query execution code uses transactions
Maybe I have a big gaping hole in my understanding of DB's, but why do you need to create a transaction if you're "just" doing a query? Are you writing to some temp tables to perform the query? Marc
Imperative to Functional Programming Succinctly Contributors Wanted for Higher Order Programming Project! Learning to code with python is like learning to swim with those little arm floaties. It gives you undeserved confidence and will eventually drown you. - DangerBunny
Not really, but let's say i would insert 20 datasets that are dependant of something else and dataset 19 fails i need to reroll the whole data. That's actually why i use a transaction, so it get's done en block and can be reverted in case of failure.
if(this.signature != "")
{
MessageBox.Show("This is my signature: " + Environment.NewLine + signature);
}
else
{
MessageBox.Show("404-Signature not found");
} -
Not really, but let's say i would insert 20 datasets that are dependant of something else and dataset 19 fails i need to reroll the whole data. That's actually why i use a transaction, so it get's done en block and can be reverted in case of failure.
if(this.signature != "")
{
MessageBox.Show("This is my signature: " + Environment.NewLine + signature);
}
else
{
MessageBox.Show("404-Signature not found");
}Again, why would you put any SELECT in a transaction? (Hint, hint)
A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak -
Again, why would you put any SELECT in a transaction? (Hint, hint)
A guide to posting questions on CodeProject
Click this: Asking questions is a skill. Seriously, do it.
Dave Kreskowiak