Hi David, I don't know wether it's faster, but try
select a,b,c from d where c in (select c from d except select c from e) Maybe you're lucky and it's faster. Kind regards Ulrich On Wednesday 15 March 2006 22:44, CARTER-HITCHIN, David, FM wrote: > Hi Brett, > > Many thanks for replying. Well I tried PRAGMA temp_store=memory and that > sadly did not help. > > I'm going to adopt a different approach. Say I've got: > > select a,b,c > from d > where c NOT IN (select c from e) > > What I'm trying is: > > 1. select a,b,c from d into a temp1 table (as far as I can see SQLite > can't do SELECT INTO, so I'll do it for it by creating the table and > selecting and inserting the rows - please correct me if there's an easier > way). > 2. select c from e into a second temp2 table > 3. delete from temp1 where temp1.c = temp2.c > 4. the results left in temp1 will be the rows where c NOT IN (select c from > e) [hopefully :-] > > I used to do this with Sybase back in the days when Sybase was really bad > with NOT IN too - they seemed to improve on things around version 12 or > 12.5... > > Thanks, > > David Carter-Hitchin. > -- > Royal Bank of Scotland > Interest Rate Derivatives IT > 135 Bishopsgate > LONDON EC2M 3TP > > Tel: +44 (0) 207 085 1088 > > > -----Original Message----- > > From: Brett Wilson [mailto:[EMAIL PROTECTED] > > Sent: 14 March 2006 16:48 > > To: [email protected] > > Subject: Re: [sqlite] Help needed to diagnose "NOT IN" query > > > > > > David, > > > > I asked a similar question, and here is drh's response to me, adapted > > to your situation. I think it is probably applicable to you as well. > > I'm not sure this is the type of query that can be made to go really > > fast no matter what. I actually haven't gotten around to doing this > > yet, so I can't comment on how well it works. > > > > > > To implement this, SQLite creates a temporary index that contains all > > unique values of holiday_list.number. Then it scans through the table > > and for each entry where holiday.hol_id is not in the temp index. > > > > Do make sure that your temp tables are stored in RAM. Compile with > > -DTEMP_STORE=2. Or at run-time set > > > > PRAGMA temp_store=memory; > > > > That will put the temporary index in RAM and things will tend > > to go faster. > > > > Brett > > > > On 3/14/06, CARTER-HITCHIN, David, FM > > > > <[EMAIL PROTECTED]> wrote: > > > Hi, > > > > > > Hope someone out there can help - I've got this query which > > > > runs VERY > > > > > slowly: > > > > > > SELECT ab_tran.ins_num, holiday.hol_id, holiday.ins_num > > > FROM ab_tran, holiday > > > WHERE ab_tran.ins_num = holiday.ins_num > > > AND holiday.hol_id NOT IN ( SELECT number from holiday_list ) > > > > > > All the tables are fairly chunky, except holiday_list which > > > > has 89 rows. I > > > > > just realised that holiday.ins_num is needlessly selected > > > > but I doubt very > > > > > much that would be causing things to slow down massively. > > > > I can run this on > > > > > a similar powered Sybase server and it runs in a couple of > > > > minutes - with > > > > > SQLite, using an in-memory database I've left it all night > > > > and it didn't > > > > > finish! Evidently something is wrong somewhere. I've > > > > created indexes on > > > > > all the columns in the join and the "NOT IN" clause, but > > > > that didn't seem to > > > > > help. > > > > > > Is there anything else I check? > > > > > > I've run the EXPLAIN (output below), but I'm a novice here, > > > > not sure what > > > > > could be wrong. > > > > > > Any ideas will be highly appreciated. > > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:476 - 64 rows > > > > extracted in > > > > > 0.03532 seconds. > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 0 > > > > Goto 0 60 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 1 > > > > Integer 0 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 2 > > > > OpenRead 0 > > > > > 3 > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 3 > > > > SetNumColumns 0 > > > > > 7 > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 4 > > > > Integer 0 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 5 > > > > OpenRead 1 > > > > > 5 > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 6 > > > > SetNumColumns 1 > > > > > 2 > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 7 > > > > Integer 0 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 8 > > > > OpenRead 3 > > > > > 137675 keyinfo(1,BINARY) > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 9 > > > > Rewind 0 56 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 10 > > > > Column 0 1 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 11 > > > > NotNull -1 14 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 12 Pop > > > > 1 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 13 > > > > Goto 0 55 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 14 > > > > MakeRecord 1 > > > > > 0 i > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 15 > > > > MemStore 0 > > > > > 0 > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 16 > > > > MoveGe 3 55 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 17 > > > > MemLoad 0 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 18 > > > > IdxGE 3 55 > > > > > + > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 19 > > > > RowKey 3 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 20 > > > > IdxIsNull 1 > > > > > 54 > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 21 > > > > IdxRowid 3 > > > > > 0 > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 22 > > > > MoveGe 1 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 23 > > > > MemLoad 1 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 24 If > > > > 0 40 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 25 > > > > MemInt 1 1 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 26 > > > > OpenVirtual 4 > > > > > 0 keyinfo(1,BINARY) > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 27 > > > > SetNumColumns 4 > > > > > 1 > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 28 > > > > Integer 0 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 29 > > > > OpenRead 2 > > > > > 6 > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 30 > > > > SetNumColumns 2 > > > > > 1 > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 31 > > > > Rewind 2 39 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 32 > > > > Column 2 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 33 > > > > NotNull -1 36 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 34 Pop > > > > 1 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 35 > > > > Goto 0 38 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 36 > > > > MakeRecord 1 > > > > > 0 i > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 37 > > > > IdxInsert 4 > > > > > 0 > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 38 > > > > Next 2 32 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 39 > > > > Close 2 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 40 > > > > Integer 1 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 41 > > > > Column 1 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 42 > > > > NotNull -1 46 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 43 Pop > > > > 2 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 44 > > > > Null 0 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 45 > > > > Goto 0 49 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 46 > > > > MakeRecord 1 > > > > > 0 i > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 47 > > > > Found 4 49 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 48 > > > > AddImm -1 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 49 If > > > > 1 54 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 50 > > > > Column 0 1 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 51 > > > > Column 1 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 52 > > > > Column 1 1 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 53 > > > > Callback 3 > > > > > 0 > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 54 > > > > Next 3 17 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 55 > > > > Next 0 10 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 56 > > > > Close 0 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 57 > > > > Close 1 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 58 > > > > Close 3 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 59 > > > > Halt 0 0 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 60 > > > > Transaction 0 > > > > > 0 > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 61 > > > > VerifyCookie 0 > > > > > 20 > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 62 > > > > Goto 0 1 > > > > > Tue Mar 14 12:05:10 2006 DEBUG at Cache.cpp:487 - 63 > > > > Noop 0 0 > > > > > Many thanks, > > > > > > David Carter-Hitchin. > > > -- > > > Royal Bank of Scotland > > > Interest Rate Derivatives IT > > > 135 Bishopsgate > > > LONDON EC2M 3TP > > > > > > Tel: +44 (0) 207 085 1088 > > > > ************************************************************** > > ********************* > > > > > The Royal Bank of Scotland plc. Registered in Scotland No > > > > 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB. > > > > > Authorized and regulated by the Financial Services Authority > > > > > > This e-mail message is confidential and for use by the > > > addressee only. If the message is received by anyone other > > > than the addressee, please return the message to the sender > > > by replying to it and then delete the message from your > > > computer. Internet e-mails are not necessarily secure. The > > > Royal Bank of Scotland plc does not accept responsibility for > > > changes made to this message after it was sent. > > > > > > Whilst all reasonable care has been taken to avoid the > > > transmission of viruses, it is the responsibility of the > > > > recipient to > > > > > ensure that the onward transmission, opening or use of this > > > message and any attachments will not adversely affect its > > > systems or data. No responsibility is accepted by The Royal > > > Bank of Scotland plc in this regard and the recipient should carry > > > out such virus and other checks as it considers appropriate. > > > Visit our websites at: > > > http://www.rbos.com > > > http://www.rbsmarkets.com > > > > ************************************************************** > > ******************
