On Fri, May 23, 2008 at 12:55:47PM -0600, Peter K. Stys wrote: > On Fri, May 23, 2008 at 4:31 AM, Christian Smith < > [EMAIL PROTECTED]> wrote: > > > On Tue, May 13, 2008 at 11:15:51PM -0600, Peter K. Stys wrote: > > > Hi Folks: > > > I'm new to this list and just came across a major issue so I thought I'd > > > post here: I'm using SQLite (from REALbasic which uses the SQLite DB > > engine > > > for SQL support) to store image files, and while R/W to local files is > > > excellent, the same operations on files residing on a remote volume > > mounted > > > via afp or smb (all Mac OS X) suffer a 20-fold performance hit. > > > > > > > Try playing with synchronous writes turned off. The reduced synchronous > > requirements may allow you to make more optimum use of the network file > > protocols, which operate best asynchronously. > > > > Try, in order: > > PRAGMA synchronous = NORMAL; > > PRAGMA synchronous = OFF; > > > > And measure performance of each. But SQLite is simply not designed to work > > efficiently across a network based file system, so manage your > > expectations. > > > > BTW, those PRAGMAs made little difference. I resorted to caching the remote > file to the local drive via a fast OS-level file copy then doing the SQL > R/W, then copying back to the remote in a bkgnd thread. A programming > headache to keep everything in sync, but very acceptable performance.
Actually, you might want to try using a larger page size. SQLite uses, by default, 1KB pages. Increasing that to 16KB or perhaps larger will not only reduce the overhead of BLOBs, but also increase performance significantly, as each page will be going across the network one by one. Christian _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
