Constraint to prevent UPDATE after row is committed

Question

Constraint to prevent UPDATE after row is committed

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

David Wiseman SSCommitted Points: 1713 More actions · Answer 1

Hi Jeff,

That solution would work, but it requires changes to the way the application works. The application currently calls one stored procedure to do the initial insert and another stored procedure the stream the data into the table. If the temp table is created in the insert SP, it would fall out of scope. Also, there is currently no way to trigger the insert from the temp table.

The solution would work and it feels like less of a hack, but it does require the developers to make some changes to the way the application works. Also, our tempdb log file is quite busy on our DB server - I'd need to consider the additional load this might add.

Thanks,

David

DBA Dash - Free, open source monitoring for SQL Server

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Ah, understood. I was going by what you had posted for code.

Shifting gears a bit, I'm concerned about your "busy" Temp DB. This is frequently caused by things like "all in one" queries where SQL Server needs to make a lot of "work tables" in Temp DB to get the job done. It might be well worth it to find the code that produces a large number of reads and writes and examine their execution plans. A lot of such code has either a DISTINCT or a GROUP BY in it in an attempt to eliminate the duplicate rows that "all in one" queries frequently produce because of accidental many-to-many joins.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Wiseman SSCommitted Points: 1713 More actions · Answer 3

Jeff Moden (3/16/2012)
Ah, understood. I was going by what you had posted for code.
Shifting gears a bit, I'm concerned about your "busy" Temp DB. This is frequently caused by things like "all in one" queries where SQL Server needs to make a lot of "work tables" in Temp DB to get the job done. It might be well worth it to find the code that produces a large number of reads and writes and examine their execution plans. A lot of such code has either a DISTINCT or a GROUP BY in it in an attempt to eliminate the duplicate rows that "all in one" queries frequently produce because of accidental many-to-many joins.

Performance is another can of worms...

I agree with what you are saying about DISTINCT/GROUP BY - too often this is used as a quick fix to prevent duplicates from been returned. It's bad for performance and it's also sloppy coding.

Our tempdb database accounts for ~30% of the write I/O and ~2% of the read activity. The worst offender is actually the ASPState db which accounts for over 60% of the write I/O. We will be replacing this with AppFabric shortly. 😀

It's a busy system in general and it's growing rapidly - growing in users, data volumes and features. I like to be pro-active with performance tuning and it's probably the part of my job I find the most interesting. There is always more that can be done. 😎

Thanks again to everyone for their input.

DBA Dash - Free, open source monitoring for SQL Server