Hi
Thanks for your resposne.
In the end its not important as you point out as many options are available,
I guess I allowed myself to indulge in "idealic" fantasy for a moment :-)
S
On 10/24/06, Dennis Cote <[EMAIL PROTECTED]> wrote:
Da Martian wrote:
> Hi
>
> I understand the problem in thoery and you are saying that sqlite is
> using
> an iterative algorithm which returns data as it finds it, is this
> correct?
>
> It appears that DBs like oracle etc.. get the whole resultset into
> temporary
> memory/space and then return the query (at least for unions this appears
> true), although this is just based off assumptions based on
observations.
>
> It seems to me that the DB (ie sqlite) can determine the number of
> results
> far more effiently than a client application. The reason is, the client
> application has to call step (n) times and a column extract (n x no
> columns). While the db could just move through the results set and count
> them up without every doing any processing on the data being counted.
> Perhaps this could done as a seperate api, like preparewithcount() which
> returns the count as well. With carefull design most of the work
> needed to
> prepare the statement etc.. could avoid being repeated as would happen
> with
> a count(*) query.
>
> This is just an idea, and I am speaking simply as a developer, but one
> who
> has not looked at sqlite implentation at all.
>
Yes, sqlite iterates and returns each result row as it is located.
SQLite also has a legacy sqlite3_get_table API that will return the
entire result set in a table in ram. It can fail if there is not enough
memory to hold the result set though.
Your idea can (almost) be implemented in your application like this.
int prepare_and_count(sqlite3* db, const char* sql, int len,
sqlite3_stmt** s, const char** tail, int* count)
{
int rc = sqlite3_prepare(db, sql, len, s, tail);
*count = 0;
if (rc == SQLITE_OK) {
while (sqlite3_step(*s) == SQLITE_ROW)
++(*count);
sqlite3_reset(*s);
}
return rc;
}
This will avoid the need to prepare two queries by using the same query
twice, once to count the result rows and a second time to collect the
results. It does require N extra calls to sqlite3_step (which are very
low overhead compared to the execution of a step).
The extra calls to step are eliminated if you use a "select count(*)
...." query instead. With a count query SQLite will scan through the
table as quickly as possible and count the results without stopping and
returning to the caller after each row. But this requires a second query
to be prepared.
When you look at the big picture though, optimizing the count query
isn't likely worth the effort. The count is usually only needed to
implement GUI controls like scrollbars. The time is takes to collect the
results and present them in the GUI will dominate the time it takes to
prepare and execute a second count query unless the result set is very
large. With large results the execution time of the count query
dominates, and the overhead time to prepare the query becomes
insignificant. It really doesn't take that long to prepare a count query.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
