Performance problem with big table!
Xavier Mertens reported that on IDS 7.31 on a Sun Enterprise 450 a simple select on a unique-indexed label of a new 10,000,000-row table took over 10 seconds. Michael Krzepkowski, Jonathan Leffler and Art S. Kagel suggested running UPDATE STATISTICS per the Performance Guide; Art offered his dostats.ec utility. Kim, Hyung Soo asked about a clustered index. No outcome was reported.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi *, We run an IDS 7.31 on a SUN Enterprise 450. The database contains some tables with no more than 1000000 rows. Today we added a new table with 10.000.000 rows and 3 indexes. (the table has less than 10 fields) A simple query like "select * from table where label = x" take more than 10"!!! (and label is a unique index) Any idea? X -- Xavier Mertens, . . EuroNet Internet Network Operation Center . * a subsidiary of France Telecom XM3-RIPE XM1-6BONE .
Hi! How about "update statistics for table ...." ? Michael Xavier Mertens wrote: > Hi *, > > We run an IDS 7.31 on a SUN Enterprise 450. > The database contains some tables with no more than 1000000 rows. > > Today we added a new table with 10.000.000 rows and 3 indexes. (the table has > less than 10 fields) > > A simple query like "select * from table where label = x" take more than 10"!!! > (and label is a unique index) > > Any idea? > > X > > -- > Xavier Mertens, . . EuroNet Internet > Network Operation Center . * a subsidiary of France Telecom > XM3-RIPE XM1-6BONE .
Xavier Mertens wrote:
> We run an IDS 7.31 on a SUN Enterprise 450.
> The database contains some tables with no more than 1000000 rows.
>
> Today we added a new table with 10.000.000 rows and 3 indexes. (the table has
> less than 10 fields)
>
> A simple query like "select * from table where label = x" take more than 10"!!!
> (and label is a unique index)
UPDATE STATISTICS?
--
Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net)
Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN
#include <disclaimer.h>
Do you using clusterd index? -- ---------------------------------------- Kim, Hyung Soo eIST Co. Senior Developer & DBA Tel : 6283-2108 Mailto:dankoon@ist.co.kr Mailto:dankoon@chollian.net ---------------------------------------- Xavier Mertens <xavier@euro.net>''('') 'Ʒ' ''''''' news:Pine.GSO.4.21.9912110107250.9821-100000@caolila'' 'Խ'''''''''. > Hi *, > > We run an IDS 7.31 on a SUN Enterprise 450. > The database contains some tables with no more than 1000000 rows. > > Today we added a new table with 10.000.000 rows and 3 indexes. (the table has > less than 10 fields) > > A simple query like "select * from table where label = x" take more than 10"!!! > (and label is a unique index) > > Any idea? > > X > > -- > Xavier Mertens, . . EuroNet Internet > Network Operation Center . * a subsidiary of France Telecom > XM3-RIPE XM1-6BONE . >
Have you updated statistics according to the recommendations in the Performance Guide? Or get my dostats.ec utility which implements this protocol for generating and maintaining useful database statistics and data distribution information for the optimizer. Dostats is part of the package utils2_ak which is available for download from the IIUG Software Repository. Art S. Kagel "Kim, Hyung Soo" wrote: > > Do you using clusterd index? > > Xavier Mertens <xavier@euro.net>ÀÌ(°¡) ¾Æ·¡ ¸Þ½ÃÁö¸¦ > news:Pine.GSO.4.21.9912110107250.9821-100000@caolila¿¡ °Ô½ÃÇÏ¿´½À´Ï´Ù. > > Hi *, > > > > We run an IDS 7.31 on a SUN Enterprise 450. > > The database contains some tables with no more than 1000000 rows. > > > > Today we added a new table with 10.000.000 rows and 3 indexes. (the table > has > > less than 10 fields) > > > > A simple query like "select * from table where label = x" take more than > 10"!!! > > (and label is a unique index) > > > > Any idea? > > > > X > > > > -- > > Xavier Mertens, . . EuroNet Internet > > Network Operation Center . * a subsidiary of France Telecom > > XM3-RIPE XM1-6BONE . > >