[sqlite] Overflow Page

[Scott Weigand]

Hello List,
Apologies if this is the wrong list to post to.  Can anyone tell me if an 
overflow page is encapsulated within a B-Tree page or if it starts on a 
standard page boundary and has its first 4 bytes as the next overflow page 
number?
Thanks,Scott  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Syntax for Counting Rows By Multiple Groups

[Pavel Ivanov]

>        sqlite> select sic, sic_desc, state, count(*) from Companies group by 
> sic;
> The 'group by' phrase returns one row per group, which is what I want as
> long as the group is a compound of industry number and state (the
> description makes it easier to read and is fixed in association with each
> number).

So is this what you want?

select sic, sic_desc, state, count(*) from Companies group by sic,
sic_desc, state;

If not explain your requirements better please.


Pavel

On Tue, Sep 21, 2010 at 1:47 PM, Rich Shepard  wrote:
>   I've a table with 15 columns, including industry number, industry
> description, and state. I'm trying to formulate the proper SELECT statement
> to return the count of rows for each industry number/description in each of
> the 5 states. I've looked at the aggregate function chapter in Rick van der
> Lans' "The SQL Guide to SQLite" without seeing a suitable example.
>
>   The closest I've come so far is:
>        sqlite> select sic, sic_desc, state, count(*) from Companies group by 
> sic;
> The 'group by' phrase returns one row per group, which is what I want as
> long as the group is a compound of industry number and state (the
> description makes it easier to read and is fixed in association with each
> number).
>
>   What I'd like to see is a table grouped either by sic or state:
>
>   sic  sic_desc    state   total companies
>
> or
>
>   state   sic   sic_desc  total companies
>
>   This shouldn't be that difficult but I'm just not thinking correctly.
>
> TIA,
>
> Rich
>
> ___
> 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] Syntax for Counting Rows By Multiple Groups

[Igor Tandetnik]

Rich Shepard  wrote:
>   The closest I've come so far is:
>  sqlite> select sic, sic_desc, state, count(*) from Companies group by sic;
> The 'group by' phrase returns one row per group, which is what I want as
> long as the group is a compound of industry number and state

Just throw more columns into GROUP BY clause:

select sic, sic_desc, state, count(*) from Companies group by sic, sic_desc, 
state;

-- 
Igor Tandetnik


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

[sqlite] Syntax for Counting Rows By Multiple Groups

[Rich Shepard]

   I've a table with 15 columns, including industry number, industry
description, and state. I'm trying to formulate the proper SELECT statement
to return the count of rows for each industry number/description in each of
the 5 states. I've looked at the aggregate function chapter in Rick van der
Lans' "The SQL Guide to SQLite" without seeing a suitable example.

   The closest I've come so far is:
sqlite> select sic, sic_desc, state, count(*) from Companies group by 
sic;
The 'group by' phrase returns one row per group, which is what I want as
long as the group is a compound of industry number and state (the
description makes it easier to read and is fixed in association with each
number).

   What I'd like to see is a table grouped either by sic or state:

   sic  sic_descstate   total companies

or

   state   sic   sic_desc  total companies

   This shouldn't be that difficult but I'm just not thinking correctly.

TIA,

Rich

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

Re: [sqlite] Performance problems and large memory size

[Pavel Ivanov]

> Is Sqlite somewhere caching data? If so, how do I disable it or decrease the 
> amount of cached data?

http://www.sqlite.org/pragma.html#pragma_cache_size


Pavel

On Tue, Sep 21, 2010 at 1:24 PM, "Richard Wähnelt"  wrote:
> Hello there,
>
> I hope, someone can help me with the problem I'm having.
>
> The whole picture:
> I'm running a .NET 3.5 application using System.Data.SQLite as provider. 
> Inserting Data happens via Entity Framework and Linq to Entitites. For 
> querying data I use DataAdapter/DataView/DataGridView combination with 
> SQL-Strings. The application runs 24/7.
> I created the database with just plain DDL commands and added two triggers, 
> no changes to configuration were made. The size of the sqlite file is about 
> 100 MB and there is data being inserted every 2 seconds. Each insert triggers 
> either one of the triggers. After a while (considerably short time of 1 day) 
> the memory consumption of my application as seen in Windows Task Manager 
> grows from about 60 MB to 150 MB. As far as I can tell, this doesn't come 
> from my application directly.
> This becomes a problem because the system is only equipped with Intel Atom 
> 1.6 GHz and 1 GB RAM (no other applications are running) and after a while my 
> application slows down and doesn't compute input as fast as needed.
>
>
> My question is now:
> Is Sqlite somewhere caching data? If so, how do I disable it or decrease the 
> amount of cached data?
>
> Or does this maybe happen in combination with the DataConnections for 
> EntityFramework and/or DataAdapter and do I need to reset them somehow to 
> clear memory?
>
> Any help is greatly appreciated.
>
> Kind Regards
> deslokrw
> ___
> WEB.DE DSL SOMMER-SPECIAL: Surf & Phone Flat 16.000 für
> nur 19,99 /mtl.!* http://produkte.web.de/go/DSL_Doppel_Flatrate/2
> ___
> 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

[sqlite] Performance problems and large memory size

[Richard Wähnelt]

Hello there,

I hope, someone can help me with the problem I'm having.

The whole picture:
I'm running a .NET 3.5 application using System.Data.SQLite as provider. 
Inserting Data happens via Entity Framework and Linq to Entitites. For querying 
data I use DataAdapter/DataView/DataGridView combination with SQL-Strings. The 
application runs 24/7.
I created the database with just plain DDL commands and added two triggers, no 
changes to configuration were made. The size of the sqlite file is about 100 MB 
and there is data being inserted every 2 seconds. Each insert triggers either 
one of the triggers. After a while (considerably short time of 1 day) the 
memory consumption of my application as seen in Windows Task Manager grows from 
about 60 MB to 150 MB. As far as I can tell, this doesn't come from my 
application directly.
This becomes a problem because the system is only equipped with Intel Atom 1.6 
GHz and 1 GB RAM (no other applications are running) and after a while my 
application slows down and doesn't compute input as fast as needed.


My question is now:
Is Sqlite somewhere caching data? If so, how do I disable it or decrease the 
amount of cached data?

Or does this maybe happen in combination with the DataConnections for 
EntityFramework and/or DataAdapter and do I need to reset them somehow to clear 
memory?

Any help is greatly appreciated.

Kind Regards
deslokrw
___
WEB.DE DSL SOMMER-SPECIAL: Surf & Phone Flat 16.000 für 
nur 19,99 /mtl.!* http://produkte.web.de/go/DSL_Doppel_Flatrate/2
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VACUUM can actually increase the DB file?

[Oliver Peters]

[...]

> Can you please run sqlite3_analyzer [...] on both
> the original database and the database after VACUUM
> and send me the output?

done, send 2 txt-files

greetings
Oliver

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