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 > ******************************************************************************** > >
