On Thu, Aug 7, 2014 at 2:50 PM, Simon Slavin <[email protected]> wrote:
>
>
> On 7 Aug 2014, at 10:56am, Giuseppe Costanzi <[email protected]>
> 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
> [email protected]
> 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.250000 sys 0.000000
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 ANALTYZE but after run ANALYZER the
situation worsens
regards
beppe
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
