wait a second....... - DROP - TRUNCATE - DELETE what are you doing in web2py and what are you issuing via sqlite3 cmdline exactly ?
On Thursday, February 27, 2014 8:57:43 PM UTC+1, Rene Dohmen wrote: > > 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 > > > On Thursday, February 27, 2014 6:01:10 AM UTC+1, Anthony wrote: >> >> 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)? >> >> On Wednesday, February 26, 2014 7:06:46 PM UTC-5, Rene Dohmen wrote: >>> >>> 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 >>> >>> -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
