Re: [sqlite] Generic speed testing
Ubuntu 7.10, SQLite 3.5.9 (debugging turned on), Intel Pentium E2140
(1.60GHz) Dual Core, 2Gb RAM, 7200RPM Drive
Bunch of crud to stdout followed by:
0|performance.txt,v 1.2|1.0|0|0.0K Rows/Second
1|Trivial Inserts|21.52|10077696|468.0K Rows/Second
2|Trivial Selects|1.43|10077696|7046.0K Rows/Second
3|Trivial Updates|114.01|10077696|88.0K Rows/Second
4|Trivial Deletes|32.91|10077696|306.0K Rows/Second
5|Insert with calculations|40.94|10077696|246.0K Rows/Second
6|Updates with calculations and longer rows|89.98|10077696|112.0K
Rows/Second
Noah Hart wrote:
> 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 than repeating it in email?
>
> Noah
> SQLite 3.6.1 running under "Vista 32 bit, QuadCore 2.4GHz, 3G ram,
> 5000rpmDisk"
>
> 0|performance.txt,v 1.2|1.0|0|0.0K Rows/Second
> 1|Trivial Inserts|16.31|10077696|618.0K Rows/Second
> 2|Trivial Selects|0.85|10077696|11898.0K Rows/Second
> 3|Trivial Updates|82.41|10077696|122.0K Rows/Second
> 4|Trivial Deletes|23.32|10077696|432.0K Rows/Second
> 5|Insert with calculations|29.02|10077696|347.0K Rows/Second
> 6|Updates with calculations and longer rows|54.52|10077696|185.0K
> Rows/Second
>
> --
> -- The author disclaims copyright to this source code. In place of
> -- a legal notice, here is a blessing:
> --
> --May you do good and not evil.
> --May you find forgiveness for yourself and forgive others.
> --May you share freely, never taking more than you give.
> --
> --**
> *
> -- This file contains code used to implement the performance scripts
> --
> -- $Id: performance.txt,v 1.2 2008/08/15 14:15:00 nbh Exp $
>
>
> --
> -- LEVEL THE PLAYING FIELD WITH PRAGMAs
> --
>
> PRAGMA auto_vacuum = NONE;
> PRAGMA cache_size = 2;
> PRAGMA count_changes = 1;
> PRAGMA encoding = "UTF-8";
> PRAGMA fullfsync = 0;
> PRAGMA journal_mode = DELETE;
> PRAGMA locking_mode = EXCLUSIVE;
> PRAGMA page_size = 1024;
> PRAGMA synchronous = OFF;
> PRAGMA temp_store = MEMORY;
> --
> -- A LITTLE SETUP BEFORE WE BEGIN
> --
>
> CREATE TABLE TIMER(TestNumber INTEGER, Description TEXT, StartTime
> REAL, EndTime REAL DEFAULT NULL, Rows INTEGER DEFAULT NULL);
> INSERT INTO TIMER VALUES(0, 'performance.txt,v 1.2', 0, 1, 0);
> CREATE TABLE TEST1 (I INTEGER, T TEXT);
> CREATE TABLE N_1(i INTEGER, t TEXT);
> INSERT INTO N_1 VALUES(1, 't1_');
> INSERT INTO N_1 VALUES(2, 't_22_');
> INSERT INTO N_1 VALUES(3, 'tx_3_3_3_');
> INSERT INTO N_1 VALUES(4, 'txt_4_4_4_4_');
> INSERT INTO N_1 VALUES(5, 'text_555_');
> INSERT INTO N_1 VALUES(6, ' ');
> CREATE TABLE N_2(i INTEGER, t TEXT);
> INSERT INTO N_2 SELECT N1.I+N2.I*7, N1.T||N2.T FROM N_1 N1 CROSS
> JOIN N_1 N2 CROSS JOIN N_1 N3;
>
>
> -- TEST 1
> -- TRIVIAL INSERTS -- KEEP THE NUMBER AND TEXT SMALL
>
> BEGIN;
> INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
> 1+MAX(TESTNUMBER), 'Trivial Inserts', (julianday('now') -
> 2440587.5)*86400 FROM TIMER;
> INSERT INTO TEST1 SELECT 1,'T' FROM N_2 N1 CROSS JOIN N_2 N2 CROSS
> JOIN N_2 N3;
> COMMIT;
>
> UPDATE TIMER SET EndTime = (julianday('now') -
> 2440587.5)*86400.0, Rows = changes()
> WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
>
>
> -- TEST 2
> -- TRIVIAL SELECTS
>
> INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
> 1+MAX(TESTNUMBER), 'Trivial Selects', (julianday('now') -
> 2440587.5)*86400 FROM TIMER;
> UPDATE TIMER SET Rows = (SELECT COUNT(*) FROM TEST1 where rowid
>> 0)
> WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
> UPDATE TIMER SET EndTime = (julianday('now') -
> 2440587.5)*86400.0
> WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
>
>
> -- TEST 3
> -- TRIVIAL UPDATES -- THE NUMBERS AND ROW SIZE ARE SMALL
>
> BEGIN;
> INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
> 1+MAX(TESTNUMBER), 'Trivial Updates', (julianday('now') -
> 2440587.5)*86400 FROM TIMER;
> UPDATE TEST1 SET I=I;
> COMMIT;
> UPDATE TIMER SET EndTime = (julianday('now') -
> 2440587.5)*86400.0, Rows = changes()
>
Re: [sqlite] Memory profiling SQLite database
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 > values? Generate an equivalent database in a disk file. Then run the disk file through the sqlite3_analyzer utility which you can download from the website. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generic speed testing
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, 2008 2:52 PM To: [EMAIL PROTECTED]; General Discussion of SQLite Database Subject: Re: [sqlite] Generic speed testing 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 allocate the space and how fragmented the result is. This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generic speed testing
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 more efficient (whatever
that means) on one platform vs. another.
This first script was intended to focus on CPU which (with enough data) should
give a nice baseline for further testing on various platforms.
I should also note that it is intended that sqlite3 be launched without a
filename, since this will give a memory based database, ignoring all I/O
Regards -- Noah
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ken
Sent: Friday, August 15, 2008 2:46 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Generic speed testing
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 begs the question, What do you really want to test?
CPU prepare/parse with calculations ? Or select insert/update/delete that Is
more I/O bound (what about indexing ?)
Maybe think of creating another test suite that stresses i/o subsystem and does
not run complex calculations.
HTH
Ken
--- On Fri, 8/15/08, Noah Hart <[EMAIL PROTECTED]> wrote:
From: Noah Hart <[EMAIL PROTECTED]>
Subject: Re: [sqlite] Generic speed testing
To: "General Discussion of SQLite Database"
Date: Friday, August 15, 2008, 4:34 PM
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 than repeating it in email?
Noah
SQLite 3.6.1 running under "Vista 32 bit, QuadCore 2.4GHz, 3G ram,
5000rpmDisk"
0|performance.txt,v 1.2|1.0|0|0.0K Rows/Second
1|Trivial Inserts|16.31|10077696|618.0K Rows/Second
2|Trivial Selects|0.85|10077696|11898.0K Rows/Second
3|Trivial Updates|82.41|10077696|122.0K Rows/Second
4|Trivial Deletes|23.32|10077696|432.0K Rows/Second
5|Insert with calculations|29.02|10077696|347.0K Rows/Second
6|Updates with calculations and longer rows|54.52|10077696|185.0K
Rows/Second
--
-- The author disclaims copyright to this source code. In place of
-- a legal notice, here is a blessing:
--
--May you do good and not evil.
--May you find forgiveness for yourself and forgive others.
--May you share freely, never taking more than you give.
--
--**
*
-- This file contains code used to implement the performance scripts
--
-- $Id: performance.txt,v 1.2 2008/08/15 14:15:00 nbh Exp $
--
-- LEVEL THE PLAYING FIELD WITH PRAGMAs
--
PRAGMA auto_vacuum = NONE;
PRAGMA cache_size = 2;
PRAGMA count_changes = 1;
PRAGMA encoding = "UTF-8";
PRAGMA fullfsync = 0;
PRAGMA journal_mode = DELETE;
PRAGMA locking_mode = EXCLUSIVE;
PRAGMA page_size = 1024;
PRAGMA synchronous = OFF;
PRAGMA temp_store = MEMORY;
--
-- A LITTLE SETUP BEFORE WE BEGIN
--
CREATE TABLE TIMER(TestNumber INTEGER, Description TEXT, StartTime
REAL, EndTime REAL DEFAULT NULL, Rows INTEGER DEFAULT NULL);
INSERT INTO TIMER VALUES(0, 'performance.txt,v 1.2', 0, 1, 0);
CREATE TABLE TEST1 (I INTEGER, T TEXT);
CREATE TABLE N_1(i INTEGER, t TEXT);
INSERT INTO N_1 VALUES(1, 't1_');
INSERT INTO N_1 VALUES(2, 't_22_');
INSERT INTO N_1 VALUES(3, 'tx_3_3_3_');
INSERT INTO N_1 VALUES(4, 'txt_4_4_4_4_');
INSERT INTO N_1 VALUES(5, 'text_555_');
INSERT INTO N_1 VALUES(6, ' ');
CREATE TABLE N_2(i INTEGER, t TEXT);
INSERT INTO N_2 SELECT N1.I+N2.I*7, N1.T||N2.T FROM N_1 N1 CROSS
JOIN N_1 N2 CROSS JOIN N_1 N3;
-- TEST 1
-- TRIVIAL INSERTS -- KEEP THE NUMBER AND TEXT SMALL
BEGIN;
INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Inserts', (julianday('now') -
2440587.5)*86400 FROM TIMER;
INSERT INTO TEST1 SELECT 1,'T' FROM N_2 N1 CROSS JOIN N_2 N2 CROSS
JOIN N_2 N3;
COMMIT;
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0, Rows = changes()
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
-- TEST 2
-- TRIVIAL SELECTS
Re: [sqlite] Generic speed testing
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 allocate the space and how fragmented the result is. This email and any attachments have been scanned for known viruses using multiple scanners. We believe that this email and any attachments are virus free, however the recipient must take full responsibility for virus checking. This email message is intended for the named recipient only. It may be privileged and/or confidential. If you are not the named recipient of this email please notify us immediately and do not copy it or use it for any purpose, nor disclose its contents to any other person. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Memory profiling SQLite database
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 be great. Cheers, Daniel Brown "The best laid schemes o' mice an' men, gang aft agley" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generic speed testing
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 begs the question, What do you really want to test?
CPU prepare/parse with calculations ? Or select insert/update/delete that Is
more I/O bound (what about indexing ?)
Maybe think of creating another test suite that stresses i/o subsystem and does
not run complex calculations.
HTH
Ken
--- On Fri, 8/15/08, Noah Hart <[EMAIL PROTECTED]> wrote:
From: Noah Hart <[EMAIL PROTECTED]>
Subject: Re: [sqlite] Generic speed testing
To: "General Discussion of SQLite Database"
Date: Friday, August 15, 2008, 4:34 PM
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 than repeating it in email?
Noah
SQLite 3.6.1 running under "Vista 32 bit, QuadCore 2.4GHz, 3G ram,
5000rpmDisk"
0|performance.txt,v 1.2|1.0|0|0.0K Rows/Second
1|Trivial Inserts|16.31|10077696|618.0K Rows/Second
2|Trivial Selects|0.85|10077696|11898.0K Rows/Second
3|Trivial Updates|82.41|10077696|122.0K Rows/Second
4|Trivial Deletes|23.32|10077696|432.0K Rows/Second
5|Insert with calculations|29.02|10077696|347.0K Rows/Second
6|Updates with calculations and longer rows|54.52|10077696|185.0K
Rows/Second
--
-- The author disclaims copyright to this source code. In place of
-- a legal notice, here is a blessing:
--
--May you do good and not evil.
--May you find forgiveness for yourself and forgive others.
--May you share freely, never taking more than you give.
--
--**
*
-- This file contains code used to implement the performance scripts
--
-- $Id: performance.txt,v 1.2 2008/08/15 14:15:00 nbh Exp $
--
-- LEVEL THE PLAYING FIELD WITH PRAGMAs
--
PRAGMA auto_vacuum = NONE;
PRAGMA cache_size = 2;
PRAGMA count_changes = 1;
PRAGMA encoding = "UTF-8";
PRAGMA fullfsync = 0;
PRAGMA journal_mode = DELETE;
PRAGMA locking_mode = EXCLUSIVE;
PRAGMA page_size = 1024;
PRAGMA synchronous = OFF;
PRAGMA temp_store = MEMORY;
--
-- A LITTLE SETUP BEFORE WE BEGIN
--
CREATE TABLE TIMER(TestNumber INTEGER, Description TEXT, StartTime
REAL, EndTime REAL DEFAULT NULL, Rows INTEGER DEFAULT NULL);
INSERT INTO TIMER VALUES(0, 'performance.txt,v 1.2', 0, 1, 0);
CREATE TABLE TEST1 (I INTEGER, T TEXT);
CREATE TABLE N_1(i INTEGER, t TEXT);
INSERT INTO N_1 VALUES(1, 't1_');
INSERT INTO N_1 VALUES(2, 't_22_');
INSERT INTO N_1 VALUES(3, 'tx_3_3_3_');
INSERT INTO N_1 VALUES(4, 'txt_4_4_4_4_');
INSERT INTO N_1 VALUES(5, 'text_555_');
INSERT INTO N_1 VALUES(6, ' ');
CREATE TABLE N_2(i INTEGER, t TEXT);
INSERT INTO N_2 SELECT N1.I+N2.I*7, N1.T||N2.T FROM N_1 N1 CROSS
JOIN N_1 N2 CROSS JOIN N_1 N3;
-- TEST 1
-- TRIVIAL INSERTS -- KEEP THE NUMBER AND TEXT SMALL
BEGIN;
INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Inserts', (julianday('now') -
2440587.5)*86400 FROM TIMER;
INSERT INTO TEST1 SELECT 1,'T' FROM N_2 N1 CROSS JOIN N_2 N2 CROSS
JOIN N_2 N3;
COMMIT;
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0, Rows = changes()
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
-- TEST 2
-- TRIVIAL SELECTS
INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Selects', (julianday('now') -
2440587.5)*86400 FROM TIMER;
UPDATE TIMER SET Rows = (SELECT COUNT(*) FROM TEST1 where rowid
> 0)
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
-- TEST 3
-- TRIVIAL UPDATES -- THE NUMBERS AND ROW SIZE ARE SMALL
BEGIN;
INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Updates', (julianday('now') -
2440587.5)*86400 FROM TIMER;
UPDATE TEST1 SET I=I;
COMMIT;
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)
Re: [sqlite] Generic speed testing
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 than repeating it in email?
Noah
SQLite 3.6.1 running under "Vista 32 bit, QuadCore 2.4GHz, 3G ram,
5000rpmDisk"
0|performance.txt,v 1.2|1.0|0|0.0K Rows/Second
1|Trivial Inserts|16.31|10077696|618.0K Rows/Second
2|Trivial Selects|0.85|10077696|11898.0K Rows/Second
3|Trivial Updates|82.41|10077696|122.0K Rows/Second
4|Trivial Deletes|23.32|10077696|432.0K Rows/Second
5|Insert with calculations|29.02|10077696|347.0K Rows/Second
6|Updates with calculations and longer rows|54.52|10077696|185.0K
Rows/Second
--
-- The author disclaims copyright to this source code. In place of
-- a legal notice, here is a blessing:
--
--May you do good and not evil.
--May you find forgiveness for yourself and forgive others.
--May you share freely, never taking more than you give.
--
--**
*
-- This file contains code used to implement the performance scripts
--
-- $Id: performance.txt,v 1.2 2008/08/15 14:15:00 nbh Exp $
--
-- LEVEL THE PLAYING FIELD WITH PRAGMAs
--
PRAGMA auto_vacuum = NONE;
PRAGMA cache_size = 2;
PRAGMA count_changes = 1;
PRAGMA encoding = "UTF-8";
PRAGMA fullfsync = 0;
PRAGMA journal_mode = DELETE;
PRAGMA locking_mode = EXCLUSIVE;
PRAGMA page_size = 1024;
PRAGMA synchronous = OFF;
PRAGMA temp_store = MEMORY;
--
-- A LITTLE SETUP BEFORE WE BEGIN
--
CREATE TABLE TIMER(TestNumber INTEGER, Description TEXT, StartTime
REAL, EndTime REAL DEFAULT NULL, Rows INTEGER DEFAULT NULL);
INSERT INTO TIMER VALUES(0, 'performance.txt,v 1.2', 0, 1, 0);
CREATE TABLE TEST1 (I INTEGER, T TEXT);
CREATE TABLE N_1(i INTEGER, t TEXT);
INSERT INTO N_1 VALUES(1, 't1_');
INSERT INTO N_1 VALUES(2, 't_22_');
INSERT INTO N_1 VALUES(3, 'tx_3_3_3_');
INSERT INTO N_1 VALUES(4, 'txt_4_4_4_4_');
INSERT INTO N_1 VALUES(5, 'text_555_');
INSERT INTO N_1 VALUES(6, ' ');
CREATE TABLE N_2(i INTEGER, t TEXT);
INSERT INTO N_2 SELECT N1.I+N2.I*7, N1.T||N2.T FROM N_1 N1 CROSS
JOIN N_1 N2 CROSS JOIN N_1 N3;
-- TEST 1
-- TRIVIAL INSERTS -- KEEP THE NUMBER AND TEXT SMALL
BEGIN;
INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Inserts', (julianday('now') -
2440587.5)*86400 FROM TIMER;
INSERT INTO TEST1 SELECT 1,'T' FROM N_2 N1 CROSS JOIN N_2 N2 CROSS
JOIN N_2 N3;
COMMIT;
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0, Rows = changes()
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
-- TEST 2
-- TRIVIAL SELECTS
INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Selects', (julianday('now') -
2440587.5)*86400 FROM TIMER;
UPDATE TIMER SET Rows = (SELECT COUNT(*) FROM TEST1 where rowid
> 0)
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
-- TEST 3
-- TRIVIAL UPDATES -- THE NUMBERS AND ROW SIZE ARE SMALL
BEGIN;
INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Updates', (julianday('now') -
2440587.5)*86400 FROM TIMER;
UPDATE TEST1 SET I=I;
COMMIT;
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0, Rows = changes()
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
-- TEST 4
-- TRIVIAL DELETES
BEGIN;
INSERT INTO TIMER (TestNumber, Description, StartTime) SELECT
1+MAX(TESTNUMBER), 'Trivial Deletes', (julianday('now') -
2440587.5)*86400 FROM TIMER;
DELETE FROM TEST1 WHERE I >0;
COMMIT;
UPDATE TIMER SET EndTime = (julianday('now') -
2440587.5)*86400.0, Rows = changes()
WHERE TestNumber = (SELECT MAX(TESTNUMBER) FROM TIMER);
--
-- A LITTLE CLEANUP BEFORE WE CONTINUE
--
DROP TABLE TEST1;
CREATE TABLE TEST1 (I INTEGER, T TEXT)
Re: [sqlite] Generic speed testing
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 "MS Windows Vista 32bit on Intel Core 2 Duo T5500 1.66GHz, 667 MHz FSB, 2GB DDR2 RAM, Hitachi HTS541616J9SA00 SATA 5400RPM with 8MB buffer, write caching enabled" 0|performance.txt,v 1.1|1.0|0|0.0K Rows/Second 1|Trivial Inserts|31.41|10077696|321.0K Rows/Second 2|Trivial Selects|2.06|10077696|4887.0K Rows/Second 3|Trivial Updates|142.87|10077696|71.0K Rows/Second 4|Trivial Deletes|41.39|10077696|243.0K Rows/Second 5|Insert with calculations|61.81|10077696|163.0K Rows/Second 6|Updates with calculations and longer rows|383.63|10077696|26.0K Rows/Second I might be able to squeeze more performance out of this box. Hint: For a genuinely level playing field you'd have to use more PRAGMAs, like page size and cache size. Defaults might be different in different environments. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] If row exists use UPDATE else INSERT
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 row, and replaces it with the > newly provided one? Correct. > It seems I need to make sure I set a UNIQUE > constraint on that ideaId field in my ideas table and not just set it > as a PRIMARY KEY. PRIMARY KEY implies UNIQUE. > Does the INSERT OR REPLACE Internally figure out whether to do an > INSERT or UPDATE? Maybe it does a delete and then an insert every > time? It tries to insert. If this results in a conflict, it deletes and then inserts again. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generic speed testing
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[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] If row exists use UPDATE else INSERT
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 share a common field (KEY), which is unique in both tables. It then inserts 3 rows into FOO and one row in BAR: create table foo (key integer unique, foodata ); create table bar (key integer unique, bardata ); insert into foo values(1,"one"); insert into foo values(2,"two"); insert into foo values(3,"three"); insert into bar values(2,"two"); To insert into BAR only those 2 rows from FOO that do not exist in BAR: insert into bar (key, bardata) select key, foodata from foo where foo.key not in(select key from bar); BAR now has the same rows as FOO. If you change a data value in row 2 in FOO: update foo set foodata="changed" where key=2; and you want to update BAR with this new value, you can run an update based on a join of the common field 'key': update bar set bardata=(select foodata from foo where key=bar.key); This is a simple example that may not be appropriate for your situation. For instance, the update statement updates ALL rows whether they have different values or not. This may not be suitable for a very large database, but there are other approaches, e.g.: update bar set bardata=(select foodata from foo where key=bar.key and foodata <> bar.bardata); This only updates rows where foodata <> bardata. Depending on the specifics, you may want to index the 'key' fields to optimize the inserts and updates. Also, depending on the nature of the data being update, this may be inefficient. You are probably better off using your code to identify the rows needing updating/inserting then running specific SQL statements. Chris Christopher F. Martin School of Medicine Center for Digestive Diseases & Nutrition CB# 7555, 4104 Bioinformatics Bldg. University of North Carolina at Chapel Hill Chapel Hill, North Carolina 27599-7555 Phone: 919.966.9340 Fax: 919.966.7592 ~~~ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] If row exists use UPDATE else INSERT
> > 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: > > http://sqlite.org/lang_insert.html > http://sqlite.org/lang_conflict.html > > Otherwise you will have to emulate it in your program code. Perform an > UPDATE, use sqlite3_changes to see if any rows were actually > modified as > a result, if not run INSERT. > >> I believe these are done in procedures which I know is not supported >> in SQLite. > > But I'm pretty sure the language you write your host application in > supports some concept of a subroutine. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Igor, I have seen that you help out others often and am grateful you find the time. I've looked into the INSERT OR REPLACE docs. It should have made sense to me before but was probably overwhelmed with it at first. I've dug through it again and am wondering then maybe if this will do it. I will have an id field that will always be unique. It won't be an auto increment field since I'll be using the id from the resultset coming from the server so I will manually insert that id. It's an auto incremented on the server's database so its always unique. So from the description: "When a UNIQUE constraint violation occurs, the pre-existing rows that are causing the constraint violation are removed prior to inserting or updating the current row. Thus the insert or update always occurs." 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 row, and replaces it with the newly provided one? It seems I need to make sure I set a UNIQUE constraint on that ideaId field in my ideas table and not just set it as a PRIMARY KEY. Does the INSERT OR REPLACE Internally figure out whether to do an INSERT or UPDATE? Maybe it does a delete and then an insert every time? If that is correct then yes that is all I need. My only concern is maybe I don't want to do this for each row of data if its not necessary. For example, maybe that record has had no change based on a date field. Do you know if that INSERT OR REPLACE is an expensive operation? I probably should be cautious and not do that unless necessary. Since I'll be syncing the data one way, I probably should run a simple SELECT statement first to check the last modified date. Thanks Igor. Ciao! Javi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] If row exists use UPDATE else INSERT
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: http://sqlite.org/lang_insert.html http://sqlite.org/lang_conflict.html Otherwise you will have to emulate it in your program code. Perform an UPDATE, use sqlite3_changes to see if any rows were actually modified as a result, if not run INSERT. > I believe these are done in procedures which I know is not supported > in SQLite. But I'm pretty sure the language you write your host application in supports some concept of a subroutine. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] If row exists use UPDATE else INSERT
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 some experience with SQL Server and have been doing research on SQLite to find out what it supports and what it doesn't. 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? I believe these are done in procedures which I know is not supported in SQLite. I've seen a lot of discussions on triggers though and wonder if this is maybe something I should look into for what I want to achieve? Probably won't do the trick as it seems it runs after a data change/operation is made. I'm just learning and trying to figure out the best way to use SQLite through AIR to sync data from the server to the desktop as its only one way sync. Trying to make sure I get the best performance. If I can cut down on the statements I need to use figure it would help. But for now I have a feeling I need to do a SELECT statement to see if that row exists and based on that result perform an INSERT or UPDATE. I would repeat that for every result from the server. Ciao! Javi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Loading a existing database 100% into memory
Ok I've moved the test database into the same directory as my C++ test application (I'm on windows) to remove the directory reference, my queries now look like: "ATTACH DATABASE 'Test01.db' AS Test01" - returns SQLITE_OK "SELECT tbl_name FROM Test01.sqlite_master WHERE type = 'table'" - returns SQLITE_ERROR I'm still getting the same error message as before 'no such table Test01.sqlite_master', I've opened the test database in SQLite Analyzer and it seems valid. If I take the ' away in the first query I get SQLITE_ERROR with the message 'No such column: Test01.db' if that helps? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brown, Daniel Sent: Thursday, August 14, 2008 4:05 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Loading a existing database 100% into memory Sorry I should have said, I'm using these calls in C++ on windows. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Thursday, August 14, 2008 1:45 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Loading a existing database 100% into memory On Thu, 14 Aug 2008, D. Richard Hipp wrote: > On Aug 14, 2008, at 4:18 PM, Brown, Daniel wrote: > >> Hello Stefan, >> >> I'm trying to use the code snippet you suggested but when I try to >> query >> the master table of the attached database I get and error with the >> following message: >> "SQL error: no such table: 'test.sqlite_master'; >> >> I am running the following queries: >> 1. "ATTACH DATABASE 'data\\test.db' AS test" - this seems to succeed >> (no >> error code). >> 2. "SELECT tbl_name FROM test.sqlite_master' WHERE type = 'table'" - >> this one is failing. >> > > Works when I try it. > > But I'm not running on windows. Do you really need two backslashes in > the filename? Should there be just a single backslash? You do not > quote backslashes in SQL strings as you do in C. > > But I'm not running on windows. Do you really need two backslashes in > the filename? Should there be just a single backslash? You do not Backslashes are illegal in a Windows filenames, except as folder delimiters, so "data\\test.db" is not a valid Windows filename. Hence, if this is on Windows, the ATTACH must be failing. It must be 'data\test.db'. Chris ___ 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Grabbing a record from an "in-use" database via a second process...
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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Grabbing a record from an "in-use" database via a second process...
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 now... ;^) I have one specific situation where I need to retrieve a single BLOB record from another database in the pool (not the one that's currently checked out). In fact, this other database could be checked out to (and be in use by) another individual when I need the record. So, my question. Is it safe for me to blindly open the other database and grab the record I need? This would only be a SELECT operation, and would never actually write anything to the other database file. If that's deemed unsafe, could I simply make a temporary copy of the other database (literally by copying the db file itself), grab the record from there, and remove the copy? In case it's important, the application will be running on WinXP... Thanks for any advice. Jeff ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] implementing a vfs
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 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 Snippet function on two column MATCHes
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 same problem as I did: > > http://www.nabble.com/Fts3-and-JOIN-sometimes-results-in-inability-to-use-MATCH-operator-td18851478.html > > Unfortunately there is no solution right now. I've discussed that on > the development mailing list as well where I have been confirmed this > is a bug. I have also opened a trac ticket about it: > > http://www.sqlite.org/cvstrac/tktview?tn=3281,3 > > There is a workaround, which is to used nested queries instead of > joins (i.e. "where x in (select ...)" instead of "join"). I've met the > Snippet function for the first time in your mail and failed to find > any documentation about (mind to give me a pointer here? it looks > interesting) so I cannot convert your example query but I'm confident > it can be fixed this way. http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex The above is the only location known to me where Snippet is documented (other than inside the source code files perhaps). > > Alex. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 Snippet function on two column MATCHes
> 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: http://www.nabble.com/Fts3-and-JOIN-sometimes-results-in-inability-to-use-MATCH-operator-td18851478.html Unfortunately there is no solution right now. I've discussed that on the development mailing list as well where I have been confirmed this is a bug. I have also opened a trac ticket about it: http://www.sqlite.org/cvstrac/tktview?tn=3281,3 There is a workaround, which is to used nested queries instead of joins (i.e. "where x in (select ...)" instead of "join"). I've met the Snippet function for the first time in your mail and failed to find any documentation about (mind to give me a pointer here? it looks interesting) so I cannot convert your example query but I'm confident it can be fixed this way. Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS3 Snippet function on two column MATCHes
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 ( SELECT rowid, Snippet(fts_poems, '', '', '…') AS context FROM fts_poems WHERE poem MATCH ? ) b ON a.poem_id = b.rowid 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 -- SELECT poem_id, context FROM poems a JOIN ( SELECT rowid, Snippet(fts_poems, '', '', '…') AS context FROM fts_poems WHERE poem MATCH ? OR history MATCH ? ) b ON a.poem_id = b.rowid The Snippet query syntax seems wrong and awkward to me because the Snippet function requires the table name rather than the more intuitive (to me) column name on which the MATCH is being done. What am I doing wrong? -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE operator and ATTACH databases memory usage
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 are attached to a main database. I hope I can do it with GLOB. At the moment, the question is still unanswered, why LIKE consumes multiple times 2.5MB when applied to attached dabases. Daniel Ursprüngliche Nachricht Von: [EMAIL PROTECTED] Datum: 15.08.2008 16:30 An: "General Discussion of SQLite Database" Betreff: Re: [sqlite] LIKE operator and ATTACH databases memory usage 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 ?; // consumes <50kB RAM > > Example 2: > SELECT fs_rec FROM _job01.fs_main WHERE fs_itemtype=? AND fs_textid > LIKE ?; // consumes 2.5MB RAM (Note to mailing list readers: Daniel sent me a sample database by private email) The database is about 3MB in size and the example 2 query is probably doing something close to a full table scan. This causes most of the database to be loaded into cache. That will use about 2.5MB of RAM. The cache will flush itself automatically when you close the database connection or when the cache becomes stale. Depending on what you are storing in fs_textid and what your LIKE pattern is, you might get much better performance (and lower memory usage) if you use GLOB instead of LIKE and if you explicitly code the pattern rather than using the wildcard "?", and if you create a new index: CREATE INDEX newidx1 ON fs_main(fs_itemtype, fs_textid); See http://www.sqlite.org/optoverview.html#like_opt D. Richard Hipp [EMAIL PROTECTED] ___ 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] LIKE operator and ATTACH databases memory usage
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 statements because there is no more RAM. On the desktop pc side, all statements are successful, but process viewer shows an increased amount of memory used by the application. I would be happy if the problem is in our application, but I wasn't able to find anything yet. Why does the statement with LIKE (Example 2) consume the memory and the statement without LIKE (Example3) does not consume the memory on a ATTACH'ed database? Database schema: CREATE TABLE fs_main ( 'fs_recid' INTEGER PRIMARY KEY NOT NULL, 'fs_itemtype' INTEGER, 'fs_textid' TEXT, 'fs_flag1' INTEGER, 'fs_object' BLOB ); The size of a single record is typically 100 bytes. Please let me know if you didnt get the example database I sent directly to [EMAIL PROTECTED] Regards Daniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generic speed testing
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 Selects|1.61|10077696|6259.0K Rows/Second 3|Trivial Updates|105.89|10077696|95.0K Rows/Second 4|Trivial Deletes|28.31|10077696|356.0K Rows/Second 5|Insert with calculations|0.0|1| 6|Updates with calculations and longer rows|0.0|1| Not sure why the last two results didn't print with "Rows/Second" column. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE operator and ATTACH databases memory usage
> 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 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE operator and ATTACH databases memory usage
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 ?; // consumes <50kB RAM > > Example 2: > SELECT fs_rec FROM _job01.fs_main WHERE fs_itemtype=? AND fs_textid > LIKE ?; // consumes 2.5MB RAM (Note to mailing list readers: Daniel sent me a sample database by private email) The database is about 3MB in size and the example 2 query is probably doing something close to a full table scan. This causes most of the database to be loaded into cache. That will use about 2.5MB of RAM. The cache will flush itself automatically when you close the database connection or when the cache becomes stale. Depending on what you are storing in fs_textid and what your LIKE pattern is, you might get much better performance (and lower memory usage) if you use GLOB instead of LIKE and if you explicitly code the pattern rather than using the wildcard "?", and if you create a new index: CREATE INDEX newidx1 ON fs_main(fs_itemtype, fs_textid); See http://www.sqlite.org/optoverview.html#like_opt D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE operator and ATTACH databases memory usage
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 is > allocated once per attached database and used for caching reasons? > Memory leaks in SQLite are uncommon. Especially 2.5MB memory leaks. See http://www.sqlite.org/malloc.html#testing Are you sure that the memory is not freed? Calling free() does not normally return memory to the operating system so just because the process memory usage went up does *not* mean that the memory is still in use. It might just mean that the memory is being held by the malloc()/free() for possible reuse later. What does the sqlite3_memory_used() interface tell you? What about sqlite3_memory_highwater()? Why does the first query require 2.5MB of memory? I don't know. That would depend on your schema and the content of the database. Perhaps it is going to cache. Perhaps something else. If one of the rows in your table contains a 2.5MB blob, that would explain where the memory is going, would it not? We will need quite a bit more information from you if we are to help you debug your problem. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generic speed testing
Ubuntu 7.10, SQLite 3.5.9 (debugging turned on), Intel Pentium E2140 (1.60GHz) Dual Core, 2Gb RAM, 7200RPM Drive SQL error near line 111: no such table: TEST1 SQL error near line 126: no such table: TEST1 0|performance.txt,v 1.0|1.0|0|0.0K Rows/Second 1|Trivial Inserts|21.56|10077696|468.0K Rows/Second 2|Trivial Selects|1.43|10077696|7037.0K Rows/Second 3|Trivial Updates|113.97|10077696|88.0K Rows/Second 4|Trivial Deletes|33.59|10077696|300.0K Rows/Second 5|Insert with calculations|0.0|1|2.0K Rows/Second 6|Updates with calculations and longer rows|0.0|1|2.0K Rows/Second ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE operator and ATTACH databases memory usage
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 is allocated once per attached database and used for caching reasons? Attached… oh sorry, I meant: “a few databases are attached with the ATTACH command to the main in-memory database”. Please let me know if you really need the database file. I can see how the memory usage of my application increases by 2.5MB as soon as I call sqlite3_step(). Database and parameters: Records: Typically 20’000 records Values for ?: fs_itemtype is an integer between 0 and 100 Values for ?: fs_textid is a string with length between 0 and 16 characters Indexes: On fs_textid Indexes: There is another index on fs_itemtype but is not used here Environment: SQLite version: 3.5.9 (we will release our product very soon) Operation system: Windows XP and WinCE (problem exists on both platforms) Compiled: Ourselves with Visual Studio 2005 (no changes to source code) Regards Daniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
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 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] LIKE operator and ATTACH databases memory usage
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 that SQLite is "consuming" 2.5MB? What version of SQLite are you running? What operating system? Did you compile it yourself or are using using a pre-build binary? What values are us using for the "?" parameters in your queries? > > > 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 fs_itemtype=? AND fs_textid > LIKE ?; // consumes 2.5MB RAM > SELECT fs_rec FROM _job02.fs_main WHERE fs_itemtype=? AND fs_textid > LIKE ?; // consumes 2.5MB RAM > SELECT fs_rec FROM _job03.fs_main WHERE fs_itemtype=? AND fs_textid > LIKE ?; // consumes 2.5MB RAM > SELECT fs_rec FROM _job04.fs_main WHERE fs_itemtype=? AND fs_textid > LIKE ?; // consumes 2.5MB RAM > > Example 3: > SELECT fs_rec FROM _job01.fs_main WHERE fs_itemtype=? AND > fs_textid=?; // consumes <1KB RAM > > Memory is consumed immediately after the first sqlite3_step() is > called. > > The PRAGMA cache_size is set to 2000 on the in-memory main database. > > Regards > Daniel > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] LIKE operator and ATTACH databases memory usage
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 fs_itemtype=? AND fs_textid LIKE ?; // consumes 2.5MB RAM SELECT fs_rec FROM _job02.fs_main WHERE fs_itemtype=? AND fs_textid LIKE ?; // consumes 2.5MB RAM SELECT fs_rec FROM _job03.fs_main WHERE fs_itemtype=? AND fs_textid LIKE ?; // consumes 2.5MB RAM SELECT fs_rec FROM _job04.fs_main WHERE fs_itemtype=? AND fs_textid LIKE ?; // consumes 2.5MB RAM Example 3: SELECT fs_rec FROM _job01.fs_main WHERE fs_itemtype=? AND fs_textid=?; // consumes <1KB RAM Memory is consumed immediately after the first sqlite3_step() is called. The PRAGMA cache_size is set to 2000 on the in-memory main database. Regards Daniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
"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, > ExternalID > INTEGER, Value ); > INSERT INTO "test_table" VALUES(1007,1,37,'-5'); > INSERT INTO "test_table" VALUES(1044,4,37,'-10'); > INSERT INTO "test_table" VALUES(1081,2,37,'-20'); > INSERT INTO "test_table" VALUES(1118,3,37,'-1'); > INSERT INTO "test_table" VALUES(1155,5,37,'-7'); > COMMIT; > sqlite> > > And here's the output: > > sqlite> select * from test_table; > 1007|1|37|-5 > 1044|4|37|-10 > 1081|2|37|-20 > 1118|3|37|-1 > 1155|5|37|-7 > sqlite> select min(Value) from test_table; > -1 > sqlite> select max(Value) from test_table; > -7 Well, '-7' comes lexicographically after '-1', no surprise here. By the same token, '5' would be greater than '10', since you insist on storing and comparing them as strings. Try select min(cast(Value as integer)), max(cast(Value as integer)) from test_table; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
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, ...> Value ); sqlite> INSERT INTO "test_table" VALUES(1007,1,37,'-5'); sqlite> INSERT INTO "test_table" VALUES(1044,4,37,'-10'); sqlite> INSERT INTO "test_table" VALUES(1081,2,37,'-20'); sqlite> INSERT INTO "test_table" VALUES(1118,3,37,'-1'); sqlite> INSERT INTO "test_table" VALUES(1155,5,37,'-7'); sqlite> INSERT INTO "test_table" VALUES( 2044,4,37,'fred'); sqlite> INSERT INTO "test_table" VALUES( 3044,4,37,'bill'); sqlite> sqlite> create view test_view as ...> select ID, ExternalID2, ExternalID, cast( value as integer ) as Value ...> from test_table where cast( Value as text)=cast(Value as integer); sqlite> sqlite> select * from test_view; 1007|1|37|-5 1044|4|37|-10 1081|2|37|-20 1118|3|37|-1 1155|5|37|-7 sqlite> sqlite> select min( Value ) from test_view; -20 sqlite> select max( Value ) from test_view; -1 Rgds, Simon 2008/8/15 Dennis Volodomanov <[EMAIL PROTECTED]>: > >> 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 this, > then I guess I'll do it. Or if replacing the collation sequence is not > too hard, I'd rather go that route. > > Thank you, > > Dennis > > ___ > 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] SQL statement to get min/max values
> 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 this, then I guess I'll do it. Or if replacing the collation sequence is not too hard, I'd rather go that route. Thank you, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
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); sqlite> INSERT INTO "test_table" VALUES(1007,1,37,'-5'); sqlite> INSERT INTO "test_table" VALUES(1044,4,37,'-10'); sqlite> INSERT INTO "test_table" VALUES(1081,2,37,'-20'); sqlite> INSERT INTO "test_table" VALUES(2081,2,37,'fred'); sqlite> INSERT INTO "test_table" VALUES(3081,2,37,'bill'); sqlite> INSERT INTO "test_table" VALUES(1118,3,37,'-1'); sqlite> INSERT INTO "test_table" VALUES(1155,5,37,'-7'); sqlite> COMMIT; sqlite> sqlite> sqlite> sqlite> select max( value ) from test_table; fred sqlite> select min( value ) from test_table; -20 sqlite> select *, typeof( value ) from test_table; 1007|1|37|-5|integer 1044|4|37|-10|integer 1081|2|37|-20|integer 1118|3|37|-1|integer 1155|5|37|-7|integer 2081|2|37|fred|text 3081|2|37|bill|text sqlite> select max( value ) from test_table where typeof( value ) = 'integer'; -1 sqlite> Rgds, Simon 2008/8/15 Dennis Volodomanov <[EMAIL PROTECTED]>: > >> 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 as INTEGER, because it > contains strings as well (of course they're not selected to get min/max > values). Would I need to write my own min/max functions to handle this? > > Thanks, > > Dennis > > > P.S. Sorry for the message I sent to you personally - the Reply-to > button is too smart :) > > ___ > 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] SQL statement to get min/max values
> 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 as INTEGER, because it contains strings as well (of course they're not selected to get min/max values). Would I need to write my own min/max functions to handle this? Thanks, Dennis P.S. Sorry for the message I sent to you personally - the Reply-to button is too smart :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
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" VALUES(1007,1,37,'-5'); INSERT INTO "test_table" VALUES(1044,4,37,'-10'); INSERT INTO "test_table" VALUES(1081,2,37,'-20'); INSERT INTO "test_table" VALUES(1118,3,37,'-1'); INSERT INTO "test_table" VALUES(1155,5,37,'-7'); COMMIT; sqlite> And here's the output: sqlite> select * from test_table; 1007|1|37|-5 1044|4|37|-10 1081|2|37|-20 1118|3|37|-1 1155|5|37|-7 sqlite> select min(Value) from test_table; -1 sqlite> select max(Value) from test_table; -7 I'm confused :) Thanks, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 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; -- Multumesc, Mihai Limbasan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL statement to get min/max values
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 "test_table" VALUES(1044,4,37,'-10'); INSERT INTO "test_table" VALUES(1081,2,37,'-20'); INSERT INTO "test_table" VALUES(1118,3,37,'-1'); INSERT INTO "test_table" VALUES(1155,5,37,'-7'); COMMIT; sqlite> And here's the output: sqlite> select * from test_table; 1007|1|37|-5 1044|4|37|-10 1081|2|37|-20 1118|3|37|-1 1155|5|37|-7 sqlite> select min(Value) from test_table; -1 sqlite> select max(Value) from test_table; -7 I'm confused :) Thanks, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] returning multiple rows from custom functions
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 Pechnikov <[EMAIL PROTECTED]>wrote: > 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 2008 02:16:56 Michael Janis написал(а): > > The approach is to construct (and index) a simple one-dimensional table > > with integers ranging from 1 to some arbitrarily large number (larger > than > > the number of rows in your largest table, for example) and then use this > as > > a type of "iterator" across the data. > > > > ... > > > sqlite>create table iterator(i int); > > > > sqlite>insert into iterator values (1); > > > > sqlite>insert into iterator values (2); > > > > sqlite>insert into iterator values (3); > > > > sqlite>insert into iterator values (4); > > > > sqlite>insert into iterator values (5); > > > > sqlite>insert into iterator values (6); > > > > sqlite>insert into iterator values (7); > > > > sqlite>insert into iterator values (8); > > > > sqlite>insert into iterator values (9); > > > > sqlite>create index iter on iterator(i); > > > > Best regards, Alexey. > -- ** Michael Janis [EMAIL PROTECTED] ** ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] returning multiple rows from custom functions
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 2008 02:16:56 Michael Janis написал(а): > The approach is to construct (and index) a simple one-dimensional table > with integers ranging from 1 to some arbitrarily large number (larger than > the number of rows in your largest table, for example) and then use this as > a type of "iterator" across the data. > ... > sqlite>create table iterator(i int); > > sqlite>insert into iterator values (1); > > sqlite>insert into iterator values (2); > > sqlite>insert into iterator values (3); > > sqlite>insert into iterator values (4); > > sqlite>insert into iterator values (5); > > sqlite>insert into iterator values (6); > > sqlite>insert into iterator values (7); > > sqlite>insert into iterator values (8); > > sqlite>insert into iterator values (9); > > sqlite>create index iter on iterator(i); Best regards, Alexey. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
