Performance difference between IN and OR ?

Posted in 2011

Summary

Question: does IN (1,2,3) perform differently from equivalent OR conditions in Informix SQL? Replies were mixed. Khaled Bentebal initially claimed IN uses an index while ORs force a sequential scan, but Art Kagel and John Miller showed SET EXPLAIN output where both forms produced identical index paths (with only slightly different estimated costs) on 11.70. Khaled then posted a 11.70 case where the OR form did pick a sequential scan, while 11.50 used the index for both. Conclusion: the optimizer may or may not rewrite IN/OR/UNION ALL into each other, behaviour varies by version/platform, so test each form with SET EXPLAIN rather than assume. No single definitive answer.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Performance & Tuning

VALéRIE TAESCH — Wed, 09 Mar 2011 14:58:52 — source: IIUG Forums & Mailing Lists

Hello, is there any performance difference between IN and OR in a sql request ? A/select * from table where id in (1,2,3) B/select * from table where id=1 or id=2 or id=3 thanks

↪ replying to VALéRIE TAESCH

Khaled Bentebal — Wed, 09 Mar 2011 15:16:40 — source: IIUG Forums & Mailing Lists

It used to be the same thing . Not anymore. The*in* works better since the optimizer will choose an index if the column is indexed. With the*ORs*, the optimizer will be choose a sequential access. Try it with a set explain prior to executing the query. Khaled Bentebal Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 09/03/11 15:58, VALéRIE TAESCH a écrit : > Hello, > > is there any performance difference between IN and OR in a sql request ? > > A/select * from table where id in (1,2,3) > > B/select * from table where id=1 or id=2 or id=3 > > thanks > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

↪ replying to VALéRIE TAESCH

Art Kagel — Wed, 09 Mar 2011 16:40:09 — source: IIUG Forums & Mailing Lists

In general, no. The optimizer will sometimes map an IN() to a set or OR's or OR's to an IN() or may even map either to a UNION ALL of several queries. In general all versions will perform equally, but sometimes one may be extremely faster than the other versions. You have to try all three! There is a session presentation that I gave at the IIUG Conference last year called SQL Every Which Way But Loose that deals with this and related subjects and the other vagueries of the SQL language. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (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, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Mar 9, 2011 at 9:58 AM, VALéRIE TAESCH <valerie.taesch@yahoo.fr>wrote: > Hello, > > is there any performance difference between IN and OR in a sql request ? > > A/select * from table where id in (1,2,3) > > B/select * from table where id=1 or id=2 or id=3 > > thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015175cdda02fd890049e0f61ec

↪ replying to Khaled Bentebal

John Miller iii — Wed, 09 Mar 2011 16:54:20 — source: IIUG Forums & Mailing Lists

I have tested the case below and it does choose the exact same scan path in both case in version 11.70. While I do admit that in version there is are many new scan types with indexes that the Informix engine can choose from. select * from table1 where id in (1,2,3) select * from table1 where id=3D1 or id=3D2 or id=3D3 Estimated Cost: 3 Estimated # of Rows Returned: 3 1) informix.table1: INDEX PATH (1) Index Name: informix.ix1 Index Keys: id (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.table1.id =3D 1 (2) Index Name: informix.ix1 Index Keys: id (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.table1.id =3D 2 (3) Index Name: informix.ix1 Index Keys: id (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.table1.id =3D 3 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 07:16:41 AM: > [image removed] > > Re: Performance difference between IN and OR ? [23010] > > Khaled Bentebal > > to: > > ids > > 03/09/2011 07:17 AM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > It used to be the same thing . Not anymore. > > The*in* works better since the optimizer will choose an index if thecolumn is > indexed. > > With the*ORs*, the optimizer will be choose a sequential access. > > Try it with a set explain prior to executing the query. > > Khaled Bentebal > > Email: khaled.bentebal@consult-ix.fr > Site Web: www.consult-ix.fr > > Le 09/03/11 15:58, VAL=E9RIE TAESCH a =E9crit : > > Hello, > > > > is there any performance difference between IN and OR in a sql request ? > > > > A/select * from table where id in (1,2,3) > > > > B/select * from table where id=3D1 or id=3D2 or id=3D3 > > > > thanks > > > > > > > ***********************************************************************= ******** > > Forum Note: Use "Reply" to post a response in the discussion forum.= > > > > > > > ***********************************************************************= ******** > Forum Note: Use "Reply" to post a response in the discussion forum.= >=

↪ replying to Khaled Bentebal

Art Kagel — Wed, 09 Mar 2011 16:55:59 — source: IIUG Forums & Mailing Lists

Not true Kaled. Here is the same query both as an IN() and as OR's and the optimizer used the identical indexed query path for both. Interestingly, it calculated sightly different costs for the two versions, but identical results. The optimizer MAY make different path decisions for IN(), OR and UNION ALL, but it will not neccessarily do so: QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 11:50:44) ------ select * from files where create in ('2001-01-01 12:12:12', '2002-01-01 12:12:12', '2003-01-01 12:12:12') Estimated Cost: 165 Estimated # of Rows Returned: 1147 1) informix.files: INDEX PATH (1) Index Name: informix.files_create Index Keys: create (Serial, fragments: ALL) Lower Index Filter: informix.files.create = datetime(2001-01-01 12:12:12) year to second (2) Index Name: informix.files_create Index Keys: create (Serial, fragments: ALL) Lower Index Filter: informix.files.create = datetime(2002-01-01 12:12:12) year to second (3) Index Name: informix.files_create Index Keys: create (Serial, fragments: ALL) Lower Index Filter: informix.files.create = datetime(2003-01-01 12:12:12) year to second Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 files type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 0 1147 0 00:00.03 166 QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 11:51:15) ------ select * from files where create ='2001-01-01 12:12:12' or create='2002-01-01 12:12:12' or create ='2003-01-01 12:12:12' Estimated Cost: 171 Estimated # of Rows Returned: 1147 1) informix.files: INDEX PATH (1) Index Name: informix.files_create Index Keys: create (Serial, fragments: ALL) Lower Index Filter: informix.files.create = datetime(2001-01-01 12:12:12) year to second (2) Index Name: informix.files_create Index Keys: create (Serial, fragments: ALL) Lower Index Filter: informix.files.create = datetime(2002-01-01 12:12:12) year to second (3) Index Name: informix.files_create Index Keys: create (Serial, fragments: ALL) Lower Index Filter: informix.files.create = datetime(2003-01-01 12:12:12) year to second Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 files type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 0 1147 0 00:00.00 172 Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (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, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Mar 9, 2011 at 10:16 AM, Khaled Bentebal < khaled.bentebal@consult-ix.fr> wrote: > It used to be the same thing . Not anymore. > > The*in* works better since the optimizer will choose an index if the column > is > indexed. > > With the*ORs*, the optimizer will be choose a sequential access. > > Try it with a set explain prior to executing the query. > > Khaled Bentebal > > Email: khaled.bentebal@consult-ix.fr > Site Web: www.consult-ix.fr > > Le 09/03/11 15:58, VALéRIE TAESCH a écrit : > > Hello, > > > > is there any performance difference between IN and OR in a sql request ? > > > > A/select * from table where id in (1,2,3) > > > > B/select * from table where id=1 or id=2 or id=3 > > > > thanks > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf300fb283a1ab5b049e0f99ee

↪ replying to Art Kagel

Khaled Bentebal — Wed, 09 Mar 2011 17:04:10 — source: IIUG Forums & Mailing Lists

Well, this is what I always adviced. IN equivalent to ORs since the INs were translated to ORs. The following example shows things differently. Check this out on IDS 11.70.FC1 using the customer table: QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 16:12:07) ------ select * from customer where customer_num in (100,101, 104) Estimated Cost: 1 Estimated # of Rows Returned: 3 1) informix.customer: INDEX PATH (1) Index Name: informix. 100_1 Index Keys: customer_num (Serial, fragments: ALL) Lower Index Filter: informix.customer.customer_num = 100 (2) Index Name: informix. 100_1 Index Keys: customer_num (Serial, fragments: ALL) Lower Index Filter: informix.customer.customer_num = 101 (3) Index Name: informix. 100_1 Index Keys: customer_num (Serial, fragments: ALL) Lower Index Filter: informix.customer.customer_num = 104 Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 customer type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 2 3 2 00:00.01 1 QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 16:12:56) ------ select * from customer where customer_num =100 or customer_num=101 orcustomer_num=104 Estimated Cost: 2 Estimated # of Rows Returned: 3 1) informix.customer: SEQUENTIAL SCAN Filters: ((informix.customer.customer_num = 100 OR informix.customer.customer_num = 101 ) OR informix.customer.customer_num = 104 ) Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 customer type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 2 3 28 00:00.00 3 Cordialement, Khaled Bentebal Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 09/03/11 17:55, Art Kagel a écrit : > Not true Kaled. Here is the same query both as an IN() and as OR's and the > optimizer used the identical indexed query path for both. Interestingly, it > calculated sightly different costs for the two versions, but identical > results. The optimizer MAY make different path decisions for IN(), OR and > UNION ALL, but it will not neccessarily do so: > > QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 11:50:44) > ------ > select * from files where create in ('2001-01-01 12:12:12', '2002-01-01 > 12:12:12', '2003-01-01 12:12:12')> > Estimated Cost: 165 > Estimated # of Rows Returned: 1147 > > 1) informix.files: INDEX PATH > > (1) Index Name: informix.files_create > > Index Keys: create (Serial, fragments: ALL) > > Lower Index Filter: informix.files.create = datetime(2001-01-01 > 12:12:12) year to second > > (2) Index Name: informix.files_create > > Index Keys: create (Serial, fragments: ALL) > > Lower Index Filter: informix.files.create = datetime(2002-01-01 > 12:12:12) year to second > > (3) Index Name: informix.files_create > > Index Keys: create (Serial, fragments: ALL) > > Lower Index Filter: informix.files.create = datetime(2003-01-01 > 12:12:12) year to second > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 files > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 0 1147 0 00:00.03 166 > > QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 11:51:15) > ------ > select * from files where create ='2001-01-01 12:12:12' or create> ='2002-01-01 12:12:12' or create ='2003-01-01 12:12:12' > > Estimated Cost: 171 > Estimated # of Rows Returned: 1147 > > 1) informix.files: INDEX PATH > > (1) Index Name: informix.files_create > > Index Keys: create (Serial, fragments: ALL) > > Lower Index Filter: informix.files.create = datetime(2001-01-01 > 12:12:12) year to second > > (2) Index Name: informix.files_create > > Index Keys: create (Serial, fragments: ALL) > > Lower Index Filter: informix.files.create = datetime(2002-01-01 > 12:12:12) year to second > > (3) Index Name: informix.files_create > > Index Keys: create (Serial, fragments: ALL) > > Lower Index Filter: informix.files.create = datetime(2003-01-01 > 12:12:12) year to second > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 files > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 0 1147 0 00:00.00 172 > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (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, the IIUG, nor any other > organization with which I am associated either explicitly, implicitly, or by > inference. Neither do those opinions reflect those of other individuals > affiliated with any entity with which I am affiliated nor those of the > entities themselves. > > On Wed, Mar 9, 2011 at 10:16 AM, Khaled Bentebal< > khaled.bentebal@consult-ix.fr> wrote: > >> It used to be the same thing . Not anymore. >> >> The*in* works better since the optimizer will choose an index if the column >> is >> indexed. >> >> With the*ORs*, the optimizer will be choose a sequential access. >> >> Try it with a set explain prior to executing the query. >> >> Khaled Bentebal >> >> Email: khaled.bentebal@consult-ix.fr >> Site Web: www.consult-ix.fr >> >> Le 09/03/11 15:58, VALéRIE TAESCH a écrit : >>> Hello, >>> >>> is there any performance difference between IN and OR in a sql request ? >>> >>> A/select * from table where id in (1,2,3) >>> >>> B/select * from table where id=1 or id=2 or id=3 >>> >>> thanks >>> >>> >>> >> > ******************************************************************************* >>> Forum Note: Use "Reply" to post a response in the discussion forum. >>> >>> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --20cf300fb283a1ab5b049e0f99ee > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

