Python 2.6 and Sqlite3 - Slow

[bruceg113355 at gmail.com]

Uli,

Answers to your questions:
1) There are approx 65 records and each record is 68 bytes in length.
2) Not applicable because number of records is fixed.
3) Takes less than a second to read all 65 records when all is well.
   Takes 17 seconds to read all 65 records when all is NOT WELL
4) Performance is also sluggish, at least 12 seconds.
5) Most likely, I misspoken. Restarting my program does not always help with performance.

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)

I like your idea of trying Python 2.7

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)

Thanks,
Bruce





On Monday, August 27, 2012 11:50:15 AM UTC-4, Ulrich Eckhardt wrote:
> Am 27.08.2012 03:23, schrieb bruceg113355 at gmail.com:
> 
> > My program uses Python 2.6 and Sqlite3 and connects to a network
> 
> > database 100 miles away.
> 
> 
> 
> Wait, isn't SQLite completely file-based? In that case, SQLite accesses
> 
> a file, which in turn is stored on a remote filesystem. This means that 
> 
> there are other components involved here, namely your OS, the network 
> 
> (bandwidth & latency), the network filesystem and the filesystem on the 
> 
> remote machine. It would help if you told us what you have there.
> 
> 
> 
> 
> 
> > My program reads approx 60 records (4000 bytes) from a Sqlite
> 
> > database in less than a second. Each time the user requests data, my
> 
> > program can continuously read 60 records in less than a second.
> 
> > However, if I access the network drive  (e.g. DOS command DIR /S)
> 
> > while my program is running, my program takes 20 seconds to read the
> 
> > same 60 records. If I restart my program, my program once again takes
> 
> > less than a second to read 60 records.
> 
> 
> 
> Questions here:
> 
> 1. Is each record 4kB or are all 60 records together 4kB?
> 
> 2. Does the time for reading double when you double the number of 
> 
> records? Typically you have B + C * N, but it would be interesting to 
> 
> know the bias B and the actual time (and size) of each record.
> 
> 3. How does the timing change when running dir/s?
> 
> 4. What if you run two instances of your program?
> 
> 5. Is the duration is only reset by restarting the program or does it 
> 
> also decrease when the dir/s call has finished? What if you close and 
> 
> reopen the database without terminating the program?
> 
> 
> 
> My guess is that the concurrent access by another program causes the 
> 
> accesses to become synchronized, while before most of the data is 
> 
> cached. That would cause a complete roundtrip between the two machines 
> 
> for every access, which can easily blow up the timing via the latency.
> 
> 
> 
> In any case, I would try Python 2.7 in case this is a bug that was 
> 
> already fixed.
> 
> 
> 
> Good luck!
> 
> 
> 
> Uli