Re: [sqlite] Re: Regarding sqlite3_exec
Hi Thanks for the reposnse. The main reason is my record count could be from a few thousands to a million. But even at the lowly numbers of around 1 the interface can seem slugish if you read every record before displaying anything. As you mention, and has been disucssed above, doing stuff in the background is good way to go, but more complex. As a generla rule of coding I put as few unneccessary threads into a phase 1 program as I can, because the complexity goes up hugly, threads can be complex to use, co-ordinate, interrupt etc... and chance of bugs goes up drmatically. So I tend to do that for a Phase 2 - Bells and whistles phase and only when there isnt a simpler way. This thread has covered just about all approaches I can think of :-) thanks for the reponses. S On 10/27/06, Isaac Raway [EMAIL PROTECTED] wrote: Why don't you design the table with a unique row ID, stored in an integer field, then fetch a list of those ID numbers? For 5000 rows, assuming you store them in you application as 4 byte longs, that's about 19 k of memory. Counting that result as you receive it isn't that difficult. If it takes a long time (it probably won't) you can do it in another thread and update the interface as appropriate. I'm not seeing a downside here. Isaac On 10/26/06, Da Martian [EMAIL PROTECTED] wrote: No there isnt, but RDBM systems are a generalised data retrieval mechanism. As such they suffer from that generality. Dont get me wrong, RDBM systems are appropriate for 95% of all data requirements I have had to deal with and I would never dream of trying to write one from scratch, nor can I imagine a world without them. However certain applications (Weather data, Gnome data, Large indices (like google)) require using somethng designed specifically for that purpose. If you customise data retrieval (and particluar your sorting/indcies/access path) you can leave rdbms in the dust in terms of performance. All I have read about google, suggests they do exactly this. Although I must point out, I dont actually know anything about google with any certainty. Just what has leaked out over the years on the rumour mill. But designiing my own google like indices (on a smaller scale of coure) and some specialisted weather stuff, it neccessary to throw away the rdbms and do it yourself. For a goole query for instance, they know they will get a list of 1 or more words. They also know they will only ever search through the index of words. They dont have other data types, records or tables. Why go through all the hassles of compiling SQLs, and that generic overhead when your application will only ever do one thing? You can just make an API like this search(wordlist): Resultset. You immediatly save yourself complexity and processing time. Then for large indices you will know your data set, so instead of using a std BTree you would use a more appropraite DS possible with skip lists etc.. . As for performing a database search twice, this whole thread has shown, that sometimes the you have to :-) S On 10/25/06, John Stanton [EMAIL PROTECTED] wrote: There is no magic in data retrieval. Google use the same physical laws as us ordinary mortals. I see no reason to ever perform a dataabase search twice. -- Isaac Raway Entia non sunt multiplicanda praeter necessitatem. http://blueapples.org - blog http://stonenotes.com - personal knowledge management - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Regarding sqlite3_exec
You cannot even consider loading even a thousand records directly. Get a set of ID numbers. Load each record as it's needed. This is very basic stuff, and not even that hard to implement. I am just saying for the record that this is not hard to do, hopefully no one else will be scared away from the concept. As a general rule of coding, do it right the first time. One thread reading a list of integers while another is on the GUI is not complex. Isaac On 10/28/06, Da Martian [EMAIL PROTECTED] wrote: Hi Thanks for the reposnse. The main reason is my record count could be from a few thousands to a million. But even at the lowly numbers of around 1 the interface can seem slugish if you read every record before displaying anything. As you mention, and has been disucssed above, doing stuff in the background is good way to go, but more complex. As a generla rule of coding I put as few unneccessary threads into a phase 1 program as I can, because the complexity goes up hugly, threads can be complex to use, co-ordinate, interrupt etc... and chance of bugs goes up drmatically. So I tend to do that for a Phase 2 - Bells and whistles phase and only when there isnt a simpler way. This thread has covered just about all approaches I can think of :-) thanks for the reponses. S On 10/27/06, Isaac Raway [EMAIL PROTECTED] wrote: Why don't you design the table with a unique row ID, stored in an integer field, then fetch a list of those ID numbers? For 5000 rows, assuming you store them in you application as 4 byte longs, that's about 19 k of memory. Counting that result as you receive it isn't that difficult. If it takes a long time (it probably won't) you can do it in another thread and update the interface as appropriate. I'm not seeing a downside here. Isaac On 10/26/06, Da Martian [EMAIL PROTECTED] wrote: No there isnt, but RDBM systems are a generalised data retrieval mechanism. As such they suffer from that generality. Dont get me wrong, RDBM systems are appropriate for 95% of all data requirements I have had to deal with and I would never dream of trying to write one from scratch, nor can I imagine a world without them. However certain applications (Weather data, Gnome data, Large indices (like google)) require using somethng designed specifically for that purpose. If you customise data retrieval (and particluar your sorting/indcies/access path) you can leave rdbms in the dust in terms of performance. All I have read about google, suggests they do exactly this. Although I must point out, I dont actually know anything about google with any certainty. Just what has leaked out over the years on the rumour mill. But designiing my own google like indices (on a smaller scale of coure) and some specialisted weather stuff, it neccessary to throw away the rdbms and do it yourself. For a goole query for instance, they know they will get a list of 1 or more words. They also know they will only ever search through the index of words. They dont have other data types, records or tables. Why go through all the hassles of compiling SQLs, and that generic overhead when your application will only ever do one thing? You can just make an API like this search(wordlist): Resultset. You immediatly save yourself complexity and processing time. Then for large indices you will know your data set, so instead of using a std BTree you would use a more appropraite DS possible with skip lists etc.. . As for performing a database search twice, this whole thread has shown, that sometimes the you have to :-) S On 10/25/06, John Stanton [EMAIL PROTECTED] wrote: There is no magic in data retrieval. Google use the same physical laws as us ordinary mortals. I see no reason to ever perform a dataabase search twice. -- Isaac Raway Entia non sunt multiplicanda praeter necessitatem. http://blueapples.org - blog http://stonenotes.com - personal knowledge management - To unsubscribe, send email to [EMAIL PROTECTED] - -- Isaac Raway Entia non sunt multiplicanda praeter necessitatem. http://blueapples.org - blog http://stonenotes.com - personal knowledge management - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Regarding sqlite3_exec
No there isnt, but RDBM systems are a generalised data retrieval mechanism. As such they suffer from that generality. Dont get me wrong, RDBM systems are appropriate for 95% of all data requirements I have had to deal with and I would never dream of trying to write one from scratch, nor can I imagine a world without them. However certain applications (Weather data, Gnome data, Large indices (like google)) require using somethng designed specifically for that purpose. If you customise data retrieval (and particluar your sorting/indcies/access path) you can leave rdbms in the dust in terms of performance. All I have read about google, suggests they do exactly this. Although I must point out, I dont actually know anything about google with any certainty. Just what has leaked out over the years on the rumour mill. But designiing my own google like indices (on a smaller scale of coure) and some specialisted weather stuff, it neccessary to throw away the rdbms and do it yourself. For a goole query for instance, they know they will get a list of 1 or more words. They also know they will only ever search through the index of words. They dont have other data types, records or tables. Why go through all the hassles of compiling SQLs, and that generic overhead when your application will only ever do one thing? You can just make an API like this search(wordlist): Resultset. You immediatly save yourself complexity and processing time. Then for large indices you will know your data set, so instead of using a std BTree you would use a more appropraite DS possible with skip lists etc.. . As for performing a database search twice, this whole thread has shown, that sometimes the you have to :-) S On 10/25/06, John Stanton [EMAIL PROTECTED] wrote: There is no magic in data retrieval. Google use the same physical laws as us ordinary mortals. I see no reason to ever perform a dataabase search twice.
Re: [sqlite] Re: Regarding sqlite3_exec
What prevents you from doing the same yourself? Just collect the data in a memory buffer as you get it, before processing it. Nothing but effiency as discussed. Basically, as Mr Cote has said, its either a count(*) query or O(n) step calls. The former means two statements have to be run, if this is a heavy statement its not that great. The latter is best option available, because as Mr Cote points out step isnt that heavy. The idea behind me prompting of this discussion is to get the best of both worlds. ie. the effiency of count(*) query without the need to execute two queries. At the end of the day its not serious as many work arounds/solutions are available. That doesn't seem to make sense – after all, the count can only be returned *after* all the rows have been collected. By then you know the count yourself anyway. But to return all the rows just to count them requires N calls to step. If the data set is large you only want to return a subset to start with. So you wouldnt know the count. If you dont know the count, you cant update GUI type things etc.. S
Re: [sqlite] Re: Regarding sqlite3_exec
Da Martian wrote: But to return all the rows just to count them requires N calls to step. If the data set is large you only want to return a subset to start with. So you wouldnt know the count. If you dont know the count, you cant update GUI type things etc.. I haven't been following this thread closely, but isn't this exactly the problem that Google solves by returning Results 1 - 10 of about 9,940,000 for sqlite. (0.11 seconds) for a query with a very large result set? If Google can't do it with all the resources they have... Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Regarding sqlite3_exec
Yes but google doesnt us an RDMS, its all propriatary to support there speed and huge volumes. Its anyones guess (excpet google themselves) what exactly they do, and rumours abound, but I have done many apps which require custom data handling to achieve some end that doesnt fit with RDBM Systems. But yes paging and using LIMIT and OFFSET is also a solution. Again not as efficent though, cause of all the repeated queris :-) On 10/25/06, Martin Jenkins [EMAIL PROTECTED] wrote: Da Martian wrote: But to return all the rows just to count them requires N calls to step. If the data set is large you only want to return a subset to start with. So you wouldnt know the count. If you dont know the count, you cant update GUI type things etc.. I haven't been following this thread closely, but isn't this exactly the problem that Google solves by returning Results 1 - 10 of about 9,940,000 for sqlite. (0.11 seconds) for a query with a very large result set? If Google can't do it with all the resources they have... Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Regarding sqlite3_exec
Da Martian wrote: Yes but google doesnt us an RDMS, its all propriatary to support there speed and huge volumes. I know, but that was my point. If they can't/don't do it (with their custom kit) then surely it's non-trivial? Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Regarding sqlite3_exec
* Da Martian [EMAIL PROTECTED] [2006-10-25 11:35]: What prevents you from doing the same yourself? Just collect the data in a memory buffer as you get it, before processing it. Nothing but effiency as discussed. Basically, as Mr Cote has said, its either a count(*) query or O(n) step calls. The former means two statements have to be run, if this is a heavy statement its not that great. The latter is best option available, because as Mr Cote points out step isnt that heavy. The idea behind me prompting of this discussion is to get the best of both worlds. ie. the effiency of count(*) query without the need to execute two queries. At the end of the day its not serious as many work arounds/solutions are available. That doesn't seem to make sense – after all, the count can only be returned *after* all the rows have been collected. By then you know the count yourself anyway. But to return all the rows just to count them requires N calls to step. If the data set is large you only want to return a subset to start with. So you wouldnt know the count. If you dont know the count, you cant update GUI type things etc.. I still fail to understand the problem. Either you want the data from the query, or you don’t. If you want it, then you run the query once and buffer the results in memory before you process them, so you know the count before you start processing. Or you don’t want the data, then you use a COUNT(*) query. In either case, it is only one query you need to run. Regards, -- Aristotle Pagaltzis // http://plasmasturm.org/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Regarding sqlite3_exec
Its quiet simple: 1) If there are too many results the user will have to wait a long time before they see anything because we will be buffering away results. The application will appear slugish. The user would get bored. I can point you to dozens of studies which show the correlation between response time and productivity where humans are concerned. 2) Often users will find what they want in the first 50 results. This means you would have wasted a lot of time brinigng back data you dont need. However they wont always find what they want in the first 50. So the option for more must be there. So why not use web like paging I hear you say. Well because the query is heavy. To re-run it each with a different limit and offset still requires re-running it. One of the solutions (there are many none ideal) is to have a growing scroll bar. Ie it grows each time you fetch a batch of results. But this like most of the solutions looks a little tardy to a user (me being one of them). Perosnally I hate it when a scroll bar keeps growing when you reach the bottom. The few other approaches have been mentioned in the previos post to this thread. Your extremly simplistic view on this is a result of never dealing in volumous data and result sets and quick running queries. Once you put volumes into your thinking cap you will begin to see why you dont just read everything into memory for the hell of it. Think about it. On 10/25/06, A. Pagaltzis [EMAIL PROTECTED] wrote: I still fail to understand the problem. Either you want the data from the query, or you don't. If you want it, then you run the query once and buffer the results in memory before you process them, so you know the count before you start processing. Or you don't want the data, then you use a COUNT(*) query. In either case, it is only one query you need to run.
Re: [sqlite] Re: Regarding sqlite3_exec
Your extremly simplistic view on this is a result of never dealing in volumous data and result sets and quick running queries. Once you put volumes into your thinking cap you will begin to see why you dont just read everything into memory for the hell of it. Just to complete the above thought. If I wanted everything in memory I would not have any need for sqlite. Standard Data Structures list BST, Hash etc.. will be far more effient for in memory use.
[sqlite] Re: Regarding sqlite3_exec
* Da Martian [EMAIL PROTECTED] [2006-10-25 14:15]: 1) If there are too many results the user will have to wait a long time before they see anything because we will be buffering away results. The application will appear slugish. The user would get bored. I can point you to dozens of studies which show the correlation between response time and productivity where humans are concerned. 2) Often users will find what they want in the first 50 results. This means you would have wasted a lot of time brinigng back data you dont need. However they wont always find what they want in the first 50. So the option for more must be there. So why not use web like paging I hear you say. Well because the query is heavy. To re-run it each with a different limit and offset still requires re-running it. One of the solutions (there are many none ideal) is to have a growing scroll bar. Ie it grows each time you fetch a batch of results. But this like most of the solutions looks a little tardy to a user (me being one of them). Perosnally I hate it when a scroll bar keeps growing when you reach the bottom. The few other approaches have been mentioned in the previos post to this thread. Your extremly simplistic view on this is a result of never dealing in volumous data and result sets and quick running queries. Once you put volumes into your thinking cap you will begin to see why you dont just read everything into memory for the hell of it. Think about it. Thanks for your vote of confidence in my intelligence. Clearly, you are smart enough to figure out a solution without assistance. Nevermind, -- Aristotle Pagaltzis // http://plasmasturm.org/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Regarding sqlite3_exec
Its was not meant as an insult, however you did set the tone with your post (ala: Either you want the data from the query, or you don't.). I mearly responded in kind. If you live in glass houses dont throw stones and all that. I mean its not hard to see that loading 20 million records into memory isnt the most effient approach to showing a list box on the screen. Thanks for your vote of confidence in my intelligence. Clearly, you are smart enough to figure out a solution without assistance. Nevermind, -- Aristotle Pagaltzis // http://plasmasturm.org/ - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Regarding sqlite3_exec
* Da Martian [EMAIL PROTECTED] [2006-10-25 15:05]: Its was not meant as an insult, however you did set the tone with your post (ala: Either you want the data from the query, or you don't.). I mearly responded in kind. If you live in glass houses dont throw stones and all that. I mean its not hard to see that loading 20 million records into memory isnt the most effient approach to showing a list box on the screen. I suggested that after you said that Oracle collects results in memory before returning them; you seemed to hint that this wouldn’t be a problem, in which case whether you do it yourself or the database does it for you doesn’t make a difference. Solutions that come to mind are a) to populate the UI from an idle callback, where the scollbar would simply keep growing independently from any user interaction until all the results are fetched; or if that’s unacceptable, b) run a separate COUNT(*) query, since preparing a extra query is cheap, but using COUNT(*) tells SQLite that it can forgo a lot of processing, which makes the up-front counting query quicker. There are other options as well. A lot depends on your circumstances. F.ex. paged queries can be made cheaper by selecting results into a temporary table so that you can re-retrieve them with a much cheaper query. Regards, -- Aristotle Pagaltzis // http://plasmasturm.org/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Regarding sqlite3_exec
On 10/25/06, Da Martian [EMAIL PROTECTED] wrote: [...] But to return all the rows just to count them requires N calls to step. If the data set is large you only want to return a subset to start with. So you wouldnt know the count. If you dont know the count, you cant update GUI type things etc.. There is another alternative if you don't mind to have the overhead of having an automatic row count (which sqlite avoids by design). It's by having a trigger that will update the table row count on each insert/delete. I'm not good with triggers, but this was already mentioned on the list and a quick google showed this sample SQL code here: http://www.northcode.com/forums/archive/index.php?t-6852.html copy/paste from unknown origin and untested code CREATE TABLE rowcount ( name TEXT, rows INTEGER); insert into rowcount VALUES ('myTable',0); UPDATE rowcount SET rows = (SELECT count(myNum) from myTable) WHERE name = 'myTable'; CREATE TRIGGER incrows AFTER INSERT ON myTable BEGIN UPDATE rowcount SET rows = rows+1 WHERE name = 'myTable'; END; CREATE TRIGGER decrows AFTER DELETE ON myTable BEGIN UPDATE rowcount SET rows = rows-1 WHERE name = 'myTable'; END; /copy/paste from unknown origin and untested code After this you can easily access your row count with a SELECT rows FROM rowcount WHERE name = 'myTable'; Hope this helps, ~Nuno Lucas - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Regarding sqlite3_exec
Hi All these options are good, and the discussion was interesting. I mearly wanted to see what peoples thoughts on the sqlite providing this were. There are many ways to skin a cat as they say. Since this is probably drifting off topic, I suggest we discontinue the thread. Thanks for your cander, discussing is whats its all about. Stephen On 10/25/06, A. Pagaltzis [EMAIL PROTECTED] wrote: * Da Martian [EMAIL PROTECTED] [2006-10-25 15:05]: Its was not meant as an insult, however you did set the tone with your post (ala: Either you want the data from the query, or you don't.). I mearly responded in kind. If you live in glass houses dont throw stones and all that. I mean its not hard to see that loading 20 million records into memory isnt the most effient approach to showing a list box on the screen. I suggested that after you said that Oracle collects results in memory before returning them; you seemed to hint that this wouldn't be a problem, in which case whether you do it yourself or the database does it for you doesn't make a difference. Solutions that come to mind are a) to populate the UI from an idle callback, where the scollbar would simply keep growing independently from any user interaction until all the results are fetched; or if that's unacceptable, b) run a separate COUNT(*) query, since preparing a extra query is cheap, but using COUNT(*) tells SQLite that it can forgo a lot of processing, which makes the up-front counting query quicker. There are other options as well. A lot depends on your circumstances. F.ex. paged queries can be made cheaper by selecting results into a temporary table so that you can re-retrieve them with a much cheaper query. Regards, -- Aristotle Pagaltzis // http://plasmasturm.org/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Regarding sqlite3_exec
Nuno Lucas wrote: There is another alternative if you don't mind to have the overhead of having an automatic row count (which sqlite avoids by design). It's by having a trigger that will update the table row count on each insert/delete. Nuno, This technique only works if you never use conditions on your queries. If your query returns a subset of the rows in a table this carefully maintained count of all the rows in the table is useless. Dennis Cote. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Regarding sqlite3_exec
On 10/25/06, Dennis Cote [EMAIL PROTECTED] wrote: Nuno Lucas wrote: There is another alternative if you don't mind to have the overhead of having an automatic row count (which sqlite avoids by design). It's by having a trigger that will update the table row count on each insert/delete. Nuno, This technique only works if you never use conditions on your queries. If your query returns a subset of the rows in a table this carefully maintained count of all the rows in the table is useless. Sure, but I wasn't trying to solve the general issue. The only solution for that is to run the query to full length, whatever the SQL engine is (even if they hide it from the user/programmer). The trigger(s) could be elaborated to specific queries, off course, and that would solve the GUI issue for fixed queries, but the generic case will never have a solution other than the full scan. Anyway, I never used this solution, just trying to show it can be solved for the specific cases of most GUI's, if the trigger overhead is not a problem. In my modest opinion, if one really needs to have an accurate scrollbar, one should show the rows by pages, with buttons to go to the next/prev page (and the scrollbar will be correct per page). No need to know the total rows in the view (unless we know the table doesn't grow that much that a select count(*), or a full select into memory, doesn't add much to the window rendering, which is most times). Regards, ~Nuno Lucas Dennis Cote. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Regarding sqlite3_exec
Indeed, Thanks to all for the responses. Currently I use the seperate count(*), but think I will change to the prepare, step(n), reset option only because the query is a little slow. Utlimatly sqlite is brilliant, hands down the best embeddable and standalone db I have yet to encounter. Thanks to all involved in it. S On 10/25/06, Nuno Lucas [EMAIL PROTECTED] wrote: On 10/25/06, Dennis Cote [EMAIL PROTECTED] wrote: Nuno Lucas wrote: There is another alternative if you don't mind to have the overhead of having an automatic row count (which sqlite avoids by design). It's by having a trigger that will update the table row count on each insert/delete. Nuno, This technique only works if you never use conditions on your queries. If your query returns a subset of the rows in a table this carefully maintained count of all the rows in the table is useless. Sure, but I wasn't trying to solve the general issue. The only solution for that is to run the query to full length, whatever the SQL engine is (even if they hide it from the user/programmer). The trigger(s) could be elaborated to specific queries, off course, and that would solve the GUI issue for fixed queries, but the generic case will never have a solution other than the full scan. Anyway, I never used this solution, just trying to show it can be solved for the specific cases of most GUI's, if the trigger overhead is not a problem. In my modest opinion, if one really needs to have an accurate scrollbar, one should show the rows by pages, with buttons to go to the next/prev page (and the scrollbar will be correct per page). No need to know the total rows in the view (unless we know the table doesn't grow that much that a select count(*), or a full select into memory, doesn't add much to the window rendering, which is most times). Regards, ~Nuno Lucas Dennis Cote. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Regarding sqlite3_exec
There is no magic in data retrieval. Google use the same physical laws as us ordinary mortals. I see no reason to ever perform a dataabase search twice. Da Martian wrote: Yes but google doesnt us an RDMS, its all propriatary to support there speed and huge volumes. Its anyones guess (excpet google themselves) what exactly they do, and rumours abound, but I have done many apps which require custom data handling to achieve some end that doesnt fit with RDBM Systems. But yes paging and using LIMIT and OFFSET is also a solution. Again not as efficent though, cause of all the repeated queris :-) On 10/25/06, Martin Jenkins [EMAIL PROTECTED] wrote: Da Martian wrote: But to return all the rows just to count them requires N calls to step. If the data set is large you only want to return a subset to start with. So you wouldnt know the count. If you dont know the count, you cant update GUI type things etc.. I haven't been following this thread closely, but isn't this exactly the problem that Google solves by returning Results 1 - 10 of about 9,940,000 for sqlite. (0.11 seconds) for a query with a very large result set? If Google can't do it with all the resources they have... Martin - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Regarding sqlite3_exec
Nuno Lucas wrote: On 10/25/06, Dennis Cote [EMAIL PROTECTED] wrote: Nuno Lucas wrote: There is another alternative if you don't mind to have the overhead of having an automatic row count (which sqlite avoids by design). It's by having a trigger that will update the table row count on each insert/delete. Nuno, This technique only works if you never use conditions on your queries. If your query returns a subset of the rows in a table this carefully maintained count of all the rows in the table is useless. Sure, but I wasn't trying to solve the general issue. The only solution for that is to run the query to full length, whatever the SQL engine is (even if they hide it from the user/programmer). The trigger(s) could be elaborated to specific queries, off course, and that would solve the GUI issue for fixed queries, but the generic case will never have a solution other than the full scan. Anyway, I never used this solution, just trying to show it can be solved for the specific cases of most GUI's, if the trigger overhead is not a problem. In my modest opinion, if one really needs to have an accurate scrollbar, one should show the rows by pages, with buttons to go to the next/prev page (and the scrollbar will be correct per page). No need to know the total rows in the view (unless we know the table doesn't grow that much that a select count(*), or a full select into memory, doesn't add much to the window rendering, which is most times). Regards, ~Nuno Lucas Dennis Cote. - To unsubscribe, send email to [EMAIL PROTECTED] - Some possible solutions to the problem of defining a result set size without using much extra memory, disk space or machine overhead. If you want to know the size of your result set in advance and then select pages from that set an efficient way you could execute the query and build some form of index to the returned rows, using the rowid as the unique ID. Then you can traverse that index at leisure, reading data columns as required. You could organize your index so that a pre-order traversal gives you the sequence you want later. That avoids the need for an ORDER BY. The effect would be like having a CURSOR. It involves a little programming, but then nothing is free. A somewhat heavier duty, but simpler, alternative is just to write the result set to a temporary table, index it on the access key then use it for output. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Regarding sqlite3_exec
* Da Martian [EMAIL PROTECTED] [2006-10-24 16:15]: 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. What prevents you from doing the same yourself? Just collect the data in a memory buffer as you get it, before processing it. Perhaps this could done as a seperate api, like preparewithcount() which returns the count as well. That doesn’t seem to make sense – after all, the count can only be returned *after* all the rows have been collected. By then you know the count yourself anyway. Regards, -- Aristotle Pagaltzis // http://plasmasturm.org/ - To unsubscribe, send email to [EMAIL PROTECTED] -