Python 2.6 and Sqlite3 - Slow

[bruceg113355 at gmail.com]

On Tuesday, August 28, 2012 4:27:48 AM UTC-4, Cameron Simpson wrote:
> On 27Aug2012 13:41, bruceg113355 at gmail.com <bruceg113355 at gmail.com> wrote:
> 
> | When using the database on my C Drive, Sqlite performance is great!   (<1S)
> 
> | When using the database on a network, Sqlite performance is terrible! (17S)
> 
> 
> 
> Let me first echo everyone saying not to use SQLite on a network file.
> 
> 
> 
> | I like your idea of trying Python 2.7
> 
> 
> 
> I doubt it will change anything.
> 
> 
> 
> | Finally, the way my program is written is:
> 
> |   loop for all database records:
> 
> |      read a database record
> 
> |      process data
> 
> |      display data (via wxPython)
> 
> | 
> 
> | Perhaps, this is a better approach:
> 
> |      read all database records
> 
> |      loop for all records:
> 
> |         process data
> 
> |         display data (via wxPython)
> 
> 
> 
> Yes, provided the "read all database records" is a single select
> 
> statement. In general, with any kind of remote resource you want to
> 
> minimise the number of transactions - the to and fro part, because each
> 
> such item tends to have latency while something is sent to and again
> 
> receiving from. So if you can say "gimme all the records" you get one
> 
> "unit" of latency at the start and end, versus latency around each
> 
> record fetch.
> 
> 
> 
> Having said all that, because SQLite works directly against the file, if
> 
> you say to it "giev me all the records" and the file is remote, SQLite
> 
> will probably _still_ fetch each record individually internally, gaining
> 
> you little.
> 
> 
> 
> This is why people are suggesting a database "server": then you can say
> 
> "get me all the records" over the net, and the server does
> 
> local-to-the-server file access to obtain the data. So all the "per
> 
> record" latency is at its end, and very small. Not to mention any
> 
> cacheing it may do.
> 
> 
> 
> Of course, if your requirements are very simple you might be better off
> 
> with a flat text file, possibly in CSV format, and avoid SQLite
> 
> altogether.
> 
> 
> 
> Cheers,
> 
> -- 
> 
> Cameron Simpson <cs at zip.com.au>
> 
> 
> 
> I do not trust thee, Cage from Hell, / The reason why I cannot tell, /
> 
> But this I know, and know full well: / I do not trust thee, Cage from Hell.
> 
>         - Leigh Ann Hussey, leighann at 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) 

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. 
  #1  0:00:00.078000 
  #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 to 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 using some kind of interprocess communication (typically TCP/IP) to send requests 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 writes directly from the database files on disk. There is no intermediary server process.

    There are advantages and disadvantages to being serverless. The main advantage is that there is no separate server process to install, setup, configure, 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 are run. Any program that is able to access the disk is able to use an SQLite database.

    On the other hand, a database engine that uses a server can provide better protection from bugs in the client application - stray pointers in a client 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 serverless, SQLite is the only one that this author knows of that allows multiple applications to access the same database at the same time. 
------------------------------------------------------------------------------


Doesn't the last paragraph imply that SQLite can operate on a network drive.

Thanks,
Bruce