[mieu]

Hey everyone! I'm new to the forum but I've lurked around for past projects! I've just taken on a developer role for my team--previously I was a business analyst. I have a computer science degree, but I'm a little rusty, and the database courses that I took were pretty superficial and didn't get too deep into programming. I've completed training in Informatica as most of our applications are ETL based, but many of them leverage stored procedures. My SQL skills are strong, but I'm just starting to foray into the world of stored procs and I want to make sure I'm following all the best practices. I've read a couple of books on programming with T-SQL/Sybase and DB2 (two of the three DBMSes my company uses, Oracle is next!) and I've noticed that the stored procedures for one of our 'priority one' applications seems pretty outdated (syntactically) and could do with some hardening and optimization, based on my somewhat-limited knowledge and my experience troubleshooting them when they blow up.

Firstly, I'd love a recommendation on some reading material for stored procedure optimization--particularly in T-SQL (all of my DB2 scripts get a rigorous code review by our DB2 DBAs, so I'm sure I'll learn as I go). I'm refreshing my memory on algorithm analysis, but something leads me to believe that the costs of operations in databases differ than operations in your everyday C++ or Java program. Something that has helpful tips to compare the overhead of different approaches (for example, where that magical 'line' is drawn when using a cursor outstrips multiple update-with-join statements for complex business logic operations)

Secondly, I have a question whose answer is probably pretty obvious...

I've read that it's possible to nest transactions. I've got a stored procedure that--in a nutshell--processes orders that are sent electronically by our customers. There are 5 different stored procedures that work on this data sequentially, and each procedure naturally has many transactions for each 'step' in the processing. The problem we've encountered is that if we have a system error or some 'bad' data sneaks in that trips up the processing, it'll roll back the transaction, but the rest of the transactions/stored procedures can't really run if a previous step hasn't been completed, so we end up with half-processed orders sitting in the staging tables that require manual intervention to delete, and sometimes interfere with the next run of the job (another opportunity that needs fixing, anyway). Assuming that the need for 5 different stored procedures is valid (which it isn't, but I'll be getting after that) would it be better to have one all-encapsulating transaction at the beginning of each stored procedure? That way if any of the transactions nested within it fail, and cause each subsequent transaction to fail like dominoes, I can roll back the entire stored procedure?

Whew that was a mouthful, thanks in advance, guys!