Re: [sqlite] Memory profiling SQLite database

2008-08-15 Thread D. Richard Hipp
On Aug 15, 2008, at 5:58 PM, Brown, Daniel wrote: > Good afternoon List, > > Is there any way at runtime to find out how much memory each of my > tables in my database are using, other than loading each table > individually into a :memory: database and comparing the before and > after >

Re: [sqlite] Generic speed testing

2008-08-15 Thread Noah Hart
Greg, I intended that sqlite3 be launched without a filename, so this will give a memory based database and disk I/O would not need to be considered. Regards -- Noah -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald Sent: Friday, August 15,

Re: [sqlite] Generic speed testing

2008-08-15 Thread Noah Hart
Ken, I'm not really sure what I want to test, or rather what would be a meaningful test, so I wanted to start a public discussion about relative performances. All systems will have limits in some way based on CPU, memory and disk. But an interesting question to me is sqlite whether sqlite is

Re: [sqlite] Generic speed testing

2008-08-15 Thread Griggs, Donald
Regarding: "On my AMD system the tests seem to be CPU bound." On that note, I believe the test creates a 625 megabyte database before deleting most of it and vacuuming down to a tiny size. So I guess included in the test is not just one's disk speed, but how fast one's operating system can

[sqlite] Memory profiling SQLite database

2008-08-15 Thread Brown, Daniel
Good afternoon List, Is there any way at runtime to find out how much memory each of my tables in my database are using, other than loading each table individually into a :memory: database and comparing the before and after values? Any sort of ability to generate a memory usage breakdown would

Re: [sqlite] Generic speed testing

2008-08-15 Thread Ken
Noah, really nice job with this tool. I find it quire useful just to get a relative performance comparison between my hardware systems. On my AMD system the tests seem to be CPU bound. But this is a DB, My concern is that this is really more of a CPU stress test than an I/O  DB test. That

Re: [sqlite] Generic speed testing

2008-08-15 Thread Noah Hart
Good Point Ken, Here is version 1.2 with the missing CREATE TABLE statement and some new PRAGMA settings. Any suggestions for the CACHE_SIZE setting? Also -- I can see how to modify an existing Wiki page, but does anyone know how to create a new Wiki page, so I can put this script there rather

Re: [sqlite] Generic speed testing

2008-08-15 Thread Kees Nuyt
On Thu, 14 Aug 2008 13:25:56 -0700, you wrote: >I'm not sure if this will even be a valid comparison, so your feedback >and initial numbers are appreciated. > >Please reply with your data as follows (v1.1 without the DROP TABLE TEST1 statement): sqlite 3.6.0 running on Acer Aspire 9423 under

Re: [sqlite] If row exists use UPDATE else INSERT

2008-08-15 Thread Igor Tandetnik
Javier Julio <[EMAIL PROTECTED]> wrote: > Reading that carefully I figure that means if I have a record in the > ideas table with an ideaId of 5 and I perform an INSERT with that same > ideaId of 5 (remember I always provide the id as I want to use the id > from the server) then it removes that

Re: [sqlite] Generic speed testing

2008-08-15 Thread Kees Nuyt
On Thu, 14 Aug 2008 13:25:56 -0700, you wrote: >-- >-- A LITTLE CLEANUP BEFORE WE CONTINUE >-- > > DROP TABLE TEST1; I don't think you really want to drop TEST1. We'll need it later. -- ( Kees Nuyt ) c[_]

Re: [sqlite] If row exists use UPDATE else INSERT

2008-08-15 Thread cmartin
On Fri, 15 Aug 2008, Javier Julio wrote: > Is it possible in SQLite to have a single statement that basically > says if this row exists run an UPDATE statement, if not run an INSERT? You can INSERT rows that don't already exist. For example, the following creates 2 tables, FOO and BAR that

Re: [sqlite] If row exists use UPDATE else INSERT

2008-08-15 Thread Javier Julio
> > Javier Julio <[EMAIL PROTECTED]> wrote: >> Is it possible in SQLite to have a single statement that basically >> says if this row exists run an UPDATE statement, if not run an >> INSERT? > > In some situations, depending on your definition of "exists", INSERT > OR > REPLACE statement may

Re: [sqlite] If row exists use UPDATE else INSERT

2008-08-15 Thread Igor Tandetnik
Javier Julio <[EMAIL PROTECTED]> wrote: > Is it possible in SQLite to have a single statement that basically > says if this row exists run an UPDATE statement, if not run an INSERT? In some situations, depending on your definition of "exists", INSERT OR REPLACE statement may be suitable:

[sqlite] If row exists use UPDATE else INSERT