↪ replying to Khaled Bentebal

Khaled Bentebal — Wed, 09 Mar 2011 17:16:09 — source: IIUG Forums & Mailing Lists

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 = 100 (2) Index Keys: customer_num (Serial, fragments: ALL) Lower Index Filter: informix.customer.customer_num = 101 (3) Index Keys: customer_num (Serial, fragments: ALL) Lower Index Filter: informix.customer.customer_num = 104 Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 customer type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 2 2 2 00:00.02 1 QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 18:11:46) ------ select * from customer where customer_num = 100 or customer_num=101 orcustomer_num= 104 Estimated Cost: 3 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 = 100 (2) Index Keys: customer_num (Serial, fragments: ALL) Lower Index Filter: informix.customer.customer_num = 101 (3) Index Keys: customer_num (Serial, fragments: ALL) Lower Index Filter: informix.customer.customer_num = 104 Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 customer type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 2 2 2 00:00.00 3 Khaled Bentebal Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 09/03/11 17:55, Art Kagel a écrit : > Not true Kaled. Here is the same query both as an IN() and as OR's > and the optimizer used the identical indexed query path for both. > Interestingly, it calculated sightly different costs for the two > versions, but identical results. The optimizer MAY make different > path decisions for IN(), OR and UNION ALL, but it will not > neccessarily do so: > > > > QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 11:50:44) > ------ > select * from files where create in ('2001-01-01 12:12:12', > '2002-01-01 12:12:12', '2003-01-01 12:12:12')> > Estimated Cost: 165 > Estimated # of Rows Returned: 1147 > > 1) informix.files: INDEX PATH > > (1) Index Name: informix.files_create > Index Keys: create (Serial, fragments: ALL) > Lower Index Filter: informix.files.create = > datetime(2001-01-01 12:12:12) year to second > > (2) Index Name: informix.files_create > Index Keys: create (Serial, fragments: ALL) > Lower Index Filter: informix.files.create = > datetime(2002-01-01 12:12:12) year to second > > (3) Index Name: informix.files_create > Index Keys: create (Serial, fragments: ALL) > Lower Index Filter: informix.files.create = > datetime(2003-01-01 12:12:12) year to second > > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 files > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 0 1147 0 00:00.03 166 > > > QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 11:51:15) > ------ > select * from files where create ='2001-01-01 12:12:12' or create> ='2002-01-01 12:12:12' or create ='2003-01-01 12:12:12' > > Estimated Cost: 171 > Estimated # of Rows Returned: 1147 > > 1) informix.files: INDEX PATH > > (1) Index Name: informix.files_create > Index Keys: create (Serial, fragments: ALL) > Lower Index Filter: informix.files.create = > datetime(2001-01-01 12:12:12) year to second > > (2) Index Name: informix.files_create > Index Keys: create (Serial, fragments: ALL) > Lower Index Filter: informix.files.create = > datetime(2002-01-01 12:12:12) year to second > > (3) Index Name: informix.files_create > Index Keys: create (Serial, fragments: ALL) > Lower Index Filter: informix.files.create = > datetime(2003-01-01 12:12:12) year to second > > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 files > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 0 1147 0 00:00.00 172 > > 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, the > IIUG, nor any other organization with which I am associated either > explicitly, implicitly, or by inference. Neither do those opinions > reflect those of other individuals affiliated with any entity with > which I am affiliated nor those of the entities themselves. > > > > On Wed, Mar 9, 2011 at 10:16 AM, Khaled Bentebal > <khaled.bentebal@consult-ix.fr <mailto:khaled.bentebal@consult-ix.fr>> > wrote: > > It used to be the same thing . Not anymore. > > The*in* works better since the optimizer will choose an index if > the column is > indexed. > > With the*ORs*, the optimizer will be choose a sequential access. > > Try it with a set explain prior to executing the query. > > Khaled Bentebal > > Email: khaled.bentebal@consult-ix.fr > <mailto:khaled.bentebal@consult-ix.fr> > Site Web: www.consult-ix.fr <http://www.consult-ix.fr>; > > Le 09/03/11 15:58, VALéRIE TAESCH a écrit : > > Hello, > > > > is there any performance difference between IN and OR in a sql > request ? > > > > A/select * from table where id in (1,2,3) > > > > B/select * from table where id=1 or id=2 or id=3 > > > > thanks > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

