BareFeet wrote:
> Hi All (especially Dennis I guess ;-) )
>
> Focusing on the "instead of update" trigger in my example (copied
> below), you'll notice that the trigger scans through the table [Orders
> Refunds Amount] three times to find the matching ID, once for each of
> insert, delete, update, whose where tests contain the following
> (partial):
>
> insert: where new.ID not in ( select ID from [Orders Refunds Amount] )
> delete: where [Orders Refunds Amount].ID = new.ID
> update: where [Orders Refunds Amount].ID = new.ID
>
> When you look at the complete where clauses in each, you'll realize
> that, by design, only one of the where clauses is ever true. So it
> seems inefficient for the trigger to test three times. Is there a more
> efficient method?
>
> Quoting the relevant section of my original post:
>
>> Now for updating the Paying and Amount fields. Again, instead of the
>> view, I want to update the corresponding joined table for each.
>> Paying has to convert a text label to the related integer value.
>> Because the [Orders Refunds Paying] and [Orders Refunds Amount]
>> tables are designed to only contain a row if their value is not
>> null, I have to:
>>
>> 1. Insert a row if it doesn't already exist for that ID, or
>> 2. Delete the row if the value has changed to null, or
>> 3. Simply update the value if the row exists and the new value is
>> not null.
>>
>> So I have one trigger ... which performs either an insert, delete or
>> update:
>
>> create trigger [Update Orders Refunds Joined Amount]
>> instead of update of Amount
>> on [Orders Refunds Joined]
>> for each row
>> begin
>> -- insert if row doesn't exist and new value not null
>> insert into [Orders Refunds Amount]
>> (
>> ID
>> , Amount
>> )
>> select
>> new.ID
>> , new.Amount
>> where new.Amount not null
>> and new.ID not in ( select ID from [Orders Refunds Amount] )
>> ;
>> -- delete if row exists and new value is null
>> delete from [Orders Refunds Amount]
>> where new.Amount is null
>> and [Orders Refunds Amount].ID = new.ID
>> ;
>> -- update if row exists and new value not null
>> update [Orders Refunds Amount]
>> set Amount = new.Amount
>> where new.Amount not null
>> and [Orders Refunds Amount].ID = new.ID
>> ;
>> end
>> ;
>
Tom,
I don't think there is any way in SQL to eliminate the redundant lookups.
The only thing I see that could be done differently is to change the
where clause in the insert case to use a correlated subquery.
insert into [Orders Refunds Amount]
(
ID
, Amount
)
select
new.ID
, new.Amount
where new.Amount not null
and not exists
( select ID from [Orders Refunds Amount]
where ID = new.ID)
;
This exists clause will exit the subquery as soon as it finds a matching
ID. It will also be able to use the index on the ID to do an O(logN)
lookup to find or eliminate the new.ID value.
The other cases already do O(logN) searches in the index to find the row
to delete or update, and only need to do a very fast isnull check on
that one row.
Because all three cases use the same index, all the pages needed should
be in the page cache after the first scan, so they should not require
any additional I/O, and hence should complete very quickly.
While SQL does require doing some redundant work, it isn't really that
much extra, and I suspect that you would be hard pressed to measure the
extra overhead.
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
