Complex Database Transactions

[Jonathan Wood]

Until now, I've been using stored procedures to update my databases because
I've been taught stored procedures are faster (precompiled) and eliminate
the possibility of some kinds of injection attacks.

But now I need to create code to record a purchase transaction. Not only
would this require around a dozen arguments, but these arguments could
include any number of invoice items. Based only on trying to pass these
arguments, it does not appear that a stored procedures is up to the job.

I'm still pretty new to database development and would appreciate any
comments on the best way to proceed with this.

Thanks.

 

[Norman Yuan]

You could either do the transaction at the SQL Server end, or do it in your
ADO.NET code with SqlTransaction object.

With SQL Server transaction, you could place all the updateing process in a
single stored procedure, which wraps up the processes with

BEGIN TRANS
....
multiple updates
....

COMMIT TRANS/ROLLBACK TRANS

You need to be pretty good at T-SQL, of course. With SQL Server2005's
TRY...CATCH..., you could hanlde COMMIT or ROLLBACK a bit easier than using
SQL Server2000.

As .NET coder, I found in many cases, ADO.NET transaction is a bit easier to
use/write. Here is pseudo code:

public void TransactionUpdate(....)
{
using (SqlConnection cn=new SqlConnection(..))
{
try
{
cn.Open();
}
catch{//open connection failed, return}

//Start Transaction
using (SqlTransaction tran=cn.BeginTransaction())
{
//Define you first SqlCommand and add it into the transaction
SqlCommand cmd1=cn.CreateCommand();
cmd1.CommandType=CommandType.StoredProcedure;
cmd1.CommandText="MySP1";
...set up command parameter
cmd1.Transaction=tran;

//Define you second SqlCommand and add it into the transaction
SqlCommand cmd2=cn.CreateCommand();
cmd2.CommandType=CommandType.StoredProcedure;
cmd2.CommandText="MySP2";
...set up command parameter
cmd2.Transaction=tran;

//Define more commands to execute different SPs in the same
transaction, as your business logic needs
...

try
{
cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
...
//If all commads execution are OK, commit the transaction
tran.Commit();
}
catch(...)
{
//Roll back the transaction, which is multiple SP executions
tran.RollBack();
//Notify the transaction rollbacl
...
}
}
}
}

Of course, in the real world, you need to be very careful as to what is
placed in a single transaction and write your code robust.

HTH.

 

[Cor Ligthert[MVP]]

Jonathan,

Stored procedures are only faster as they are often used, because they are
not precompiled but stay a (relatively short) while compiled in the server
cache, while injection attacks have not direct much to do with Stored
Procedures although it forces you to use parameters.

However, why do you not have a look at Linq to SQL as you are new, for most
operations as you describe that is much easier to do.

http://msdn.microsoft.com/en-us/library/bb386976.aspx

Cor

 

[Jonathan Wood]

Cor,

Stored procedures are only faster as they are often used, because they are
not precompiled but stay a (relatively short) while compiled in the server
cache, while injection attacks have not direct much to do with Stored
Procedures although it forces you to use parameters.

However, why do you not have a look at Linq to SQL as you are new, for
most operations as you describe that is much easier to do.

I'd be happy to consider LINQ to SQL. My reservations are the issues I
raised: speed and security. These are key to me and I probably won't use
LINQ if it is slower and/or less secure.

So I'd be very curious as to why you dismiss the speed issue and what makes
you say stored procedures are not precompiled. Looking around on the Web a
little, here's a few quotes. Can you clarify why you said stored procedures
are not precompiled?

http://databases.about.com/od/specificproducts/g/storedprocedure.htm
Stored procedures are precompiled database queries that improve the
security, efficiency and usability of database client/server applications.

http://www.codeproject.com/KB/database/hkstoredproc.aspx
Stored procedures differ from ordinary SQL statements and from batches of
SQL statements in that they are pre-compiled.

http://en.wikipedia.org/wiki/Stored_procedure
SQL statements implemented as stored procedures in some cases run faster, as
they can be pre-compiled.

Thanks.

Jonathan

 

[Jonathan Wood]

Norman,

You could either do the transaction at the SQL Server end, or do it in
your ADO.NET code with SqlTransaction object.

With SQL Server transaction, you could place all the updateing process in
a single stored procedure, which wraps up the processes with

BEGIN TRANS
...
multiple updates
...

COMMIT TRANS/ROLLBACK TRANS

Yes, I've made use of transactions. Again my limitation here is in the
number of arguments needed and the fact that the number of arguments could
vary. I don't see a problem writing the actual stored procedure. But the
arguments needed just seems unweildy to me, if even possible.

As .NET coder, I found in many cases, ADO.NET transaction is a bit easier
to use/write. Here is pseudo code:

Thanks for this. I will print this code out and study it further. I really
don't have a good understanding of ADO.NET code, how efficient it is
compared with stored procedures, and how secure it is compared with stored
procedures. I probably need to consume a few books in this area but they
will need to wait a bit.

But I will study your code.

Thanks.

Jonathan

 

[Kerry Moorman]

Jonathan,

In later versions of SQL Server, for example, both adhoc queries and stored
procedures have their query plans cached. So neither technique offers an
inherent speed advantage over the other.

There may very well be a list of good reasons for you to use stored
procedures, but speed isn't very high on that list.

Kerry Moorman

 

[Jonathan Wood]

Kerry,

In later versions of SQL Server, for example, both adhoc queries and
stored
procedures have their query plans cached. So neither technique offers an
inherent speed advantage over the other.

There may very well be a list of good reasons for you to use stored
procedures, but speed isn't very high on that list.

I'd really love to understand this better. Adhoc queries may be cached, but
don't stored procedures get stored in a compiled state, therefore skipping
the compilation that would be necessary the first time an adhoc query runs?

I'll search the Web for more info, but would be interested in any additional
details you are able to provide.

Thanks.

 

[Kerry Moorman]

Jonathan,

At least with SQL Server the term "compiled", as applied to stored
procedures, means that a query or execution plan is created by the database
server and used to execute the stored procedure. This execution plan is then
cached and reused the next time the stored procedure is executed.

But exactly the same thing takes place with adhoc sql. As long as the adhod
sql is not significantly different than the last time, the cached execution
plan is also reused.

And, as far as I know, none of these execution plans is actually persisted
in the database. In other words, if the database server is re-started, the
execution plan has to be "re-compiled" and cached, for both stored procedures
and adhoc sql, the next time they are executed.

Kerry Moorman

 

[Jonathan Wood]

Kerry,

At least with SQL Server the term "compiled", as applied to stored
procedures, means that a query or execution plan is created by the
database
server and used to execute the stored procedure. This execution plan is
then
cached and reused the next time the stored procedure is executed.

But exactly the same thing takes place with adhoc sql. As long as the
adhod
sql is not significantly different than the last time, the cached
execution
plan is also reused.

Okay, that makes sense. But one would normally run a variety of queries. So
if they were all stored procedures, they'd all be precompiled. If they were
adhoc queries, then each query would need to be compiled the first time they
were run. In this case, I would expect stored procedures to be a little
faster under most circumstances.

And, as far as I know, none of these execution plans is actually persisted
in the database. In other words, if the database server is re-started, the
execution plan has to be "re-compiled" and cached, for both stored
procedures
and adhoc sql, the next time they are executed.

This is not what I've read.

http://en.wikipedia.org/wiki/Stored_procedure
Stored procedures (sometimes called a sproc or SP) are actually stored in
the database data dictionary.

http://www.codeproject.com/KB/database/hkstoredproc.aspx
A much better option would be to have the database server compile the query,
store it in a compiled format and run it on request, without having to
recompile it each and every time. This is where the concept of stored
procedures comes into play.

http://databases.about.com/od/specificproducts/g/storedprocedure.htm
The major benefits of this technology are the substantial performance gains
from precompiled execution, [...]

Thanks.

Jonathan

 

[Kerry Moorman]

Jonathan,

From SQL Server Books Online:

"As a database is changed by such actions as adding indexes or changing data
in indexed columns, the original query plans used to access its tables should
be optimized again by recompiling them. This optimization happens
automatically the first time a stored procedure is run after Microsoft SQL
Server 2005 is restarted."

Kerry Moorman

Kerry,

And, as far as I know, none of these execution plans is actually persisted
in the database. In other words, if the database server is re-started, the
execution plan has to be "re-compiled" and cached, for both stored
procedures
and adhoc sql, the next time they are executed.

This is not what I've read.

http://en.wikipedia.org/wiki/Stored_procedure
Stored procedures (sometimes called a sproc or SP) are actually stored in
the database data dictionary.

http://www.codeproject.com/KB/database/hkstoredproc.aspx
A much better option would be to have the database server compile the query,
store it in a compiled format and run it on request, without having to
recompile it each and every time. This is where the concept of stored
procedures comes into play.

http://databases.about.com/od/specificproducts/g/storedprocedure.htm
The major benefits of this technology are the substantial performance gains
from precompiled execution, [...]

Thanks.

Jonathan

 

[Jonathan Wood]

Okay, I think I get where you're coming from, and I appreciate your
comments. My take is that stored procedures can still offer a performance
improvement in many cases. Thanks.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

Jonathan,

From SQL Server Books Online:

"As a database is changed by such actions as adding indexes or changing
data
in indexed columns, the original query plans used to access its tables
should
be optimized again by recompiling them. This optimization happens
automatically the first time a stored procedure is run after Microsoft SQL
Server 2005 is restarted."

Kerry Moorman

Kerry,

And, as far as I know, none of these execution plans is actually
persisted
in the database. In other words, if the database server is re-started,
the
execution plan has to be "re-compiled" and cached, for both stored
procedures
and adhoc sql, the next time they are executed.

This is not what I've read.

http://en.wikipedia.org/wiki/Stored_procedure
Stored procedures (sometimes called a sproc or SP) are actually stored in
the database data dictionary.

http://www.codeproject.com/KB/database/hkstoredproc.aspx
A much better option would be to have the database server compile the
query,
store it in a compiled format and run it on request, without having to
recompile it each and every time. This is where the concept of stored
procedures comes into play.

http://databases.about.com/od/specificproducts/g/storedprocedure.htm
The major benefits of this technology are the substantial performance
gains
from precompiled execution, [...]

Thanks.

Jonathan

 

[Kerry Moorman]

Jonathan,

This isn't a matter of your take or my take. Stored procedures can offer
many benefits, but performance really isn't one of them.

Kerry Moorman

 

[Mary Chipman [MSFT]]

Yes, stored procedures can offer a performance benefit, mainly because
the code is executing on the server, not on the client. If your sproc
executes a single statement, then you may not see much difference, or
it would be imperceptible. But if you have multiple operations and/or
logic involved in a transaction, then sprocs definitely offer a
performance boost as opposed to multiple trips across the wire. Your
mileage will vary

--Mary

 

[Cor Ligthert[MVP]]

Mary,

Any idea how many pico seconds that performance benefit is?
(It is about the transfer of in most cases 4Kbit on a network wire).

I don't call this a boost by the way.

Cor

 

[Jonathan Wood]

Interesting. I would have thought that even adhoc queries would eventually
execute on the server. Guess I don't quite get how that would work any other
way. Thanks.

 

[Mary Chipman [MSFT]]

As I said, your mileage will vary depending on your data logic. That's
the rationale for CLR sprocs and UDFs executing on the server -- for
complex scenarios they're more performant. You could of course have
similar logic in T-SQL, or executing on the client, where you'd likely
see a perf hit. If all you are doing is simple SELECTs or DML, then
you probably wouldn't notice any difference.

--Mary

 

[Mary Chipman [MSFT]]

I apologize if my explanation wasn't clear enough. I never meant to
imply that ad hoc queries don't execute on the server. Here's a link
to SQLS 2005 BOL that explains how stored procedures work:
http://msdn.microsoft.com/en-us/library/ms191428.aspx.

This topic explains the performance benefits for SQLS 2000:
http://msdn.microsoft.com/en-us/library/aa174792.aspx.

This article describes how to troubleshoot the slow performance of ad
hoc queries http://support.microsoft.com/kb/243588/.

--Mary

 

[Jonathan Wood]

Thanks for the links!