Path: csiph.com!newsfeed.hal-mli.net!feeder3.hal-mli.net!newsfeed.hal-mli.net!feeder1.hal-mli.net!newsfeed.xs4all.nl!newsfeed5.news.xs4all.nl!xs4all!post.news.xs4all.nl!not-for-mail
Newsgroups: comp.lang.python
Date: Tue, 28 Aug 2012 10:25:35 -0700 (PDT)
In-Reply-To: <mailman.3896.1346142467.4697.python-list@python.org>
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=206.16.226.196; posting-account=cxDMTgoAAAD0NMd7UaWriutT-PIoI910
References: <d6e9f696-da86-4aa3-8930-69cf33dd2837@googlegroups.com> <mailman.3896.1346142467.4697.python-list@python.org>
User-Agent: G2/1.0
MIME-Version: 1.0
Subject: Re: Python 2.6 and Sqlite3 - Slow
From: bruceg113355@gmail.com
To: comp.lang.python@googlegroups.com
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Cc: python-list@python.org, bruceg113355@gmail.com
Precedence: list
Message-ID: <mailman.3901.1346174737.4697.python-list@python.org>
Lines: 187
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:28020

On Tuesday, August 28, 2012 4:27:48 AM UTC-4, Cameron Simpson wrote:
> On 27Aug2012 13:41, bruceg113355@gmail.com <bruceg113355@gmail.com> wrote=
:
>=20
> | When using the database on my C Drive, Sqlite performance is great!   (=
<1S)
>=20
> | When using the database on a network, Sqlite performance is terrible! (=
17S)
>=20
>=20
>=20
> Let me first echo everyone saying not to use SQLite on a network file.
>=20
>=20
>=20
> | I like your idea of trying Python 2.7
>=20
>=20
>=20
> I doubt it will change anything.
>=20
>=20
>=20
> | Finally, the way my program is written is:
>=20
> |   loop for all database records:
>=20
> |      read a database record
>=20
> |      process data
>=20
> |      display data (via wxPython)
>=20
> |=20
>=20
> | Perhaps, this is a better approach:
>=20
> |      read all database records
>=20
> |      loop for all records:
>=20
> |         process data
>=20
> |         display data (via wxPython)
>=20
>=20
>=20
> Yes, provided the "read all database records" is a single select
>=20
> statement. In general, with any kind of remote resource you want to
>=20
> minimise the number of transactions - the to and fro part, because each
>=20
> such item tends to have latency while something is sent to and again
>=20
> receiving from. So if you can say "gimme all the records" you get one
>=20
> "unit" of latency at the start and end, versus latency around each
>=20
> record fetch.
>=20
>=20
>=20
> Having said all that, because SQLite works directly against the file, if
>=20
> you say to it "giev me all the records" and the file is remote, SQLite
>=20
> will probably _still_ fetch each record individually internally, gaining
>=20
> you little.
>=20
>=20
>=20
> This is why people are suggesting a database "server": then you can say
>=20
> "get me all the records" over the net, and the server does
>=20
> local-to-the-server file access to obtain the data. So all the "per
>=20
> record" latency is at its end, and very small. Not to mention any
>=20
> cacheing it may do.
>=20
>=20
>=20
> Of course, if your requirements are very simple you might be better off
>=20
> with a flat text file, possibly in CSV format, and avoid SQLite
>=20
> altogether.
>=20
>=20
>=20
> Cheers,
>=20
> --=20
>=20
> Cameron Simpson <cs@zip.com.au>
>=20
>=20
>=20
> I do not trust thee, Cage from Hell, / The reason why I cannot tell, /
>=20
> But this I know, and know full well: / I do not trust thee, Cage from Hel=
l.
>=20
>         - Leigh Ann Hussey, leighann@sybase.com, DoD#5913



Cameron,

I did some testing and approach #1 is significantly faster than approach #2=
:
Approach #1:
      read all database records
      loop for all records:
         process data
         display data (via wxPython)=20

Approach #2:
   loop for all database records:
      read a database record
      process data
      display data (via wxPython)

Various test results to read 50 records from a network drive.=20
  #1  0:00:00.078000=20
  #2  0:00:04.219000

  #1  0:00:00.875000
  #2  0:00:08.031000

  #1  0:00:00.063000
  #2  0:00:06.109000

  #1  0:00:00.078000
  #2  0:00:05.110000

  #1  0:00:00.156000
  #2  0:00:02.625000

This explains some of my slowness issues.

Note: When the network drive is behaving (not slow), approach #2 is close t=
o approach #1.


>From the site: http://www.sqlite.org/different.html
---------------------------------------------------------------------------=
---
    Most SQL database engines are implemented as a separate server process.=
 Programs that want to access the database communicate with the server usin=
g some kind of interprocess communication (typically TCP/IP) to send reques=
ts to the server and to receive back results. SQLite does not work this way=
. With SQLite, the process that wants to access the database reads and writ=
es directly from the database files on disk. There is no intermediary serve=
r process.

    There are advantages and disadvantages to being serverless. The main ad=
vantage is that there is no separate server process to install, setup, conf=
igure, initialize, manage, and troubleshoot. This is one reason why SQLite =
is a "zero-configuration" database engine. Programs that use SQLite require=
 no administrative support for setting up the database engine before they a=
re run. Any program that is able to access the disk is able to use an SQLit=
e database.

    On the other hand, a database engine that uses a server can provide bet=
ter protection from bugs in the client application - stray pointers in a cl=
ient cannot corrupt memory on the server. And because a server is a single =
persistent process, it is able control database access with more precision,=
 allowing for finer grain locking and better concurrency.

    Most SQL database engines are client/server based. Of those that are se=
rverless, SQLite is the only one that this author knows of that allows mult=
iple applications to access the same database at the same time.=20
---------------------------------------------------------------------------=
---


Doesn't the last paragraph imply that SQLite can operate on a network drive=
.

Thanks,
Bruce=20