SQLite Forum

How to skip intermediate data of DB in file?
Login

How to skip intermediate data of DB in file?

(1) By lopot (Lyalot) on 2021-12-07 07:22:13 [link] [source]

I create database of rather big (some Gbytes) size. All operations are of type "write to DB", no read operations. While adding records to DB, memory of computer is also captured. Is any possibility to stop the stream of SQL-operations, to write accumulated data of DB into output file, clear memory and then to continue main stream of SQL-write commands?

(2) By Stephan Beal (stephan) on 2021-12-07 09:19:03 in reply to 1 [link] [source]

Is any possibility to stop the stream of SQL-operations, to write accumulated data of DB into output file, clear memory and then to continue main stream of SQL-write commands?

That's entirely up to your application which imports the SQL data. Import it in blocks of 10k rows or 10M rows or whatever you need.

If you're using the sqlite3 shell application (as opposed to the library) to do the import, break your input files into chunks of a size appropriate for your systems. On Unix-like systems, the split CLI command can be used to split files into X number of bytes or lines. On non-Unix-like systems (i.e. Windows), you'll have to provide your own solution.

(3) By Gunter Hick (gunter_hick) on 2021-12-07 10:03:11 in reply to 1 [link] [source]

There are two issues here:

Transactions: By default, SQLite uses automatic transactions, i.e. each statement is run in its own transaction. This will be slow for initial loads, as data needs to be committed to the database after each insert. Enclosing multiple INSERT statements between BEGIN and COMMIT allows SQLite to perform multiple changes of the same set of pages before writing them to disk. This will run much faster.

Memory Managment: SQLite does require some memory to operate (larger transactions require more memory) and memory that is freed is generally not returned to the OS because most useful processes expect to be using that memory again quite soon - like processing the next transaction. A well written process will tend to grow its memory to the point where new processing can be run by reuse of freed memory without needing "new" memory.

A well written application using sensible sized transactions will show an initial increase in memory requirement and then remain stable.

If you are observing linear increase im memory requirement, then your application is probably allocating memory for each new statement and never releasing it. This is called a memory leak and is 99,99% the fault of your application, as SQLite is very carefully tested to not cause memory leaks.

Unfortunately you are not even sharing which version of SQLite you are using, let alone which API routines your are calling from which language nor any of the statements. This severely limits the amount of help you will get.

(4) By Mangal Prajapati (V16be1p11) on 2021-12-07 19:41:30 in reply to 3 [link] [source]

I want example in c, c++. For feel this below statement practically.

"A well written process will tend to grow its memory to the point where new processing can be run by reuse of freed memory without needing "new" memory."

(5) By Larry Brasfield (larrybr) on 2021-12-07 20:16:10 in reply to 4 [link] [source]

I want example in c, c++.

Are you suggesting that somebody else either post some code that behaves this way or go find some for you to study? If that is your hope, you should prepare yourself for disappointment. Otherwise, you need to clarify.

FYI, Gunter has stated something that is true for practically all programs that must allocate memory to operate and are written by careful programmers. It should not be difficult to find some. The SQLite shell (with its incorporated SQLite library) would be a good example. See the Downloads page.

(10) By Gunter Hick (gunter_hick) on 2021-12-09 06:34:44 in reply to 4 [source]

You need to call free() on any memory you allocated by calling malloc/calloc.

You need to call sqlite3_free() on any memory you allocated by calling sqlite3_malloc() et. al. and any memory passed back from SQLite to your control, like the errmsg parameter.

You need to call sqlite3_finalize() when you are finished using a statement prepared by calling sqlite3_prepare().

(6) By Simon Slavin (slavin) on 2021-12-07 20:49:48 in reply to 1 [link] [source]

SQLite does not use a background process or a server process. It does things only when you call one of the functions of the SQLite API. So if you want to "stop the stream of SQL-operations" just stop calling the SQLite API.

To clear memory of accumulated data of SQLite, close all SQLite connections. Note that you cannot properly close a SQLite connection unless all statements of that connection have had sqlite3_finalize() or sqlite3_reset() called on them.

(7) By Keith Medcalf (kmedcalf) on 2021-12-07 21:24:37 in reply to 6 [link] [source]

To clear memory of accumulated data of SQLite, close all SQLite connections.

This is untrue. This will "free" the memory used by SQLite3 (or whatever else you close and free). It will not, however, reduce the size of the virtual arena which has been allocated by the Operating System to satisfy memory allocation requests from the executing process.

That is, if you do something like this p = malloc(1000000) will allocate 1 million bytes (pointed to by p) from the virtual arena to the process. If the arena is too small, then the address space of the arena will be increased (alternatively another V:V block may be allocated to the process by the Operating System from which the allocation request can be satisfied).

Subsequently executing free(p) will return the block of 1 million bytes back into the memory allocation arena provided to the process by the Operating System.

How the Operating System manages V:V mapped memory blocks allocated to the memory arena belonging to the process does not necessarily mean that the size of the arena will decrease.

Let's take a specific example on Windows x64.

The process makes memory allocation (malloc, calloc, etc) requests which are satisfied from a "pool" (V:V arena) allocated by the Operating System to the process. When this memory is released by the process (free) nothing at all happens except that the allocation is moved to the process runtime free list.

Only when the process terminates is the V:V memory arena released back to the Operating System free pool.

Some runtime's and some Operating Systems may "coalesce" each of their respective free lists so that only when the entire "arena" block allocated to the process (or one of the arena blocks) is entirely free, then that arena block can be deallocated from the process by the Operating System.

This means that, as has been the case since forever, processes which do much malloc/free will expand so that the total size of all arena blocks is sufficient that the use of malloc/free does not require the Operating System to allocate more V:V address blocks to the process.

Badly written software (including ill-conceived malloc/free) will result in what is called "memory fragmentation".

You may wish to refer to https://sqlite.org/malloc.html

There are many scholarly articles available which discuss memory allocation behaviour, though a proper analysis requires that you have a background in mathematics (not arithmetic).

(8) By Simon Slavin (slavin) on 2021-12-08 01:19:30 in reply to 7 [link] [source]

That is actually what I wrote, and what I meant. I didn't write that the memory blocks would be released, just that they would no longer be occupied by SQLite data (or at least, not data SQLite was holding on to, or had allocated to itself). As you wrote, releasing memory allocated to a process is that process' problem.

(9) By Keith Medcalf (kmedcalf) on 2021-12-08 01:31:39 in reply to 8 [link] [source]

Note to the OP:

You need to use the facilities of the Operating System to manage the process heap. For example, on Windows you can define SQLITE_WIN32_MALLOC and SQLITE_WIN32_HEAP_CREATE so that a separate arena is allocated to SQLite3.

You can then call sqlite3_win32_compact_heap or sqlite3_win32_reset_heap to either comact or reset the heap.

You can probably do the same thing on other Operating Systems.