↪ replying to Khaled Bentebal

Art Kagel — Wed, 09 Mar 2011 17:22:35 — source: IIUG Forums & Mailing Lists

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) IIUG Board of Directors (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, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with 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> 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 = 100 > > (2) Index Keys: customer_num (Serial, fragments: ALL) > Lower Index Filter: informix.customer.customer_num = 101 > > (3) Index Keys: customer_num (Serial, fragments: ALL) > Lower Index Filter: informix.customer.customer_num = 104 > > > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 customer > > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 2 2 2 00:00.02 1 > > QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 18:11:46) > ------ > select * from customer where customer_num = 100 or customer_num=101 or> customer_num= 104 > > Estimated Cost: 3 > 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 = 100 > > (2) Index Keys: customer_num (Serial, fragments: ALL) > Lower Index Filter: informix.customer.customer_num = 101 > > (3) Index Keys: customer_num (Serial, fragments: ALL) > Lower Index Filter: informix.customer.customer_num = 104 > > > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 customer > > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 2 2 2 00:00.00 3 > > > > Khaled Bentebal > > Email: khaled.bentebal@consult-ix.fr > Site Web: www.consult-ix.fr > > > Le 09/03/11 17:55, Art Kagel a écrit : > > Not true Kaled. Here is the same query both as an IN() and as OR's and the > optimizer used the identical indexed query path for both. Interestingly, it > calculated sightly different costs for the two versions, but identical > results. The optimizer MAY make different path decisions for IN(), OR and > UNION ALL, but it will not neccessarily do so: > > > > QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 11:50:44) > ------ > select * from files where create in ('2001-01-01 12:12:12', '2002-01-01 > 12:12:12', '2003-01-01 12:12:12')> > Estimated Cost: 165 > Estimated # of Rows Returned: 1147 > > 1) informix.files: INDEX PATH > > (1) Index Name: informix.files_create > Index Keys: create (Serial, fragments: ALL) > Lower Index Filter: informix.files.create = datetime(2001-01-01 > 12:12:12) year to second > > (2) Index Name: informix.files_create > Index Keys: create (Serial, fragments: ALL) > Lower Index Filter: informix.files.create = datetime(2002-01-01 > 12:12:12) year to second > > (3) Index Name: informix.files_create > Index Keys: create (Serial, fragments: ALL) > Lower Index Filter: informix.files.create = datetime(2003-01-01 > 12:12:12) year to second > > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 files > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 0 1147 0 00:00.03 166 > > > QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 11:51:15) > ------ > select * from files where create ='2001-01-01 12:12:12' or create> ='2002-01-01 12:12:12' or create ='2003-01-01 12:12:12' > > Estimated Cost: 171 > Estimated # of Rows Returned: 1147 > > 1) informix.files: INDEX PATH > > (1) Index Name: informix.files_create > Index Keys: create (Serial, fragments: ALL) > Lower Index Filter: informix.files.create = datetime(2001-01-01 > 12:12:12) year to second > > (2) Index Name: informix.files_create > Index Keys: create (Serial, fragments: ALL) > Lower Index Filter: informix.files.create = datetime(2002-01-01 > 12:12:12) year to second > > (3) Index Name: informix.files_create > Index Keys: create (Serial, fragments: ALL) > Lower Index Filter: informix.files.create = datetime(2003-01-01 > 12:12:12) year to second > > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 files > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 0 1147 0 00:00.00 172 > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (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, the IIUG, nor any > other organization with which I am associated either explicitly, implicitly, > or by inference. Neither do those opinions reflect those of other > individuals affiliated with any entity with which I am affiliated nor those > of the entities themselves. > > > > On Wed, Mar 9, 2011 at 10:16 AM, Khaled Bentebal < > khaled.bentebal@consult-ix.fr> wrote: > >> It used to be the same thing . Not anymore. >> >> The*in* works better since the optimizer will choose an index if the >> column is >> indexed. >> >> With the*ORs*, the optimizer will be choose a sequential access. >> >> Try it with a set explain prior to executing the query. >> >> Khaled Bentebal >> >> Email: khaled.bentebal@consult-ix.fr >> Site Web: www.consult-ix.fr >> >> Le 09/03/11 15:58, VALéRIE TAESCH a écrit : >> > Hello, >> > >> > is there any performance difference between IN and OR in a sql request ? >> > >> > A/select * from

↪ replying to Khaled Bentebal

