Re: [sqlite] Database Grammar 101
On 01/02/14 14:09, Simon Slavin wrote: > > But over all, English is an acquisitive (unlike German) evolving (unlike > French) language. So how about we give it two hundred, two hundred and fifty > years, and see what happens ? > Nah, we should just hold all technical discussions in Lojban, of course! :-D ABS -- Alaric Snell-Pym http://www.snell-pym.org.uk/alaric/ signature.asc Description: OpenPGP digital signature ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inserting Multiple Rows in a single statement
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/30/2011 03:41 PM, Pavel Ivanov wrote: >> I do have multiple insertions bounded by BEGIN-COMMIT . I am looking at >> possibilities of making the insertions faster. > > Prepare your statement in the form > > INSERT INTO table_name (val1, val2, ...) VALUES (?1, ?2, ...) > > Then for each row you want to insert you will bind necessary values, > execute statement and reset. With all that wrapped in transaction > there's no way to make insertions any faster. If you have lots of indexes, then for really big inserts (where the ratio of rows added to rows already existing is high), it might be quicker to drop the indexes, do the inserts, then put them back (especially with the new faster CREATE INDEX). Failing that, if you have a block of inserts to do, sorting them by an indexed column (especially primary key) before insertion might boost throughput a bit by increasing locality of reference in the B-Trees. Aside: I was talking about B-Trees with some colleagues when my six year old daughter (then five, I think, actually) butted in and said they'd learnt about B-Trees in school that day. Sadly, it was some spelling game, rather than the school getting a bit of computer science in early... There are index structures that support faster insertions than B-Trees, at the cost of slightly slower selects: streaming merge trees in particular have come to my attention: http://www.acunu.com/blogs/tom-wilkie/castle-storage-engine-oscon/ Perhaps future versions of SQLite might support some of this crazy stuff :-) ABS - -- Alaric Snell-Pym http://www.snell-pym.org.uk/alaric/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk7WUZwACgkQRgz/WHNxCGoSkQCcCwZ7WzAPsUKMGnN2ZiTJ5AoB xicAn1sgb60s40XQPlYIXdMNmfRoOBxk =DOWQ -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] fsync of directories on AIX
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hello there!
In os_unix.c, the following logic appears in unixSync:
/* Also fsync the directory containing the file if the DIRSYNC flag
** is set. This is a one-time occurrance. Many systems (examples: AIX)
** are unable to fsync a directory, so ignore errors on the fsync.
*/
if( pFile->ctrlFlags & UNIXFILE_DIRSYNC ){
int dirfd;
OSTRACE(("DIRSYNC %s (have_fullfsync=%d fullsync=%d)\n", pFile->zPath,
HAVE_FULLFSYNC, isFullsync));
rc = osOpenDirectory(pFile->zPath, &dirfd);
if( rc==SQLITE_OK && dirfd>=0 ){
full_fsync(dirfd, 0, 0);
robust_close(pFile, dirfd, __LINE__);
}else if( rc==SQLITE_CANTOPEN ){
rc = SQLITE_OK;
}
pFile->ctrlFlags &= ~UNIXFILE_DIRSYNC;
}
return rc;
This is all well and good, as indeed, fsync on directories often fails
on AIX (although, amusingly, it sometimes succeeds as well).
However, unixDelete has the following logic:
#ifndef SQLITE_DISABLE_DIRSYNC
if( dirSync ){
int fd;
rc = osOpenDirectory(zPath, &fd);
if( rc==SQLITE_OK ){
#if OS_VXWORKS
if( fsync(fd)==-1 )
#else
if( fsync(fd) )
#endif
{
rc = unixLogError(SQLITE_IOERR_DIR_FSYNC, "fsync", zPath);
}
robust_close(0, fd, __LINE__);
}else if( rc==SQLITE_CANTOPEN ){
rc = SQLITE_OK;
}
}
#endif
return rc;
Unfortunately, this does not ignore directory fsync errors. This means
that committing on AIX will fail unless the directory sync succeeded.
As far as we can tell, fsync on a dir on AIX succeeds if no other files
have been created in the directory "recently" (although we're not sure
how recent that has to be), and fails with EBADF otherwise!
Therefore, I respectfully suggest that unixDelete be altered to silently
ignore errors on directory fsyncs!
I can't find anywhere else that fsync is called on directories.
Thanks,
ABS
- --
Alaric Snell-Pym
http://www.snell-pym.org.uk/alaric/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
iEYEARECAAYFAk7WApEACgkQRgz/WHNxCGqhewCgk+sYT9Dkwc+vq3Z0S8SJD0eb
05IAn3Rxol3qzNKju7/hLeQPRKISW28t
=oVaA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Newbie question on Data Source
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/29/2011 12:17 PM, Black, Michael (IS) wrote: > I'll note a few things here... [snip] Much good advice. Seconded! ABS - -- Alaric Snell-Pym http://www.snell-pym.org.uk/alaric/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk7UzwwACgkQRgz/WHNxCGo8YQCfbuHuGn+TswVZvoaBdKvvUTfc LuIAnRklSLS0P/KgE29lF6GHuvlnnonq =U2zn -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
