Forum > Database
How to speed up Delete
HatForCat:
I am trying to help a friend speed up his old software. It uses ZEOS 6.6 (too much work to change to ZEOS-v7) and SQLite3. Running the program for a few hours will result in about 10,000 records being gathered.
We need to clean out records as nothing older than last 1000 records is needed. Using the following code it is abysmally slow even if called several times a day. Could someone please give me some SQL language to do this as I am sure it will be quicker, but I am not yet very skilled with SQL.
--- Code: ---procedure Tdm.ClearToLatest;
var
AllDone : Boolean;
begin
tblData.DisableControls;
try
AllDone:=False;
tblData.First;
repeat
tblData.Delete;
AllDone:=tblData.RecordCount <= 1000;
until AllDone;
finally
tblData.EnableControls;
end;
end;
--- End code ---
rvk:
If there is some index or ID generated, this can be done with one SQL-statement, in which case it will be executed locally by the server which will be much much faster.
Something like this, but it depends on the table-definition:
--- 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 id NOT IN (SELECT id FROM TABLE ORDER BY id DESC LIMIT 1000)(there are other options like older than a certain time or if ID is generated sequentially you can just delete everything with id < last_id - 1000)
HatForCat:
Thanks RVK, but I got a bit more creative with searching and found this for a Query...
--- Code: --- tStr:='DELETE FROM tblData WHERE inDate <= date(''now'',''-2 day'')';
--- End code ---
Took out 20,000 records in about two seconds. By the way, anyone using that line, the ''now'' and ''-2 day'' are using multiple single quotes (un-shifted quote-key) and NOT the keyboard double-quotes "now" and "-2 day" (shifted quote-key) although they look pretty much the same on this page. :)
rvk:
--- Quote from: HatForCat on July 04, 2016, 07:46:19 pm ---... but I got a bit more creative with searching and found this for a Query...
--- Code: --- tStr:='DELETE FROM tblData WHERE inDate <= date(''now'',''-2 day'')';
--- End code ---
--- End quote ---
Yep, that will work too.
But you said:
--- Quote from: HatForCat on July 04, 2016, 06:55:14 pm ---Running the program for a few hours will result in about 10,000 records being gathered.
--- End quote ---
If you have 10.000 records in a few hours, you're still left with a lot of records if you only delete everything older than 2 days. You could also use "-4 hours" or something in which case you come closer to those 1000 records you wanted.
The problem is that if you use -x time in the query you never know how many records you are left with. If for instance in your case the last 2 days only 2 records were added you would end up with just 2 records (which could be a problem or too few). Using the ID-method I showed ensures exactly 1000 records after the statement.
But if the -2 days works (it depends on the situation) this is fine too.
HatForCat:
Thanks again RVK, yup, sorry I used the test program I wrote which was deleting by days. Should have come back and changed it. I appreciate your speedy and detailed reply though.
Your ID-suggestion is not an easy one as there is a lot of data streaming in and not very well formatted -- and with a large number if ID type items. There could be 10,000 of "001" one day then none for another week. The only current Index is on Date. :)
The 1000 records remaining was an arbitrary minimum and your point is taken there could be very few items left, but given the constant stream, using the -2 or -1 days will have to suffice. 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.
Navigation
[0] Message Index
[#] Next page