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: sqlite-users@sqlite.org > 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 > > > ************************************************************** > ****************** > > > > >
