BIG big table ....
A user asked what counts as a "big" table in Informix and whether repeatedly sequentially scanning a 2-million-row table (~40MB, 290-byte rows) would hurt performance. Respondents said 2M rows isn't large per se — size depends on row width, extents, fragmentation and buffer stats — and that it depends on the query: use indexes so you don't scan every row, and for unavoidable scans consider dirty-read isolation (light scans), fragmentation by expression for fragment elimination, and PDQ for parallel scans. Also advised checking the query plan to confirm indexes are used and rebuilding bloated/corrupt indexes. No specific outcome reported by the poster.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all, In informix, how do we define a big table? In other words, normally a big table should have how many rows? I have a table that consists of 2M rows. Do you think that a continuous sequential scan over a 2M table for around 2M times will create performance issue? If yes, what can I do in order to improve the performance? Thanks for your help. :)
> In informix, how do we define a big table? In other words, normally a > big table should have how many rows? I have a table that consists of 2M > rows. Do you think that a continuous sequential scan over a 2M table > for around 2M times will create performance issue? If yes, what can I > do in order to improve the performance? Thanks for your help. :) 2M isn't that big; what are your buffer statistics? How many extents? Is the table fragmented?
On 27 Nov 2006 17:19:18 -0800, "Henry" <ggk517@gmail.com> wrote: >Hi all, > >In informix, how do we define a big table? In other words, normally a >big table should have how many rows? I have a table that consists of 2M >rows. Do you think that a continuous sequential scan over a 2M table >for around 2M times will create performance issue? If yes, what can I >do in order to improve the performance? Thanks for your help. :) Depends on the query. . . . Does the query need to scan all the rows, or is there an effective filter? JWC
"John Carlson" <jwcarlson1@yahoo.com.invalid> wrote in message news:5j9nm2t8mnmo8t9j7e7vkhjhk0da2gu1tp@4ax.com... > On 27 Nov 2006 17:19:18 -0800, "Henry" <ggk517@gmail.com> wrote: > >>Hi all, >> >>In informix, how do we define a big table? In other words, normally a >>big table should have how many rows? I have a table that consists of 2M >>rows. Do you think that a continuous sequential scan over a 2M table >>for around 2M times will create performance issue? If yes, what can I >>do in order to improve the performance? Thanks for your help. :) > > Depends on the query. . . . > > Does the query need to scan all the rows, or is there an effective > filter? > > JWC > I would say a big table is defined by a combination of number of rows and row size, not just the number of rows. A sequential scan of a 2 million row table with 10 byte rows is going to require a lot fewer disk reads than a 2 million row table with 1000 byte rows. No matter what your row size is, 2 million continuous sequential scans is probably not the right way to go, what exactly are you trying to accomplish? To speed things up you can create indexes to get to the rows you need quickly if you don't need to evaluate every row in the table to satisfy your query. As for speeding up the sequential scans you can set your isolation level to dirty read to enable light scans and fragment your data by expression to get fragment elimination and parallel scans when you enable PDQ. Andrew
Hi Adam, Here is the table info: 1) row size = 290, number of columns = 6, index size = 43 2) in invdspace extent size 102400 next size 10240 lock mode row; 3) the size of the table is around 40Mb Thanks for your help.
Hi John, I think it will no scan all the rows as we have created indexes for this table. In addition, we are joining to this table using the keys. Thanks !
Hi Andrew: Yes, indexes have been created for this table. we are actually joining to this table using the keys to retrieve additional data. Thanks for your suggestions, i will look into it. > I would say a big table is defined by a combination of number of rows and > row size, not just the number of rows. A sequential scan of a 2 million row > table with 10 byte rows is going to require a lot fewer disk reads than a 2 > million row table with 1000 byte rows. > > No matter what your row size is, 2 million continuous sequential scans is > probably not the right way to go, what exactly are you trying to accomplish? > > To speed things up you can create indexes to get to the rows you need > quickly if you don't need to evaluate every row in the table to satisfy your > query. > > As for speeding up the sequential scans you can set your isolation level to > dirty read to enable light scans and fragment your data by expression to get > fragment elimination and parallel scans when you enable PDQ. > > Andrew
Ladies and Gentlemen: I'm kind of working in the blind without my Ron Flannery book, or my six metric tons of other references. They'll be here in a week! Meanwhile, I need a vacation... An a question answered. What are the naming conventions for a stored procedure language in IDS 7.2 on an HPUX 11 system? My lizard says its 15 characters and can't start with a number or special character... Is that right? Rob
Also check the execution plan to ensure the indexes are being used.
If they are not, double check the way the index was created to ensure
it is consistent with the use in the query. If they are consistent,
check the integrity of the index with oncheck to ensure the index is
not corrupted. The index may be consistent, but bloated to the
extent that the optimizer finds it of no benefit. In that case,
rebuild it.
Christine
On Nov 28, 2006, at 6:08 AM, Henry wrote:
> Hi Andrew:
>
> Yes, indexes have been created for this table. we are actually joining
> to this table using the keys to retrieve additional data.
>
> Thanks for your suggestions, i will look into it.
>
>> I would say a big table is defined by a combination of number of
>> rows and
>> row size, not just the number of rows. A sequential scan of a 2
>> million row
>> table with 10 byte rows is going to require a lot fewer disk reads
>> than a 2
>> million row table with 1000 byte rows.
>>
>> No matter what your row size is, 2 million continuous sequential
>> scans is
>> probably not the right way to go, what exactly are you trying to
>> accomplish?
>>
>> To speed things up you can create indexes to get to the rows you need
>> quickly if you don't need to evaluate every row in the table to
>> satisfy your
>> query.
>>
>> As for speeding up the sequential scans you can set your isolation
>> level to
>> dirty read to enable light scans and fragment your data by
>> expression to get
>> fragment elimination and parallel scans when you enable PDQ.
>>
>> Andrew
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list