The default installation on my VPS running the current release of CentOS is
pretty old as well. Unfortunately it is kind of difficult to upgrade the
version of SQLite (and/or Python) on some Linux distributions because there are
other system dependencies on the versions of Python. For example on CentOS
there are dependencies between Python version and the package manager (yum in
the case of CentOS) making such upgrades sometimes very problematic.
Linux XXXXX 3.15.4-x86_64 #1 SMP Mon Jul 7 08:42:36 EDT 2014 x86_64 x86_64
x86_64 GNU/Linux
# python
Python 2.6.6 (r266:84292, Jan 22 2014, 09:42:36)
[GCC 4.4.7 20120313 (Red Hat 4.4.7-4)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> cn = sqlite3.connect(':memory:')
>>> sqlite3.version
'2.4.1'
>>> sqlite3.sqlite_version
'3.6.20'
>>> for i in range(20):
... cn.cursor().execute('select sqlite_compileoption_get(?);',(i,)).fetchall()
...
Traceback (most recent call last):
File "<stdin>", line 2, in <module>
sqlite3.OperationalError: no such function: sqlite_compileoption_get
>>>
Also, the version of the interface module (sqlite3.py / _sqlite3.so) between
python and sqlite3 (sqlite3.so) is quite old as well (compared to the current
versions of python and sqlite3 -- though the interface has not significantly
changed in how it implements most connection/cursor operations).
Is the command line shell of sqlite3 you are using in your testing the same
version of SQLite as the shared object being used by python?
# sqlite3
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
If they are different, that might explain the different results between the two
depending on the versions. There have been many changes in sqlite since
version 3.6.20 which might affect performance of actually running a query,
depending on the version of the command shell in use.
If they are the same version then the time taken to execute a select at the
command line tool interface and retrieve the results should not be
significantly different than what you see using from python calls. cursor()
methods .execute(...) corresponds to sqlite3_prepare / sqlite3_bind (if
parameters are given); .fetchone() corresponds to a single sqlite3_step and the
sqlite3_value calls necessary to retrieve a row; and, .fetchall() is simply
.fetchone() in a loop until no more rows are available and accumulates the
retrieved row tuples as a list (all executed in C code, not in python code, so
it should be relatively the same efficiency as the sqlite3 shell tool is).
>-----Original Message-----
>From: [email protected] [mailto:sqlite-users-
>[email protected]] On Behalf Of Giuseppe Costanzi
>Sent: Friday, 8 August, 2014 03:14
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] After ANALYZE all has become slow
>
>using putty....
>
>Linux SRVXXX 2.6.26-2-686 #1 SMP Mon Aug 30 07:01:57 UTC 2010 i686
>
>The programs included with the Debian GNU/Linux system are free software;
>the exact distribution terms for each program are described in the
>individual files in /usr/share/doc/*/copyright.
>
>Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
>permitted by applicable law.
>You have new mail.
>Last login: Fri Aug 8 08:22:45 2014 from trantor.ia.lan
>hal9000@SRVXXX :~$ clear
>hal9000@SRVXXX :~$ python
>Python 2.5.2 (r252:60911, Jan 24 2010, 14:53:14)
>[GCC 4.3.2] on linux2
>Type "help", "copyright", "credits" or "license" for more information.
>>>> import sqlite3
>>>> cn = sqlite3.connect(':memory:')
>>>> sqlite3.version
>'2.3.2'
>>>> sqlite3.sqlite_version
>'3.5.9'
>>>> cn.cursor().execute("SELECT sqlite_version();").fetchall()
>[(u'3.5.9',)]
>
>>>> for i in range(20):
>>>> print cn.cursor().execute('SELECT
>sqlite_compileoption_get(?);',(i,)).fetchall()
>
>
>
>>>> sqlite3.OperationalError: no such function: sqlite_compileoption_get
> File "<stdin>", line 1
> sqlite3.OperationalError: no such function: sqlite_compileoption_get
>
>On Fri, Aug 8, 2014 at 3:47 AM, Keith Medcalf <[email protected]>
>wrote:
>> Giuseppe,
>>
>> What version of the sqlite3 library is python using?
>>
>>>python
>> Python 2.7.8 (default, Jun 30 2014, 16:03:49) [MSC v.1500 32 bit
>(Intel)] on win32
>> Type "help", "copyright", "credits" or "license" for more information.
>>>>> import sqlite3
>>>>> cn = sqlite3.connect(':memory:')
>>
>>>>> sqlite3.version
>> '2.6.0'
>>
>>>>> sqlite3.sqlite_version
>> '3.8.6'
>>
>>>>> cn.cursor().execute('select sqlite_version();').fetchall()
>> [(u'3.8.6',)]
>>
>>>>> for i in range(20):
>> ... cn.cursor().execute('select
>sqlite_compileoption_get(?);',(i,)).fetchall()
>> ...
>> [(u'DEFAULT_LOCKING_MODE=0',)]
>> [(u'ENABLE_COLUMN_METADATA',)]
>> [(u'ENABLE_FTS3',)]
>> [(u'ENABLE_FTS3_PARENTHESIS',)]
>> [(u'ENABLE_FTS4',)]
>> [(u'ENABLE_LOAD_EXTENSION',)]
>> [(u'ENABLE_MEMORY_MANAGEMENT',)]
>> [(u'ENABLE_MEMSYS5',)]
>> [(u'ENABLE_RTREE',)]
>> [(u'ENABLE_STAT4',)]
>> [(u'MAX_SCHEMA_RETRY=50',)]
>> [(u'SOUNDEX',)]
>> [(u'SYSTEM_MALLOC',)]
>> [(u'TEMP_STORE=2',)]
>> [(u'THREADSAFE=1',)]
>> [(None,)]
>> [(None,)]
>> [(None,)]
>> [(None,)]
>> [(None,)]
>>>>>
>>
>>>-----Original Message-----
>>>From: [email protected] [mailto:sqlite-users-
>>>[email protected]] On Behalf Of Simon Slavin
>>>Sent: Thursday, 7 August, 2014 09:26
>>>To: General Discussion of SQLite Database
>>>Subject: Re: [sqlite] After ANALYZE all has become slow
>>>
>>>
>>>On 7 Aug 2014, at 3:24pm, Giuseppe Costanzi
><[email protected]>
>>>wrote:
>>>
>>>> I have finished running ANALYZE and SQLite commands are taking longer
>>>> only when I use python db api
>>>> such as
>>>> [...]
>>>>
>>>> however I have done what you have pointed out me and in effects the
>>>> speed of execution
>>>> from the shell is best after having performed ANALYZE
>>>> as I would be me expected [snip]
>>>
>>>> I have also noticed that, using python db api2 if I perform a query
>>>> recalling a view on the database
>>>> this it is slower if I directly write the query in the script of
>python
>>>> For example if I perform [...]
>>>> and this independently from ANALTYZE but after run ANALYZER the
>>>> situation worsens
>>>
>>>Very good. If you had a situation where the Shell Tool got slower on
>>>ANALYZE then it would definitely be an error in SQLite and we could
>>>investigate. But you are reporting a situation where the Shell Tool
>gets
>>>faster but Python gets slower.
>>>
>>>This makes me think that the fault is with your Python library. I
>don't
>>>know enough about Python to help. I am going to hope that someone
>>>familiar with Python sees this and can help you.
>>>
>>>Simon.
>>>_______________________________________________
>>>sqlite-users mailing list
>>>[email protected]
>>>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [email protected]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
