We already discussed VACUUM, and REINDEX does pretty much what it says on the box. While there might be an arguably present
performance increase, it should be negligible - unless you are using ascending or descending indices to which I am sure reindex will
recreate the index in the proper order as with other SQL engines (but am not 100% sure in SQLite's case) or help redistribute how
the index is spaced inside the pages. Either way, the raw performance gain should be quite small.
What I think you are looking for is ANALYZE which looks at the shape of your data more than the schema and provides meaningful
statistics in an additional table which assists the query planner in making better query plan decisions and hopefully more efficient
ways of stepping through a query. Note that this is an "optimisation", as in it improves how the query is executed, as opposed to
providing a raw performance increase. Also note that there is no off-the-shelf standard on how often to analyze - It all depends on
how often and by what degree the shape of your data might change. I'd say a change of 20GB in a 50GB DB as you mentioned, is more
than sufficient cause to re-run analyze.
Other optimisations exist for various things and SQLite itself is always improved where possible - All this said, other than using
the latest SQLite engine, checking your queries are not structured silly and running "analyze" once in a while - SQLite is about as
fast as is physically possible and there is no real magic performance improvement command (if there was, it would already be part of
the normal way SQLite does things).
Some final checks would be to ensure hardware that is as fast as possible, using enough cache and so on. If the speed is still too
slow, you can start to think of partitioning the DB (splitting it into different smaller DBs) and then querying each in a separate
process and amalgamating the results (effectively multi-threading the app) and so on. Another way is to have static "queried output"
and only update the bits which have changed with some clever programming, meaning you have to firstly keep track of what changed,
and so on.
At this point you have to ask yourself exactly how important the speed is? While the extra programming will be quite a job, it
might be well worth the effort if the speed is important enough.
On another note, advising the clients on which things to run is probably less safe than building in some automatic scheduled
maintenance procedure which could include any/all of the above and be run in some off-line or off-peak period.
On 2013/12/14 02:37, veeresh kumar wrote:
We are looking for database maintenance commands for the Sqlite database and
came across 2 commands which we felt that it would improve the performance if
used. The commands are VACUUM and REINDEX. I came to know that VACUUM just
helps us to reclaim the space and does not give any performance increase. Just
wanted to check of REINDEX or any other Sqlite command which you suggest users
to run it every month?
Our database size varies from 30GB to 50 GB,with various operations like
insert/update/delete would be performed on day to day basis.Just wanted to know
if there is a need to maintain the sqlite database so that the performance does
not degrade or just leave as it is.
_______________________________________________
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
