Re: [sqlite] Row count of a partial index ?

[Richard Hipp]

On 5/5/18, Deon Brewis  wrote:
> Is there a direct way to query how many rows are in a partial index?
>
> I know I can repeat the WHERE clause expression in the query, but this is
> for a dynamically constructed index and knowing what the current "where"
> clause is on the index has some race conditions attached. Solvable... but if
> there is a direct way, I'd prefer that. Otherwise no big deal.

There is some unsupported trickery involving SQLITE_TESTCTRL_IMPOSTER,
but extracting the WHERE clause from the sqlite_master table seems
safer and easier.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Row count of a partial index ?

[Deon Brewis]

Is there a direct way to query how many rows are in a partial index?

e.g.

select count(*) from table indexed by partial_index

(this does not work - no query solution).


I know I can repeat the WHERE clause expression in the query, but this is for a 
dynamically constructed index and knowing what the current "where" clause is on 
the index has some race conditions attached. Solvable... but if there is a 
direct way, I'd prefer that. Otherwise no big deal.

(I know I can also ANALYZE and check sqlite_stat1 after, but that's not really 
a solution...)

- Deon

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] row count after a select

[Igor Tandetnik]

Ken <[EMAIL PROTECTED]> wrote:
> Igor I disagree, but please feel free to correct me if I'm wrong.
> Consider the following sql statement:
>
> select * from table1 order by last_name;
>
> Sqlite must first order the data since an order by clause is
> specified. (assuming there is no index to utilize).

Yes, in some cases, SQLite might know internally how many rows there are 
by the time the first row is produced. This is not the case in general, 
though. Anyway, as far as I can tell there is no public API to retrieve 
this information even when it's available.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] row count after a select

[Ken]

Igor I disagree, but please feel free to correct me if I'm wrong. Consider the 
following sql statement:

select * from table1 order by last_name;

Sqlite must first order the data since an order by clause is specified. 
(assuming there is no index to utilize). The data must be fetch, sorted and 
stored in a temporary file or in memory.

But without the order by then sqlite does not need to fetch the data prior to 
select. 

So only sometimes does it fetch all data at the first step.

Ken

--- On Wed, 11/5/08, Igor Tandetnik <[EMAIL PROTECTED]> wrote:

> From: Igor Tandetnik <[EMAIL PROTECTED]>
> Subject: Re: [sqlite] row count after a select
> To: sqlite-users@sqlite.org
> Date: Wednesday, November 5, 2008, 7:38 AM
> <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> > Do you know a way to count the number of rows
> identified by a select
> > statement, just after the first call to sqlite3_step ?
> Of course
> > without fetching the entire result set, nor having to
> systematically
> > add count(*)
> 
> You seem to believe SQLite retrieves the whole resultset on
> the first 
> sqlite3_step call. This is not the case. SQLite produces
> records one by 
> one, on request, every time you call sqlite3_step. It
> simply doesn't 
> know how many there are going to be, until on some
> sqlite3_step call it 
> discovers there are no more.
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] row count after a select

[Igor Tandetnik]

"LEMAIRE, Vincent (AUSY FRANCE)"
<[EMAIL PROTECTED]> wrote in
message
news:[EMAIL PROTECTED]
> I just imagined that when the engine calculates a 'count(*)' it
> doesn't loop on each result row "just" for counting

It does, in fact.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] row count after a select

[LEMAIRE, Vincent (AUSY FRANCE)]

Thanks Igor,
I just imagined that when the engine calculates a 'count(*)' it doesn't loop on 
each result row "just" for counting,
and thus I thought there could be some function on client side to use that 
supposed algorithm.

-Message d'origine-
De : [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] la part de Igor Tandetnik
Envoyé : mercredi 5 novembre 2008 14:38
À : sqlite-users@sqlite.org
Objet : Re: [sqlite] row count after a select