Khaled Bentebal — Wed, 09 Mar 2011 17:29:51 — source: IIUG Forums & Mailing Lists

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 this version. Khaled Bentebal Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 09/03/11 18:21, Art Kagel a écrit : > 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, the > IIUG, nor any other organization with which I am associated either > explicitly, implicitly, or by inference. Neither do those opinions > reflect those of other individuals affiliated with any entity with > 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 = 100 > > (2) Index Keys: customer_num (Serial, fragments: ALL) > Lower Index Filter: informix.customer.customer_num = 101 > > (3) Index Keys: customer_num (Serial, fragments: ALL) > Lower Index Filter: informix.customer.customer_num = 104 > > > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 customer > > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 2 2 2 00:00.02 1 > > QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 18:11:46) > ------ > select * from customer where customer_num = 100 or> customer_num=101 or customer_num= 104 > > Estimated Cost: 3 > 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 = 100 > > (2) Index Keys: customer_num (Serial, fragments: ALL) > Lower Index Filter: informix.customer.customer_num = 101 > > (3) Index Keys: customer_num (Serial, fragments: ALL) > Lower Index Filter: informix.customer.customer_num = 104 > > > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 customer > > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 2 2 2 00:00.00 3 > > > > Khaled Bentebal > > Email:khaled.bentebal@consult-ix.fr <mailto:khaled.bentebal@consult-ix.fr> > Site Web:www.consult-ix.fr <http://www.consult-ix.fr>; > > > Le 09/03/11 17:55, Art Kagel a écrit : >> Not true Kaled. Here is the same query both as an IN() and as >> OR's and the optimizer used the identical indexed query path for >> both. Interestingly, it calculated sightly different costs for >> the two versions, but identical results. The optimizer MAY make >> different path decisions for IN(), OR and UNION ALL, but it will >> not neccessarily do so: >> >> >> >> QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 11:50:44) >> ------ >> select * from files where create in ('2001-01-01 12:12:12', >> '2002-01-01 12:12:12', '2003-01-01 12:12:12')>> >> Estimated Cost: 165 >> Estimated # of Rows Returned: 1147 >> >> 1) informix.files: INDEX PATH >> >> (1) Index Name: informix.files_create >> Index Keys: create (Serial, fragments: ALL) >> Lower Index Filter: informix.files.create = >> datetime(2001-01-01 12:12:12) year to second >> >> (2) Index Name: informix.files_create >> Index Keys: create (Serial, fragments: ALL) >> Lower Index Filter: informix.files.create = >> datetime(2002-01-01 12:12:12) year to second >> >> (3) Index Name: informix.files_create >> Index Keys: create (Serial, fragments: ALL) >> Lower Index Filter: informix.files.create = >> datetime(2003-01-01 12:12:12) year to second >> >> >> Query statistics: >> ----------------- >> >> Table map : >> ---------------------------- >> Internal name Table name >> ---------------------------- >> t1 files >> >> type table rows_prod est_rows rows_scan time est_cost >> ------------------------------------------------------------------- >> scan t1 0 1147 0 00:00.03 166 >> >> >> QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 11:51:15) >> ------ >> select * from files where create ='2001-01-01 12:12:12' or create>> ='2002-01-01 12:12:12' or create ='2003-01-01 12:12:12' >> >> Estimated Cost: 171 >> Estimated # of Rows Returned: 1147 >> >> 1) informix.files: INDEX PATH >> >> (1) Index Name: informix.files_create >> Index Keys: create (Serial, fragments: ALL) >> Lower Index Filter: informix.files.create = >> datetime(2001-01-01 12:12:12) year to second >> >> (2) Index Name: informix.files_create >> Index Keys: create (Serial, fragments: ALL) >> Lower Index Filter: informix.files.create = >> datetime(2002-01-01 12:12:12) year to second >> >> (3) Index Name: informix.files_create >> Index Keys: create (Serial, fragments: ALL) >> Lower Index Filter: informix.files.create = >> datetime(2003-01-01 12:12:12) year to second >> >> >> Query statistics: >> ----------------- >> >> Table map : >> ---------------------------- >> Internal name Table name >> ---------------------------- >> t1 files >> >> type table rows_prod est_rows rows_scan time est_cost >> ------------------------------------------------------------------- >> scan t1 0 1147 0 00:00.00 172 >> >> 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, >> the IIUG, nor any other organization with which I am associated >> either explicitly, implicitly, or by inference. Neither do those >> opinions reflect those of other individuals affiliated with any >> entity with which I am affiliated nor those of the entities >> themselves. >> >> >> >> On Wed, Mar 9, 201

↪ replying to Khaled Bentebal

Fernando Nunes — Wed, 09 Mar 2011 18:54:00 — source: IIUG Forums & Mailing Lists

select * from customer where customer_num in (100,101, 104); select * from customer where customer_num =100 or customer_num=101 orcustomer_num=104; 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 this version. > > Khaled Bentebal > > Email: khaled.bentebal@consult-ix.fr > Site Web: www.consult-ix.fr > > Le 09/03/11 18:21, Art Kagel a écrit : > > 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, the > > IIUG, nor any other organization with which I am associated either > > explicitly, implicitly, or by inference. Neither do those opinions > > reflect those of other individuals affiliated with any entity with > > 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 = 100 > > > > (2) Index Keys: customer_num (Serial, fragments: ALL) > > Lower Index Filter: informix.customer.customer_num = 101 > > > > (3) Index Keys: customer_num (Serial, fragments: ALL) > > Lower Index Filter: informix.customer.customer_num = 104 > > > > > > > > Query statistics: > > ----------------- > > > > Table map : > > ---------------------------- > > Internal name Table name > > ---------------------------- > > t1 customer > > > > > > type table rows_prod est_rows rows_scan time est_cost > > ------------------------------------------------------------------- > > scan t1 2 2 2 00:00.02 1 > > > > QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 18:11:46) > > ------ > > select * from customer where customer_num = 100 or> > customer_num=101 or customer_num= 104 > > > > Estimated Cost: 3 > > 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 = 100 > > > > (2) Index Keys: customer_num (Serial, fragments: ALL) > > Lower Index Filter: informix.customer.customer_num = 101 > > > > (3) Index Keys: customer_num (Serial, fragments: ALL) > > Lower Index Filter: informix.customer.customer_num = 104 > > > > > > > > Query statistics: > > ----------------- > > > > Table map : > > ---------------------------- > > Internal name Table name > > ---------------------------- > > t1 customer > > > > > > type table rows_prod est_rows rows_scan time est_cost > > ------------------------------------------------------------------- > > scan t1 2 2 2 00:00.00 3 > > > > > > > > Khaled Bentebal > > > > Email:khaled.bentebal@consult-ix.fr <mailto: > khaled.bentebal@consult-ix.fr> > > Site Web:www.consult-ix.fr <http://www.consult-ix.fr>; > > > > > > Le 09/03/11 17:55, Art Kagel a écrit : > >> Not true Kaled. Here is the same query both as an IN() and as > >> OR's and the optimizer used the identical indexed query path for > >> both. Interestingly, it calculated sightly different costs for > >> the two versions, but identical results. The optimizer MAY make > >> different path decisions for IN(), OR and UNION ALL, but it will > >> not neccessarily do so: > >> > >> > >> > >> QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 11:50:44) > >> ------ > >> select * from files where create in ('2001-01-01 12:12:12', > >> '2002-01-01 12:12:12', '2003-01-01 12:12:12')> >> > >> Estimated Cost: 165 > >> Estimated # of Rows Returned: 1147 > >> > >> 1) informix.files: INDEX PATH > >> > >> (1) Index Name: informix.files_create > >> Index Keys: create (Serial, fragments: ALL) > >> Lower Index Filter: informix.files.create = > >> datetime(2001-01-01 12:12:12) year to second > >> > >> (2) Index Name: informix.files_create > >> Index Keys: create (Serial, fragments: ALL) > >> Lower Index Filter: informix.files.create = > >> datetime(2002-01-01 12:12:12) year to second > >> > >> (3) Index Name: informix.files_create > >> Index Keys: create (Serial, fragments: ALL) > >> Lower Index Filter: informix.files.create = > >> datetime(2003-01-01 12:12:12) year to second > >> > >> > >> Query statistics: > >> ----------------- > >> > >> Table map : > >> ---------------------------- > >> Internal name Table name > >> ---------------------------- > >> t1 files > >> > >> type table rows_prod est_rows rows_scan time est_cost > >> ------------------------------------------------------------------- > >> scan t1 0 1147 0 00:00.03 166 > >> > >> > >> QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 11:51:15) > >> ------ > >> select * from files where create ='2001-01-01 12:12:12' or create> >> ='2002-01-01 12:12:12' or create ='2003-01-01 12:12:12' > >> > >> Estimated Cost: 171 > >> Estimated # of Rows Returned: 1147 > >> > >> 1) informix.files: INDEX PATH > >> > >> (1) Index Name: informix.files_create > >> Index Keys: create (Serial, fragments: ALL) > >> Lower Index Filter: informix.files.create = > >> datetime(2001-01-01 12:12:12) year to second > >> > >> (2) Index Name: informix.files_create > >> Index Keys: create (Serial, fragments: ALL) > >> Lower Index Filter: informix.files.create = > >> datetime(2002-01-01 12:12:12) year to second > >> > >> (3) Index Name: informix.files_create > >> Index Keys: create (Serial, fragments: ALL) > >> Lower Index Filter: informix.files.create = > >> datetime(2003-01-01 12:12:12) year to second > >> > >> > >> Query statistics: > >> ----------------- > >> > >> Table map : > >> ---------------------------- > >> Internal name Table name > >> ---------------------------- > >> t1 files > >> > >> type table rows_prod est_rows rows_scan time est_cost > >> --------------------

