Why web2py App performance has degraded all of sudden with SQLLITE

[Sandeep Bondre]

I do not see a solution to this problem .

Can some one really help what is the solution to this 

Regards

Sandeep

sandeep...@gmail.com

[John Bannister]

Hi Sandeep,

Would be good if you can supply a bit more information in order to help you find the solution.

questions:

1: Has your database grown recently?

2: Is web2py slow on all queries or just on some tables? 
3: Can you use _lasesql to see your sql calls and timings?

4: Are you using SQLFORM.grid or custom forms to do your queries?

5: Some genric idea of what you are trying to do?

BR

John 

On Saturday, 2 May 2020 12:20:44 UTC+2, Sandeep Bondre wrote:

I do not see a solution to this problem .

Can some one really help what is the solution to this 

Regards

Sandeep

sandee...@gmail.com

[Tom Clerckx]

Hi Sandeep,

A bit a late reply, but it may be useful for someone later-on.

I recently had the exact same issue, quite suddenly, things started to become slow.

Here's what I did to solve it:

First thing to do: make a backup of your project!

Second thing to do is to vacuum your db, which does some optimizations that gets rid of some garbage in your db.

You can just do:   db.executesql('VACUUM') 

Ref: https://www.sqlite.org/lang_vacuum.html

Third thing you can do is to:

0) Make sure all your tables have a uuid field 

1) Export all your data to a csv file: 

with open('somefile.csv', 'wb') as dumpfile: db.export_to_csv_file(dumpfile)

2) Remove your db tables

3) Import again all your data from the csv file: 

with open('somefile.csv', 'rb') as dumpfile: db.import_from_csv_file(dumpfile, id_map={})

Ref: http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#CSV-all-tables-at-once-

In my case the export/import of the data finally did the trick.

I'm still not sure why this seems to speed up everything, maybe someone in this forum here has a good explanation for that?

Hope it works for you too!