<[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Do you know a way to count the number of rows identified by a select
> statement, just after the first call to sqlite3_step ? Of course
> without fetching the entire result set, nor having to systematically
> add count(*)

You seem to believe SQLite retrieves the whole resultset on the first 
sqlite3_step call. This is not the case. SQLite produces records one by 
one, on request, every time you call sqlite3_step. It simply doesn't 
know how many there are going to be, until on some sqlite3_step call it 
discovers there are no more.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

This mail has originated outside your organization, either from an external 
partner or the Global Internet.
Keep this in mind if you answer this message.



The information in this e-mail is confidential. The contents may not be 
disclosed or used by anyone other then the addressee. Access to this e-mail by 
anyone else is unauthorised.
If you are not the intended recipient, please notify Airbus immediately and 
delete this e-mail.
Airbus cannot accept any responsibility for the accuracy or completeness of 
this e-mail as it has been sent over public networks. If you have any concerns 
over the content of this message or its Accuracy or Integrity, please contact 
Airbus immediately.
All outgoing e-mails from Airbus are checked using regularly updated virus 
scanning software but you should take whatever measures you deem to be 
appropriate to ensure that this message and any attachments are virus free.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] row count after a select

[Igor Tandetnik]

<[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Do you know a way to count the number of rows identified by a select
> statement, just after the first call to sqlite3_step ? Of course
> without fetching the entire result set, nor having to systematically
> add count(*)

You seem to believe SQLite retrieves the whole resultset on the first 
sqlite3_step call. This is not the case. SQLite produces records one by 
one, on request, every time you call sqlite3_step. It simply doesn't 
know how many there are going to be, until on some sqlite3_step call it 
discovers there are no more.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] row count after a select

[vlemaire]

Hello,

Do you know a way to count the number of rows identified by a select
statement, just after the first call to sqlite3_step ? Of course without
fetching the entire result set, nor having to systematically add count(*)

Thanks !

Vincent


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row count after prepare

[Marcelo Zamateo]

Leif:
There may be better solutions, but you don't need to scan the SQL, just  
put it as subquery:
sqlite3_printf "Select Count(*) from (%s)", pSQL
will do the work for you.
Remember what i said about Group by clauses.
Anyway i think a function like sqlite3_row_count(hStmt) would be fantastic.
Marcelo.

Re: [sqlite] Row count after prepare

[Leif Jensen]

  Hi Marcelo,
  It would, but I don't know the contents of the SQL at the point where 
I need get the row count, so I would have to scan the statement. Putting 
in the extra 'count(*)' all over is not really possible.

  Does anyone know the timing of  a sqlite3_step() compared to 
sqlite3_step() + sqlite_column_() ?

Leif
Marcelo Zamateo wrote:
Hi Leif:
Will "Select Count(*) from (Your_SQL)" give you what you want?
Be careful if Your_SQL includes Group By clause, don't know if work 
as  expected.
Marcelo

Re: [sqlite] Row count after prepare

[Marcelo Zamateo]

Hi Leif:
Will "Select Count(*) from (Your_SQL)" give you what you want?
Be careful if Your_SQL includes Group By clause, don't know if work as  
expected.
Marcelo

Re: [sqlite] Row count after prepare

[Kurt Welgehausen]

> ... guess I will have to use sqlite3_get_table in this case

Be aware that sqlite3_get_table is no longer a recommended
API and may not do everything you want. See



Regards

Re: [sqlite] Row count after prepare

[Leif Jensen]

  Hi Dan,
 Ah yes, of course  (I knew this was a newbie question ;-) ). So I 
guess I will have to use sqlite3_get_table in this case.

 Thank you for the enlightment,
Leif
Dan Kennedy wrote:
--- Leif Jensen <[EMAIL PROTECTED]> wrote:
 

   Hi All,
   I'm quite new to SQLite, but like it very much.
  I am trying it out for an existing applicationm which seems good, but 
I have one problem:

  How do I get the number of rows in the result set after having called 
sqlite3_prepare( ... ) ? Of course I could call sqlite3_step() until no 
more rows are returned, but isn't there a better way ?
   

It can't be done. Calling sqlite3_prepare() just compiles the SQL to 
bytecode
(see the EXPLAIN command). It doesn't actually read any data from the database
file (except maybe the schema from sqlite_master).
Dan.


		
__ 
Do you Yahoo!? 
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/ 

 

Re: [sqlite] Row count after prepare

[Dan Kennedy]

--- Leif Jensen <[EMAIL PROTECTED]> wrote:
> Hi All,
> 
> I'm quite new to SQLite, but like it very much.
> 
>I am trying it out for an existing applicationm which seems good, but 
> I have one problem:
> 
>How do I get the number of rows in the result set after having called 
> sqlite3_prepare( ... ) ? Of course I could call sqlite3_step() until no 
> more rows are returned, but isn't there a better way ?

