Weird truncate performance problem after upgrade to 2.8.2

[Rene Dohmen]

Hi Web2py-Users,

We have an app which imports data form a .csv file into a sqlite table. The CSV has 70k lines. 

Once a week we re-import data with a new .csv.

The controller that handles this job:

1) handles the upload and moves it to location for future reference

2) truncates the main table and 2 subtables (with linked data, main table has a auth.signature)

3) imports the .csv and use the info in the data to refill the other 2 tables.

Before our update from 2.4.6  to 2.8.2 it was very fast. Max 10-15 seconds, for steps 1-3.

After the update (still not 100% sure if the update to 2.8 caused it; because we did add other functionality also and updated web2py itself); we had performance issues; The import suddenly needed somewhere between 15m and 1 hour. After a lot of debugging we isolated the problem to the truncate part of the steps.

The truncate of 68000 records took 2040 seconds (with a delete form appadmin: 3 seconds for 100 records).

We tried it from appadmin also, tried execute_sql; but they all had the same issue.

As a workaround we now drop the table from sqlite (via sqlite3 and a os.system call), delete the *tablename.table file from the databases/ folder and then import the data again. But a more web2py-ish solution would be welcome. ;)

Any suggestions in what could cause the problem are more then welcome.

Kind Regards

Rene

[Anthony]

What happens if you take the exact SQL that web2py executes and instead execute it outside of web2py altogether (i.e., via some other database client)?

[Rene Dohmen]

We now did a drop outside of web2py. That's very fast.

In the web2py logs we only see:

DELETE FROM table;

When we run that from appadmin or via execute_sql: very slow (3 sec for 100 rows)

When we run it from sqlite3: it's done <1 sec

[Niphlod]

wait a second.......
- DROP
- TRUNCATE
- DELETE

what are you doing in web2py and what are you issuing via sqlite3 cmdline exactly ?