↪ replying to Fernando Nunes

John Miller iii — Wed, 09 Mar 2011 19:15:02 — source: IIUG Forums & Mailing Lists

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 =3D100 or customer_num=3D10=1 or > customer_num=3D104; > > 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 =E9crit : > > > 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= he > > > IIUG, nor any other organization with which I am associated eithe= r > > > explicitly, implicitly, or by inference. Neither do those opinion= s > > > reflect those of other individuals affiliated with any entity wit= 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 =3D 100 > > > > > > (2) Index Keys: customer_num (Serial, fragments: ALL) > > > Lower Index Filter: informix.customer.customer_num =3D 101 > > > > > > (3) Index Keys: customer_num (Serial, fragments: ALL) > > > Lower Index Filter: informix.customer.customer_num =3D 104 > > > > > > > > > > > > Query statistics: > > > ----------------- > > > > > > Table map : > > > ---------------------------- > > > Internal name Table name > > > ---------------------------- > > > t1 customer > > > > > > > > > type table rows_prod est_rows rows_scan time est_cost > > > -----------------------------------------------------------------= -- > > > scan t1 2 2 2 00:00.02 1 > > > > > > QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 18:11:46) > > > ------ > > > select * from customer where customer_num =3D 100 or> > > customer_num=3D101 or customer_num=3D 104 > > > > > > Estimated Cost: 3 > > > 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 =3D 100 > > > > > > (2) Index Keys: customer_num (Serial, fragments: ALL) > > > Lower Index Filter: informix.customer.customer_num =3D 101 > > > > > > (3) Index Keys: customer_num (Serial, fragments: ALL) > > > Lower Index Filter: informix.customer.customer_num =3D 104 > > > > > > > > > > > > Query statistics: > > > ----------------- > > > > > > Table map : > > > ---------------------------- > > > Internal name Table name > > > ---------------------------- > > > t1 customer > > > > > > > > > type table rows_prod est_rows rows_scan time est_cost > > > -----------------------------------------------------------------= -- > > > scan t1 2 2 2 00:00.00 3 > > > > > > > > > > > > Khaled Bentebal > > > > > > Email:khaled.bentebal@consult-ix.fr <mailto: > > khaled.bentebal@consult-ix.fr> > > > Site Web:www.consult-ix.fr <http://www.consult-ix.fr>; > > > > > > > > > Le 09/03/11 17:55, Art Kagel a =E9crit : > > >> Not true Kaled. Here is the same query both as an IN() and as > > >> OR's and the optimizer used the identical indexed query path for= > > >> both. Interestingly, it calculated sightly different costs for > > >> the two versions, but identical results. The optimizer MAY make > > >> different path decisions for IN(), OR and UNION ALL, but it will= > > >> not neccessarily do so: > > >> > > >> > > >> > > >> QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 11:50:44) > > >> ------ > > >> select * from files where create in ('2001-01-01 12:12:12', > > >> '2002-01-01 12:12:12', '2003-01-01 12:12:12')> > >> > > >> Estimated Cost: 165 > > >> Estimated # of Rows Returned: 1147 > > >> > > >> 1) informix.files: INDEX PATH > > >> > > >> (1) Index Name: informix.files_create > > >> Index Keys: create (Serial, fragments: ALL) > > >> Lower Index Filter: informix.files.create =3D > > >> datetime(2001-01-01 12:12:12) year to second > > >> > > >> (2) Index Name: informix.files_create > > >> Index Keys: create (Serial, fragments: ALL) > > >> Lower Index Filter: informix.files.create =3D > > >> datetime(2002-01-01 12:12:12) year to second > > >> > > >> (3) Index Name: informix.files_create > > >> Index Keys: create (Serial, fragments: ALL) > > >> Lower Index Filter: informix.files.create =3D > > >> datetime(2003-01-01 12:12:12) year to second > > >> > > >> > > >> Query statistics: > > >> ----------------- > > >> > > >> Table map : > > >> ---------------------------- > > >> Internal name Table name > > >> ---------------------------- > > >> t1 files > > >> > > >> type table rows_prod est_rows rows_scan time est_cost > > >> ----------------------------------------------------------------= --- > > >> scan t1 0 1147 0 00:00.03 166 > > >> > > >> > > >> QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 11:51:15) > > >> ------ > > >> select * from files where create =3D'2001-01-01 12:12:12' or cre=ate > > >> =3D'2002-01-01 12:12:12' or create =3D'2003-01-01 12:12:12' > > >> > > >> Estimated Cost: 171 > > >> Estimated # of

↪ replying to John Miller iii