It can't be done. Calling sqlite3_prepare() just compiles the SQL to bytecode
(see the EXPLAIN command). It doesn't actually read any data from the database
file (except maybe the schema from sqlite_master).

Dan.






__ 
Do you Yahoo!? 
Yahoo! Small Business - Try our new resources site!
http://smallbusiness.yahoo.com/resources/ 

[sqlite] Row count after prepare

[Leif Jensen]

   Hi All,
   I'm quite new to SQLite, but like it very much.
  I am trying it out for an existing applicationm which seems good, but 
I have one problem:

  How do I get the number of rows in the result set after having called 
sqlite3_prepare( ... ) ? Of course I could call sqlite3_step() until no 
more rows are returned, but isn't there a better way ?

 Thanks,
Leif

Re: [sqlite] Row count in database

[Christopher Petrilli]

Oy! After doing some research and actually changing machines, I
isolated the problem.

Linux.

well, no, not really, but the drivers for the SATA controller I have
are so horrendously bad that they crap out under load.  How they ever
got considered "releasable" is another question entirely.  Once I
moved to another box with different controller, but nearly identical
drives, the problem went away, and performance went up by a factor of
4+.

Chris
-- 
| Christopher Petrilli
| [EMAIL PROTECTED]

Re: [sqlite] Row count in database

[Jakub Adamek]

Richard, and how about doing both? Using BEGIN ... COMMIT and at the 
same time turning off syncing?

Jakub
D. Richard Hipp wrote:
Christopher Petrilli wrote:
Has anyone had any experience in storing a million or more rows in a
SQLite3 database?  I've got a database that I've been building, which
gets 250 inserts/second, roughly, and which has about 3M rows in it. 
At that point, the CPU load is huge.

Note that I've got syncing turned off, because I'm willing to accept
the risks.
I've been running some tests.  I find that INSERTs (into a table
with 5 indices) are 3.5 times faster if you put them inside
BEGIN...COMMIT rather than turning off syncing.

Re: [sqlite] Row count in database

[Ara.T.Howard]

On Wed, 15 Dec 2004, Christopher Petrilli wrote:
One thing I've noticed is that if I turn of synchronous, the
filesystem slowly slows down, which is fun, but it doesn't do so
enough that it's a major issue.
I'm using the APSW wrapper for Python, which is basically a very thin
wrapper over the basic API, and does nothing special, so I'm 99% sure
it's not that.  You'll notice in the web page that performance seems
to change radically at several points.
sounds like a classic case of the GC is kicking in.  i see the same thing in
ruby, where disabling the GC drastically increases performance - though this
this is not always possible i realize.
regards.
-a
--
===
| EMAIL   :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
| PHONE   :: 303.497.6469
| When you do something, you should burn yourself completely, like a good
| bonfire, leaving no trace of yourself.  --Shunryu Suzuki
===

Re: [sqlite] Row count in database

[Christopher Petrilli]

On Wed, 15 Dec 2004 08:47:34 -0500, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
> Christopher Petrilli wrote:
> > Has anyone had any experience in storing a million or more rows in a
> > SQLite3 database?  I've got a database that I've been building, which
> > gets 250 inserts/second, roughly, and which has about 3M rows in it.
> > At that point, the CPU load is huge.
> >
> 
> The other thing to remember is that when a table has 5 separate
> indices (4 explicit indices + 1 primary key) then each INSERT or
> DELETE operation is really doing 6 inserts or deletes.  There
> is one insert/delete for the main table and one for each of the
> indices.  So you would expect insert performance to be at least
> six times slower on a table with 5 indices versus a table with
> no indices.
> 
> The other thing to remember is that when you insert on a table,
> the new row goes at the very end of the table, which is typically
> very fast.  (The BTree backend for SQLite is optimized for the
> common case of inserting a row at the end of a table.)  But an
> insert into an index will usually occur somewhere in the middle
> of the indice, and thus will likely involve some rebalancing
> operations to make space for the new entry and to keep the tree
> level.  Inserting into an index is thus typically a little
> slower than inserting into a table.  Hence we expect inserting
> into a table with 5 indices to be more than 6 times slower than
> inserting into a table with no indices.
> 
> So the "base insert rate" of SQLite is about 25000 rows/second.
> Divide by 6 because you have 5 indices.  Divide by 4 because
> you are using synchronous=OFF instead of BEGIN...COMMIT.  This
> leaves us with an expected insert rate in your application of
> about 1000 rows/second.  We are still missing a factor of 4.
> 
> Could there be a hardware difference?  What kind of equipment
> are you running on?

