Re: [sqlite] Regarding sqlite3_exec

[Jay Sprenkle]

On 10/24/06, Da Martian <[EMAIL PROTECTED]> wrote:


For exmaple, Lets say I run a SQL statement (its a very heavy statement
consiting of joins and subqueries). It returns 5000 rows. For speed I dont
want to retrieve 5000 rows, I want to setup a list which shows that there
are 5000 rows on the scroll bar, but only retrieves the first say 20 for
display.


My solution was simpler but most people hate it. A list that's 5000 entries
long will almost never be used by the user. Nobody has the patience to
scroll through 5000 entries. Do like google does and fetch the first N rows
of any result set and if there's more tell the user there's more and they
need to refine their query.



--
SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Regarding sqlite3_exec

[Da Martian]

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]

-

Re: [sqlite] Regarding sqlite3_exec

[Dennis Cote]

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]
-

Re: [sqlite] Regarding sqlite3_exec

[Da Martian]

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.

Thanks for your response, it was very informative, helpfull and poinient.

S


On 10/24/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


"Da Martian" <[EMAIL PROTECTED]> wrote:
> Hi
>
>
> >The optimal way is that you prepare the statement, fetch and
> > count the results with sqlite3_step.
>
> How would I "fetch and count" the results via sqlite3_step?
>
> Do you mean fetch all the records first? What if my result set is huge,
and
> I would only like to show the first few records but still know how many
> there are?
>
> For exmaple, Lets say I run a SQL statement (its a very heavy statement
> consiting of joins and subqueries). It returns 5000 rows. For speed I
dont
> want to retrieve 5000 rows, I want to setup a list which shows that
there
> are 5000 rows on the scroll bar, but only retrieves the first say 20 for
> display.
>
> Is this possible?

No, it is not possible.  In the general case where there are
user-defined functions in the query, returning the number of
rows in the result set is equivalent to the halting problem.
See

   http://en.wikipedia.com/wiki/Halting_problem

Even in the absence of the artifical constructs that make the
problem theoretically undecidable, the problem is still very
hard.  I am not aware of a general solution other than to
run the query to completion and count the rows.  I suspect
that I can write a proof that no solution exists that is
faster than running the query to completion, though I have
never taken the time to actually write that proof out.

You might be able to find special cases where you can predict
the size of the result set without actually computing the
result set.  But such techniques would only work for very
narrowly defined queries over tables with very narrowly
defined data constraints.

--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Regarding sqlite3_exec

[drh]

"Da Martian" <[EMAIL PROTECTED]> wrote:
> Hi
> 
> 
> >The optimal way is that you prepare the statement, fetch and
> > count the results with sqlite3_step.
> 
> How would I "fetch and count" the results via sqlite3_step?
> 
> Do you mean fetch all the records first? What if my result set is huge, and
> I would only like to show the first few records but still know how many
> there are?
> 
> For exmaple, Lets say I run a SQL statement (its a very heavy statement
> consiting of joins and subqueries). It returns 5000 rows. For speed I dont
> want to retrieve 5000 rows, I want to setup a list which shows that there
> are 5000 rows on the scroll bar, but only retrieves the first say 20 for
> display.
> 
> Is this possible? 

No, it is not possible.  In the general case where there are
user-defined functions in the query, returning the number of
rows in the result set is equivalent to the halting problem.
See

   http://en.wikipedia.com/wiki/Halting_problem

Even in the absence of the artifical constructs that make the
problem theoretically undecidable, the problem is still very
hard.  I am not aware of a general solution other than to
run the query to completion and count the rows.  I suspect
that I can write a proof that no solution exists that is
faster than running the query to completion, though I have
never taken the time to actually write that proof out.

You might be able to find special cases where you can predict
the size of the result set without actually computing the
result set.  But such techniques would only work for very
narrowly defined queries over tables with very narrowly 
defined data constraints.  

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Regarding sqlite3_exec

[Da Martian]

Hi



The optimal way is that you prepare the statement, fetch and
count the results with sqlite3_step.


