IDS Forum

Re: Performance difference between IN and OR ?

Posted By: Khaled Bentebal
Date: Thursday, 10 March 2011, at 4:05 p.m.

In Response To: Re: Performance difference between IN and OR ? (John Miller iii)

Hi John, Art, Fernando,

OK . I went back to the my test case.

I do understand better why it is working this way.

On the Mac, the rowsize of the customer table is 134 bytes and you have
28 rows in the table.

The formula used to calculate the space needed is: (rowsize+ 4 bytes for
the slot)*number of rows
So Spoace needed is (134+4)*28=3864 bytes. This fits into 1 page in the
Mac but takes 2 pages Linux since we used the default page size.

On the Mac, one page is enough to get the whole table into memory. A
sequential scan of one page is faster than going thru an index. If we
went thru an index, it would have been 2 pages to load from disk into
the buffer pool in memory.

When you replace the ORs by UNIONs, the optimizer goes thru an index search.

The optimizer is analyzing the accesses by using the selectivity factor
and the number of pages than need to be accessed; the selectivity factor
for an equality is 1/number of unique values. For the ORs, it is a
little different.

The optimizer is trying to choose the route that takes the least number
of pages to get the data; which makes sense.

*Why is the optimizer going 2 different ways to get the data if ORs are
equivalent to an IN*.

Apparently the optimizer goes thru 2 different algorithms to get the
same thing.

This small exemple proves that they are not the same even though we
always advice clients that it is the same thing.

Usually what counts is the run-time. If it takes a fraction of a second,
who cares in a transactional environment. But it could be important in a
batch.

What disturbs me the most is that if the optimizer is going the
sequential route and hits a row that is locked by another user who is
updating another row. That user will never get the answer back since the
row is located after the locked row. Where, if the optimizer chooses the
indexed route no problem.

I have faced this problem before.

SO we should definitely tests things before we decide on things. Worst
case, a directive will do the job.

Bottom line, ORs are not really he same as an IN even though we tend to
think so.

The IN is not translated into ORs. The optimizer has a different algorithm for each one of them.

Khaled Bentebal

Email: khaled.bentebal@consult-ix.fr
Site Web: www.consult-ix.fr

