Forum > Database

How to speed up Delete

<< < (2/2)

rvk:

--- Quote from: HatForCat on July 05, 2016, 12:52:47 am ---It would be nice to include the 1000-limit with the Date, but I can't find anything that relates to "Remaining Record Count" within SQLite3. So, no way I can tell it to stop at 1000, 5000 or whatever.

--- End quote ---
You can (kinda). There is no way to tell which records are the last 1000 (maybe there is but I'm on mobile now so can't check) but you can limit it to the date of the previous 1000 records (which might be more but never less).


--- Code: SQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---DELETE FROM TABLEWHERE DATE NOT IN  (SELECT DATE FROM TABLE ORDER BY DATE DESC LIMIT 1000)
And of course you can combine both your where with this one (with an and) so you keep all records which are newer than two days and at least 1000 (or more records). But if you have 50.000 records on that day (exactly 1000 records ago) it will keep all 50.000 records because it can't know which are "first" on that day.

shobits1:

--- Quote from: sqlite ---https://www.sqlite.org/lang_createtable.html#rowid

Except for WITHOUT ROWID tables, all rows within SQLite tables have a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid". The rowid value can be accessed using one of the special case-independent names "rowid", "oid", or "_rowid_" in place of a column name. If a table contains a user defined column named "rowid", "oid" or "_rowid_", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value.

--- End quote ---

as far as i understood right, you can use rvk's sql script without problem (with little mod)

--- Code: SQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---DELETE FROM `TABLE`WHERE rowid NOT IN  (SELECT rowid FROM `TABLE` ORDER BY id DESC LIMIT 1000) 

rvk:

--- Quote from: shobits1 on July 05, 2016, 06:14:30 am ---as far as i understood right, you can use rvk's sql script without problem (with little mod)

--- End quote ---
Yes, I kinda suspected there was something internal but couldn't check at the time.

So the final result could be:

--- Code: SQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---DELETE FROM tblDataWHERE   (inDate <= DATE('now','-2 day') AND  (rowid NOT IN (SELECT rowid FROM tblData ORDER BY id DESC LIMIT 1000)) Will delete ALL records EXCEPT records of the last 2 days (today and yesterday) and IF that's less than 1000 it will keep that 1000 records (regardless of age).

Note: The rowid-method only works for SQLite. Other DB-engines might have other options. But it's always better to have a unique ID/primary key anyway !!!

HatForCat:
Thanks guys, I remember reading about "rowid" quite some time back but had forgotten all about it as I don't use SQL much at all.

I am working with stuff that was written a few years back so want to keep as much as possible and the Serial-Data-In table has no unique identifier, I guess they just wanted to grab everything as they don't appear to using rowid anywhere.

Back then volume coming in was not a big problem but the load has slowly been increasing to where it was taking an hour to clear old stuff using the OP routine every day.

Your help is very much much appreciated.

Navigation

[0] Message Index

[*] Previous page

Go to full version