D. Richard Hipp wrote: > On Jun 24, 2009, at 10:35 AM, Michal Seliga wrote: >>> Instead of >>> >>> WHEN not exists(SELECT * FROM ....) >>> >>> create an application-defined function (perhaps called >>> "enable_triggers()") that returns either 1 or 0 depending on the >>> setting of some variable in your application. Then use >>> >>> WHEN enable_triggers() >>> >>> The triggers will still launch, but calling an application-defined >>> function takes much less time than running NOT EXISTS, so the >>> triggers >>> will very quickly figure out that they should exit without doing >>> anything. >>> >>> >> strangely, it didn't helped, time is almost the same with not exists >> or with >> your idea. actually with function its even a little bit worse >> > > > Are you reparsing every INSERT separately or are you creating a single > prepared statement and reusing it over and over? > for each table its prepared once and then reused, only new parameters are filled in. pure query.exec() time is like this: without triggers: 0.766s with triggers which use not exist(): 2.438s with triggers which use function: 2.484s
(its measured by getting 2 times, one with exec() skipped and one when it happens and subtract these times from each other) as you can see pure exec() time is about 3x as long when triggers are used. and if i use notexists() or function() doesn't make big difference (in fact it can even be considered as measuring error) btw all these times are from quite fast pc. but application is targeted to work with much more data and on much slower device (windows mobile) with slow filesystem. sample tests showd that what takes ~20 seconds on my pc is about 3.5 minutes on target device. > > D. Richard Hipp > d...@hwaci.com > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
