|
The first will return a table consisting of one column, ID's sorted by FName.
The second will return a table consisting of two columns, ID's and FName's, sorted by FName.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
What's faster to execute?
No self-coded ADO.NET transaction:
SqlCommand[] commands = ...;
foreach(SqlCommand command in commands)
{
command.ExecuteNonQuery();
}
One ADO.NET transaction for all of them:
SqlCommand[] commands = ...;
SqlTransaction xaction = connection.BeginTransaction();
foreach(SqlCommand command in commands)
{
command.ExecuteNonQuery();
}
xaction.Commit();
Is there any performance benefit to the second bit, where all the commands are executed within a single transaction? Or would this negatively hurt performance?
Tech, life, family, faith: Give me a visit.
I'm currently blogging about: Moral Muscle
The apostle Paul, modernly speaking: Epistles of Paul
Judah Himango
|
|
|
|
|
Judah Himango wrote: Is there any performance benefit to the second bit, where all the commands are executed within a single transaction?
Not more than you'd expect from the equivalent T/SQL-statements. Now, I'm no .NET wizard or anything, but anything that would deviate from the behaviour of T/SQL would be kind of stupid. Especially so since there are SQL-server tailored Sql* classes. Right? 
|
|
|
|
|
Jörgen Sigvardsson wrote: Not more than you'd expect from the equivalent T/SQL-statements.
That's different, isn't it? I've read that a single ADO.NET transaction results in a single handshake between the calling code and SQL, whereas the first code snippet would have many handshakes between the two. Is that accurate or was I reading a programming myth?
|
|
|
|
|
Judah Himango wrote: Is that accurate or was I reading a programming myth?
I'm not sure. I do know that if you deal with OLEDB (COM), you open a connection to the database once. Then you create commands which can operate upon that connection. Furthermore, once you connect to the SQL-server, you create a session, much like the Query Analyzer does for you. That means that each command with a side effect, such as BEGIN TRANSACTION, is visible for all successive commands.
I find the idea of automatic transactions a bit worrysome. Suppose you have to call a stored procedure, which arent transaction neutral. With automatic transactions, life would become hard(er). 
You can always test this by making a stored procedure return @@trancount, and retrieve that through ADO.NET. If it's not zero, and you know you haven't started any transactions manually, then ADO.NET is doing something behind your back...
|
|
|
|
|
By the way, "no manual transaction" - what do you mean by that? All single commands in SQL are atomic, and are transactions in themselves. Either an INSERT completes successfully, or it doesn't.
When dealing with database transactions, you have to specify an isolation level. Depending on what you set, you may get different penalty hits.
|
|
|
|
|
I meant I'm not coding for a transaction specifically. That said, stored procedures (which is what my SqlCommands are calling) that do more than just single actions are not 100% failure or 100% success; that has to be done on manually either by the sproc or the calling code (such as ADO.NET).
My question is really regarding ADO.NET-based trasactions: Is is better to start a transaction from ADO.NET, execute many sprocs, then commit the transaction? Or is it better to just execute a SqlCommand one at at time, letting SQL and ADO.NET handle everything else?
Tech, life, family, faith: Give me a visit.
I'm currently blogging about: Moral Muscle
The apostle Paul, modernly speaking: Epistles of Paul
Judah Himango
|
|
|
|
|
The answer depends on what you are trying/needing to accomplish. If it is important that all the steps represented by those SqlCommands succeed, or none succeeed, then wrap them in an enclosing transaction, so that any failure will roll back the parts that succeeded. From a performance viewpoint this will incurr some hit since each operation is logged to the transaction log, and only really executed when the commit is issue (the individual steps might even seem to be a bit faster, but the commit will block longer while the writes actually happen). Enclosing all in a transaction also will cause any page locks that are needed to last for the duration of the transaction, so more will be locked for a longer time. A good rule of thumb is to keep transactions as small as reasonably possible wile insuring database consistency. If some of those SqlCommands could be executed without the others succeeding and not leave the database in an inconsistent state, then leave them out of the transaction.
Transactions aren't so much about performance as they are about database integrity. They aren't free, and should be used judiciously as needed to insure database consistency. Performance is an irrelevant criteria for deciding whether or not to use transactions.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
-- modified at 18:24 Friday 10th March, 2006
|
|
|
|
|
Rob Graham wrote: ome of those SqlCommands could
Excellent answer, thanks Rob.
|
|
|
|
|
Another point: you should not mix TSQL transacions (within an sproc) and ADO .Net transactions, as this can cause weird problems. Either do your transactions inside the sproc's, or use ADO .Net transactions, not both.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
-- modified at 18:26 Friday 10th March, 2006
|
|
|
|
|
I'm using VB.Net 2003.
I want to connect my client app to a SQL Server over the internet.
How do I send my connection request through the local proxy server?
The setup:
SQL Server --> Firewall -->>>>Internet<<<<-- Proxy Server <-- Client App
The neccesary port forwarding has already been set up.
It's not a HTTP request that I'm looking for. (I think)
/*Im not sure where this post is bet fitted so I put it at the VB.Net forum too*/
|
|
|
|
|
I saw a piece of code in a stored procedure:
DECLATE @ApplicationID uniqueidentifier<br />
SELECT @ApplicationID = NULL
Why did they use SELECT? Wouldn't SET @ApplicationID = NULL be just as good enough?
If I were to declare in my own code the following:
DECLARE @MemberID int
Can I set it to NULL as well?
SELECT @MemberID = NULL<br />
Basically what I would like to do is to get a MemberID based on the input parameter @Username:
-- Variable declaration<br />
DECLARE @MemberID int<br />
SET @MemberID = 0<br />
<br />
-- Get the member ID<br />
SELECT<br />
@MemberID = MemberID_PK<br />
FROM<br />
tblMember<br />
WHERE<br />
MemberUsername = @Username
If no member was found, what would be the value of @MemberID? Would it still be 0?
Regards,
ma se
|
|
|
|
|
ma se wrote: Why did they use SELECT? Wouldn't SET @ApplicationID = NULL be just as good enough?
You could look at it the other way around:
Why use SET? Wouldn't SELECT be good enough?
When you only set one value SELECT and SET are effectively synonyms for the same operation. But, if you want to assign several values at once then SELECT is the way to go because you can do things like this:
DECLARE @a int;
DECLARE @b int;
SELECT @a = 10, @b = 20;
This is faster than doing two separate SET operations, and the more things you need to SET at the same time, the bigger the benefit of using a SELECT.
Also, SET didn't used to be in the language, so the only way to SET values was to use SELECT, so maybe the guy is has been doing databases for many years. Or maybe he just wanted to be consistent accross all of his code and use SELECT everywhere rather than mix SET and SELECT.
ma se wrote: Can I set it to NULL as well?
Yes, but you would have discovered this in the Query Analyzer already, right?
ma se wrote: If no member was found, what would be the value of @MemberID? Would it still be 0?
Yes, it would still be 0, because the SELECT operation returned no rows at all it does not change @MemberID.
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Thanks!!
When will your website be up and running? What is the URL again?
|
|
|
|
|
Hi,
I need to extract data from MSDE through ADO.net programmatically.
Can someone help me do this pls.
Thanks.
|
|
|
|
|
smadan wrote: I need to extract data from MSDE through ADO.net programmatically.
Well, that is the only way ADO.NET works....
smadan wrote: Can someone help me do this
Can you be more specific about what help you need? You may want to look up MSDN documentation for the classes in the System.Data.SqlClient[^] namespace. Particularly SqlConnection[^]and SqlCommand[^]
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Hi,
Thanks for replying to my message.
I've been able to solve my problem.
My problem was actually to acess MSDE through Outlook, the way it can be done through MSAccess. This requires the use of a Windows service or a tool that will interact with the database and return values like any other database.
Thanks.
|
|
|
|
|
Hi there,
I am very new to working with SQL Server 2005 and would like to configure it on my machine, but I have a problem. The application appears to be installed on my machine (it installed after VS2005) but I can't seem to create any databases or connect to any database servers from the configuration manager...its almost like its a stripped version
I open configuration manager and it lists Services, Network Config and Client configuration...there is no options for importing databases, connecting to databases, creating databases like I am used to with SQL Server 2000. Can you help please?
I can connect to the different SQL Servers using VS2005 though...its this just how SQL 2005 works or what?
-- modified at 2:25 Friday 10th March, 2006
|
|
|
|
|
Hi
I am using SQL server 2005 express edition.
For that there is a separate tool called SQL server management studio 2005.
Using that SQL server management studio 2005 I can do any thing like SQL 2000 server.
Regards,
Chandana.
|
|
|
|
|
Hi Chandana,
When I installed VS 2005, SQL Server 2005 Express installed aswell, but I do not have the server management studio, I just have:
-Configuration manager
-Surface area manager
-Error usage and reporting
Thats it, does this mean I have installed all the components properly?
|
|
|
|
|
hi
yes you need to install SQL server management studio
|
|
|
|
|
Thanks its all sorted now...the express technical community preview addition has some bugs i have found!
|
|
|
|
|
|
You need to install SQL Server Management System. You can download it from Microsoft.com.
Illegal Operation
Making Computer Software Talk
|
|
|
|
|
Now, i have a formula, after solving some expression i want to assign a new value to a Parameter fields.
But i dont know how to do it.
Please help me!
superdragon
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
