There is one important problem though that I just discovered. Just found out that the maximum number of tables in a join is 32! So, with my base table that is only 31 to add. This trouble doesn't of course apply to the old UPDATE method. So, I think after all I need the old way of doing it or what I could do is see how many tables are to be added and pick the method accordingly.
RBS -----Original Message----- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 17:54 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? Actually make that about 5 to 6 times as fast. RBS -----Original Message----- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 17:39 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? Can confirm now that the method with INSERT OR REPLACE is faster indeed. My timings tell me it is about twice as fast and that is worth it as that could be up to a few seconds. I now wonder if there is an even faster way avoiding all the GROUP tables and doing it all in one statement. Thanks again for the assistance. RBS -----Original Message----- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 31 January 2007 00:51 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Limit statement size? --- RB Smissaert <[EMAIL PROTECTED]> wrote: > I can see now what the trouble is if I do the SELECT without the INSERT OR > REPLACE (shortened): Can't suggest anything without seeing the schema for all the tables involved and any unique indexes related to those tables. It should work. Perhaps you're not specifying some columns related to a unique index on the table being updated. Maybe there's a bug in REPLACE? Specifically, what does ".schema A3TestB67_J" return? I suspect this REPLACE technique with its reduced number of database lookups ought to be much faster than all those subselects you are using now for every column, but if you're happy with the timings with the old way and it works, there's no point changing it. I am curious with it not updating the table, though. > > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1, > GROUP_2 g2, > GROUP_3 g3, > GROUP_4 g4 > where > t1.PATIENT_ID = g2.PID and > t1.PATIENT_ID = g3.PID and > t1.PATIENT_ID = g4.PID > > I only get the rows that have entries in all groups. So, this is like an > inner join and I need a left join. > Have tried this, but it didn't alter the table, although there was no error: > > INSERT OR REPLACE INTO > A3TestB67_J(PATIENT_ID, > ENTRY_ID_E1, > READ_CODE_E1, > TERM_TEXT_E1, > START_DATE_E1, > ADDED_DATE_E1, > NUMERIC_VALUE_E1, > ENTRY_ID_E2, > READ_CODE_E2, > TERM_TEXT_E2, > START_DATE_E2, > ADDED_DATE_E2, > NUMERIC_VALUE_E2, > ENTRY_ID_E3, > READ_CODE_E3, > TERM_TEXT_E3, > START_DATE_E3, > ADDED_DATE_E3, > NUMERIC_VALUE_E3, > ENTRY_ID_E4, > READ_CODE_E4, > TERM_TEXT_E4, > START_DATE_E4, > ADDED_DATE_E4, > NUMERIC_VALUE_E4) > select > t1.PATIENT_ID, > g2.ENTRY_ID, > g2.READ_CODE, > g2.TERM_TEXT, > g2.START_DATE, > g2.ADDED_DATE, > g2.NUMERIC_VALUE, > g3.ENTRY_ID, > g3.READ_CODE, > g3.TERM_TEXT, > g3.START_DATE, > g3.ADDED_DATE, > g3.NUMERIC_VALUE, > g4.ENTRY_ID, > g4.READ_CODE, > g4.TERM_TEXT, > g4.START_DATE, > g4.ADDED_DATE, > g4.NUMERIC_VALUE > from > A3TestB67_J t1 > left join GROUP_2 g2 on (t1.PATIENT_ID = g2.PID) > left join GROUP_3 g3 on (t1.PATIENT_ID = g3.PID) > left join GROUP_4 g4 on (t1.PATIENT_ID = g4.PID) > > My old method is actually quite fast and not sure if I can improve on it. > > RBS > > > > -----Original Message----- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: 30 January 2007 05:53 > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Limit statement size? > > --- RB Smissaert <[EMAIL PROTECTED]> wrote: > > Thanks, that is how I understood it to be. > > I must be overlooking something simple here. > > Check your SELECT sub-statement within the REPLACE statement to see > what rows it returns. > > .header on > .mode tabs > > create table t1(id primary key, e2_a, e2_b, e3_a, e3_b); > insert into t1 values(3, 30,31, 23,230); > insert into t1 values(4, 40,41, 24,240); > insert into t1 values(5, 50,51, 25,250); > > create table e2(id primary key, a, b); > insert into e2 values(3, 300, 310); > insert into e2 values(4, 400, 410); > insert into e2 values(5, 500, 510); > > create table e3(id primary key, a, b); > insert into e3 values(3, 23.1, 230.1); > insert into e3 values(4, 24.1, 240.1); > insert into e3 values(5, 25.1, 250.1); > > select * from t1 order by id; > > replace into t1(id, e2_a, e2_b, e3_a, e3_b) > select t1.id, e2.a, e2.b, e3.a, e3.b > from t1, e2, e3 > where t1.id = e2.id and t1.id = e3.id; > > select * from t1 order by id; > > id e2_a e2_b e3_a e3_b > 3 30 31 23 230 > 4 40 41 24 240 > 5 50 51 25 250 > > id e2_a e2_b e3_a e3_b > 3 300 310 23.1 230.1 > 4 400 410 24.1 240.1 > 5 500 510 25.1 250.1 > > > > > > RBS > > > > -----Original Message----- > > From: Gerry Snyder [mailto:[EMAIL PROTECTED] > > Sent: 29 January 2007 23:52 > > To: sqlite-users@sqlite.org > > Subject: Re: [sqlite] Limit statement size? > > > > RB Smissaert wrote: > > > Had a go at this, but sofar I haven't been able yet to get it to work. > > > I get no error, but A3Test115_J remains just at it is. > > > I couldn't find much information about INSERT OR REPLACE in the SQLite > > > documentation. What exactly should it do? ____________________________________________________________________________ ________ Need Mail bonding? Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users. http://answers.yahoo.com/dir/?link=list&sid=396546091 ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
