Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.python > #27958 > unrolled thread

Python 2.6 and Sqlite3 - Slow

Back to article view | Back to comp.lang.python

Contents

  Python 2.6 and Sqlite3 - Slow bruceg113355@gmail.com - 2012-08-26 18:23 -0700
    Re: Python 2.6 and Sqlite3 - Slow Ulrich Eckhardt <ulrich.eckhardt@dominolaser.com> - 2012-08-27 17:50 +0200
      Re: Python 2.6 and Sqlite3 - Slow ahsanraza211@gmail.com - 2012-08-27 09:53 -0700
      Re: Python 2.6 and Sqlite3 - Slow bruceg113355@gmail.com - 2012-08-27 13:41 -0700
        Re: Python 2.6 and Sqlite3 - Slow Cameron Simpson <cs@zip.com.au> - 2012-08-28 18:27 +1000
          Re: Python 2.6 and Sqlite3 - Slow bruceg113355@gmail.com - 2012-08-28 10:25 -0700
            Re: Python 2.6 and Sqlite3 - Slow Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2012-08-28 17:20 -0400
            Re: Python 2.6 and Sqlite3 - Slow Pedro Larroy <pedro.larroy.lists@gmail.com> - 2012-08-29 00:01 +0200
          Re: Python 2.6 and Sqlite3 - Slow bruceg113355@gmail.com - 2012-08-28 10:25 -0700
    Re: Python 2.6 and Sqlite3 - Slow Demian Brecht <demianbrecht@gmail.com> - 2012-08-27 13:54 -0700
      Re: Python 2.6 and Sqlite3 - Slow bruceg113355@gmail.com - 2012-08-27 17:55 -0700
        Re: Python 2.6 and Sqlite3 - Slow Bryan <bryanjugglercryptographer@yahoo.com> - 2012-08-27 19:32 -0700
          Re: Python 2.6 and Sqlite3 - Slow bruceg113355@gmail.com - 2012-08-27 20:09 -0700
            Re: Python 2.6 and Sqlite3 - Slow Bryan <bryanjugglercryptographer@yahoo.com> - 2012-08-27 22:26 -0700
            Re: Python 2.6 and Sqlite3 - Slow Dennis Lee Bieber <wlfraed@ix.netcom.com> - 2012-08-28 01:33 -0400

#27958 — Python 2.6 and Sqlite3 - Slow

My program uses Python 2.6 and Sqlite3 and connects to a network database 100 miles away. 
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.

Any ideas?

Thanks,
Bruce

[toc] | [next] | [standalone]

#27988

Am 27.08.2012 03:23, schrieb bruceg113355@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

[toc] | [prev] | [next] | [standalone]

#27990

On Monday, August 27, 2012 8:50:15 AM UTC-7, Ulrich Eckhardt wrote:
> Am 27.08.2012 03:23, schrieb bruceg113355@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

[toc] | [prev] | [next] | [standalone]

#28000

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@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

[toc] | [prev] | [next] | [standalone]

#28015

On 27Aug2012 13:41, bruceg113355@gmail.com <bruceg113355@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@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@sybase.com, DoD#5913

[toc] | [prev] | [next] | [standalone]

#28019

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:
> 
> | 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@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@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 

[toc] | [prev] | [next] | [standalone]

#28021

On Tue, 28 Aug 2012 10:25:35 -0700 (PDT), bruceg113355@gmail.com
declaimed the following in gmane.comp.python.general:

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

	Most anything "can operate" on a network drive... But should it?

	The main thing the documentation is explaining is that one
application accessing the database FILE does NOT LOCK OTHERS from
accessing the file. Nothing about how the file is accessed. A
low-activity web service would allow lots of people to concurrently
access it -- but the processes that are doing said access are all local
to the database file.

	Technically, M$ Access/JET (which is also file server database) also
permits multiple clients -- but the locking becomes a pain.
-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
        wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/

[toc] | [prev] | [next] | [standalone]

#28025

Try incrementing the variable cursor.arraysize a lot.

Pedro.

On Tue, Aug 28, 2012 at 11:20 PM, Dennis Lee Bieber
<wlfraed@ix.netcom.com> wrote:
> On Tue, 28 Aug 2012 10:25:35 -0700 (PDT), bruceg113355@gmail.com
> declaimed the following in gmane.comp.python.general:
>
>>
>> Doesn't the last paragraph imply that SQLite can operate on a network drive.
>>
>
>         Most anything "can operate" on a network drive... But should it?
>
>         The main thing the documentation is explaining is that one
> application accessing the database FILE does NOT LOCK OTHERS from
> accessing the file. Nothing about how the file is accessed. A
> low-activity web service would allow lots of people to concurrently
> access it -- but the processes that are doing said access are all local
> to the database file.
>
>         Technically, M$ Access/JET (which is also file server database) also
> permits multiple clients -- but the locking becomes a pain.
> --
>         Wulfraed                 Dennis Lee Bieber         AF6VN
>         wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/
>
> --
> http://mail.python.org/mailman/listinfo/python-list