2008-08-15 Thread Javier Julio
Hey everyone. Just signed up last night and had skimmed through several of the archives. Excited to partake in future discussions. I'm just taking the plunge into SQLite since I'm working with Adobe AIR and Flex. I'm building a desktop app that will sync data to a SQLite database. I have

Re: [sqlite] Grabbing a record from an "in-use" database via a second process...

2008-08-15 Thread D. Richard Hipp
On Aug 15, 2008, at 12:33 PM, Jeff Godfrey wrote: > > I'm writing a Tcl-based application that manages a pool of separate > SQLite database files. ... Is it safe for me to blindly open the > other database > and grab the record I need? Yes. D. Richard Hipp [EMAIL PROTECTED]

[sqlite] Grabbing a record from an "in-use" database via a second process...

2008-08-15 Thread Jeff Godfrey
Hi All, I'm writing a Tcl-based application that manages a pool of separate SQLite database files. The application is multi-User, but it requires each User to "check out" a specific database in order to access it, so a single database is only ever accessed by a single User That is, until

[sqlite] implementing a vfs

2008-08-15 Thread Jeffrey Becker
Are pointers sqlite3_file structures considered 'invalid' after Close has been called on them? If not, at what point can I free memory associated with them? ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] FTS3 Snippet function on two column MATCHes

2008-08-15 Thread P Kishor
On Fri, Aug 15, 2008 at 9:13 PM, Alexandre Courbot <[EMAIL PROTECTED]> wrote: >> I would like to generate Snippets from MATCHes in two columns, >> however, I get the following error: "unable to use function MATCH in >> the requested context" with the following query -- > > I think you ran into the

Re: [sqlite] FTS3 Snippet function on two column MATCHes

2008-08-15 Thread Alexandre Courbot
> I would like to generate Snippets from MATCHes in two columns, > however, I get the following error: "unable to use function MATCH in > the requested context" with the following query -- I think you ran into the same problem as I did:

[sqlite] FTS3 Snippet function on two column MATCHes

