[sqlite] Best way of merging tables
hi, I have following situation: database2006: table 'myname': Columns: ID integer primary key, timestamp integer, x text, y text database2007: table 'myname': Columns: ID integer primary key, timestamp integer, x text, y text empty comvineddatabase: : table 'myname': Columns: ID integer primary key, timestamp integer, x text, y text I would like do have a combined database table 'myname': Columns: ID integer primary key, timestamp integer, x text, y text where I insert first the columns of database2006, and afterwards database2007. at the moment I do something like this: *attach: database2006:* select timestamp, x , y from database2006 and for eachrow in selectionresult insert into comvineddatabase (timestamp, x , y) values(?,?,?) *after that the same for database2006.* select timestamp, x , y from database2006 and for eachrow in selectionresult insert into comvineddatabase (timestamp, x , y) values(?,?,?) I was wondering if there is not a more effective way of doing that: maybe even within a single SQL statement? Thanks in advance W.Braun
Re: [sqlite] Best way of merging tables
On Mon, 31 Dec 2007 09:56:23 +0100, Mag. Wilhelm Braun [EMAIL PROTECTED] wrote: hi, I have following situation: database2006: table 'myname': Columns: ID integer primary key, timestamp integer, x text, y text database2007: table 'myname': Columns: ID integer primary key, timestamp integer, x text, y text empty comvineddatabase: : table 'myname': Columns: ID integer primary key, timestamp integer, x text, y text I would like do have a combined database table 'myname': Columns: ID integer primary key, timestamp integer, x text, y text where I insert first the columns of database2006, and afterwards database2007. at the moment I do something like this: *attach: database2006:* select timestamp, x , y from database2006 and for eachrow in selectionresult insert into comvineddatabase (timestamp, x , y) values(?,?,?) *after that the same for database2006.* select timestamp, x , y from database2006 and for eachrow in selectionresult insert into comvineddatabase (timestamp, x , y) values(?,?,?) I was wondering if there is not a more effective way of doing that: maybe even within a single SQL statement? Sure there is, using the INSERT INTO / SELECT syntax on http://www.sqlite.org/lang_insert.html There are several possibilities, here is an (unteste3d) example: (open comvineddatabase) (create tables as needed) ATTACH DATABASE 'database2006' AS d2006; INSERT INTO myname (timestamp, x, y) SELECT timestamp, x, y FROM d2006.myname; DETACH DATABASE d2006; ATTACH DATABASE 'database2007' AS d2007; INSERT INTO myname (timestamp, x, y) SELECT timestamp, x, y FROM d2007.myname; DETACH DATABASE d2007; If the table structures are exactly the same, the INSERT statement can even be shortened: INSERT INTO myname SELECT * FROM d2006.myname; etc. Thanks in advance W.Braun HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Best way of merging tables
Thanks a lot Kees Nuyt, greate help W.Braun Kees Nuyt wrote: On Mon, 31 Dec 2007 09:56:23 +0100, Mag. Wilhelm Braun [EMAIL PROTECTED] wrote: hi, I have following situation: database2006: table 'myname': Columns: ID integer primary key, timestamp integer, x text, y text database2007: table 'myname': Columns: ID integer primary key, timestamp integer, x text, y text empty comvineddatabase: : table 'myname': Columns: ID integer primary key, timestamp integer, x text, y text I would like do have a combined database table 'myname': Columns: ID integer primary key, timestamp integer, x text, y text where I insert first the columns of database2006, and afterwards database2007. at the moment I do something like this: *attach: database2006:* select timestamp, x , y from database2006 and for eachrow in selectionresult insert into comvineddatabase (timestamp, x , y) values(?,?,?) *after that the same for database2006.* select timestamp, x , y from database2006 and for eachrow in selectionresult insert into comvineddatabase (timestamp, x , y) values(?,?,?) I was wondering if there is not a more effective way of doing that: maybe even within a single SQL statement? Sure there is, using the INSERT INTO / SELECT syntax on http://www.sqlite.org/lang_insert.html There are several possibilities, here is an (unteste3d) example: (open comvineddatabase) (create tables as needed) ATTACH DATABASE 'database2006' AS d2006; INSERT INTO myname (timestamp, x, y) SELECT timestamp, x, y FROM d2006.myname; DETACH DATABASE d2006; ATTACH DATABASE 'database2007' AS d2007; INSERT INTO myname (timestamp, x, y) SELECT timestamp, x, y FROM d2007.myname; DETACH DATABASE d2007; If the table structures are exactly the same, the INSERT statement can even be shortened: INSERT INTO myname SELECT * FROM d2006.myname; etc. Thanks in advance W.Braun HTH - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Fastest way to check if new row or update existing one?
Did you try using INSERT OR REPLACE? Hugo Ferreira wrote: Hmmm... Would it be possible to make a trigger on a table such that if any update fails, it does an insert? If so, then one would only need to issue updates. On Dec 26, 2007 11:35 AM, Kees Nuyt [EMAIL PROTECTED] wrote: On Wed, 26 Dec 2007 10:17:43 +0200, Ion Silvestru [EMAIL PROTECTED] wrote: QUESTION: is there a better way to make this important decision? using Sqlite INSERT OR REPLACE may work for you. There is a problem with INSERT OR REPLACE in that REPLACE is not truly replace, but is delete + insert (existing row is deleted and new row is added), or I am wrong? It is a full replacement of the row, just as the word REPLACE (Take the place or moveinto the position of) suggests. Perhaps you are confused with UPDATE. REPLACE has been introduced to increase compatibility with other database engines, they all follow the same strategy. If I am correct, then a question to developers of SQLite: Is it difficult to change the behaviour of the REPLACE part of INSERT OR REPLACE to be the correct behaviour, row content is updated, and not deleted then inserted? What would be the difference? I guess your new row doesn't provide some of the column values of the existing row, and you want to keep some of those. In that case, SELECT / UPDATE is the only option. To change the behaviour of REPLACE into selective updating of columns SQLite would have to know which columns it would have to update and which not. It simply can't. A nice solution is found in Date: Tue, 24 Apr 2007 14:36:48 -0400 Subject: [sqlite] Re: INSERT OR REPLACE without new rowid , Message-ID: [EMAIL PROTECTED] : IT You can do IT IT UPDATE ... WHERE keyfield='xxx'; IT IT then use sqlite3_changes to see whether any update IT has in fact taken place, and run INSERT if not. IT IT Igor Tandetnik Thanks in advance and happy hollidays! HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Indexes not being used after INNER JOINS?
Hi everyone, I seem to be having a problem here with LEFT JOINS between tables and results of INNER JOINS. Take for example the following example (table definition is in the end): TABLE COUNT esparqueologico: 750 TABLE COUNT data: 3828 TABLE COUNT reftemporal: 3972 This query would take 6.7s to run (750 rows): select * from esparqueologico oe left join (data d cross join reftemporal r on d.reftemporal_id = r.id) x on oe.datacao_id = x.id However this takes virtually zero time (750 rows): select * from esparqueologico oe left join data d on oe.datacao_id = d.id And this takes 0.1s (3828 rows): select * from data d inner join reftemporal r on d.reftemporal_id = r.id It seems to me that indexes are lost in the first query. Here is the data definition I'm using: CREATE TABLE data ( id guid NOT NULL, reftemporal_id guid NOT NULL, PRIMARY KEY (id), FOREIGN KEY (reftemporal_id) REFERENCES reftemporal(id), ) CREATE TABLE reftemporal ( id guid NOT NULL, subtype varchar, PRIMARY KEY (id) ) CREATE TABLE esparqueologico ( id guid NOT NULL, datacao_id guid, PRIMARY KEY (id), FOREIGN KEY (datacao_id) REFERENCES data(id), ) CREATE INDEX idx_data_reftemporal_id ON data(reftemporal_id) CREATE INDEX idx_esparqueologico_datacao_id ON esparqueologico(datacao_id) Cheers, Hugo Ferreira
RE: [sqlite] Indexes not being used after INNER JOINS?
Hello Hugo, If you preceed a SELECT with the string EXPLAIN QUERY PLAN sqlite will make it clear which, if any, indices it would use when running the select. Sqlite, unlike some of the non-light databases, uses a maxium of one index per table per select, I believe. You may want to look at the ANALYZE command. You really do want a CROSS JOIN in the first case, is that right? I'm not sure if this is info you're looking for. Regards, and Happy New Year to all, Donald G. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] HAPPY NEW YEAR - and a question, of course: SegV on Update
Wishing all a joyous and prosperous 2008 - filled with happy coding and happy clients/customers/bosses/families. Cheers... Now a question. :) I have 3 different database handles running in the same thread. One of them is :memory:. I send an update statement to one and the program crashes. Of note: - SQLite is compiled into the program, I'm not using the dynamic lib. - it's compiled within an extern of a GNU c++ compiler on OSX. I mark my sqlite wrappers in extern. - I am not running multiple threads, but I do have threadsafe enabled via sqlite3_threadsafe(); Running my program from within GDB. Program received signal EXC_BAD_ACCESS, Could not access memory. Reason: KERN_INVALID_ADDRESS at address: 0x3a676e69 0x90001408 in pthread_mutex_lock () (gdb) bt #0 0x90001408 in pthread_mutex_lock () #1 0x0002eef5 in sqlite3_mutex_enter (p=0x3a676e69) at ../../src/ext/sqlite/src/mutex_unix.c:186 #2 0x0002dd03 in sqlite3_exec (db=0x1472dc, zSql=0x59b7358 UPDATE words SET count = count+1 WHERE wid=999, xCallback=0, pArg=0x0, pzErrMsg=0xb2ac) at ../../src/ext/sqlite/src/legacy.c: I would expect, (if I understand potential problems), that I would get a return int != SQLITE_OK , with a BUSY or something. What could all this mean? (I know this may not be enough information, but please let me know I could provide more.) PRAGMA default_cache_size= 23000 PRAGMA fullfsync= 0 PRAGMA synchronous= 2 PRAGMA locking_mode=normal PRAGMA integrity_check= ok PRAGMA vdbe_listing = ON - Jonathan - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Indexes not being used after INNER JOINS?
Hey! Yes, indeed, it is doing a sequential scan according to EXPLAIN. Still, the slowness is unbelievable in such a small database. The whole db takes 11Mb, and doing a LEFT JOIN between a few hundred and a few thousand of rows on a Core 2 Duo taking 6 seconds is... I don't even know what it is :P There must be something very strange going on... I suppose it is not possible to create indexes on views, right? I'll play some more with queries and try to figure out if I can tweak this. If not, then I guess I'll unfortunately have to move to PostgreSQL :-( Cheers! Hugo Ferreira On Dec 31, 2007 8:42 PM, Griggs, Donald [EMAIL PROTECTED] wrote: Hello Hugo, If you preceed a SELECT with the string EXPLAIN QUERY PLAN sqlite will make it clear which, if any, indices it would use when running the select. Sqlite, unlike some of the non-light databases, uses a maxium of one index per table per select, I believe. You may want to look at the ANALYZE command. You really do want a CROSS JOIN in the first case, is that right? I'm not sure if this is info you're looking for. Regards, and Happy New Year to all, Donald G. - To unsubscribe, send email to [EMAIL PROTECTED] - -- スプーンが ない
Re: [sqlite] Indexes not being used after INNER JOINS?
On 12/31/07, Hugo Ferreira [EMAIL PROTECTED] wrote: Yes, indeed, it is doing a sequential scan according to EXPLAIN. Still, the slowness is unbelievable in such a small database. The whole db takes 11Mb, and doing a LEFT JOIN between a few hundred and a few thousand of rows on a Core 2 Duo taking 6 seconds is... I don't even know what it is :P There must be something very strange going on... I suppose it is not possible to create indexes on views, right? I'll play some more with queries and try to figure out if I can tweak this. If not, then I guess I'll unfortunately have to move to PostgreSQL :-( If you haven't found this page yet, it may be useful: http://sqlite.org/optoverview.html If a client-server database engine like PostgreSQL is better suited to your application, I'd probably use it anyway. SQLite is good, but it's not a compact version of a major database engine. If you're simply looking for something that's easily deployable, and SQLite turns out to not meet your needs, there are other database engines that might. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Indexes not being used after INNER JOINS?
Hugo Ferreira wrote: Hi everyone, I seem to be having a problem here with LEFT JOINS between tables and results of INNER JOINS. Take for example the following example (table definition is in the end): TABLE COUNT esparqueologico: 750 TABLE COUNT data: 3828 TABLE COUNT reftemporal: 3972 This query would take 6.7s to run (750 rows): select * from esparqueologico oe left join (data d cross join reftemporal r on d.reftemporal_id = r.id) x on oe.datacao_id = x.id However this takes virtually zero time (750 rows): select * from esparqueologico oe left join data d on oe.datacao_id = d.id And this takes 0.1s (3828 rows): select * from data d inner join reftemporal r on d.reftemporal_id = r.id It seems to me that indexes are lost in the first query. Here is the data definition I'm using: CREATE TABLE data ( id guid NOT NULL, reftemporal_id guid NOT NULL, PRIMARY KEY (id), FOREIGN KEY (reftemporal_id) REFERENCES reftemporal(id), ) CREATE TABLE reftemporal ( id guid NOT NULL, subtype varchar, PRIMARY KEY (id) ) CREATE TABLE esparqueologico ( id guid NOT NULL, datacao_id guid, PRIMARY KEY (id), FOREIGN KEY (datacao_id) REFERENCES data(id), ) CREATE INDEX idx_data_reftemporal_id ON data(reftemporal_id) CREATE INDEX idx_esparqueologico_datacao_id ON esparqueologico(datacao_id) I'm not an expert, but don't you want an index on reftemporal.id as well? You're querying it in your JOIN clause, but there's no index on the field. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: Re[2]: [sqlite] Fastest way to check if new row or update existing one?
On Mon, 31 Dec 2007 13:54:50 +, Hugo Ferreira [EMAIL PROTECTED] wrote: Hmmm... Would it be possible to make a trigger on a table such that if any update fails, it does an insert? If so, then one would only need to issue updates. I don't think that would be possible, an update trigger most likely wouldn't fire on a failed update. The only thing you can rely on is: zero rows changed. But you could try it anyway. Experiments are always worth the effort. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Indexes not being used after INNER JOINS?
@Scott: Id is a primary key. I believe PKs always have indexes. @Griggs: While the database engine is to be run on a full blown PC, I have three needs that have lead me to choose SQLite: a) It should be completely integrated/embedded within the application; no separate install. Just a single .DLL ;-) b) It must have bindings with .Net 2.0 and Mono 1.2.5. c) Open-Source. Switching to a client-server application like PostgreSQL that can't be embedded into the application would actually be my last resort. Cheers and thanks! Hugo Ferreira On Dec 31, 2007 11:56 PM, Scott Baker [EMAIL PROTECTED] wrote: Hugo Ferreira wrote: Hi everyone, I seem to be having a problem here with LEFT JOINS between tables and results of INNER JOINS. Take for example the following example (table definition is in the end): TABLE COUNT esparqueologico: 750 TABLE COUNT data: 3828 TABLE COUNT reftemporal: 3972 This query would take 6.7s to run (750 rows): select * from esparqueologico oe left join (data d cross join reftemporal r on d.reftemporal_id = r.id) x on oe.datacao_id = x.id However this takes virtually zero time (750 rows): select * from esparqueologico oe left join data d on oe.datacao_id = d.id And this takes 0.1s (3828 rows): select * from data d inner join reftemporal r on d.reftemporal_id = r.id It seems to me that indexes are lost in the first query. Here is the data definition I'm using: CREATE TABLE data ( id guid NOT NULL, reftemporal_id guid NOT NULL, PRIMARY KEY (id), FOREIGN KEY (reftemporal_id) REFERENCES reftemporal(id), ) CREATE TABLE reftemporal ( id guid NOT NULL, subtype varchar, PRIMARY KEY (id) ) CREATE TABLE esparqueologico ( id guid NOT NULL, datacao_id guid, PRIMARY KEY (id), FOREIGN KEY (datacao_id) REFERENCES data(id), ) CREATE INDEX idx_data_reftemporal_id ON data(reftemporal_id) CREATE INDEX idx_esparqueologico_datacao_id ON esparqueologico(datacao_id) I'm not an expert, but don't you want an index on reftemporal.id as well? You're querying it in your JOIN clause, but there's no index on the field. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 - To unsubscribe, send email to [EMAIL PROTECTED] - -- スプーンが ない