Khaled Bentebal — Wed, 09 Mar 2011 19:27:33 — source: IIUG Forums & Mailing Lists

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. The 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 écrit : > 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 =3D100 or customer_num=3D10=> 1 or >> customer_num=3D104; >> >> 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 =E9crit : >>>> 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= > he >>>> IIUG, nor any other organization with which I am associated eithe= > r >>>> explicitly, implicitly, or by inference. Neither do those opinion= > s >>>> reflect those of other individuals affiliated with any entity wit= > 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 =3D 100 >>>> >>>> (2) Index Keys: customer_num (Serial, fragments: ALL) >>>> Lower Index Filter: informix.customer.customer_num =3D 101 >>>> >>>> (3) Index Keys: customer_num (Serial, fragments: ALL) >>>> Lower Index Filter: informix.customer.customer_num =3D 104 >>>> >>>> >>>> >>>> Query statistics: >>>> ----------------- >>>> >>>> Table map : >>>> ---------------------------- >>>> Internal name Table name >>>> ---------------------------- >>>> t1 customer >>>> >>>> >>>> type table rows_prod est_rows rows_scan time est_cost >>>> -----------------------------------------------------------------=

↪ replying to Khaled Bentebal

John Miller iii — Wed, 09 Mar 2011 19:59:07 — source: IIUG Forums & Mailing Lists

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.= >=

↪ replying to Khaled Bentebal

Art Kagel — Wed, 09 Mar 2011 20:37:08 — source: IIUG Forums & Mailing Lists

How many row are in the customer table total? In my files table there are 1,448,507 rows so the cost of a sequential scan is very much greater than the cost of the index lookup. But, the real point is, as I said, YMMV, you have to test every version of a particular query, no hard rules apply. The optimizer can often do a better job with one version of a query than with another. Over 25 years ago, I coined what I call "Kagel's First Law of SQL" which says that "ANY SQL select can be rewritten at least four different ways". In 25 years no one has ever shown me a SELECT that I could not rewrite in multiple significantly different ways to produce the identical results. The "First Correllary to Kagel's First Law" states: "If you haven't tested them all, you may not be using the best version." Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (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, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Mar 9, 2011 at 12:04 PM, Khaled Bentebal < khaled.bentebal@consult-ix.fr> wrote: > Well, this is what I always adviced. IN equivalent to ORs since the INs > were translated to ORs. > > The following example shows things differently. > > Check this out on IDS 11.70.FC1 using the customer table: > > QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 16:12:07) > ------ > select * from customer where customer_num in (100,101, 104)> > Estimated Cost: 1 > Estimated # of Rows Returned: 3 > > 1) informix.customer: INDEX PATH > > (1) Index Name: informix. 100_1 > > Index Keys: customer_num (Serial, fragments: ALL) > > Lower Index Filter: informix.customer.customer_num = 100 > > (2) Index Name: informix. 100_1 > > Index Keys: customer_num (Serial, fragments: ALL) > > Lower Index Filter: informix.customer.customer_num = 101 > > (3) Index Name: informix. 100_1 > > Index Keys: customer_num (Serial, fragments: ALL) > > Lower Index Filter: informix.customer.customer_num = 104 > > Query statistics: > ----------------- > > Table map : > > ---------------------------- > > Internal name Table name > > ---------------------------- > > t1 customer > > type table rows_prod est_rows rows_scan time est_cost > > ------------------------------------------------------------------- > > scan t1 2 3 2 00:00.01 1 > > QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 16:12:56) > ------ > select * from customer where customer_num =100 or customer_num=101 or> customer_num=104 > > Estimated Cost: 2 > Estimated # of Rows Returned: 3 > > 1) informix.customer: SEQUENTIAL SCAN > > Filters: ((informix.customer.customer_num = 100 OR > informix.customer.customer_num = 101 ) OR informix.customer.customer_num > = 104 ) > > Query statistics: > ----------------- > > Table map : > > ---------------------------- > > Internal name Table name > > ---------------------------- > > t1 customer > > type table rows_prod est_rows rows_scan time est_cost > > ------------------------------------------------------------------- > > scan t1 2 3 28 00:00.00 3 > > Cordialement, > > Khaled Bentebal > > Email: khaled.bentebal@consult-ix.fr > Site Web: www.consult-ix.fr > > Le 09/03/11 17:55, Art Kagel a écrit : > > Not true Kaled. Here is the same query both as an IN() and as OR's and > the > > optimizer used the identical indexed query path for both. Interestingly, > it > > calculated sightly different costs for the two versions, but identical > > results. The optimizer MAY make different path decisions for IN(), OR and > > UNION ALL, but it will not neccessarily do so: > > > > QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 11:50:44) > > ------ > > select * from files where create in ('2001-01-01 12:12:12', '2002-01-01 > > 12:12:12', '2003-01-01 12:12:12')> > > > Estimated Cost: 165 > > Estimated # of Rows Returned: 1147 > > > > 1) informix.files: INDEX PATH > > > > (1) Index Name: informix.files_create > > > > Index Keys: create (Serial, fragments: ALL) > > > > Lower Index Filter: informix.files.create = datetime(2001-01-01 > > 12:12:12) year to second > > > > (2) Index Name: informix.files_create > > > > Index Keys: create (Serial, fragments: ALL) > > > > Lower Index Filter: informix.files.create = datetime(2002-01-01 > > 12:12:12) year to second > > > > (3) Index Name: informix.files_create > > > > Index Keys: create (Serial, fragments: ALL) > > > > Lower Index Filter: informix.files.create = datetime(2003-01-01 > > 12:12:12) year to second > > > > Query statistics: > > ----------------- > > > > Table map : > > ---------------------------- > > Internal name Table name > > ---------------------------- > > t1 files > > > > type table rows_prod est_rows rows_scan time est_cost > > ------------------------------------------------------------------- > > scan t1 0 1147 0 00:00.03 166 > > > > QUERY: (OPTIMIZATION TIMESTAMP: 03-09-2011 11:51:15) > > ------ > > select * from files where create ='2001-01-01 12:12:12' or create> > ='2002-01-01 12:12:12' or create ='2003-01-01 12:12:12' > > > > Estimated Cost: 171 > > Estimated # of Rows Returned: 1147 > > > > 1) informix.files: INDEX PATH > > > > (1) Index Name: informix.files_create > > > > Index Keys: create (Serial, fragments: ALL) > > > > Lower Index Filter: informix.files.create = datetime(2001-01-01 > > 12:12:12) year to second > > > > (2) Index Name: informix.files_create > > > > Index Keys: create (Serial, fragments: ALL) > > > > Lower Index Filter: informix.files.create = datetime(2002-01-01 > > 12:12:12) year to second > > > > (3) Index Name: informix.files_create > > > > Index Keys: create (Serial, fragments: ALL) > > > > Lower Index Filter: informix.files.create = datetime(2003-01-01 > > 12:12:12) year to second > > > > Query statistics: > > ----------------- > > > > Table map : > > ---------------------------- > > Internal name Table name > > ---------------------------- > > t1 files > > > > type table rows_prod est_rows rows_scan time est_cost > > ------------------------------------------------------------------- > > scan t1 0 1147 0 00:00.00 172 > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > IIUG Board of Directors (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, the IIUG, nor any > other > > organization with

