Help with Performance on a Conditional Trigger

I have a pretty massive conditional trigger. If there is another way of going about this, please let me know. But I'm populating a temp table with records and based on many conditions, I am transforming this data to another table in a corrected format. These conditions I am using reference the final table in many ways, and this seems to become slower and slower as the final table grows larger.

Take a look and see if you can help me please.There are some major issues with your trigger. The length and repetetiveness is just symptomatic.

When you evaluate statements like:
IF (SELECT RESV_TOTAL FROM inserted) = 0
...you need to keep in mind that the trigger executes once for every insert statement, not once for every inserted record. If you load 100 records into this table the trigger is still going to fire only once, and thus SELECT RESV_TOTAL FROM inserted will return more than one record and can't be compared to the scalar value "0".

And regarding:
IF (SELECT COUNT(*) FROM RESERVATION_MASTER RM, inserted
WHERE RM.BOOK_NO = inserted.BOOK_NO) >= @.@.ROWCOUNT
...I can't even be sure which last successful transaction @.@.ROWCOUNT refers to, and I'm not convinced you can either.

I suggest you back off, think about your application process a bit more, and try to write up what you are trying to accomplish as succinctly as possible. Having a clear picture of Point A and Point B often helps in devising the best route from Point A to Point B.|||it certainly does appear that you're not quite consistent in the method of identifying your conditions. but i'd say that can be fixed once you follow blindman's advice. what cannot be fixed is the problem that you addressed us with in the first place, - "it seems to become slower and slower as the final table grows larger."

if you can revamp your insert process by referencing that final table through a lefty outer join (left that is.) i'd even go further, i'd create a view using that join, and bcp records out. then, i'd drop the trigger all together and just use bulk insert. after all, at that point i only have records that i need.

Help with Insert/Trigger

Just curious if there's anything in SQL comparable to a FOR EACH ROW trigger in Oracle.
I'm doing an import of a bunch of rows into a parent table using an INSERT/SELECT statement and I have triggers that fire pulling data from the same row in the source table into the child tables and creating the foreign key references. The problem lies in
the fact that the triggers only fire once seeing as how it reads the INSERT statement as one statement and not row by row.
Any thoughts?
Thanks.
Pete
There is no equivalent. You can search the newsgroups (.programming) for
many examples. In general, set-based solutions are faster and more
efficient for any sql programming.
"Pete" <Pete@.discussions.microsoft.com> wrote in message
news:3287C30A-AAF3-408C-83F9-9F72DC78ADAD@.microsoft.com...
> Just curious if there's anything in SQL comparable to a FOR EACH ROW
trigger in Oracle.
> I'm doing an import of a bunch of rows into a parent table using an
INSERT/SELECT statement and I have triggers that fire pulling data from the
same row in the source table into the child tables and creating the foreign
key references. The problem lies in the fact that the triggers only fire
once seeing as how it reads the INSERT statement as one statement and not
row by row.
> Any thoughts?
> Thanks.
> Pete

Help with INSERT TRIGGER - fails with error.

I need a trigger (well, I don't *need* one, but it would be optimal!)
but I can't get it to work because it references ntext fields.

Is there any alternative? I could write it in laborious code in the
application, but I'd rather not!

DDL for table and trigger below.

TIA

Edward

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblMyTable]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblMyTable]
GO