Le 09/03/11 20:59, John Miller iii a écrit :
> In short I believe your testcase is not the best.
>
> You are using the stores_demo database on a MAC which
> means the base pagesize is 4KB. The entire customer
> table will then fit on a single data page.
>
> You have not update statistics high, so the optimizer is missing
> data distributions which means more rounding will occur in
> estimating how many rows will match.
>
> Now know that a sequential scan mean reading just a single
> data page versus and index lookup then reading the data page,
> which is better?? This is actually a hard question and does
> depend on your isolation level beside other things. Dirty read
> (default for non-logged database) might be the sequential
> scan as you do not have to worry about locks, but for others
> the index can be better as you can avoid encounter locks.
>
> Just some food for thought when coming up with testcases.
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 03/09/2011 11:27:34 AM:
>
>> [image removed]
>>
>> Re: Performance difference between IN and OR ? [23021]
>>
>> Khaled Bentebal
>>
>> to:
>>
>> ids
>>
>> 03/09/2011 11:33 AM
>>
>> Sent by:
>>
>> ids-bounces@iiug.org
>>
>> Please respond to ids
>>
>> Hi Guys,
>>
>> The tests have been done with the stores database out of the box:
>> dbaccessdemo or dbaccessdemo7
>>
>> I event tried to run update statistics low on the entire database. Th=
> e
>> same result.
>>
>> Is it something weird on IDS 11.70 on MAC OS 10.6.6 ?
>>
>> Khaled Bentebal
>>
>> Email: khaled.bentebal@consult-ix.fr
>> Site Web: www.consult-ix.fr
>>
>> Le 09/03/11 20:15, John Miller iii a =E9crit :
>>> How are the statistics updated? Were they high on the table??
>>>
>>> John F. Miller III
>>> STSM, Embedability Architect
>>> miller3@us.ibm.com
>>> 503-578-5645
>>> IBM Informix Dynamic Server (IDS)
>>>
>>> ids-bounces@iiug.org wrote on 03/09/2011 10:54:01 AM:
>>>
>>>> [image removed]
>>>>
>>>> Re: Performance difference between IN and OR ? [23019]
>>>>
>>>> Fernando Nunes
>>>>
>>>> to:
>>>>
>>>> ids
>>>>
>>>> 03/09/2011 10:56 AM
>>>>
>>>> Sent by:
>>>>
>>>> ids-bounces@iiug.org
>>>>
>>>> Please respond to ids
>>>>
>>>> select * from customer where customer_num in (100,101, 104);
>>>> select * from customer where customer_num =3D3D100 or customer_num=
> =3D3D10=3D
>
>>> 1 or
>>>> customer_num=3D3D104;
>>>>
>>>> on 11.70.UC1 on Linux have the same (indexed) query plan...
>>>> I believe there's more to it than we're seeing...?
>>>>
>>>> Regards.
>>>>
>>>> On Wed, Mar 9, 2011 at 5:29 PM, Khaled Bentebal<
>>>> khaled.bentebal@consult-ix.fr> wrote:
>>>>
>>>>> My run was on 11.70.FC1 on Mac OS not Linux.
>>>>>
>>>>> My test on 11.50.FC4 was on Linux.
>>>>>
>>>>> Strange. I always said it should be same until I tested it on
>>> thisversion.
>>>>> Khaled Bentebal
>>>>>
>>>>> Email: khaled.bentebal@consult-ix.fr
>>>>> Site Web: www.consult-ix.fr
>>>>>
>>>>> Le 09/03/11 18:21, Art Kagel a =3DE9crit :
>>>>>> Interesting. My run was on 11.70.FC1 on Linux as well. Just
>>>>>> reinforces my emphasis on testing testing testing.
>>>>>>
>>>>>> Art
>>>>>>
>>>>>> Art S. Kagel
>>>>>> Advanced DataTools (www.advancedatatools.com
>>>>>> <http://www.advancedatatools.com>)
>>>>>> IIUG Board of Directors (art@iiug.org<mailto:art@iiug.org>)
>>>>>> Blog: http://informix-myview.blogspot.com/
>>>>>>
>>>>>> Disclaimer: Please keep in mind that my own opinions are my own
>>>>>> opinions and do not reflect on my employer, Advanced DataTools, =
> t=3D
>>> he
>>>>>> IIUG, nor any other organization with which I am associated eith=
> e=3D
>>> r
>>>>>> explicitly, implicitly, or by inference. Neither do those opinio=
> n=3D
>>> s
>>>>>> reflect those of other individuals affiliated with any entity wi=
> t=3D
>>> h
>>>>>> which I am affiliated nor those of the entities themselves.
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Wed, Mar 9, 2011 at 12:15 PM, Khaled Bentebal
>>>>>> <khaled.bentebal@consult-ix.fr
>>> <mailto:khaled.bentebal@consult-ix.fr>>
>>>>>> wrote:
>>>>>>
>>>>>> I tried it on 11.50.FC4 and the result is not the same. That is
>>>>>> why I said NOT anymore.
>>>>>>
>>>>>> There is definitely a difference between the versions.
>>>>>>
>>>>>> Here is what I get on 11.50.FC4 on LINUX:
>>>>>>
>>>>>> QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 18:11:46)
>>>>>> ------
>>>>>> select * from customer where customer_num in (100,101, 104)
>>>>>>
>>>>>> Estimated Cost: 1
>>>>>> Estimated # of Rows Returned: 2
>>>>>>
>>>>>> 1) informix.customer: INDEX PATH
>>>>>>
>>>>>> (1) Index Keys: customer_num (Serial, fragments: ALL)
>>>>>> Lower Index Filter: informix.customer.customer_num =3D3D 100
>>>>>>
>>>>>> (2) Index Keys: customer_num (Serial, fragments: ALL)
>>>>>> Lower Index Filter: informix.customer.customer_num =3D3D 101
>>>>>>
>>>>>> (3) Index Keys: customer_num (Serial, fragments: ALL)
>>>>>> Lower Index Filter: informix.customer.customer_num =3D3D 104
>>>>>>
>>>>>>
>>>>>>
>>>>>> Query statistics:
>>>>>> -----------------
>>>>>>
>>>>>> Table map :
>>>>>> ----------------------------
>>>>>> Internal name Table name
>>>>>> ----------------------------
>>>>>> t1 customer
>>>>>>
>>>>>>
>>>>>> type table rows_prod est_rows rows_scan time est_cost
>>>>>> ----------------------------------------------------------------=
> -=3D
>>
>>
> ***********************************************************************=
> ********
>
>> Forum Note: Use "Reply" to post a response in the discussion forum.=
>> =
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