↪ replying to John Miller iii

Fernando Nunes — Wed, 09 Mar 2011 22:35:24 — source: IIUG Forums & Mailing Lists

Bingo... Just create a dbspace with 4K and the ORs start using a full scan. Everything about a full scan being better is ok... BUT: Why the different query plan between OR and IN? Honestly I can defend that one is better than the other. But I don't have a clue why the OR is using a sequential scan and the IN an index scan... In abstract, you would think that the OR could be using different columns for the conditions and then it would really be better a sequential scan. In this particular case it isn't... Maybe the optimizer is assuming that an OR would not be used where a IN could (assuming the programmer would use the easier way)... :) I will not change my recommendation: OR and IN should be the same... nevertheless I believe I've seen situations that challenge this... Regards. On Wed, Mar 9, 2011 at 7:59 PM, John Miller iii <miller3@us.ibm.com> wrote: > 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 No

↪ replying to VALéRIE TAESCH

DAVE GRIFFEN — Thu, 10 Mar 2011 16:56:36 — source: IIUG Forums & Mailing Lists

VALéRIE TAESCH WROTE: ------------------------------------------------------------------------------- is there any performance difference between IN and OR in a sql request ? A/select * from table where id in (1,2,3) B/select * from table where id=1 or id=2 or id=3 ------------------------------------------------------------------------------- Response: Pathing and performance is often the same between IN and OR. Although sometimes there can be huge performance differences. As Art has stated, there is no hard and fast rule to which way will be better. Always test your options. Below is an example we ran into a couple weeks ago. In this case an additional column is included in the criteria and the relevant index. The IN clause led to sequential scan of a 138 million row / 27 GB table. The OR clause utilized an index which quickly returned 290 rows currently matching the criteria. Although, we are going with union all since I believe it has a lower chance to tip to a sequential scan in this situation. Dave Griffen QUERY: (OPTIMIZATION TIMESTAMP: 03-10-2011 09:51:05) ------ select * from payments where reconcile_date is null and pay_type in ('VISA','MASTERCARD','DISCOVER','AMEX','BML','DEBITCARD') Estimated Cost: 17988290 Estimated # of Rows Returned: 28075460 1) informix.payments: SEQUENTIAL SCAN Filters: (informix.payments.pay_type IN ('VISA' , 'MASTERCARD' , 'DISCOVER' , 'AMEX' , 'BML' , 'DEBITCARD' )AND informix.payments.reconcile_date IS NULL ) Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 payments type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 0 28075460 317430 00:00.00 17988290 QUERY: (OPTIMIZATION TIMESTAMP: 03-10-2011 09:52:24) ------ select * from payments where reconcile_date is null and (pay_type = 'VISA' or pay_type = 'MASTERCARD' or pay_type = 'DISCOVER' or pay_type = 'AMEX' or pay_type = 'BML' or pay_type = 'DEBITCARD') Estimated Cost: 15601960 Estimated # of Rows Returned: 28075460 1) informix.payments: INDEX PATH (1) Index Name: informix.i5_payments Index Keys: reconcile_date pay_type settlement_date locnum (Serial, fragments: ALL) Lower Index Filter: (informix.payments.reconcile_date IS NULL AND informix.payments.pay_type = 'VISA' ) (2) Index Name: informix.i5_payments Index Keys: reconcile_date pay_type settlement_date locnum (Serial, fragments: ALL) Lower Index Filter: (informix.payments.reconcile_date IS NULL AND informix.payments.pay_type = 'MASTERCARD' ) (3) Index Name: informix.i5_payments Index Keys: reconcile_date pay_type settlement_date locnum (Serial, fragments: ALL) Lower Index Filter: (informix.payments.reconcile_date IS NULL AND informix.payments.pay_type = 'DISCOVER' ) (4) Index Name: informix.i5_payments Index Keys: reconcile_date pay_type settlement_date locnum (Serial, fragments: ALL) Lower Index Filter: (informix.payments.reconcile_date IS NULL AND informix.payments.pay_type = 'AMEX' ) (5) Index Name: informix.i5_payments Index Keys: reconcile_date pay_type settlement_date locnum (Serial, fragments: ALL) Lower Index Filter: (informix.payments.reconcile_date IS NULL AND informix.payments.pay_type = 'BML' ) (6) Index Name: informix.i5_payments Index Keys: reconcile_date pay_type settlement_date locnum (Serial, fragments: ALL) Lower Index Filter: (informix.payments.reconcile_date IS NULL AND informix.payments.pay_type = 'DEBITCARD' ) Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 payments type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 268 28075460 268 00:01.04 15601960

↪ replying to DAVE GRIFFEN

Art Kagel — Thu, 10 Mar 2011 18:34:49 — source: IIUG Forums & Mailing Lists

