Fragement by round robin

Version: IDS 10 Assuming a box has 15 CPU. And table is fragmented to go in 15 dbspaces. Now, if a table is fragmeneted by round robin and say there are 10 inserts on the same table is going in parallel, then: How does informix inserts them. Does it inserts them sequentially because it's round robin or does it inserts them in parallel in 10 dbspaces ? Having more fragmentation is always better ? In my opinion I think it's better when fragmented by expression, but depending on how informix inserts/select/update it might be better for round robin.

On Aug 20, 12:00 pm, mohitanch...@gmail.com wrote: > Version: IDS 10 > > Assuming a box has 15 CPU. And table is fragmented to go in 15 > dbspaces. Now, if a table is fragmeneted by round robin and say there > are 10 inserts on the same table is going in parallel, then: > > How does informix inserts them. Does it inserts them sequentially > because it's round robin or does it inserts them in parallel in 10 > dbspaces ? The access to the 'next fragment' entry is probably single threaded, but the more intensive insert to the various fragments are made in parallel. > Having more fragmentation is always better ? In my opinion I think > it's better when fragmented by expression, but depending on how > informix inserts/select/update it might be better for round robin. Not a simple question. It depends on what you are optimizing for. For insert speed round robin tends to excel. For querying and updating specific records fragment by expression allows faster access and more parallelism with less contention. For larger queries accessing large subsets of the table, if fragments elimination can reduce the scope of the search then fragment by expression is best. For table scans under PDQPRIORITY where fragment elimination doesn't apply all fragmentation schemes are about the same EXCEPT if PDQPRIORITY >1 and OPTIMIZATION FIRST_ROWS and an order by matches an index on the same key then by expression on that sort key for the index will eliminate the sort in favor of merging the parallel index returns which may be more responsive for interactive use (though not neccessarily faster to return the entire query result set). Art S. Kagel

On Aug 20, 9:12 am, "Art S. Kagel" <art.ka...@gmail.com> wrote: > On Aug 20, 12:00 pm, mohitanch...@gmail.com wrote: > > > Version: IDS 10 > > > Assuming a box has 15 CPU. And table is fragmented to go in 15 > > dbspaces. Now, if a table is fragmeneted by round robin and say there > > are 10 inserts on the same table is going in parallel, then: > > > How doesinformixinserts them. Does it inserts them sequentially > > because it's round robin or does it inserts them in parallel in 10 > > dbspaces ? > > The access to the 'next fragment' entry is probably single threaded, > but the more intensive insert to the various fragments are made in > parallel. > > > Having more fragmentation is always better ? In my opinion I think > > it's better when fragmented by expression, but depending on how > >informixinserts/select/update it might be better for round robin. > > Not a simple question. It depends on what you are optimizing for. > For insert speed round robin tends to excel. For querying and > updating specific records fragment by expression allows faster access > and more parallelism with less contention. For larger queries > accessing large subsets of the table, if fragments elimination can > reduce the scope of the search then fragment by expression is best. > For table scans under PDQPRIORITY where fragment elimination doesn't > apply all fragmentation schemes are about the same EXCEPT if > PDQPRIORITY >1 and OPTIMIZATION FIRST_ROWS and an order by matches an > index on the same key then by expression on that sort key for the > index will eliminate the sort in favor of merging the parallel index > returns which may be more responsive for interactive use (though not > neccessarily faster to return the entire query result set). > > Art S. Kagel I am tyring to understand last few lines of your post. So does it mean that if order by clause is mentioned in the query and there is an index on the column that's being sorted then index will eliminate the sort, which means index will be used ? Also, talking about PDQPRIORITY. Why is it that even after setting PDQPRIORITY to 100 in onconfig when we run a query onstat -g ses still shows the pdqpriority as 0.

On Aug 21, 1:55 pm, mohitanch...@gmail.com wrote: > On Aug 20, 9:12 am, "Art S. Kagel" <art.ka...@gmail.com> wrote: > > > > > On Aug 20, 12:00 pm, mohitanch...@gmail.com wrote: > > > > Version: IDS 10 > > > > Assuming a box has 15 CPU. And table is fragmented to go in 15 > > > dbspaces. Now, if a table is fragmeneted by round robin and say there > > > are 10 inserts on the same table is going in parallel, then: > > > > How doesinformixinserts them. Does it inserts them sequentially > > > because it's round robin or does it inserts them in parallel in 10 > > > dbspaces ? > > > The access to the 'next fragment' entry is probably single threaded, > > but the more intensive insert to the various fragments are made in > > parallel. > > > > Having more fragmentation is always better ? In my opinion I think > > > it's better when fragmented by expression, but depending on how > > >informixinserts/select/update it might be better for round robin. > > > Not a simple question. It depends on what you are optimizing for. > > For insert speed round robin tends to excel. For querying and > > updating specific records fragment by expression allows faster access > > and more parallelism with less contention. For larger queries > > accessing large subsets of the table, if fragments elimination can > > reduce the scope of the search then fragment by expression is best. > > For table scans under PDQPRIORITY where fragment elimination doesn't > > apply all fragmentation schemes are about the same EXCEPT if > > PDQPRIORITY >1 and OPTIMIZATION FIRST_ROWS and an order by matches an > > index on the same key then by expression on that sort key for the > > index will eliminate the sort in favor of merging the parallel index > > returns which may be more responsive for interactive use (though not > > neccessarily faster to return the entire query result set). > > > Art S. Kagel > > I am tyring to understand last few lines of your post. So does it mean > that if order by clause is mentioned in the query and there is an > index on the column that's being sorted then index will eliminate the > sort, which means index will be used ? If optimization is set to FIRST_ROWS (ALL_ROWS is the default), the optimizer will favor query paths that tend to return the first few rows of a query most quickly. Since if the engine sorts the first row cannot be returned until all rows have been fetched and sorted, the optimizer will avoid sorts if there is an index available. This is true even for fragmented tables and fragmented indexes as the engine is capable of merging the in-order results from the separate fragments. (The default is to optimize for the total runtime of the query and since IDS sorts very quickly using parallel sorting methods, the optimizer tends to select a sort to satisfy order by clauses if ALL_ROWS optimization is enabled.) > Also, talking about PDQPRIORITY. Why is it that even after setting > PDQPRIORITY to 100 in onconfig when we run a query onstat -g ses still > shows the pdqpriority as 0. PDQPRIORITY is not an ONCONFIG parameter, nor is it effective when set in the engine's environment. It can only be set in the client's environment or by using the SET PDQPRIORITY... command within the client session. You can double check that with: SELECT * FROM sysmaster:sysconfig; If it's not listed it's not a valid parameter. There is MAX_PDQPRIORITY which limits or scales users' PDQPRIORITY settings to prevent a single user from hogging resources. Art S. Kagel