>I am a developer of accounting software and mostly learned my "trade" by trail >and error and from this forum. >(Thanks for all the info and tips I receive daily on this forum.) > >Setup: >Delphi XE2 + DBX + Firebird 2.5x >Firebird mostly running on dual core pentiums, Windows XP/7 Pro. >Workstations = Celerons +, running the application exclusively > >To date I have used mostly "On Insert / On Update" triggers to insert or >update tables that must be update when a certain >record is posted. For example: On the sale of an item, I must post a record to >the Salesperson Commission table, update >the quantities on hand in the Stock Table etc. But due to the complexity of >for example the commission structures I am >thinking of moving it to the application. > >The question is: How much slower/faster will 5 SQL statements issue from the >application be compared to 1 SQL statement >with 4 inserts/updates inside a trigger? > >Or I am missing some bigger issue here?
Well, Cornie, you will at least send more data from the application to Firebird by having things in the application. I've no clue how much slower this will be, I assume part of the answer depends on whether you run the application on the server or through some slow link. Moreover, you may or may not have the added cost of preparing the additional queries over and over again. There is one thing you write that may be a problem in a multiuser environment (although more regarding lock conflicts than speed) - updating quantities on hand in the stock table. One thing I've learnt from this list is that sometimes it is better to have a separate application updating the quantities at set times whereas your normal application doesn't update, just inserts (using positive numbers when adding to the stock and negative numbers when removing from stock and use sum() when trying to get the current stock). This may or may not be a problem for you (estate agents will typically not have any problem with lock conflicts since each flat will typically only have a stock of only 1, huge stores with large sales of only a few items will have lock conflicts), if it is, I think you have to choose between getting (potentially lots of) lock conflicts or risking the chance of two simultaneous transactions both removing the last stock item. The chance of the simultaneous transactions may be reduced by e.g. saying that you should not allow sales to take place if there's less than e.g. 5 items in stock or even mix the two strategies with inserting if the total number in stock exceeds a certain number and use the lock conflict prone strategy if the total number is below this threshold. Having said that I assume triggers to be quicker than separate SQL statements, if things are slow, you may need to extract the triggers into EXECUTE BLOCK or separate SQL statements to see the plan(s) chosen by the optimizer (or at least, I've never tried to see a plan for a trigger and don't know of quicker ways to get it). This can reveal if there's some changes you can do to (sometimes dramatically) improve performance. HTH, Set