Actually, I do wrap inside BEGIN/COMMIT, as that was the first thing I
tried.  That created a HUGE increase in performance.  There's some
numbers up on my blog, BTW:

http://www.amber.org/~petrilli/archive/2004/11/28/sqlite_insertion_performance.html

Notice the odd behavior?  Anyway...

Past thatt, I've decided to do some of my own database manipulations,
and only add the additional indices after the database has been
"closed" for insertions.  That seems to help a lot, and doesn't
require but a couple seconds (I throw it into a separate thread to
do).

Hardware is a AMD64/3000, 2Gb RAM, SATA drives, Fedora Core 3

One thing I've noticed is that if I turn of synchronous, the
filesystem slowly slows down, which is fun, but it doesn't do so
enough that it's a major issue.

I'm using the APSW wrapper for Python, which is basically a very thin
wrapper over the basic API, and does nothing special, so I'm 99% sure
it's not that.  You'll notice in the web page that performance seems
to change radically at several points.

Chris

-- 
| Christopher Petrilli
| [EMAIL PROTECTED]

Re: [sqlite] Row count in database

[D. Richard Hipp]

Christopher Petrilli wrote:
Has anyone had any experience in storing a million or more rows in a
SQLite3 database?  I've got a database that I've been building, which
gets 250 inserts/second, roughly, and which has about 3M rows in it. 
At that point, the CPU load is huge.

The other thing to remember is that when a table has 5 separate
indices (4 explicit indices + 1 primary key) then each INSERT or
DELETE operation is really doing 6 inserts or deletes.  There
is one insert/delete for the main table and one for each of the
indices.  So you would expect insert performance to be at least
six times slower on a table with 5 indices versus a table with
no indices.
The other thing to remember is that when you insert on a table,
the new row goes at the very end of the table, which is typically
very fast.  (The BTree backend for SQLite is optimized for the
common case of inserting a row at the end of a table.)  But an
insert into an index will usually occur somewhere in the middle
of the indice, and thus will likely involve some rebalancing
operations to make space for the new entry and to keep the tree
level.  Inserting into an index is thus typically a little
slower than inserting into a table.  Hence we expect inserting
into a table with 5 indices to be more than 6 times slower than
inserting into a table with no indices.
So the "base insert rate" of SQLite is about 25000 rows/second.
Divide by 6 because you have 5 indices.  Divide by 4 because
you are using synchronous=OFF instead of BEGIN...COMMIT.  This
leaves us with an expected insert rate in your application of
about 1000 rows/second.  We are still missing a factor of 4.
Could there be a hardware difference?  What kind of equipment
are you running on?
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

Re: [sqlite] Row count in database

[D. Richard Hipp]

Christopher Petrilli wrote:
Has anyone had any experience in storing a million or more rows in a
SQLite3 database?  I've got a database that I've been building, which
gets 250 inserts/second, roughly, and which has about 3M rows in it. 
At that point, the CPU load is huge.

Note that I've got syncing turned off, because I'm willing to accept
the risks.
I've been running some tests.  I find that INSERTs (into a table
with 5 indices) are 3.5 times faster if you put them inside
BEGIN...COMMIT rather than turning off syncing.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

Re: [sqlite] Row count in database

[Ara.T.Howard]

On Tue, 14 Dec 2004, Christopher Petrilli wrote:
On Tue, 14 Dec 2004 12:03:01 -0700 (MST), Ara.T.Howard
<[EMAIL PROTECTED]> wrote:
On Tue, 14 Dec 2004, Christopher Petrilli wrote:
Has anyone had any experience in storing a million or more rows in a
SQLite3 database?  I've got a database that I've been building, which gets
250 inserts/second, roughly, and which has about 3M rows in it.  At that
point, the CPU load is huge.
Note that I've got syncing turned off, because I'm willing to accept the
risks.
Thoughts?
Chris
--
| Christopher Petrilli
| [EMAIL PROTECTED]
on linux perhaps?
   cp ./db /dev/shm && a.out /dev/shm/db && mv /dev/shm/db ./db