How would I "fetch and count" the results via sqlite3_step?

Do you mean fetch all the records first? What if my result set is huge, and
I would only like to show the first few records but still know how many
there are?

For exmaple, Lets say I run a SQL statement (its a very heavy statement
consiting of joins and subqueries). It returns 5000 rows. For speed I dont
want to retrieve 5000 rows, I want to setup a list which shows that there
are 5000 rows on the scroll bar, but only retrieves the first say 20 for
display.

Is this possible? I know I can do a "select count(*) from (SQL)" but its a
heavy query and then I would be running it twice?

Any solution to this?

S




On 10/6/06, He Shiming <[EMAIL PROTECTED]> wrote:


> Hi List,
>  If I use sqlite3_exec to query a database,
> How can I know that the results in the data base got over. For example
If
> I am expecting a 10 results in some for loop and actually there are only
> five results , How can I get a notification or return value that the
> results completed or Is there any way I can get SQLITE_DONE through
> sqlite3_Exec.  What return value I will get If I query an empty table.
>
>
> Thanks and Regards,
>  Vivek R
>

SQLite didn't provide a "get number of rows" function for the result set.
It
is mentioned in the document that sqlite3_exec is actually a wrapper for
sqlite3_prepare and sqlite3_step. It is in my opinion that sqlite3_exec
should only be used when the result of the query isn't that important. For
instance, a pragma query. For the record, sqlite3_exec did provide a
callback function in which you can count and get the number of rows in a
resultset. The optimal way is that you prepare the statement, fetch and
count the results with sqlite3_step.

Another thing I noticed from your question is that you might not want to
"expect 10 results". It's not very wise to design a hard loop such as
for(i=0;i<10;i++) when comes to a database query resultset. A better way
would be to use an array to store the result set they way you could
understand, and process them later. Then you'll have
for(i=0;i

Re: [sqlite] Regarding sqlite3_exec

[He Shiming]

Hi List,
 If I use sqlite3_exec to query a database,
How can I know that the results in the data base got over. For example If
I am expecting a 10 results in some for loop and actually there are only
five results , How can I get a notification or return value that the
results completed or Is there any way I can get SQLITE_DONE through
sqlite3_Exec.  What return value I will get If I query an empty table.


Thanks and Regards,
 Vivek R



SQLite didn't provide a "get number of rows" function for the result set. It 
is mentioned in the document that sqlite3_exec is actually a wrapper for 
sqlite3_prepare and sqlite3_step. It is in my opinion that sqlite3_exec 
should only be used when the result of the query isn't that important. For 
instance, a pragma query. For the record, sqlite3_exec did provide a 
callback function in which you can count and get the number of rows in a 
resultset. The optimal way is that you prepare the statement, fetch and 
count the results with sqlite3_step.


Another thing I noticed from your question is that you might not want to 
"expect 10 results". It's not very wise to design a hard loop such as 
for(i=0;i<10;i++) when comes to a database query resultset. A better way 
would be to use an array to store the result set they way you could 
understand, and process them later. Then you'll have 
for(i=0;i

Re: [sqlite] Regarding sqlite3_exec

[Lloyd]

I think you are asking for this... This is the case when we use
wxSQLite3.


wxSQLite3ResultSet result = samp.ExecuteQuery(wxT("select name,age from
test"));

  while (result.NextRow())
  {
   cout << (const char*)(result.GetString(0).mb_str()) << result.GetInt
(1)<< endl;
  }

On Fri, 2006-10-06 at 17:07 +0530, Vivek R wrote:
> Hi List,
>   If I use sqlite3_exec to query a database,
> How can I know that the results in the data base got over. For example If
> I am expecting a 10 results in some for loop and actually there are only
> five results , How can I get a notification or return value that the
> results completed or Is there any way I can get SQLITE_DONE through
> sqlite3_Exec.  What return value I will get If I query an empty table.
> 
> 
> Thanks and Regards,
>   Vivek R


__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-