Messages In This Thread

• Performance difference between IN and OR ?
  VALéRIE TAESCH -- Wednesday, 9 March 2011, at 9:58 a.m.
  • Re: Performance difference between IN and OR ?
    Khaled Bentebal -- Wednesday, 9 March 2011, at 10:16 a.m.
    • Re: Performance difference between IN and OR ?
      John Miller iii -- Wednesday, 9 March 2011, at 11:54 a.m.
    • Re: Performance difference between IN and OR ?
      Art Kagel -- Wednesday, 9 March 2011, at 11:55 a.m.
      • Re: Performance difference between IN and OR ?
        Khaled Bentebal -- Wednesday, 9 March 2011, at 12:04 p.m.
        • Re: Performance difference between IN and OR ?
          Art Kagel -- Wednesday, 9 March 2011, at 3:37 p.m.
    • Re: Performance difference between IN and OR ?
      Khaled Bentebal -- Wednesday, 9 March 2011, at 12:16 p.m.
    • Re: Performance difference between IN and OR ?
      Art Kagel -- Wednesday, 9 March 2011, at 12:22 p.m.
    • Re: Performance difference between IN and OR ?
      Khaled Bentebal -- Wednesday, 9 March 2011, at 12:29 p.m.
      • Re: Performance difference between IN and OR ?
        Fernando Nunes -- Wednesday, 9 March 2011, at 1:54 p.m.
        • Re: Performance difference between IN and OR ?
          John Miller iii -- Wednesday, 9 March 2011, at 2:15 p.m.
          • Re: Performance difference between IN and OR ?
            Khaled Bentebal -- Wednesday, 9 March 2011, at 2:27 p.m.
            • Re: Performance difference between IN and OR ?
              John Miller iii -- Wednesday, 9 March 2011, at 2:59 p.m.
              • Re: Performance difference between IN and OR ?
                Fernando Nunes -- Wednesday, 9 March 2011, at 5:35 p.m.
              • Re: Performance difference between IN and OR ?
                Khaled Bentebal -- Thursday, 10 March 2011, at 4:05 p.m.
                • Re: Performance difference between IN and OR ?
                  Fernando Nunes -- Thursday, 10 March 2011, at 5:29 p.m.
                • Re: Performance difference between IN and OR ?
                  Khaled Bentebal -- Thursday, 10 March 2011, at 6:10 p.m.
  • Re: Performance difference between IN and OR ?
    Art Kagel -- Wednesday, 9 March 2011, at 11:40 a.m.
  • Re: Performance difference between IN and OR ?
    DAVE GRIFFEN -- Thursday, 10 March 2011, at 11:56 a.m.
    • Re: Performance difference between IN and OR ?
      Art Kagel -- Thursday, 10 March 2011, at 1:34 p.m.
      • Re: Performance difference between IN and OR ?
        DAVE GRIFFEN -- Thursday, 10 March 2011, at 2:26 p.m.
        • Re: Performance difference between IN and OR ?
          Art Kagel -- Thursday, 10 March 2011, at 2:40 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.