Re: [sqlite] After ANALYZE all has become slow

[Giuseppe Costanzi]

well,
after an intense session of debug, we have come to understand the why
of these decreases of performance.
In practice in two tables on 40 they missed two indexes on a total of 50.
Now adversity has wanted these two indexes to be used more or less by
a lot of interrogations.
Restored these the problem it is practically resolved.
sqlite and python never disappoint us.
It stays us the doubt however of how come such problem is amplified
after having performed ANALYZE.

p.s.
I arrange with Keith that is difficult to change versions on you linux
OS, we use debian 5 as server.
I believe even that the panic doesn't often, help to resolve the problems...

you excuse for the trouble

regars
beppe

On Fri, Aug 8, 2014 at 2:09 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
>
> 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 X 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 "", line 2, in 
> 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: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>>boun...@sqlite.org] 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 

Re: [sqlite] After ANALYZE all has become slow

[Keith Medcalf]

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 X 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 "", line 2, in 
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: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] 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 "", line 1
>sqlite3.OperationalError: no such function: sqlite_compileoption_get
>
>On Fri, Aug 8, 2014 at 3:47 AM, Keith Medcalf <kmedc...@dessus.com>
>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:')
>>
>>>>> s

Re: [sqlite] After ANALYZE all has become slow

[Kees Nuyt]

On Fri, 8 Aug 2014 11:14:29 +0200, Giuseppe Costanzi
 wrote:

 sqlite3.sqlite_version
>'3.5.9'

Version 3.5.9 is more than 6 years old. A lot of optimizations
were introduced since that version. Please upgrade and try
again.

-- 
Groet,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] After ANALYZE all has become slow

[Giuseppe Costanzi]

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 "", line 1
sqlite3.OperationalError: no such function: sqlite_compileoption_get

On Fri, Aug 8, 2014 at 3:47 AM, Keith Medcalf <kmedc...@dessus.com> 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: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>>boun...@sqlite.org] 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 <giuseppecosta...@gmail.com>
>>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
>>sqlite-users@sqlite.org
>>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] After ANALYZE all has become slow

[Keith Medcalf]

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: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] 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 <giuseppecosta...@gmail.com>
>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
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] After ANALYZE all has become slow

[Simon Slavin]

On 7 Aug 2014, at 3:24pm, Giuseppe Costanzi  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] After ANALYZE all has become slow

[Giuseppe Costanzi]

On Thu, Aug 7, 2014 at 2:50 PM, Simon Slavin  wrote:
>
>
> On 7 Aug 2014, at 10:56am, Giuseppe Costanzi  
> wrote:
>
> > Hi to everybody,
> > I have the following problem, on a database, 20M on a server linux, when I
> > make ANALYZE all the interrogations become slow.
>
> I'm very sorry, but I'm having a little trouble with your English and want to 
> be sure I understand your report.
>
> While you are running ANALYZE, you can expect the system to run slowly.  
> ANALYZE does lots of work.  After you have finished running ANALYZE, the 
> system should not be slower than it was before you started running ANALYZE.
>
> If you have finished running ANALYZE and SQLite commands are taking longer 
> than they did before, please tell us which version of SQLite you're running.
>
> If you are executing commands in your own application can you please download 
> the SQLite shell tool and try the same commands in that ?  You can use the 
> following command
>
> .timer ON
>
> to make the shell tool show you timing for each command it runs.
>
> You should be able to reverse the results of ANALYZE using the following 
> commands:
>
> DROP TABLE sqlite_stat1;
> DROP TABLE sqlite_stat2;
> DROP TABLE sqlite_stat3;
> DROP TABLE sqlite_stat4;
>
> (Some of these will give errors because different versions of SQLite create 
> different tables.)  If you do this, and run your queries again, do they run 
> faster like they ran before you did ANALYZE ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users




ok I will try to explain better
I have finished running ANALYZE and SQLite commands are taking longer
only when I use python db api
such as
def read(self, fetch, sql, args=()):

try:
cur = self.con.cursor()
cur.execute(sql,args)
if fetch == True:
rs =  cur.fetchall()
else:
rs =  cur.fetchone()
cur.close()
return rs
except Exception, e:
self.handle_exception(e)

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

