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. > > > > Why is this, and is there a workaround? > > > > To be clear, while the remote files are on a server, this is still single > > user access to a single file at a time, just remotely. > > > > Any input greatly appreciated! > > > A local disk is on a >1Gb/s connection, probably at the end of a wire <0.5m > long. The remote volume will have that, but also a <1Gb/s connection, on > top > of a >10m length of cabling to implement the network. > > Laws of physics, such as the speed of light, limit the turn-around of > synchronous writes across a network. Your hard disk has probably an order > of magnitude quicker synchronous write than your network share. > > 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. > Thanks Christian, however, laws of physics says that a packet will travel along a 100ft run of cable in about 100 ns, give or take. Also laws of physics (actually laws of well coded copy routines I suspect) allows Finder copies to run @ 50 Mbytes/sec over our Gbit LAN (this includes reading from the drive at one end AND writing to the drive at the other) vs. SQLite that can barely manage 2 MB/sec only reading BLOBs from one end into RAM at the other. So the other explanations people have advanced (namely huge protocol overheads for SQL access/networked volume access, etc...) are more likely the cause. With much effort I suspect this can be overcome ('cause raw binary file copies can do it), but unlikely to happen. 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. thanks for responding anyway, Peter. -- --------------------------------------------- Peter K. Stys, MD Dept. of Clinical Neurosciences Hotchkiss Brain Institute University of Calgary tel (403) 210-8646 --------------------------------------------- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users