this will be fast.
Right, but not really workable when total DB size is in gigabytes. :-)
ya never know - it's hard to beat the kernel with regards to io...  
gigabytes
of ram are cheap too compared to a couple days of a prgrammer's time.
are you sure it's not YOUR 'building' code which is killing the cpu?  can
you gperf it?
Yes, my code is using under 20% of the CPU.  The rest is basically blocked
up in sqlite3 code, and kernel time.  In order to eliminate all possibility
of my code being the issue, I actually built a rig that prebuilds 10,000
rows, and inserts them in sequence repeatedly putting new primary keys on
them as its going alone.  So the system basically just runs in a loop doing
sqlite calls.
this is probably a stupid question - but are the inserts inside of a
transaction?
-a
--
===
| EMAIL   :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
| PHONE   :: 303.497.6469
| When you do something, you should burn yourself completely, like a good
| bonfire, leaving no trace of yourself.  --Shunryu Suzuki
===

Re: [sqlite] Row count in database

[Christopher Petrilli]

On Tue, 14 Dec 2004 13:54:35 -0500, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
> Christopher Petrilli wrote:
> > Has anyone had any experience in storing a million or more rows in a
> > SQLite3 database?  I've got a database that I've been building, which
> > gets 250 inserts/second, roughly, and which has about 3M rows in it.
> > At that point, the CPU load is huge.
> >
> 
> I just ran a test case inserting 3 million rows in a database.
> Wall-clock time was 122 seconds for a rate just shy of 25000 inserts
> per second.  The final database size was 222428160.  To check to see
> if performance was falling off with increases size, I then inserted
> an additional million rows.  41 seconds: 24390 inserts per second.
> New file size 297440256.  This is on three year old hardware.

That was about my insert performance as well (AMD64), as it's
basically disc limited any more.  I found substantial differences
between my SATA drive and my PATA drive though.

> Inserts can be significantly slower if you have indices.  The more
> indices you have the slower things might go.  (Depending on what
> your indices and your data look like.) If possible, it is recommended
> that you do all your inserts first, then do the CREATE INDEX statements
> as a separate step afterwards.

Alas, I think it is the indexing that's killing me.  I'm contemplating
shrinking the size of the database (i.e. partitioning the data into
multiple databases, and doing some in-memory joining in my
application), which means it'd be easier to add the indexes after the
database has been "filled" if that makes sense.
 
> What does your schema look like?

Here's the schema, or mostly... I've removed a bunch of columns that
aren't really public... think of it as another 8 INTEGER columns,
without indexes.

CREATE TABLE events (
event_id VARCHAR(32) PRIMARY KEY,
sensor_ts INTEGER NOT NULL,
my_ts INTEGER NOT NULL,
sensor_id INTEGER NOT NULL,
src_ip INTEGER NOT NULL,
dst_ip INTEGER NOT NULL,
event_class INTEGER NOT NULL,
event_type INTEGER NOT NULL,
user_name TEXT,
info TEXT);

CREATE INDEX events_sensor_ts_idx ON events(sensor_ts);
CREATE INDEX events_conduit_ts_idx ON events(conduit_ts);
CREATE INDEX events_src_ip_idx ON events(src_ip);
CREATE INDEX events_dst_ip_idx ON events(dst_ip);
-- 
| Christopher Petrilli
| [EMAIL PROTECTED]

Re: [sqlite] Row count in database

[Christopher Petrilli]

On Tue, 14 Dec 2004 12:03:01 -0700 (MST), Ara.T.Howard
<[EMAIL PROTECTED]> wrote:
> On Tue, 14 Dec 2004, Christopher Petrilli wrote:
> 
> > Has anyone had any experience in storing a million or more rows in a
> > SQLite3 database?  I've got a database that I've been building, which
> > gets 250 inserts/second, roughly, and which has about 3M rows in it.
> > At that point, the CPU load is huge.
> >
> > Note that I've got syncing turned off, because I'm willing to accept
> > the risks.
> >
> > Thoughts?
> >
> > Chris
> >
> > --
> > | Christopher Petrilli
> > | [EMAIL PROTECTED]
> 
> on linux perhaps?
> 
>cp ./db /dev/shm && a.out /dev/shm/db && mv /dev/shm/db ./db
> 
> this will be fast.

Right, but not really workable when total DB size is in gigabytes. :-)

> are you sure it's not YOUR 'building' code which is killing the cpu?  can you
> gperf it?

