Can you elaborate, TB? I'd been hoping to put the number incrementing there, since it's taking a couple SQL calls to do it now. Do you think triggers/stored procedures would be slower than two SQL hits?

Well this was only in SQL, Oracle and MYSQL - which are the only web backends I've ever used - but yes. With very few exceptions the triggers themselves constitute an additional hit. (And if you are just doing scalar calculations that don't require any querying, then code will peform that faster than executing calculations in the databases internal scripting language.) And while in theory in doesn't have , in practice trigggers will usually get invoked at times when they really don't need to be - usually harmlessly, but again taking up bandwidth.

All this makes sense. Triggers were never intended as performance enhancers. The orginal intent was to improve database integrity and stability, and mostly that is still the core of them as a feature. They are also sometimes used as an "easy" way to add functionality, and to ensure application indendence from the front end. (There are a lot of arguments that middleware and other forms of n-tiering is a better way to do this - and then you get back to integrity vs. performance arguments again. Bottom line: doing it in code (and n-tier is still doing it in code) gives you better performance. Doiing in it in the database gives you better integrity. Thats why triggers are used mainly for relational integrity - where code can't give you much of a performance advantage anyway, and problems have major consequences and are hard to debug.

However to the extent we use database functionality, Postgres will problably work better. On edit - OK - I gather we are hand coding incrementing. Postgres autoincrement might be faster. But bear in mind that this kind of incrementing still requires an internal database do keep track of the incremented numbers. In short you still get two hits - it is just that one of them is to a table in the data dictionary.

Triggers were never intended as performance enhancers.

Integrity is the issue, really. We're jumping through hoops in code because we have no ability to work with transactions, or do locking.

You did triggers in MySQL? I didn't know they'd implemented that yet.

Sure - at least relational integrity (which is a form of trigger).

What sort of relational integrity do you mean?

Anyway, they say that they're not implementing triggers until version 5.something. We're on 3.23.something.

Even autoincrement is a trigger, admittedly the only one available in MYSQL.

Nominally, sure. And we're already using those. MySQL docs don't claim they have triggers, and I was using their definition.

However, what Postgres offers is trigger functionality that we can define.

What does that have to do inherently with relational integrity?

Sure - and like I said it will be slower, but offer better data integrity - which I gather is a good trade-off because data integrity is what we are jumping through hoops to provide.

t on edit User defined triggerss offer data integrity, because among other things they ensure code is appled consistently.

In others words you don't have to worry about - if I do this new function am I calling all my standard calculations and incrementing and stuff. It is right there in the database; if you alter the database you trigger the code that does everything it should.

It may very well be faster, since it'll probably need to do less SQL to achieve the same effect -- it's being done very clumsily right now.

It may - it depends; is this standard casade, restrict delete type functionality? Because yes, most full-fledged databases can do that pretty quickly (although MS_SQL sometimes can be amazingly slow in implemntation). Or are you going to be doing a lot of custom code? Because true custom codes tends to give worse performance.

I'm curious. Since we have multiple "threads'/"discussions" in a single table, I presume we are using our code, not the autoincrement to create post numbers. And the occassional skipped thread numbers is one of the minor problems that arises from this, but there are major ones as well? Or are our major problems elsewhere?