Re: Informix Online 5.0 Query Optimizer

->From: Jack Parker <jparker@hpbs2561.boi.hp.com> ->Subject: Re: Informix Online 5.0 Query Optimizer ->To: informix-list@rmy.emory.edu (Informix Users Net) ->Date: Fri, 30 Jul 93 10:19:11 MDT ->> ->> >Estimated Cost: 2447 ->> >Estimated Cost: 3583 -> ->Both numbers way to high. A sign that schema is perhaps not the most ->efficient for what you are trying to do. When I see numbers like that ->I tend to rewrite the query or revamp the schema to bring it down. -> ->Something you may consider, which I forgot to verbalise the other day, ->is doing a UNION instead of an OR. I've seen this bring 'costs' down ->from the multi-k into the twenties. -> ->j. -> ->_____________________________________________________________________________ ->Jack Parker - Contractor | ->Hewlett Packard, BSMC Boise, Idaho, USA| If you keep staring at it like that, ->jparker@hpbs2561.boi.hp.com | your nose is going to grow ->(208) 396-5388 (W) (208) 384-1623 (H) | into the bark. ->_____________________________________________________________________________ -> Any opinions expressed herein are my own and not those of my employers. ->_____________________________________________________________________________ I don't remember the exact details of the original question, but I want to concur with my friend Jack. *OR* seems to be the weakest link in SQL evaluation. I have seen simple- looking queries take a geological epoch, just because they contained an OR. Replacing WHERE var = "A" OR var = "B" with WHERE var IN ( "A", "B" ) sometimes made a two or three order of magnitude performance change, even tho' the two are logically identical. ORs on different columns obviously can't be combined in this way, so a UNION seems the least painful alternative. Regards, Alan +---------------------------+--------------------------------------------+ | R. Alan Popiel | Internet: alan@den.mmc.com | | Martin Marietta, Tech Ops | Voice: 303-977-9998 | | P.O. Box 179, M/S 5422 | My opinions may not reflect Martin policy. | | Denver, CO 80201-0179 USA | In fact, we often disagree. | +---------------------------+--------------------------------------------+