Yes, my code is using under 20% of the CPU.  The rest is basically
blocked up in sqlite3 code, and kernel time.  In order to eliminate
all possibility of my code being the issue, I actually built a rig
that prebuilds 10,000 rows, and inserts them in sequence repeatedly
putting new primary keys on them as its going alone.  So the system
basically just runs in a loop doing sqlite calls.

Chris
-- 
| Christopher Petrilli
| [EMAIL PROTECTED]

Re: [sqlite] Row count in database

[Ara.T.Howard]

On Tue, 14 Dec 2004, Christopher Petrilli wrote:
Has anyone had any experience in storing a million or more rows in a
SQLite3 database?  I've got a database that I've been building, which
gets 250 inserts/second, roughly, and which has about 3M rows in it.
At that point, the CPU load is huge.
Note that I've got syncing turned off, because I'm willing to accept
the risks.
Thoughts?
Chris
--
| Christopher Petrilli
| [EMAIL PROTECTED]
on linux perhaps?
  cp ./db /dev/shm && a.out /dev/shm/db && mv /dev/shm/db ./db
this will be fast.
are you sure it's not YOUR 'building' code which is killing the cpu?  can you
gperf it?
-a
--
===
| EMAIL   :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
| PHONE   :: 303.497.6469
| When you do something, you should burn yourself completely, like a good
| bonfire, leaving no trace of yourself.  --Shunryu Suzuki
===

Re: [sqlite] Row count in database

[D. Richard Hipp]

Christopher Petrilli wrote:
Has anyone had any experience in storing a million or more rows in a
SQLite3 database?  I've got a database that I've been building, which
gets 250 inserts/second, roughly, and which has about 3M rows in it. 
At that point, the CPU load is huge.

I just ran a test case inserting 3 million rows in a database.
Wall-clock time was 122 seconds for a rate just shy of 25000 inserts
per second.  The final database size was 222428160.  To check to see
if performance was falling off with increases size, I then inserted
an additional million rows.  41 seconds: 24390 inserts per second.
New file size 297440256.  This is on three year old hardware.
Inserts can be significantly slower if you have indices.  The more
indices you have the slower things might go.  (Depending on what
your indices and your data look like.) If possible, it is recommended
that you do all your inserts first, then do the CREATE INDEX statements
as a separate step afterwards.
What does your schema look like?
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

Re: [sqlite] Row count in database

[amead]

Christopher Petrilli wrote:
Has anyone had any experience in storing a million or more rows in a
SQLite3 database?  I've got a database that I've been building, which
gets 250 inserts/second, roughly, and which has about 3M rows in it. 
At that point, the CPU load is huge.

Note that I've got syncing turned off, because I'm willing to accept
the risks.
Thoughts?  

Chris
 

I have a database with almost 1.4 records in one table and over half a 
million in another.  I treat them like they are read-only so I cannot 
advise you about write performance except to urge you to read the 
document on www.sqlite.org about performance...  I do notice that doing 
anything like creating an index takes a while, as do joins.  When I do, 
I hear my variable speed fan rev-up, so the load is definitely higher...

Here's a silly idea:  If you have enough RAM and you don't care about 
sync'ing then could you run things in memory... like on a RAM disk or 
buy one of those RAM hard-drives?

-Alan
--
Alan Mead - [EMAIL PROTECTED]
People often find it easier to be a result of the past than a cause of
the future.

[sqlite] Row count in database

[Christopher Petrilli]

Has anyone had any experience in storing a million or more rows in a
SQLite3 database?  I've got a database that I've been building, which
gets 250 inserts/second, roughly, and which has about 3M rows in it. 
At that point, the CPU load is huge.

Note that I've got syncing turned off, because I'm willing to accept
the risks.

Thoughts?  

Chris

-- 
| Christopher Petrilli
| [EMAIL PROTECTED]

Re: [sqlite] Row Count

[tezozomoc]

BlankConsider doing a 

SELECT COUNT (*) FROM db WHERE etc

This should give you the number of rows before you... do the select again... Use where 
to match the condition.


Tezo.
  - Original Message - 
  From: Drew, Stephen 
  To: '[EMAIL PROTECTED]' 
  Sent: Wednesday, August 11, 2004 4:59 AM
  Subject: [sqlite] Row Count


  Hello,

  Is there any way to obtain the number of rows returned by a SELECT statement, before 
