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