Maximum result set size

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Maximum result set size

Tim Streater-3
What is the maximum size in bytes that a result set may be? And what happens if that size were to be exceeded?


--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Maximum result set size

Simon Slavin-3
On 11 Mar 2019, at 7:30pm, Tim Streater <[hidden email]> wrote:

> What is the maximum size in bytes that a result set may be? And what happens if that size were to be exceeded?

[The following is simplified for clarity.  I discuss only worst cases and ignore caching.]

SQLite does not prepare an entire result set at once.  Instead, it returns one row each time you call sqlite3_step().  It's up to your own program to process each row as it is returned.  Since SQLite does not hold more than one row in memory at once, theoretically there's no limit on how many rows it can return for a single query.

However, some queries may require SQLite to prepare a temporary index.  For example, a SELECT with an ORDER BY where there's no useful index.  A large temporary index would be saved on disk until it is no longer needed.  In these situations, SQLite is limited by the amount of disk space made available to the application calling SQLite.

Similarly, you may be calling sqlite3_exec() instead of sqlite3_step().  Or you may be calling SQLite through a library which does hold an entire result set in memory at one time.  In that case, the maximum size of a result set is limited by the amount of memory the application can use, and management of that memory takes place outside of SQLite.

Attempts to exceed any of the above limits would lead to result codes such as SQLITE_NOMEM or SQLITE_IOERR.  A list of result codes can be found here:

<https://sqlite.org/c3ref/c_abort.html>

For details on the limits to SQLite's other things such as the most rows a table can hold, please see this document:

<https://sqlite.org/limits.html>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Maximum result set size

wmertens
In reply to this post by Tim Streater-3
There is no fixed limit, and the sqlite API just walks through the results,
so any memory overrun that happens is due to application level code.

Wout.


On Mon, Mar 11, 2019 at 8:30 PM Tim Streater <[hidden email]> wrote:

> What is the maximum size in bytes that a result set may be? And what
> happens if that size were to be exceeded?
>
>
> --
> Cheers  --  Tim
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Maximum result set size

wmertens
Don't listen to me, Simon's answer is way better :)

Wout.


On Mon, Mar 11, 2019 at 9:22 PM Wout Mertens <[hidden email]> wrote:

> There is no fixed limit, and the sqlite API just walks through the
> results, so any memory overrun that happens is due to application level
> code.
>
> Wout.
>
>
> On Mon, Mar 11, 2019 at 8:30 PM Tim Streater <[hidden email]> wrote:
>
>> What is the maximum size in bytes that a result set may be? And what
>> happens if that size were to be exceeded?
>>
>>
>> --
>> Cheers  --  Tim
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Maximum result set size

Tim Streater-3
In reply to this post by Simon Slavin-3
On 11 Mar 2019, at 20:21, Simon Slavin <[hidden email]> wrote:

> On 11 Mar 2019, at 7:30pm, Tim Streater <[hidden email]> wrote:
>
>> What is the maximum size in bytes that a result set may be? And what happens
>> if that size were to be exceeded?
>
> [The following is simplified for clarity. I discuss only worst cases and
> ignore caching.]
>
> SQLite does not prepare an entire result set at once. Instead, it returns one
> row each time you call sqlite3_step(). It's up to your own program to process
> each row as it is returned. Since SQLite does not hold more than one row in
> memory at once, theoretically there's no limit on how many rows it can return
> for a single query.

[snip]

> <https://sqlite.org/limits.html>

Thanks for that, Simon. That would account for there being nothing on the limits.html page about a maximum result set size. My question came up because someone, using a language that has an API for a number of flavours of SQL (including SQLite), was experiencing a crash when his result set got to about a gig in size. So, as seems very often to be the case, it must be the interface API code to the SQLite library that was at fault.

@Wout: nothing wrong with your reply, it was the TL:DR; version :-)


--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Maximum result set size

Simon Slavin-3
In reply to this post by wmertens
On 11 Mar 2019, at 8:32pm, Wout Mertens <[hidden email]> wrote:

> Don't listen to me, Simon's answer is way better :)

Your answer was absolutely correct.  I just answered some other stuff too.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Maximum result set size

niklas
A clarification about sqlite3_exec please.

Surely that works the same way as sqlite3_step in respect to memory use
since it's using step internally and just forwards the results to the
callback function of exec.

Or did I miss something?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Maximum result set size

Simon Slavin-3
On 12 Mar 2019, at 6:38am, niklas <[hidden email]> wrote:

> Surely that works the same way as sqlite3_step in respect to memory use since it's using step internally and just forwards the results to the callback function of exec.
>
> Or did I miss something?

You missed nothing.  _exec() returns only the same integer result code as most other API calls.  It does not return the result from a SELECT, for example.

Just as with individual calls to _step(), _exec() never has to accumulate a complete set of results.  So it doesn't have to accumulate results in memory, using a lot of memory if there are a lot of results.

You can make it call your callback with each row, one by one, but it does not remember the rows itself.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users