I'm working on a fairly well established piece of software consisting
of a SQL 2000 database which is accessed by a Windows application and
a couple of web sites. All business logic etc. is implemented by the
database. It started off as a fairly simple application with a small
number of concurrent users. It has now grown in to a more complicated
one with many concurrent users and we are having the inevitable
performance problems - principally, slowness and locking.

These performance problems are mainly confined to the handling of
certain entities, such as clients, by the system. When a new client
etc. is added or an existing one modified, we have to do the following
tasks:

- Validation
- Processing

Both of these steps require a lot of SQL to be run and I don't think
there's any way to avoid this. However, perhaps the manner in which it
is run could be improved? Currently, the validation and processing are
both done in triggers on the client table - this approach was
initially chosen so that the code could be centralised and run no
matter what was making the change (i.e. stored procedures called by
the Windows application or any of the web sites).

One option might be to do some of the tasks asynchronously. However,
the validation has to be done at the time the change is being made so
that the user can be informed of any problems, and doing the
processing at a later time raises all manner of difficult problems.

I was therefore wondering if the fact that the validation and
processing tasks are both done in the client table's trigger would be
causing performance problems. Would it be better if we just bit the
bullet and moved both out of the trigger to a stored procedure which
was called by each of the SPs which writes to the client table?
Although this would mean that we could mistakenly bypass validation
and processing, if it led to significant performance problems, it
would be worth it.

I've just about run out of idea, so any suggestions or guidance would
be very gratefully received. Thanks very much in advance.

I agree it generally best to avoid the triggers from both a performance and
concurrency perspective. You'll have the same risk of bypassing rules if
you move the business logic to the middle tier.

I assume you've already don't due diligence in SQL query and index tuning
but you should consider turning on the READ_COMMITTED_SNAPSHOT database
option. This might improve concurrency.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Hi
Its hard to suggest on such kind of qustions. Sure if you have a trigger it
may hurt performance and you should move the logic into SP.


"JulianFletcherPhotography" <julianfletcherphotography (AT) googlemail (DOT) com> wrote
in message
news:f94373bf-5979-43db-9b9a-aafc365e7fe2 (AT) p36g2000vbn (DOT) googlegroups.com...

Dan
He is running SQL Server 2000....





"Dan Guzman" <guzmanda (AT) nospam-online (DOT) sbcglobal.net> wrote

Motivation to upgrade :-)

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

Thanks for the replies.

Yes, we have tuned indexes, prefixed everything with "dbo", not
interleaved DDL and DML operations, split large SPs in to smaller
ones, got rid of cursors where possible, used unique and check
constraints instead of code where possible, done some careful
denormalisation, implemented and used a tally table, added WITH
(NOLOCK) to carefully selected SPs, minimised use of views, SET
NOCOUNT ON everywhere, and turned all knobs to 11.

As for READ_COMMITTED_SNAPSHOT, we're still on SQL 2000 unfortunately.

So you're saying that the same processing (obviously achieved in
slightly different ways) will run faster in a stored procedure than in
a trigger? What are the reasons for this? Is it anything to do with
not running code within the implicit transaction of an insert / update
statement? I did read somewhere that there might be a problem with the
time taken to compile code in triggers compared to SPs.

Thanks again.

Trigger code must use the virtual inserted and deleted tables to identify
modified data and these tables have no indexes. With stored procedures, the
overhead of generating these table is eliminated and the query plans are
typically more efficient. You might try a prototype to compare stored
procedure vs. trigger performance.

Are you seeing blocking due to compiles?

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/


"Dr Jools" <julian.fletcher (AT) oxfordcc (DOT) co.uk> wrote