Re: Very slow index generation.

En Pons Roca, Isidre va escriure el dia 25 Oct 99, a les 9:10: > Hi all. > > This weekend I have reorganized my database. > > I have changed slow disks (SCSII-2 of 2Gb) for new disks (FW- > SCSII of 8.5 Gb) > My instance of Informix has a size, of data, of around some 14Gb. > > To be able to make these operations I created an identical instance in > another machine, I reorganized the new instance and pass over the data > from it schemes it of backup toward the new one. With the new > configuration I have a dbspace for rootdbs, one for the logicalslogs, > another for the physicallog, those of data (4) and one for the > catalogs of the databases (create database in...). The load of data > has been carried out with the database in NON LOG. > > Until here all well. The problem arises in the moment to regenerate > the indexes (this operation is already carrying out it with the data > loaded). The time of regeneration of the indexes is enormous almost > consuming all the resources of the CPUs of the system. > > To regenerate the indexes I have followed the advice of Art > ,PSORT_NPROCS=6, PSORT_DBTEMP=<3 filesystems> and the > PDQPRIORITY=50, but I have not noticed any yield improvement. > > To generate the primary keys and the indexes I have used two > scripts with the following syntax: > > database perico << EOF > alter table table_a add contraint primary key (col_1,.. col_n)> constraint pk_table_a; > create index i1_table_a (col_1,.. col_n);> . > . > EOF > > > Working environment: > Platform: Sun SS1000E with 4 cpu's and 524288K mem > OS, Solaris 2.4 with last ones patches. > Informix: IDS 7.24.UC7-1 > Onconfig: > # System Configuration > > SERVERNUM 0 > DBSERVERNAME perico > DBSERVERALIASES perico0,perico1 > DEADLOCK_TIMEOUT 60 > RESIDENT 1 > MULTIPROCESSOR 1 > NUMCPUVPS 3 > SINGLE_CPU_VP 0 > NOAGE 1 > AFF_SPROC 1 > AFF_NPROCS 3> > > And now the question: > Because the generation of indexes is so slow? > > Thanks in advance. > > --------------------------------------- > Isidre PONS ROCA > BASE - Gesti' d'Ingressos Locals > (Diputacio de Tarragona) > Servei de Sistemes de Informacio > Av President Lluis Companys 12-C > 43005 - Tarragona > SPAIN > Tel # +34 977 236731 > Fax # +34 977 227302 > http://www.altanet.org > ipons@dtgna.altanet.org > --------------------------------------- I will respond me my own question. The problem of the great slowness in the generation of indexes comes given by the use of fields of the type NCHAR in the indexes. According to the technical service of Informix The use of type fields NCHAR in indexes, consultations, etc can suppose a degradation of the yield of until 40 times of the of the database's engine." If we keep in mind that my company not this in a country in which English is spoken that where we are, Spain that we have characters that the English doesn't haveh, etc, that I need to order according to my alphabet and that for it the agent's of data yield will be penalized in 4000% I don't find him the advantage that it can have fields type NCHAR, of being able to have a language different from English in the database and in the clients. :-((( That's all folks. --------------------------------------- Isidre PONS ROCA BASE - Gesti' d'Ingressos Locals (Diputacio de Tarragona) Servei de Sistemes de Informacio Av President Lluis Companys 12-C 43005 - Tarragona SPAIN Tel # +34 977 236731 Fax # +34 977 227302 http://www.altanet.org ipons@dtgna.altanet.org ---------------------------------------