CREATE TABLE [dbo].[tblMyTable] (
[fldCSID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[fldSubject][ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldKBSubject] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldKBDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TRIGGER PrepopulateKBFieldsFromQuery ON dbo.tblMyTable

FOR INSERT

AS

BEGIN

IF UPDATE(fldKBSubject)
BEGIN

UPDATE
tblMyTable
SET
fldSubject = i.fldKBSubject
FROM
inserted i INNER JOIN
tblMyTable ON i.fldCSID = tblMyTable.fldCSID

END

IF UPDATE (fldKBDescription)
BEGIN
UPDATE
tblMyTable
SET
fldDescription = i.fldKBDescription
FROM
inserted i INNER JOIN
tblMyTable ON i.fldCSID = tblMyTable.fldCSID
END
ENDOn 17 Mar 2006 06:24:01 -0800, teddysnips@.hotmail.com wrote:

>I need a trigger (well, I don't *need* one, but it would be optimal!)
>but I can't get it to work because it references ntext fields.
>Is there any alternative? I could write it in laborious code in the
>application, but I'd rather not!
>DDL for table and trigger below.

Hi Edward,

Thanks for providing the DDL!

I'll come to your problem later, but first some comments.

>CREATE TABLE [dbo].[tblMyTable] (
>[fldCSID] uniqueidentifier ROWGUIDCOL NOT NULL ,
>[fldSubject][ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[fldDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[fldKBSubject] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[fldKBDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>GO

You didn't declare any PRIMARY KEY in this database. I think you
intended to make the column fldCSID a PRIMARY KEY, but you didn't
declare it as such.

After that, you should also declare some other column (or combination of
columns) as UNIQUE. With this design, there's nothing to prevent you
from accidentally inserting the same data twice.

Does the fldSCID column really have to be uniqueidentifier? If you
choose to use surrogate keys, then IDENTITY should be the regular
choice; situations that call for uniqueidentifier are very rare.

Apart from the uniqueidentifier column, all your columns accept NULLs.
Do you really want to accept rows with just NULLs in your database?
Nullable columns should be the exception, not the rule.

Are you sure that all these columns need to be ntext? I can somewhat
imagine having descriptions of over 4,000 characters - but subjects? I
think that you should probably define Subject and KBSubject ar nvarchar
with an appropriate maximum length (hopefully less than 100, but I don;t
know your business of course). You might also want to rethiink the
choice of ntext/nvarchar over text/varchar - unless you really need to
store characters from non-Western alphabets or other characters that are
only available in unicode, there's no reason to use double the space
taken.

On to the trigger (I removed the empty lines for readability)

>CREATE TRIGGER PrepopulateKBFieldsFromQuery ON dbo.tblMyTable
>FOR INSERT
>AS
>BEGIN
>IF UPDATE(fldKBSubject)
>BEGIN
>UPDATE
>tblMyTable
>SET
>fldSubject = i.fldKBSubject
>FROM
>inserted i INNER JOIN
>tblMyTable ON i.fldCSID = tblMyTable.fldCSID
>END
>IF UPDATE (fldKBDescription)
>BEGIN
>UPDATE
>tblMyTable
>SET
>fldDescription = i.fldKBDescription
>FROM
>inserted i INNER JOIN
>tblMyTable ON i.fldCSID = tblMyTable.fldCSID
>END
>END

In an INSERT trigger, you don't need IF UPDATE(). It only makes sense in
an UPDATE trigger; for an INSERT, the IF UPDATE() will be true for each
column in the table.

There's also no need to use two seperate update statements. You can
combine these into one and gain some performance.

But the most important question, I think, is why you want to do this. If
the KBSubject and KBDescription are always a copy of the Subject and
Description columns, why have them?

Anyway, back to your question:

>I need a trigger (well, I don't *need* one, but it would be optimal!)
>but I can't get it to work because it references ntext fields.

You can't reference ntext columns in the inserted column. But you can
join to the base table and get the data from there. (Or you could
convert the trigger to an instead of trigger, in which case the ntext
data *WILL* be available in the inserted table - but that's not the
easiest solution in this case).

CREATE TRIGGER PrepopulateKBFieldsFromQuery
ON dbo.tblMyTable
FOR INSERT
AS
UPDATE MyTable
SET Subject = KBSubject,
Description = KBDescription
WHERE EXISTS
(SELECT *
FROM inserted AS i
WHERE i.CSID = MyTable.CSID)
go

--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
> After that, you should also declare some other column (or combination of
> columns) as UNIQUE. With this design, there's nothing to prevent you
> from accidentally inserting the same data twice.

I would guess that one of the subjects are intended to be a key of some
sort, but since it's probably a free-text column, a PK/UNIQUE constraint
only gives you half protection, as it will not catch variations due to
typos and spaces.

> Does the fldSCID column really have to be uniqueidentifier? If you
> choose to use surrogate keys, then IDENTITY should be the regular
> choice; situations that call for uniqueidentifier are very rare.

Unless you are into replication. GUIDs are also popular among web
programmers, because they can save a roundtrip to get the key value.
I've seen more than one URL with GUIDs in them.

> You might also want to rethiink the choice of ntext/nvarchar over
> text/varchar - unless you really need to store characters from
> non-Western alphabets or other characters that are only available in
> unicode, there's no reason to use double the space taken.

Not sure I agree. The cost for a change when a requirement to support,
say, Japanese, comes can prove to be prohibitive.

> But the most important question, I think, is why you want to do this. If
> the KBSubject and KBDescription are always a copy of the Subject and
> Description columns, why have them?

The trigger name says "prepopulate". I guess Edward is setting an initial
default.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hugo Kornelis wrote:
> On 17 Mar 2006 06:24:01 -0800, teddysnips@.hotmail.com wrote:
[...]
> >CREATE TABLE [dbo].[tblMyTable] (
> >[fldCSID] uniqueidentifier ROWGUIDCOL NOT NULL ,
> >[fldSubject][ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >[fldDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >[fldKBSubject] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >[fldKBDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> >) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> >GO
> You didn't declare any PRIMARY KEY in this database. I think you
> intended to make the column fldCSID a PRIMARY KEY, but you didn't
> declare it as such.

Weird, it IS the PK! An error with the script, or maybe I was a bit
eager on the editing.

> Does the fldSCID column really have to be uniqueidentifier? If you
> choose to use surrogate keys, then IDENTITY should be the regular
> choice; situations that call for uniqueidentifier are very rare.

At one time the database was replicated.

> Apart from the uniqueidentifier column, all your columns accept NULLs.
> Do you really want to accept rows with just NULLs in your database?
> Nullable columns should be the exception, not the rule.

Couldn't agree more - not my DB design!

> Are you sure that all these columns need to be ntext? I can somewhat
> imagine having descriptions of over 4,000 characters - but subjects? I
> think that you should probably define Subject and KBSubject ar nvarchar
> with an appropriate maximum length (hopefully less than 100, but I don;t
> know your business of course). You might also want to rethiink the
> choice of ntext/nvarchar over text/varchar - unless you really need to
> store characters from non-Western alphabets or other characters that are
> only available in unicode, there's no reason to use double the space
> taken.

The ntext come from the Access upsizing wizard. The original designer
simply left the default values (it would have been memo columns in
Access)

> On to the trigger (I removed the empty lines for readability)
> >CREATE TRIGGER PrepopulateKBFieldsFromQuery ON dbo.tblMyTable
> >FOR INSERT
> >AS
> >BEGIN
> >IF UPDATE(fldKBSubject)
> >BEGIN
> >UPDATE
> >tblMyTable
> >SET
> >fldSubject = i.fldKBSubject
> >FROM
> >inserted i INNER JOIN
> >tblMyTable ON i.fldCSID = tblMyTable.fldCSID
> >END
> >IF UPDATE (fldKBDescription)
> >BEGIN
> >UPDATE
> >tblMyTable
> >SET
> >fldDescription = i.fldKBDescription
> >FROM
> >inserted i INNER JOIN
> >tblMyTable ON i.fldCSID = tblMyTable.fldCSID
> >END
> >END
> In an INSERT trigger, you don't need IF UPDATE(). It only makes sense in
> an UPDATE trigger; for an INSERT, the IF UPDATE() will be true for each
> column in the table.

Yes, I realise that now - thanks.

> There's also no need to use two seperate update statements. You can
> combine these into one and gain some performance.

I tend to be very "belt and braces" with my code.

> But the most important question, I think, is why you want to do this. If
> the KBSubject and KBDescription are always a copy of the Subject and
> Description columns, why have them?

I want to do it because the underlying application is a query system.
Some queries will form part of a Knowledge Base system. The client
wants the (QUERY)Subject and Description columns to be mirrored by the
KBSubject and KBDescription fields, at least initially. Only the KB
versions will be exposed to the customer.

> Anyway, back to your question:
> >I need a trigger (well, I don't *need* one, but it would be optimal!)
> >but I can't get it to work because it references ntext fields.
> You can't reference ntext columns in the inserted column. But you can
> join to the base table and get the data from there. (Or you could
> convert the trigger to an instead of trigger, in which case the ntext
> data *WILL* be available in the inserted table - but that's not the
> easiest solution in this case).
> CREATE TRIGGER PrepopulateKBFieldsFromQuery
> ON dbo.tblMyTable
> FOR INSERT
> AS
> UPDATE MyTable
> SET Subject = KBSubject,
> Description = KBDescription
> WHERE EXISTS
> (SELECT *
> FROM inserted AS i
> WHERE i.CSID = MyTable.CSID)
> go

And that is absolutely spot on! Many thanks.

Edward