CODEWITHSUNDEEP
csqlite
user12050586
user12050586

Reputation:

Understanding sqlite3_step and why it "takes time" between iterations

I'm wondering why sometimes the query results return quite quick and other times it seems to take a while between the sqlite3_step() calls. For example:

rc = sqlite3_prepare_v2(db, "SELECT * FROM mytable m GROUP BY field1, field2, field3, field4, field5 LIMIT 500", -1, &stmt, NULL);
while ((rc = sqlite3_step(stmt)) == SQLITE_ROW);

Each sqlite3_step takes about the same time when I view the print statements, and the fetch statements add up to take about 6.5s

And then with a normal, fast unaggregated query, sqlite3_step is almost instant:

rc = sqlite3_prepare(db, "SELECT * FROM mytable LIMIT 500", -1, &stmt, NULL);

In the above query, everything takes under 0.1s to query and retrieve.

I understand why the query itself would be much slower, but it seems like sqlite3_stmt is not really "doing anything" and all the SQL-ing work goes into the sqlite3_step and is spread out within each step. A few questions from this:

The documentation of this method is listed here, but it doesn't say much about what it actually does other than saying what return codes it might give. From the docs:

This routine is used to evaluate a prepared statement that has been previously created by the sqlite3_prepare() interface. The statement is evaluated up to the point where the first row of results are available. To advance to the second row of results, invoke sqlite3_step() again. Continue invoking sqlite3_step() until the statement is complete. Statements that do not return results (ex: INSERT, UPDATE, or DELETE statements) run to completion on a single call to sqlite3_step().

What does "to completion" mean in this case? Does it basically just do a LIMIT 1 and the user needs to call it again to do an OFFSET 1, or what does that mean exactly?

Upvotes: 1

Views: 1747

Answers (1)

Shawn
Shawn

Reputation: 52409

sqlite3_prepare() compiles the statement into sqlite's internal bytecode. sqlite3_step() evaluates that bytecode up to the point where a row is returned or there are no more rows. The next call resumes evaluating at that point; it doesn't always calculate all result rows all at once, but often just one at a time. sqlite3_step() can take different times depending on how much work has to be done to get the next row (like processing groups of different sizes), if pages have to be fetched from disc instead of being present in the page cache, etc.

Upvotes: 4

Related Questions