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