Re: Informix SE vs. Online Query

->To: informix-list@rmy.emory.edu ->Cc: fsc0@bigbang.isg.com, abc0@bigbang.isg.com ->Subject: Informix SE vs. Online Query ->Date: Fri, 06 Aug 93 09:49:11 -0700 ->From: fsc0@bigbang.isg.com -> ->I have this one application that is running on 2 machines. They are both ->sun sparc 2 and running Informix 4.1 . The difference is that one machine ->is using Informix Standard Engine (SE) and the other one is using Online ->Engine. The data schema is identical on both machines and the amount of ->data is the same too. I am researching to see why this application is ->taking a long time to do a query on the SE and it takes no time at all on ->the Online ( I did take into consideration that the Online should be ->faster but still the difference is very significant , like 1 minute versus ->50 minutes). So I used 'set explain on' and here are the results: -> ->Query: ->Select count(*) from table1 where field1 not in -> (select field1 from table2) -> ->Note: field1 has a unique index on both table1 and table2 -> ->Results: ->On the SE, the subquery is done thru a SEQUENTIAL SCAN -> ->On the Online, the subquery is done thru a INDEX PATH -> Index keys: field1 (Key-Only) -> ->I have called Informix Technical Support and the engineer first told ->me that it was correct the way how the SE optimizer worked (doing ->SEQUENTIAL SCAN) and then when I told him about the way how Online ->optimized the query, he suggested me to check for index corruption ->in the SE ???? Anyway,I complied and found nothing wrong with the index on ->both machines . -> ->QUESTIONS: -> 1) Is the SE optimizer doing the correct thing ,i.e. doing a ->SEQUENTIAL SCAN? -> -> 2) If SE optimizer is doing the correct thing, then is it correct ->to say that the Online Optimizer is in a way 'enhanced' and knows how to ->make better use of the indexes? -> Any input would be very much appreciated. Thanks! -> One set of answers to your questions (I'm sure that other who know more about the optimizer can give still more information) : 1. It appears that SE is indeed doing one version of the correct thing. A subquery of NOT IN ( SELECT field1 FROM table2 ) needs to access every value of field1. Since a UNIQUE INDEX exists on field1, one likely optimization would be to scan the entire *index* without ever accessing the table itself. This appears to be what OnLine is doing. Scanning the index only should be faster, since most tables are significantly wider than their indexes, and time spent transferring non-index values from disk to memory would be wasted in this context. Can someone from Informix or elsewhere tell us whether SE's SEQUENTIAL SCAN in this context refers to the table or the index? 2. Yes, the OnLine optimizer IS enhanced vis-a-vis the Standard Engine. OnLine maintains significantly more statistics about table size, index tree structure, etc., than does SE. A recent thread in this forum listed the statistics kept by each. I made a quick check thru my saved stuff and can't find it at the moment. (I appear to have my own index corruption problems.) Perhaps someone who filed it more effectively could send you this info. Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, Tech Ops | / \\ alan@den.mmc.com | P.O. Box 179, M/S 5422 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\