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
