In general, under a minute inserts in full sync and periodic autocommit for 33 Mb worth of data is not a bad performance.
Thanks to David Mitchell for ideas about bulk insert. There is an update of SQLite addon: - bulkins verb forms string of inserts from data matrix - extran executes SQL under optional transaction See also Phrase DB online service in JHP live demo. http://olegykj.sourceforge.net/jhp/ --- David Mitchell <[EMAIL PROTECTED]> wrote: > Oleg Kobchenko wrote: > > There is an update to SQLite addon: > > > > - new SQLite Browser application views and queries database schema and data > > - blobsize > > - optional transaction in apply (per Bill Lam) > > - strtbl (per David Mitchell) > > - double binding fix > > - lib location fix to allow spaces in folder names (per Yuvaraj Athur > > Raghuvir) > > > > Open issues: > > - utf16 (per Bill Lam) > > > > Thanks to everyone for feedback! > > > > > > --- Oleg Kobchenko <[EMAIL PROTECTED]> wrote: > > > >> SQLite Addon for J > >> > >> http://olegykj.sourceforge.net/ > >> http://olegykj.sourceforge.net/addons/sqlite.htm > >> > >> J bindings to SQLite embedded engine. > >> Contains direct library calls as well as array-based economical bulk API. > >> Includes Win32, Linux binaries; on Mac OS X it comes standard. > >> > >> Picture Gallery: http://olegykj.sourceforge.net/jhp/ > >> > >> A new object-oriented database API is used, which has some > >> enhancements over the dd interface: > >> > >> - connection is maintained in an object, so calling > >> method__db leaves free additional left argument for > >> passing vector or matrix (repeated rows) SQL parameters > >> > >> - state is maintained inside the object, so there > >> is no need to pass statement handle, etc. arround. > >> > >> - errors are automatically processed and raised as > >> a standard J signal with a meaningful message; > >> necessary resouce unreleases are done. > >> > >> - parameter types are recognized automatically by the > >> types of the arguments; for the only rare exception of > >> BLOB vs string, a boolean vector indicator is used. > >> > >> - query limit indicator provides automatic pagination. > >> > >> It would be good to have some discussion on the proposed API > >> format in order to adopt a better common J database interface. > >> The existing odbc interface could then be adapted to the new > >> format. > >> > >> In general, ODBC in general is increasingly getting > >> outdated and needs replacement with a flexible set of > >> direct interfaces to Oracle OCI, and other RDBMSes. > > > > > > > A quick benchmark: > > Environment: WinXP, 1G memory, 1.4G Pentium R, J601 > > Test: table table1(fname varchar(250),fsize long,fts timestamp, > int1 long, int2 long, int3 long) > > insert data(nr=.100000): da=. (nr$<250$'abc'),. > (<"0 i.nr),.(nr$<'2006-07-28'),.(nr$<"0 ]4 5 6),. > (nr$<"0]7 8 9),.(nr$<"0]0 1 2) > > ODBC/MSAccess/ddins - 10.12 seconds > SQLite/apply - 39.70 seconds > SQLite/apply/pragma sync off - 35.35 seconds > SQLite/exec 1 big sql/sync off - 98.84 seconds > SQLite/exec 1 big sql/begin/commit/sync off - 6.66 seconds > SQLite/exec 1 big sql/no field names/sync off - 98.59 seconds > SQLite/exec 1 big sql/no field names/sync off/begin/com - 5.95 seconds > > -- > David Mitchell __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---------------------------------------------------------------------- For information about J forums see http://www.jsoftware.com/forums.htm
