Re: [sqlite] Re: Regarding sqlite3_exec

[Isaac Raway]

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

[Da Martian]

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

[Isaac Raway]

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

[Da Martian]

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

[John Stanton]

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

Re: [sqlite] Re: Regarding sqlite3_exec

[John Stanton]

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

[Da Martian]

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

[Nuno Lucas]

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

[Dennis Cote]

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

[Da Martian]

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


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Re: Regarding sqlite3_exec

[Nuno Lucas]

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



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;



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

[sqlite] Re: Regarding sqlite3_exec

[A. Pagaltzis]

* 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 // 

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

Re: [sqlite] Re: Regarding sqlite3_exec

[Da Martian]

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


-
To unsubscribe, send email to [EMAIL PROTECTED]

-

[sqlite] Re: Regarding sqlite3_exec

[A. Pagaltzis]

* 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 // 

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

Re: [sqlite] Re: Regarding sqlite3_exec

[Da Martian]

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.



>

Re: [sqlite] Re: Regarding sqlite3_exec

[Da Martian]

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.

[sqlite] Re: Regarding sqlite3_exec

[A. Pagaltzis]

* 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 // 

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

Re: [sqlite] Re: Regarding sqlite3_exec

[Martin Jenkins]

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

Re: [sqlite] Re: Regarding sqlite3_exec

[Da Martian]

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

[Martin Jenkins]

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]

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

[sqlite] Re: Regarding sqlite3_exec

[A. Pagaltzis]

* 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 // 

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