Re: [sqlite] Regarding sqlite3_exec
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
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
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
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
"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
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
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 Best regards, He Shiming - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Regarding sqlite3_exec
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] -
[sqlite] Regarding sqlite3_exec
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