sqlite failed::out of memory while deleting the records

(1) By Mangal Prajapati (V16be1p11) on 2022-02-16 10:13:56 [link] [source]

When my device execute below query

DELETE FROM TBLINSPECTEDTICKETDETAIL WHERE DATE(DTEINSPECTEDTICKETDATETIME) <='2022-02-11' and BUPLOADED = 1 and BMOVED = 1 and STRWAYBILLNO !='W2001220866' LIMIT 300;

Its give : "sqlite failed::out of memory"

How to resolve this error.

(2) By Simon Slavin (slavin) on 2022-02-16 11:22:18 in reply to 1 [link] [source]

Do you have an index on the table TBLINSPECTEDTICKETDETAIL which would allow SQLite to find those rows quickly ?

Is it finding the rows which is the problem or deleting them ? Change the command to a SELECT. Does your application still run out of memory ?

(4) By Mangal Prajapati (V16be1p11) on 2022-02-16 12:24:38 in reply to 2 [source]

No i am not using the index.

i am deleting that rows as per my where condition.

I have replaced the above query like below:

DELETE FROM TBLINSPECTEDTICKETDETAIL WHERE INTINSPECTEDTICKETID IN(select INTINSPECTEDTICKETID from TBLINSPECTEDTICKETDETAIL where DATE(DTEINSPECTEDTICKETDATETIME) <='"+strDateBeforeExtensionDays+"' and BUPLOADED = 1 and BMOVED = 1 and STRWAYBILLNO !='"+strWaybillNo+"' ORDER BY DTEINSPECTEDTICKETDATETIME limit 10)

But still its show same error.

when i am execute this query in sqlitebrowser software its working. but when i am executing this query on device i am getting below error:

sqlite failed::out of memory

I have so many records i.e. 24400 records.

(6) By Gunter Hick (gunter_hick) on 2022-02-16 13:19:43 in reply to 4 [link] [source]

If you change "DELETE" to "SELECT COUNT()" and omit the LIMIT clause I am guessing you will get a count approaching the total number of records. This means that
(1) the LIMIT clause does not work in your SQLite build and
(2) your mobile device (or if using MEMSYS5 as recommended, the allocated memory block) does not afford enough memory to complete the operation.

(8) By Simon Slavin (slavin) on 2022-02-16 22:53:34 in reply to 4 [link] [source]

Unfortunately you didn't run the test I suggested so I didn't learn anything.

I suspect that since there is no index to help the 'WHERE' clause, you copy of SQLite is constructing a temporary index, or iterating through the whole table and finding all qualifying rows.

You can create a good index, and if you really want the LIMIT you have to make the compilation change suggested in another post to this thread.

(3) By Gunter Hick (gunter_hick) on 2022-02-16 11:24:55 in reply to 1 [link] [source]

SQLite needs to be compiled from sources with SQLITE_ENABLE_UPDATE_DELETE_LIMIT for the LIMIT clause to work on UPDATE and DELETE statements. It is probably failing because too many records match the WHERE clause.

(5) By Mangal Prajapati (V16be1p11) on 2022-02-16 12:28:05 in reply to 3 [link] [source]

yes there are so many records but what is solution of it?

(7) By Gunter Hick (gunter_hick) on 2022-02-16 15:10:43 in reply to 5 [link] [source]

Compile SQLite as already instructed and/or provide more memory.

(9) By Mangal Prajapati (V16be1p11) on 2022-02-17 09:31:12 in reply to 7 [link] [source]

I have seen that by reducing the records. its get started to delete the record.

Ultimately i have find the solution is that i have to maintain less records or i have to delete them early such that they don't throw out of memory error.