Hi Dennis, > I don't think there is any way in SQL to eliminate the redundant > lookups.
OK, thanks. I thought I saw syntax of some other SQL engines that permit if/then or case/when type branching within a trigger, but I may be mistaken. >> where new.Amount not null >> and new.ID not in ( select ID from [Orders Refunds Amount] ) > 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. > 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. OK, thanks. I didn't realize that "not exists" in this context would be faster than my original "not in" syntax. I thought they'd both exit when the first match is found. > 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. Ah yes, good point :-) > 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. Yes, although now I'm keen to see fixed the apparent huge overhead of the inefficiency that Steve raised about using updatable views ;-) Thanks for all your input. I'll hone my updatable views strategy and apply it to more cases to find any tangent situations. Tom BareFeet -- 5000 computer accessories delivered anywhere in Australia: http://www.tandb.com.au/forsale/?sig _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