receiving all the rows returned?  I am using the sqlite_exec() / sqlite_step() 
functions to return the data, but need to populate a progress bar.

  Regards,
  Stephen Drew 

  DISCLAIMER: This e-mail and any files transmitted with it contain confidential and 
proprietary information and is intended solely for the use of the intended recipient. 
If you are not the intended recipient, please return the e-mail to the sender and 
delete it from your computer and you must not use, disclose, distribute, copy, print 
or rely on this e-mail. The contents of this e-mail and any files transmitted with it 
may have been changed or altered without the consent of the author. Email transmission 
cannot be guaranteed to be secure or error-free. 

Re: [sqlite] Row Count

[Derrell . Lipman]

"Drew, Stephen" <[EMAIL PROTECTED]> writes:

> It's not a problem if I read the whole rowset into memory and then start
> sending it, but this can cause massive memory usage and time delays. It's
> not such a big deal if it's not possible, I'll just have to use a Microsoft
> style progress bar (i.e. one that keeps resetting). 

Or present the number of rows processed so far rather than a traditional
progress bar.  That way, you impress people with how fast it's processing
without resorting to what looks like a progress bar but really isn't (the MS
style).

Derrell

RE: [sqlite] Row Count

[Drew, Stephen]

Yes, I know it sounds a little optimistic :)

It's not a problem if I read the whole rowset into memory and then start
sending it, but this can cause massive memory usage and time delays. It's
not such a big deal if it's not possible, I'll just have to use a Microsoft
style progress bar (i.e. one that keeps resetting). 

 

-Original Message-
From: Will Leshner [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 11, 2004 4:46 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Row Count


On Aug 11, 2004, at 8:41 AM, Drew, Stephen wrote:

> This will be a big overhead on really small queries
>
> I need to be able to say roughly how many rows, but obviously the 
> exact number would be good.  Some queries are very time critical and 
> complex so I don't want to have to do a COUNT(*) on them.
>

So you would like a way to get a count of rows a SELECT will return 
without actually processing the SELECT?

Re: [sqlite] Row Count

[Will Leshner]

On Aug 11, 2004, at 8:41 AM, Drew, Stephen wrote:
This will be a big overhead on really small queries
I need to be able to say roughly how many rows, but obviously the exact
number would be good.  Some queries are very time critical and complex 
so I
don't want to have to do a COUNT(*) on them.

So you would like a way to get a count of rows a SELECT will return 
without actually processing the SELECT?

RE: [sqlite] Row Count

[Drew, Stephen]

This will be a big overhead on really small queries

I need to be able to say roughly how many rows, but obviously the exact
number would be good.  Some queries are very time critical and complex so I
don't want to have to do a COUNT(*) on them.

-Original Message-
From: Scott Baker [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 11, 2004 4:10 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Row Count

SELECT count(*) FROM Table WHERE Foo = 'bar';



Drew, Stephen wrote:
> Hello,
>  
> Is there any way to obtain the number of rows returned by a SELECT 
> statement, before receiving all the rows returned?  I am using the
> sqlite_exec() / sqlite_step() functions to return the data, but need 
> to populate a progress bar.
>  
> Regards,
> Stephen Drew

Re: [sqlite] Row Count

[Scott Baker]

SELECT count(*) FROM Table WHERE Foo = 'bar';

Drew, Stephen wrote:
Hello,
 
Is there any way to obtain the number of rows returned by a SELECT
statement, before receiving all the rows returned?  I am using the
sqlite_exec() / sqlite_step() functions to return the data, but need to
populate a progress bar.
 
Regards,
Stephen Drew 

[sqlite] Row Count

[Drew, Stephen]

Hello,
 
Is there any way to 
obtain the number of rows returned by a SELECT statement, before receiving all 
the rows returned?  I am using the sqlite_exec() / sqlite_step() functions 
to return the data, but need to populate a progress bar.
 
Regards,Stephen Drew 
DISCLAIMER: This e-mail and any files transmitted with it 
contain confidential and proprietary information and is intended solely for the 
use of the intended recipient. If you are not the intended recipient, please 
return the e-mail to the sender and delete it from your computer and you must 
not use, disclose, distribute, copy, print or rely on this e-mail. The contents 
of this e-mail and any files transmitted with it may have been changed or 
altered without the consent of the author. Email transmission cannot be 
guaranteed to be secure or error-free.