The big difference between actual and estimated rows (268 actual versus 28075460 estimated) is likely the problem. It indicates that your data distributions are not up-to-date. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (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, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Mar 10, 2011 at 11:56 AM, DAVE GRIFFEN <dgriffen@finishline.com>wrote: > VALéRIE TAESCH WROTE: > > > ------------------------------------------------------------------------------- > is there any performance difference between IN and OR in a sql request ? > > A/select * from table where id in (1,2,3) > > B/select * from table where id=1 or id=2 or id=3 > > > ------------------------------------------------------------------------------- > > Response: > Pathing and performance is often the same between IN and OR. Although > sometimes there can be huge performance differences. As Art has stated, > there > is no hard and fast rule to which way will be better. Always test your > options. > > Below is an example we ran into a couple weeks ago. In this case an > additional > column is included in the criteria and the relevant index. The IN clause > led > to sequential scan of a 138 million row / 27 GB table. The OR clause > utilized > an index which quickly returned 290 rows currently matching the criteria. > Although, we are going with union all since I believe it has a lower chance > to > tip to a sequential scan in this situation. > > Dave Griffen > > QUERY: (OPTIMIZATION TIMESTAMP: 03-10-2011 09:51:05) > ------ > select * > from payments > where reconcile_date is null > and pay_type in ('VISA','MASTERCARD','DISCOVER','AMEX','BML','DEBITCARD')> > Estimated Cost: 17988290 > Estimated # of Rows Returned: 28075460 > > 1) informix.payments: SEQUENTIAL SCAN > > Filters: (informix.payments.pay_type IN ('VISA' , 'MASTERCARD' , 'DISCOVER' > , > 'AMEX' , 'BML' , 'DEBITCARD' )AND informix.payments.reconcile_date IS NULL > ) > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 payments > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 0 28075460 317430 00:00.00 17988290 > > QUERY: (OPTIMIZATION TIMESTAMP: 03-10-2011 09:52:24) > ------ > select * > from payments > where reconcile_date is null > and (pay_type = 'VISA' > or pay_type = 'MASTERCARD' > or pay_type = 'DISCOVER' > or pay_type = 'AMEX' > or pay_type = 'BML' > or pay_type = 'DEBITCARD')> > Estimated Cost: 15601960 > Estimated # of Rows Returned: 28075460 > > 1) informix.payments: INDEX PATH > > (1) Index Name: informix.i5_payments > > Index Keys: reconcile_date pay_type settlement_date locnum (Serial, > fragments: > ALL) > > Lower Index Filter: (informix.payments.reconcile_date IS NULL AND > informix.payments.pay_type = 'VISA' ) > > (2) Index Name: informix.i5_payments > > Index Keys: reconcile_date pay_type settlement_date locnum (Serial, > fragments: > ALL) > > Lower Index Filter: (informix.payments.reconcile_date IS NULL AND > informix.payments.pay_type = 'MASTERCARD' ) > > (3) Index Name: informix.i5_payments > > Index Keys: reconcile_date pay_type settlement_date locnum (Serial, > fragments: > ALL) > > Lower Index Filter: (informix.payments.reconcile_date IS NULL AND > informix.payments.pay_type = 'DISCOVER' ) > > (4) Index Name: informix.i5_payments > > Index Keys: reconcile_date pay_type settlement_date locnum (Serial, > fragments: > ALL) > > Lower Index Filter: (informix.payments.reconcile_date IS NULL AND > informix.payments.pay_type = 'AMEX' ) > > (5) Index Name: informix.i5_payments > > Index Keys: reconcile_date pay_type settlement_date locnum (Serial, > fragments: > ALL) > > Lower Index Filter: (informix.payments.reconcile_date IS NULL AND > informix.payments.pay_type = 'BML' ) > > (6) Index Name: informix.i5_payments > > Index Keys: reconcile_date pay_type settlement_date locnum (Serial, > fragments: > ALL) > > Lower Index Filter: (informix.payments.reconcile_date IS NULL AND > informix.payments.pay_type = 'DEBITCARD' ) > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 payments > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 268 28075460 268 00:01.04 15601960 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015175ca98a1b87c8049e251914

↪ replying to Art Kagel

DAVE GRIFFEN — Thu, 10 Mar 2011 19:26:17 — source: IIUG Forums & Mailing Lists

Art Kagel WROTE: ------------------------------------------------------------------------------- The big difference between actual and estimated rows (268 actual versus 28075460 estimated) is likely the problem. It indicates that your data distributions are not up-to-date. Art ------------------------------------------------------------------------------- Response: Actually, statistics on these columns are fully up to date. Around 60% of records in the table have a null value for reconcile_date. However, nearly all of those records have a pay_type different than those listed. And although the pay_types listed combine to represent nearly 40% of records in the table, all but a small handful have been previously reconciled. When the optimizer looks at the individual column statistics, neither the reconcile_date specified, nor the aggregate list of pay_types is anywhere near unique to the table. However, the combination of specified values for both columns is highly selective. Individual column statistics are simply misleading in this situation. Dave Griffen

↪ replying to DAVE GRIFFEN

Art Kagel — Thu, 10 Mar 2011 19:40:35 — source: IIUG Forums & Mailing Lists

Understood. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (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, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Mar 10, 2011 at 2:26 PM, DAVE GRIFFEN <dgriffen@finishline.com>wrote: > Art Kagel WROTE: > > > ------------------------------------------------------------------------------- > The big difference between actual and estimated rows (268 actual versus > 28075460 estimated) is likely the problem. It indicates that your data > distributions are not up-to-date. > > Art > > > ------------------------------------------------------------------------------- > > Response: > Actually, statistics on these columns are fully up to date. Around 60% of > records in the table have a null value for reconcile_date. However, nearly > all > of those records have a pay_type different than those listed. And although > the > pay_types listed combine to represent nearly 40% of records in the table, > all > but a small handful have been previously reconciled. When the optimizer > looks > at the individual column statistics, neither the reconcile_date specified, > nor > the aggregate list of pay_types is anywhere near unique to the table. > However, > the combination of specified values for both columns is highly selective. > Individual column statistics are simply misleading in this situation. > > Dave Griffen > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf300faf9f3fddb1049e260483

↪ replying to John Miller iii

Khaled Bentebal — Thu, 10 Mar 2011 21:05:19 — source: IIUG Forums & Mailing Lists

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

↪ replying to Khaled Bentebal

Fernando Nunes — Thu, 10 Mar 2011 22:29:11 — source: IIUG Forums & Mailing Lists

On Thu, Mar 10, 2011 at 9:05 PM, Khaled Bentebal < khaled.bentebal@consult-ix.fr> wrote: > > 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. > > This would take us to another BIG discussion. But you need to take into account several things: OPTCOMPIND, isolation level (COMMITTED READ LAST COMMITTED), OPTGOAL, lock mode.... So... there are a lot of factors that would need to be taken into account for that discussion. What I'm trying to say is simply: Don't throw it all on the optimizer :) By the way, as a side note, once in a while I get involved with some optimizer related issues. And some of this situations increase my feeling: It has to deal with so many variables, take so many decisions, take so much into account... I'm dealing with one of those issues currently and I've just learned a bunch of things... Which make my admiration for that piece of software increase.... Of course, if it goes wrong it must be fixed! Thankfully it goes right much more often :) Regards -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0015174bef26138949049e285f53

↪ replying to Khaled Bentebal

Khaled Bentebal — Thu, 10 Mar 2011 23:10:20 — source: IIUG Forums & Mailing Lists

That is not the point. I am big defender of this great product. Most of the time, the optimizer works really well. There is always room for improvement. As far as locking, COMMITTED READ LAST COMMITTED did not exist in the previous versions. This is a great improvement that we have been waiting for for years. Most of the existing applications that the clents have do not have this improvement included. The locking mode does not have anything to do with this since we usually use row locking as opposed to Page locking. OPTCOMPIND can have an effect of course. OPTCOMPIND=0 usually works better in this case and especially if indexes exist on yhe columns involved. I already discussed some of these issues with Keshava Murthy at one time and he said that the optimizer is mainly concentrating on performance and not really on the locking issues that might be involved. My point is that we should NOT consider that ORs work the same way as an IN and that locking issues should be included in the work that the optimizer does. Once again, most of time, the optimizer does a great job. Khaled Bentebal Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 10/03/11 23:28, Fernando Nunes a écrit : > > > On Thu, Mar 10, 2011 at 9:05 PM, Khaled Bentebal > <khaled.bentebal@consult-ix.fr <mailto:khaled.bentebal@consult-ix.fr>> > wrote: > > > 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. > > > This would take us to another BIG discussion. But you need to take > into account several things: > OPTCOMPIND, isolation level (COMMITTED READ LAST COMMITTED), OPTGOAL, > lock mode.... > > So... there are a lot of factors that would need to be taken into > account for that discussion. What I'm trying to say is simply: Don't > throw it all on the optimizer :) > By the way, as a side note, once in a while I get involved with some > optimizer related issues. And some of this situations increase my > feeling: It has to deal with so many variables, take so many > decisions, take so much into account... I'm dealing with one of those > issues currently and I've just learned a bunch of things... Which make > my admiration for that piece of software increase.... > > Of course, if it goes wrong it must be fixed! > Thankfully it goes right much more often :) > Regards > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently...