Sql cost order by too hight
User reported slow ORDER BY query on name and surname columns returning 500k rows from 500m-row fragmented table in Informix 11.50 Workgroup. Suggestions included: adding indexes on sort columns with HIGH distributions, increasing DS_NONPDQ_QUERY_MEM parameter to 2048 (PDQ unavailable in Workgroup edition). No final resolution recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
hello we have and sql to order name and surname but it's too slow. how can i improve it in order to speed up? should i add a huge temp dataspace? this query returns 500k rows and in this table there is 500m rows in a fragment mode. regards --00248c6a6736a08fe004cc6be76b
How about adding an index on the same columns that you are ordering by and make sure that you have HIGH data distributions on those columns. If you are filtering on some other column(s), then append the filter columns to the index key and make MEDIUM or HIGH distributions on those columns as well. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) 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 Fri, Oct 19, 2012 at 12:22 PM, Juan Francisco González Navarro < jfrancisco.navarro@gmail.com> wrote: > hello > > we have and sql to order name and surname but it's too slow. how can i > improve it in order to speed up? should i add a huge temp dataspace? > > this query returns 500k rows and in this table there is 500m rows in a > fragment mode. > > regards > > --00248c6a6736a08fe004cc6be76b > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f838b3540482404cc6c2c09
Just to start off with, it generally helps to include your version. I would consider increase DS_NONPDQ_QUERY_MEM and associated parameters. If this is a very large query you can consider running the= query under PDQ. 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 10/19/2012 09:22:09 AM: > From: "Juan Francisco Gonz=E1lez Navarro" <jfrancisco.navarro@gmail.c= om> > To: ids@iiug.org, > Date: 10/19/2012 09:29 AM > Subject: Sql cost order by too hight [28588] > Sent by: ids-bounces@iiug.org > > hello > > we have and sql to order name and surname but it's too slow. how can = i > improve it in order to speed up? should i add a huge temp dataspace? > > this query returns 500k rows and in this table there is 500m rows in = a > fragment mode. > > regards > > --00248c6a6736a08fe004cc6be76b > > > ***********************************************************************= ******** > Forum Note: Use "Reply" to post a response in the discussion forum.= >=
thank you. informix 11.50 workgroup. there is already an index in these column. i think we don't have pdq in this informix version. anyway i'm going to try pdq. regards. El 19/10/2012 18:51, "John Miller iii" <miller3@us.ibm.com> escribió: > Just to start off with, it generally helps to include your version. > > I would consider increase DS_NONPDQ_QUERY_MEM and associated > parameters. If this is a very large query you can consider running the= > > query under PDQ. > > 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 10/19/2012 09:22:09 AM: > > > From: "Juan Francisco Gonz=E1lez Navarro" <jfrancisco.navarro@gmail.c= > om> > > To: ids@iiug.org, > > Date: 10/19/2012 09:29 AM > > Subject: Sql cost order by too hight [28588] > > Sent by: ids-bounces@iiug.org > > > > hello > > > > we have and sql to order name and surname but it's too slow. how can = > i > > improve it in order to speed up? should i add a huge temp dataspace? > > > > this query returns 500k rows and in this table there is 500m rows in = > a > > fragment mode. > > > > regards > > > > --00248c6a6736a08fe004cc6be76b > > > > > > > ***********************************************************************= > ******** > > > Forum Note: Use "Reply" to post a response in the discussion forum.= > > >= > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bb04e40d8a18f04cc6ce088
You are correct, PDQ is not available in workgroup. I would increase DS_NONPDQ_QUERY_MEM to 2048 for a start. 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 10/19/2012 10:31:51 AM: > From: "Juan Francisco Gonz=E1lez Navarro" <jfrancisco.navarro@gmail.c= om> > To: ids@iiug.org, > Date: 10/19/2012 10:33 AM > Subject: Re: Sql cost order by too hight [28592] > Sent by: ids-bounces@iiug.org > > thank you. informix 11.50 workgroup. there is already an index in the= se > column. > > i think we don't have pdq in this informix version. > > anyway i'm going to try pdq. > > regards. > El 19/10/2012 18:51, "John Miller iii" <miller3@us.ibm.com> escribi=F3= : > > > Just to start off with, it generally helps to include your version.= > > > > I would consider increase DS_NONPDQ_QUERY_MEM and associated > > parameters. If this is a very large query you can consider running = the=3D > > > > query under PDQ. > > > > 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 10/19/2012 09:22:09 AM: > > > > > From: "Juan Francisco Gonz=3DE1lez Navarro" <jfrancisco.navarro@gmail.c=3D > > om> > > > To: ids@iiug.org, > > > Date: 10/19/2012 09:29 AM > > > Subject: Sql cost order by too hight [28588] > > > Sent by: ids-bounces@iiug.org > > > > > > hello > > > > > > we have and sql to order name and surname but it's too slow. how = can =3D > > i > > > improve it in order to speed up? should i add a huge temp dataspa= ce? > > > > > > this query returns 500k rows and in this table there is 500m rows= in =3D > > a > > > fragment mode. > > > > > > regards > > > > > > --00248c6a6736a08fe004cc6be76b > > > > > > > > > > > ***********************************************************************= =3D > > ******** > > > > > Forum Note: Use "Reply" to post a response in the discussion foru= m.=3D > > > > >=3D > > > > > > > > > ***********************************************************************= ******** > > Forum Note: Use "Reply" to post a response in the discussion forum.= > > > > > > --047d7bb04e40d8a18f04cc6ce088 > > > ***********************************************************************= ******** > Forum Note: Use "Reply" to post a response in the discussion forum.= >=
For future reference: Having developed applications in Puerto Rico, which like in many other Latin-American countries use Surname's, I always define a VARCHAR column called "LastNames" ("Apellidos" in Spanish), which holds both the Father's last name and the Mother's Maiden Name. Examples: "Gonzalez Navarro", "Del Torro Dos Santos", "Smith Clark", etc. This makes it much easier to deal with compound last names, especially when some of these names are separated by spaces, such as "Dos Santos".