[toc] | [prev] | [next] | [standalone]

#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:
> 
> | 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@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@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 

[toc] | [prev] | [next] | [standalone]

#28001

Is there a reason that you're using SQLite in a network environment rather than a database server?

[toc] | [prev] | [next] | [standalone]

#28008

Demian,

I am not a database expert!
I selected sqlite for the following reasons:

1) Ships with Python.
2) Familiar with Python.
3) The Sqlite description at http://www.sqlite.org/whentouse.html appears to meet my requirements:
    Very low volume and concurrency, small datasets, simple to use.

Bruce
 

On Monday, August 27, 2012 4:54:07 PM UTC-4, Demian Brecht wrote:
> Is there a reason that you're using SQLite in a network environment rather than a database server?

[toc] | [prev] | [next] | [standalone]

#28009

bruceg113 wrote:
> I selected sqlite for the following reasons:
>
> 1) Ships with Python.
> 2) Familiar with Python.
> 3) The Sqlite description athttp://www.sqlite.org/whentouse.htmlappears to meet my requirements:
>     Very low volume and concurrency, small datasets, simple to use.

All good reasons, but a database file on a network drive is
contraindication for SQLite. A Google site-specific search
for "network" on www.sqlite.org, finds such warnings as:

"We have received reports of implementations of both Windows network
filesystems and NFS in which locking was subtly broken. We can not
verify these reports, but as locking is difficult to get right on a
network filesystem we have no reason to doubt them. You are advised to
avoid using SQLite on a network filesystem in the first place, since
performance will be slow."

That said, I don't know where your 17 seconds is going.

-Bryan

[toc] | [prev] | [next] | [standalone]

#28010

On Monday, August 27, 2012 10:32:47 PM UTC-4, Bryan wrote:
> bruceg113 wrote:
> 
> > I selected sqlite for the following reasons:
> 
> >
> 
> > 1) Ships with Python.
> 
> > 2) Familiar with Python.
> 
> > 3) The Sqlite description athttp://www.sqlite.org/whentouse.htmlappears to meet my requirements:
> 
> >     Very low volume and concurrency, small datasets, simple to use.
> 
> 
> 
> All good reasons, but a database file on a network drive is
> 
> contraindication for SQLite. A Google site-specific search
> 
> for "network" on www.sqlite.org, finds such warnings as:
> 
> 
> 
> "We have received reports of implementations of both Windows network
> 
> filesystems and NFS in which locking was subtly broken. We can not
> 
> verify these reports, but as locking is difficult to get right on a
> 
> network filesystem we have no reason to doubt them. You are advised to
> 
> avoid using SQLite on a network filesystem in the first place, since
> 
> performance will be slow."
> 
> 
> 
> That said, I don't know where your 17 seconds is going.
> 
> 
> 
> -Bryan

Bryan,

Thank you for your reply. 
Are you saying having a sqlite database file on a shared LOCAL network drive is problematic? 

Bruce

[toc] | [prev] | [next] | [standalone]

#28011

bruceg113 wrote:
> Thank you for your reply.
> Are you saying having a sqlite database file on a
> shared LOCAL network drive is problematic?

Yes, mostly, I think I am saying that.

A "LOCAL network drive" is network drive, and is not a
local drive, local as the network may be. We read and
write such a drive over a network protocol, in this
case a Microsoft protocol and implementation in the
SMB/CIFS family.

Where are your 17 seconds going? Hard to tell. Is
your experience of astonishing filesystem slothfulness
rare? Not so much.

We could probably diagnose the problem in a few weeks.
We'd use some open-source tools, WireShark among them,
plus some Microsoft tools for which we might have to
pay, plus the SQLite3 project's C library. With that
investment I'd bet we could diagnose, but not cure.

-Bryan

[toc] | [prev] | [next] | [standalone]

#28012

On Mon, 27 Aug 2012 20:09:27 -0700 (PDT), bruceg113355@gmail.com
declaimed the following in gmane.comp.python.general:

> Are you saying having a sqlite database file on a shared LOCAL network drive is problematic? 
>
	I'd avoid it...

	SQLite3 (and M$ Access/JET) are "file server" databases. The client
program runs the code of the engine as part of its processes,
maintaining open file handles on the database file. That means an awful
lot of network traffic to handle a query (it's not a case of a client
getting just a few records back from the database in response to a
select/where query... instead every record in the affected tables get
transmitted across the network so the client side library can determine
if it qualifies -- and then maybe writing the result set /back/ to the
file in a temporary region before sending them back to the client as the
result; on a true client/server only the query goes across, the database
engine runs on the machine with the files, and only the smaller result
set gets returned).

	At my level of knowledge, your DIR command shell might be sending
"keep-alive" packets across the network and those of interfering with
caching on the remote end.
-- 
	Wulfraed                 Dennis Lee Bieber         AF6VN
        wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.python

csiph-web