Re: Long Query - how to improve?

Subject: Re: Long Query - how to improve? Newsgroups: comp.databases.informix References: <1992May30.184053.1064@Thalatta.COM> Distribution: bossert@Thalatta.COM (John Bossert) writes: : Using OnLine 4.1 on an HP 9000/807, I'm executing the following queries: : : select : ticket_nr, contract_nr, ticket_desc : from : ticket : where : where_condition : into temp : t1 without log; : : table_a has > 100k records, as a result of this query (which runs in about 8 : seconds, t1 contains 8 records. Next, : : select : t1.*, : contract.* : from : t1, outer ( contract, vendor, vendor_contract ) : where : t1.contract_nr = contract.contract_nr and : contract.contract_nr = vendor_contract.contract_nr and : vendor.vendor_nr = vendor_contract.vendor_nr and : vendor_contract.priority = 'P' : : This should return the contract information for a ticket, if it exists. : There may be many vendors on a particular contract - we only want the prime... : : vendor contains 5k records, vendor_contract contains 8k records. : : The following indexes exist: : : i1 on contract(contract_nr) : i2 on vendor(vendor_nr) : i3 on vendor_contract(vendor_nr, contract_nr, priority) : : The problem is that query2 takes almost 5 minutes, even with no nulls in : t1.contract_nr (outer joins unnecessary). : : set explain shows indexes being followed for everything, except : t1.contract_nr (SEQUENTIAL SCAN) but with 8 records, so what? : : If I try: : : select : t1.*, : contract.* : from : t1, outer contract : where : t1.contract_nr = contract.contract_nr : : The query is almost instantaneous. As I add tables (vendor, vendor_contract) : to the outer join, the query slows down. : : If a contract exists, it must have a vendor_contract and vendor entry. : : What is happening here, and how can I speed things up? : AHA!! Another victim of the sub-optimal optimizer! 8> It sounds like you are on the right track. The best workaround for optimizer problems is to force the optimizer to do things in the order that you want them to be done. First, save the contents of your "almost intantaneous" query above in another temporary table "t2" (minus duplicate column names). Then, use "t2" to join directly to the vendor tables without the outer join. This should work well if t2.contract_nr comes from contract.contract_nr and you restrict t2.contract_nr to values that are not null when you join it to the vendor tables. Adding an index to t2(contract_nr) may also speed things up but it really shouldn't be neccessary. Good Luck! ############## __ ######################################################## ########### / / ############ Ron Whiteleather # ######### / / ######### Network Server Div. - Mfg. Test Sys. # ####### / /___ ______ ####### # ###### / __ // __ / ###### email: ronw@salmon.boi.hp.com # ###### / / / // /_/ / ###### US Mail: 11411 Chinden Blvd., MS 265 # ####### /_/ /_// ____/ ####### Boise, Idaho 83707 # ######## / / ######### Telnet: 1-323-3476 # ######### / / ########### FAX: (208) 323-4215 # ############ /_/ ##########################################################