2008-08-15 Thread P Kishor
I have the following tables CREATE TABLE poems (poem_id, poem, history); CREATE VIRTUAL TABLE fts_poems USING fts3 (poem, history); INSERT INTO fts_poems (rowid, poem, history) SELECT poem_id, poem, history FROM poems; The following works -- SELECT poem_id, context FROM poems a JOIN (

Re: [sqlite] LIKE operator and ATTACH databases memory usage

2008-08-15 Thread [EMAIL PROTECTED]
Our e-mails crossed on the way to the list... sorry for the confusion. Thank you for your advices - i'll follow up them to make the mentioned wildcard search working. Basically, I'm looking for a way to have a fast wildcard search on a dataset that is distributed over multiple databases whose

Re: [sqlite] LIKE operator and ATTACH databases memory usage

2008-08-15 Thread [EMAIL PROTECTED]
I'm going to check sqlite3_memory_used()/sqlite3_memory_highwater() next week as soon as I can. At the moment the following is clear: The application needs to run the same statement with LIKE operator for multiple attached databases. On the embedded side, the device crashes after a few

Re: [sqlite] Generic speed testing

2008-08-15 Thread Shane Harrelson
Windows XP SP2, SQLite 3.6.1, Intel T2400 (1.83GHZ) Dual Core, 2Gb RAM, 5000RPM Drive SQL error near line 112: no such table: TEST1 SQL error near line 127: no such table: TEST1 0|performance.txt,v 1.0|1.0|0|0.0K Rows/Second 1|Trivial Inserts|21.17|10077696|476.0K Rows/Second 2|Trivial

Re: [sqlite] LIKE operator and ATTACH databases memory usage

2008-08-15 Thread [EMAIL PROTECTED]
> This causes most of the database to be loaded into cache. Is there one cache per database connection or one cache per ATTACH'ed database? ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] LIKE operator and ATTACH databases memory usage

2008-08-15 Thread D. Richard Hipp
On Aug 15, 2008, at 8:06 AM, [EMAIL PROTECTED] wrote: > Hello > > Why does SQLite consume 2.5MB memory every time when running a > statement on a attached database with LIKE operator? > > Example 1: > SELECT fs_rec FROM fs_main WHERE fs_itemtype=? AND fs_textid > LIKE ?; //

Re: [sqlite] LIKE operator and ATTACH databases memory usage

2008-08-15 Thread D. Richard Hipp
On Aug 15, 2008, at 9:56 AM, [EMAIL PROTECTED] wrote: > > I don’t dare to use the term “leak” here. It is hard so say at the > moment where the memory is going to. At least, the > memory is not freed when sqlite3_finalize() is called on the > statement. Might it be possible, that this memory

Re: [sqlite] SQL statement to get min/max values

2008-08-15 Thread Dennis Volodomanov
Thank you Simon and Igor for suggesting the cast() - that works without me having to change anything! And of course I appreciate everyone's replies as well. Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] LIKE operator and ATTACH databases memory usage

2008-08-15 Thread D. Richard Hipp
On Aug 15, 2008, at 8:06 AM, [EMAIL PROTECTED] wrote: > Hello > > Why does SQLite consume 2.5MB memory every time when running a > statement on a attached database with LIKE operator? This mailing list strips off attachments. Please send the database by some other means. How do you know

[sqlite] LIKE operator and ATTACH databases memory usage

2008-08-15 Thread [EMAIL PROTECTED]
Hello Why does SQLite consume 2.5MB memory every time when running a statement on a attached database with LIKE operator? Example 1: SELECT fs_rec FROM fs_main WHERE fs_itemtype=? AND fs_textid LIKE ?; // consumes <50kB RAM Example 2: SELECT fs_rec FROM _job01.fs_main WHERE

Re: [sqlite] SQL statement to get min/max values

2008-08-15 Thread Igor Tandetnik
"Dennis Volodomanov" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Funny enough, but it doesn't work on real data using v3.6.1... > > Here's the table: > > sqlite> .dump test_table > BEGIN TRANSACTION; > CREATE TABLE test_table (ID INTEGER PRIMARY KEY, ExternalID2 INTEGER, >

Re: [sqlite] SQL statement to get min/max values

2008-08-15 Thread Simon Davies
Not sure about replacing the collation sequence - does not sound easier than recreating the table. You could just add a view: sqlite> CREATE TABLE test_table (ID INTEGER PRIMARY KEY, ...> ExternalID2 INTEGER, ...> ExternalID INTEGER, ...>

Re: [sqlite] SQL statement to get min/max values

2008-08-15 Thread Dennis Volodomanov
> Declaring the column as integer does not prevent you from storing strings: > > Yes, except for a small problem of updating all live databases with the new column type. I don't think I can update the column type without recreating the table, right? It's not hard, so if it comes down to

Re: [sqlite] SQL statement to get min/max values

2008-08-15 Thread Simon Davies
Hi Dennis, Declaring the column as integer does not prevent you from storing strings: SQLite version 3.6.0 Enter ".help" for instructions sqlite> BEGIN TRANSACTION; sqlite> CREATE TABLE test_table (ID INTEGER PRIMARY KEY, ExternalID2 INTEGER, ...> ExternalID ...> INTEGER, Value INTEGER);

Re: [sqlite] SQL statement to get min/max values

2008-08-15 Thread Dennis Volodomanov
> Works just fine with 3.6.1 if you declare the Value column to be > INTEGER. As it is, I have no idea what collation is used, but the > Value column will be declared to default to TEXT values, as shown by > > select typeof(value) from test_table; > I haven't tried that, but I cannot declare it

Re: [sqlite] SQL statement to get min/max values

2008-08-15 Thread Mihai Limbasan
Dennis Volodomanov wrote: Funny enough, but it doesn't work on real data using v3.6.1... Here's the table: sqlite> .dump test_table BEGIN TRANSACTION; CREATE TABLE test_table (ID INTEGER PRIMARY KEY, ExternalID2 INTEGER, ExternalID INTEGER, Value ); INSERT INTO "test_table"

Re: [sqlite] SQL statement to get min/max values

2008-08-15 Thread Dennis Volodomanov
Funny enough, but it doesn't work on real data using v3.6.1... Here's the table: sqlite> .dump test_table BEGIN TRANSACTION; CREATE TABLE test_table (ID INTEGER PRIMARY KEY, ExternalID2 INTEGER, ExternalID INTEGER, Value ); INSERT INTO "test_table" VALUES(1007,1,37,'-5'); INSERT INTO

Re: [sqlite] returning multiple rows from custom functions

2008-08-15 Thread Michael Janis
Alexey, This is really great! Thanks very much for putting the code online. I will definitely be using this as a template for returning tables from functions. Thank you! Best Regards, * Michael Janis [EMAIL PROTECTED] * On Fri, Aug 15, 2008 at 1:07 AM, Alexey

Re: [sqlite] returning multiple rows from custom functions

2008-08-15 Thread Alexey Pechnikov
Hello! I did write yesterday message "Table functions emulation" with description of function for generating table with integers ranging. create table testrange(rowid); select intrange2table (1,10,1,'testrange'); select * from testrange; 1 2 3 4 5 6 7 8 9 10 В сообщении от Friday 15 August