V:\>sqlite3 lab.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .timer ON
sqlite> .schema package_unloaded
CREATE VIEW package_unloaded
AS
SELECT
dict_products.dict_product_id,
products.product,
products.code,
SUM(CASE WHEN events.category_id =  0  THEN 1 ELSE 0  END) AS download,
SUM(CASE WHEN packages.available =  1  THEN 1 ELSE 0 END) AS stock,
dict_products.department_id AS department_id,
strftime('%Y-%m-%d', events.log_time) AS log_time
FROM dict_products
INNER JOIN products ON (dict_products.product_id = products.product_id)
LEFT JOIN batchs ON (dict_products.dict_product_id = batchs.dict_product_id)
LEFT JOIN packages ON (batchs.batch_id = packages.batch_id)
LEFT JOIN events ON (packages.package_id = events.package_id)
WHERE events.enable =1
GROUP BY dict_products.dict_product_id
ORDER BY products.code;
sqlite> SELECT code,product,download,stock FROM package_unloaded WHERE
department_id = 1 AND downl
oad >0 AND log_time
   ...> BETWEEN '2014-04-01' AND '2014-06-30' ORDER BY code;

some rows as
15439|VB12 Diluente|5|2
15440|FOL DTT Releasing Agent|6|1
15443|CKMB DIL|3|1
CPU Time: user 0.375000 sys 0.203125
sqlite>

sqlite> ANALYZE;
CPU Time: user 0.203125 sys 0.140625
sqlite>
sqlite> SELECT code,product,download,stock FROM package_unloaded WHERE
department_id = 1 AND downl
oad >0 AND log_time
   ...> BETWEEN '2014-04-01' AND '2014-06-30' ORDER BY code;
some rows as
15439|VB12 Diluente|5|2
15440|FOL DTT Releasing Agent|6|1
15443|CKMB DIL|3|1
CPU Time: user 0.25 sys 0.00
sqlite>
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
SELECT * FROM package_dowloaded WHERE department_id = ?  ORDER BY code;
the execution is slower if I perform
SELECT
dict_products.dict_product_id,
products.product,
products.code,
SUM(CASE WHEN events.category_id =  0  THEN 1 ELSE 0  END) AS download,
SUM(CASE WHEN packages.available =  1  THEN 1 ELSE 0 END) AS stock,
dict_products.department_id AS department_id,
strftime('%Y-%m-%d', events.log_time) AS log_time
FROM dict_products
INNER JOIN products ON (dict_products.product_id = products.product_id)
LEFT JOIN batchs ON (dict_products.dict_product_id = batchs.dict_product_id)
LEFT JOIN packages ON (batchs.batch_id = packages.batch_id)
LEFT JOIN events ON (packages.package_id = events.package_id)
WHERE department_id = ?
AND events.enable =1
GROUP BY dict_products.dict_product_id
ORDER BY products.code
and this independently from 

Re: [sqlite] After ANALYZE all has become slow

[Simon Slavin]

On 7 Aug 2014, at 10:56am, Giuseppe Costanzi  wrote:

> Hi to everybody,
> I have the following problem, on a database, 20M on a server linux, when I
> make ANALYZE all the interrogations become slow.

I'm very sorry, but I'm having a little trouble with your English and want to 
be sure I understand your report.

While you are running ANALYZE, you can expect the system to run slowly.  
ANALYZE does lots of work.  After you have finished running ANALYZE, the system 
should not be slower than it was before you started running ANALYZE.

If you have finished running ANALYZE and SQLite commands are taking longer than 
they did before, please tell us which version of SQLite you're running.

If you are executing commands in your own application can you please download 
the SQLite shell tool and try the same commands in that ?  You can use the 
following command

.timer ON

to make the shell tool show you timing for each command it runs.

You should be able to reverse the results of ANALYZE using the following 
commands:

DROP TABLE sqlite_stat1;
DROP TABLE sqlite_stat2;
DROP TABLE sqlite_stat3;
DROP TABLE sqlite_stat4;

(Some of these will give errors because different versions of SQLite create 
different tables.)  If you do this, and run your queries again, do they run 
faster like they ran before you did ANALYZE ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users