[sqlite] Re: sqite2.8 performance snag when filesize growsmore than 70MB on a 256MB ram
On Mon, Aug 22, 2005 at 08:44:07AM +0800, Damian Slee wrote: > >>In order to implement the ORDER BY clause, SQLite reads the entire result > >>set into memory and sorts it there. When your result set gets very large > >>(13000 rows) and each row uses in excess of 1KB or memory,this is > >>apparently causing your machine to thrash. > > > If an index exists on the same column that Order By is used, does this still > result in reading the entire result set? Yes. It seems you have to trust the database, and avoid the 'order by' when you want the result to be sorted according the primary key. Actually I think it is a small bottleneck which should be solved, and it will really make sqlite the best database solution. I had switch to mysql (i am checking pgsql too), and it seems to be working fine. The problem seems to be that the whole row is loaded, which is not acceptable since there are even scenarios where you store entire images into database. The row size should not ever be an issue when dealing with the table. Thanks.
[sqlite] SQLite from a lib?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I'm working out an assistive-type program that allows applications developers to offer help to the user reactive to certain conditions. This program tracks what messages are sent, reacted to, followed, dismissed, etc. I want to store this information using an sqlite database, as a sort of configuration file. The library is general purpose. Basically, any application can use it. When it starts, the application has to call int somosis_spawn_thread(pthread_t *restrict thread, char *configfile, void (*handler)(int,int)); to spawn a thread with a given config file and handler function. The handler will handle responses to messages, if needed, in this separate thread; this will be a point when the config file is updated. This thread will also get application-level configuration data from the assistant's interface and store the changes in the same SQLite database. I'm worried here. Other threads will be sending and killing messages; I can structure this so that this doesn't need to alter the SQLite database by simply reacting to the data sent back about the message. In the end, if I do it just right, there should be one thread handling the SQLite database. Problem here. Programs like to fork() :( Should I simply not be using SQLite; or is there a way for me to have the thread immediately die in the child on fork()? - -- All content of all messages exchanged herein are left in the Public Domain, unless otherwise explicitly stated. Creative brains are a valuable, limited resource. They shouldn't be wasted on re-inventing the wheel when there are so many fascinating new problems waiting out there. -- Eric Steven Raymond -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFDCTZyhDd4aOud5P8RAjgCAJ9OweJR33e3ZTF34TpA1kToLi5pUwCeNMyU vno00sqs4f2JeTaD7cik8ow= =ws8J -END PGP SIGNATURE-
RE: [sqlite] Re: sqite2.8 performance snag when filesize growsmore than 70MB on a 256MB ram
>>In order to implement the ORDER BY clause, SQLite reads the entire result set >>into memory and sorts it there. When your result set gets very large (13000 >>rows) and each row uses in excess of 1KB or memory,this is apparently causing >>your machine to thrash. If an index exists on the same column that Order By is used, does this still result in reading the entire result set? -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.12/75 - Release Date: 17/08/2005
Re: [sqlite] Version 3.2.3
Khamis Abuelkomboz wrote: D. Richard Hipp wrote: Version 3.2.3 of SQLite is now available on the website http://www.sqlite.org/ In addition to fixing a lot of minor bugs, this release adds a number of important new enhancements. Most of the enhancements are centered around the much-improved query optimizer, but there are some unrelated additions. * CAST operators are now supported. * The order of tables in a join is adjusted automatically as needed to make better use of indices. * Some OR-connected terms in the WHERE clause can now make use of indices. * BETWEEN operators in the WHERE clause are able to use indices. * A new pragma "case_sensitive_like" turns case sensitivity for the LIKE operator on and off. * The GLOB operator can use indices if its right-hand side consists of text followed by a wildcard. The LIKE operator does this too if case sensitivity is turned on. * There is a new ANALYZE command that gather statistics on indices and helps the optimizer make a better choice when it needs to choose between two or more indices. As always, please let me know if you find any problems. Hi D. Richard thank you for the great work. I used to use 3.0.8 and upgraded to this current release. It seems that with this release some sql statements get broken. the broken sql statements (selects) simply don't return any value, where they should. I would make an example, if I find a reproducable case. khamis this seems to have to do with ORDER BY clause. I get results when the clause is omitted. However I got a case where I omitted the ORDER BY clause and the database went to use memory until I killed the process by virtual size of 2GB. khamis -- Try Code-Navigator on http://www.codenav.com a source code navigating, analysis and developing tool. It supports following languages: * C/C++ * Java * .NET (including CSharp, VB.Net and other .NET components) * Classic Visual Basic * PHP, HTML, XML, ASP, CSS * Tcl/Tk, * Perl * Python * SQL, * m4 Preprocessor * Cobol
Re: [sqlite] Version 3.2.3
D. Richard Hipp wrote: Version 3.2.3 of SQLite is now available on the website http://www.sqlite.org/ In addition to fixing a lot of minor bugs, this release adds a number of important new enhancements. Most of the enhancements are centered around the much-improved query optimizer, but there are some unrelated additions. * CAST operators are now supported. * The order of tables in a join is adjusted automatically as needed to make better use of indices. * Some OR-connected terms in the WHERE clause can now make use of indices. * BETWEEN operators in the WHERE clause are able to use indices. * A new pragma "case_sensitive_like" turns case sensitivity for the LIKE operator on and off. * The GLOB operator can use indices if its right-hand side consists of text followed by a wildcard. The LIKE operator does this too if case sensitivity is turned on. * There is a new ANALYZE command that gather statistics on indices and helps the optimizer make a better choice when it needs to choose between two or more indices. As always, please let me know if you find any problems. Hi D. Richard thank you for the great work. I used to use 3.0.8 and upgraded to this current release. It seems that with this release some sql statements get broken. the broken sql statements (selects) simply don't return any value, where they should. I would make an example, if I find a reproducable case. khamis -- Try Code-Navigator on http://www.codenav.com a source code navigating, analysis and developing tool. It supports following languages: * C/C++ * Java * .NET (including CSharp, VB.Net and other .NET components) * Classic Visual Basic * PHP, HTML, XML, ASP, CSS * Tcl/Tk, * Perl * Python * SQL, * m4 Preprocessor * Cobol
Re: [sqlite] Can a trigger update more than one column?
On Sun, 21 Aug 2005, Mark de Vries wrote:
> > > I have tried two versions of the trigger:
> > >
> > > CREATE TRIGGER task_list_1
> > > AFTER INSERT ON task_list
> > > BEGIN
> > > UPDATE task_list
> > > SET insert_ts = datetime('now','localtime'),
> > >last_upd_ts = datetime('now','localtime')
> > > WHERE task_id = NEW.task_id;
> > > END;
> > >
> > > and, thinking that update might only be allowed to populate one column
> > > at a time,
> > >
> > > CREATE TRIGGER task_list_1
> > > AFTER INSERT ON task_list
> > > BEGIN
> > > UPDATE task_list SET insert_ts = datetime('now','localtime')
> > > WHERE
> > > task_id = NEW.task_id;
> > > UPDATE task_list SET last_upd_ts = datetime('now','localtime')
> > > WHERE
> > > task_id = NEW.task_id;
> > > END;
> > >
> > >
> >
> > As far as I can see, both TRIGGERs above will only happen after INSERT,
> > and therefore, will insert exactly the same time in both insert_ts and
> > last_upd_ts defeating the intended purpose of the two fields.
>
> That's one of the problems I ran into...
>
> > Perhaps you want to create one AFTER INSERT and one AFTER UPDATE
> > TRIGGER.
>
> No, that does not work... Think; the AFTER INSERT trigger does what?
> Which triggers? So you still end up with both collumns set after the just
> the initial insert.
Hmm... just thinking; are you guarenteed to get the same time in this
case? Does CURRENT_TIMESTAMP get the real 'current timestamp', or the time
the transaction started?
If it is the latter then you could tell it was not modified if
insert_ts=last_upd_ts (Provided it's impossible for your app to insert &
update in the same second.) If it's always the real current time you
couldn't even be sure of that...
Rgds,
Mark
Re: [sqlite] Can a trigger update more than one column?
On Sun, 21 Aug 2005, Puneet Kishor wrote:
>
> On Aug 21, 2005, at 9:20 AM, <[EMAIL PROTECTED]> wrote:
>
> > Hi,
> >
> > There was some recent discussion about timestamp triggers, something I
> > have been trying to get working on my own. Specifically, I have two
> > fields, insert_ts and last_upd_ts, which I would like to be the insert
> > time and last update time, respectively. However, when I try to write
> > a trigger to populate these fields, only last_upd_ts gets populated.
>
> can someone explain to me why folks try to accomplish the above using
> TRIGGERs when the insert time can be set so easily by simply defining
> the column as
>
> insert_ts DATETIME DEFAULT CURRENT_TIMESTAMP
>
> and last_upd_ts (or its equivalent) can set in the application logic
> and updated during an UPDATE.
Because you depend on the application or person to do 'the right thing',
and/or because I don't want to want to write all those update statements
in all those apps & scripts that access the DB... MUCH easier to just do
it once in the DB
> That said...
>
> >
> > I have tried two versions of the trigger:
> >
> > CREATE TRIGGER task_list_1
> > AFTER INSERT ON task_list
> > BEGIN
> > UPDATE task_list
> > SET insert_ts = datetime('now','localtime'),
> >last_upd_ts = datetime('now','localtime')
> > WHERE task_id = NEW.task_id;
> > END;
> >
> > and, thinking that update might only be allowed to populate one column
> > at a time,
> >
> > CREATE TRIGGER task_list_1
> > AFTER INSERT ON task_list
> > BEGIN
> > UPDATE task_list SET insert_ts = datetime('now','localtime')
> > WHERE
> > task_id = NEW.task_id;
> > UPDATE task_list SET last_upd_ts = datetime('now','localtime')
> > WHERE
> > task_id = NEW.task_id;
> > END;
> >
> >
>
> As far as I can see, both TRIGGERs above will only happen after INSERT,
> and therefore, will insert exactly the same time in both insert_ts and
> last_upd_ts defeating the intended purpose of the two fields.
That's one of the problems I ran into...
> Perhaps you want to create one AFTER INSERT and one AFTER UPDATE
> TRIGGER.
No, that does not work... Think; the AFTER INSERT trigger does what?
Which triggers? So you still end up with both collumns set after the just
the initial insert.
You can get a little facy with triggers with WHERE clauses to (try to)
fix that, but then complexity quickly rises to the point where putting it
into the application level is easier.
And if that's not an option because you can't, or don't want, to trust the
applications/scripts/users then sqlite prolly isn't the right tool for the
job
This is the exact same question I had. And I realized that this is just a
limitation of sqlite. No problem, I will just have to do things a little
different than I'm used to. I my case there is no need to be absolutely
'secure' about the value in the these fields. And the pros of using sqlite
for the project I'm working on outweigh these cons.
Thnx to all who responded to my version of this question.
Regards,
Mark
Re: [sqlite] table or record size without reading the entire table/record?
On Sun, 2005-08-21 at 14:26 -0400, Kervin L. Pierre wrote: > > Note: This will only work if the "size" column > > comes before the "data" column in the table > > definition. > > Thanks for the heads up. Out of curiosity, > why does the size column have to come before > the data column? > When reading information from the disk, SQLite starts at the beginning of a row and reads till it finds the information it needs. If the size comes first, it will therefore stop after reading the size and ignore the data. If the data comes first, it will have to read all of the data in order to determine the size, which defeats the purpose of putting the size column in the table in the first place. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Version 3.2.3
Version 3.2.3 of SQLite is now available on the website http://www.sqlite.org/ In addition to fixing a lot of minor bugs, this release adds a number of important new enhancements. Most of the enhancements are centered around the much-improved query optimizer, but there are some unrelated additions. * CAST operators are now supported. * The order of tables in a join is adjusted automatically as needed to make better use of indices. * Some OR-connected terms in the WHERE clause can now make use of indices. * BETWEEN operators in the WHERE clause are able to use indices. * A new pragma "case_sensitive_like" turns case sensitivity for the LIKE operator on and off. * The GLOB operator can use indices if its right-hand side consists of text followed by a wildcard. The LIKE operator does this too if case sensitivity is turned on. * There is a new ANALYZE command that gather statistics on indices and helps the optimizer make a better choice when it needs to choose between two or more indices. As always, please let me know if you find any problems.
Re: [sqlite] table or record size without reading the entire table/record?
D. Richard Hipp wrote: Are you trying to estimate the size of a table, or a single row in that table? Your words say the table but the context suggest you really want the size of a row. I need the table. But I thought if I could have the row, calculating the total for the table wouldn't be difficult. Then when you need to know the size, just do a query for the size. Thanks. That was my backup plan if there was no other way. Note: This will only work if the "size" column comes before the "data" column in the table definition. Thanks for the heads up. Out of curiosity, why does the size column have to come before the data column? Regards, Kervin
Re: [sqlite] table or record size without reading the entire table/record?
On Sun, 2005-08-21 at 13:43 -0400, Kervin L. Pierre wrote: > Hello, > > I am trying to figure out a way to return > the actual or approximate table size in a > database. > > Basically, in my application a table relates > to an object and I need to calculate the > approximate size of an object. > > There is the sqlite3_column_bytes(), but I > understand that that function returns the > value into memory before counting the bytes. > > Is there another way of estimating a table's > size on disk? > Are you trying to estimate the size of a table, or a single row in that table? Your words say the table but the context suggest you really want the size of a row. No, there is no way to determine the size of a row in SQLite without loading the whole row into memory. But if you really need to know the row size before loading it, you can store that value in a separate column in the table. CREATE TABLE object( size INT, data BLOB ); Then when you need to know the size, just do a query for the size. Note: This will only work if the "size" column comes before the "data" column in the table definition. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] table or record size without reading the entire table/record?
Hello, I am trying to figure out a way to return the actual or approximate table size in a database. Basically, in my application a table relates to an object and I need to calculate the approximate size of an object. There is the sqlite3_column_bytes(), but I understand that that function returns the value into memory before counting the bytes. Is there another way of estimating a table's size on disk? Thanks, Kervin
Re: [sqlite] Can a trigger update more than one column?
> Both insert_ts and last_upd_ts get updated when I try this. > What version of SQLite are you using? What platform? What > language binding? I'm running sqlite 3.2.2 on Win XP, and I'm running things inside sqlite itself, not another language. Thanks for the help! Bill
[sqlite] ANN: New version of SQLite Analyzer
Hello, I'm pleased to announce the new beta of SQLite Analyzer - New "Data Editor" allow to use "WHERE..., LIMIT.. OFFSET" clause. - Ability to view BLOB column as picture and as text. - Ability to load BLOB data from file or save to file and edit BLOB data. You can download new version from our web site: http://www.kraslabs.com/files/sqlanlz3034.exe -- Best regards Sergey Startsev
Re: [sqlite] Can a trigger update more than one column?
On Sun, 2005-08-21 at 10:20 -0400, [EMAIL PROTECTED] wrote:
> There was some recent discussion about timestamp triggers, something I
> have been trying to get working on my own. Specifically, I have two
> fields, insert_ts and last_upd_ts, which I would like to be the insert
> time and last update time, respectively. However, when I try to write
> a trigger to populate these fields, only last_upd_ts gets populated.
>
> I have tried two versions of the trigger:
>
> CREATE TRIGGER task_list_1
> AFTER INSERT ON task_list
> BEGIN
> UPDATE task_list
> SET insert_ts = datetime('now','localtime'),
>last_upd_ts = datetime('now','localtime')
> WHERE task_id = NEW.task_id;
> END;
>
Both insert_ts and last_upd_ts get updated when I try this.
What version of SQLite are you using? What platform? What
language binding?
--
D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Can a trigger update more than one column?
On Aug 21, 2005, at 9:55 AM, <[EMAIL PROTECTED]> wrote: can someone explain to me why folks try to accomplish the above using TRIGGERs when the insert time can be set so easily by simply defining the column as insert_ts DATETIME DEFAULT CURRENT_TIMESTAMP and last_upd_ts (or its equivalent) can set in the application logic and updated during an UPDATE. In practice, I have found it to be more effective to keep data-related logic in sql rather than in any external application. Triggers are great for enforcing these types of rules. The problem with using table defaults is that it does not enforce any type of rule, sql will accept any value I give it, like '2909-12-25', for the insert_ts field, rather than forcing it to be the actual time of insertion. well, while true, neither you nor your application actually will be setting the value of the insert_ts at all. It is the database that will be doing that for you. By defining a column as foo DATETIME DEFAULT CURRENT_TIMESTAMP and then _not_ explicitly INSERTing any value, you have automagically stored your logic for the insert_ts right in the database by letting the db do it for you. The DATETIME column type, and the DEFAULT value facility are provided for a purpose. Use them. If the database erroneously starts inserting typos like '2909-11-31' by itself then the SQLite team have bigger problems on their hand. Now you are left to worry about only the last_upd_ts. You can now update that column either with an AFTER UPDATE TRIGGER or within the application. While I understand the wisdom of pushing as much data-related logic in the database, you have the application layer for some reason after all. You are, after all, doing an UPDATE from the application, which, you want, then to kick-start the TRIGGER. Hence, simply doing a UPDATE table SET last_upd_ts = '' WHERE condition is not that revolutionary. -- Puneet Kishor
Re: [sqlite] Can a trigger update more than one column?
> can someone explain to me why folks try to accomplish the above using > TRIGGERs when the insert time can be set so easily by simply defining > the column as > > insert_ts DATETIME DEFAULT CURRENT_TIMESTAMP > > and last_upd_ts (or its equivalent) can set in the application logic > and updated during an UPDATE. In practice, I have found it to be more effective to keep data-related logic in sql rather than in any external application. Triggers are great for enforcing these types of rules. The problem with using table defaults is that it does not enforce any type of rule, sql will accept any value I give it, like '2909-12-25', for the insert_ts field, rather than forcing it to be the actual time of insertion. > As far as I can see, both TRIGGERs above will only happen after INSERT, > and therefore, will insert exactly the same time in both insert_ts and > last_upd_ts defeating the intended purpose of the two fields. That's what I would expect them to do. The problem is that no time gets inserted into insert_ts. > Perhaps you want to create one AFTER INSERT and one AFTER UPDATE > TRIGGER. I have an AFTER UPDATE trigger, which doesn't do me much good if the insert trigger isn't working. Bill
Re: [sqlite] Can a trigger update more than one column?
On Aug 21, 2005, at 9:20 AM, <[EMAIL PROTECTED]> wrote:
Hi,
There was some recent discussion about timestamp triggers, something I
have been trying to get working on my own. Specifically, I have two
fields, insert_ts and last_upd_ts, which I would like to be the insert
time and last update time, respectively. However, when I try to write
a trigger to populate these fields, only last_upd_ts gets populated.
can someone explain to me why folks try to accomplish the above using
TRIGGERs when the insert time can be set so easily by simply defining
the column as
insert_ts DATETIME DEFAULT CURRENT_TIMESTAMP
and last_upd_ts (or its equivalent) can set in the application logic
and updated during an UPDATE.
That said...
I have tried two versions of the trigger:
CREATE TRIGGER task_list_1
AFTER INSERT ON task_list
BEGIN
UPDATE task_list
SET insert_ts = datetime('now','localtime'),
last_upd_ts = datetime('now','localtime')
WHERE task_id = NEW.task_id;
END;
and, thinking that update might only be allowed to populate one column
at a time,
CREATE TRIGGER task_list_1
AFTER INSERT ON task_list
BEGIN
UPDATE task_list SET insert_ts = datetime('now','localtime')
WHERE
task_id = NEW.task_id;
UPDATE task_list SET last_upd_ts = datetime('now','localtime')
WHERE
task_id = NEW.task_id;
END;
As far as I can see, both TRIGGERs above will only happen after INSERT,
and therefore, will insert exactly the same time in both insert_ts and
last_upd_ts defeating the intended purpose of the two fields.
Perhaps you want to create one AFTER INSERT and one AFTER UPDATE
TRIGGER.
Although why, I still don't understand.
--
Puneet Kishor
[sqlite] Can a trigger update more than one column?
Hi,
There was some recent discussion about timestamp triggers, something I
have been trying to get working on my own. Specifically, I have two
fields, insert_ts and last_upd_ts, which I would like to be the insert
time and last update time, respectively. However, when I try to write
a trigger to populate these fields, only last_upd_ts gets populated.
I have tried two versions of the trigger:
CREATE TRIGGER task_list_1
AFTER INSERT ON task_list
BEGIN
UPDATE task_list
SET insert_ts = datetime('now','localtime'),
last_upd_ts = datetime('now','localtime')
WHERE task_id = NEW.task_id;
END;
and, thinking that update might only be allowed to populate one column
at a time,
CREATE TRIGGER task_list_1
AFTER INSERT ON task_list
BEGIN
UPDATE task_list SET insert_ts = datetime('now','localtime')
WHERE
task_id = NEW.task_id;
UPDATE task_list SET last_upd_ts = datetime('now','localtime')
WHERE
task_id = NEW.task_id;
END;
Do I have to write a seperate trigger for each column that I want to
update? That seems inefficient to me, so hopefully there is another
way.
Thanks for any help,
Bill
Re: [sqlite] Re: sqite2.8 performance snag when filesize grows more than 70MB on a 256MB ram
D. Richard Hipp wrote: I am running on linux, and I will need this to scale to at least 200,000 rows If you upgrade to the vary latest code in CVS (version 3.2.2+) and you create an index like this: CREATE INDEX idx ON table(parent_name, name); Then the query above should be very efficient. You can try it creating the index above with version 2.x. It might work. I do not remember how smart the optimizer was about using indexes to optimize sorting in version 2.x (that was so long ago.) If you really need to use 2.8, you could just create the index above, make sure that it is the *only* index on the table, then omit the ORDER BY clause all together. When the index above is used, things will come out sorted or by name just because of the way they work in SQLite. SQL does not guarantee that behavior so it probably will not work that way on other database engines, but it should work fine in SQLite. I'm actually experiencing similar problem with sqlite3.0, when tables are empty, select and modify operations are very fast, but when the table gets more data the speed actually breaks suddenly down. My experience, it doesn't matter, witch page size I use. Latest I tried with page number size of 32000 and page size of 8192, but got the same results. Tempspace is set to memory. To clarify what I mean, in my application the parsers scan files and produce cross-reference entries using inserts in two tables. In a project with about 3000 files, the first 500 files are done in about 15sec. the latest 500 files take about 90sec. Is this issue fixed in 3.3.2+ too? Does anyone have any idea, what could be wrong here? thanks khamis -- Try Code-Navigator on http://www.codenav.com a source code navigating, analysis and developing tool. It supports following languages: * C/C++ * Java * .NET (including CSharp, VB.Net and other .NET components) * Classic Visual Basic * PHP, HTML, XML, ASP, CSS * Tcl/Tk, * Perl * Python * SQL, * m4 Preprocessor * Cobol
Re: [sqlite] Why can i open a textfile?
O well, read-only? Then exclusive as well :) - Original Message - From: "Walter Meerschaert" <[EMAIL PROTECTED]> To: Sent: Sunday, August 21, 2005 2:04 AM Subject: Re: [sqlite] Why can i open a textfile? I agree, since that makes error/exception handling easier. On the subject of open(), I also would like it to have a read_only option, if that is possible. I am not even sure that a read-only state is tracked through the library, or if the writing attempts just fail with an file access error. Or is there already a way to open the database read only? Edwin Knoppert wrote: I haven't test on exactly 1kb file but larger and indeed an error is shown. May i stress again that the test should be during open() imo? And rather not using a 2nd function to examine the db.
