Hi Diego,

What about de-activating the index, load all the data and then
activate/or create the index?

See you
Werner

zimrilin2000 wrote:

> Hi again!
>
> I have made some more testing and find the problem:
>
> I have tested to add a surrogate key, and make fields C and D unique
> key. The result is the same, from file 26 the import time grows (file
> 35 takes 1 hour again). But if I don't define the unique key, all
> files get imported in less than 2 minutes, so the overall process of
> importing 35 million rows is made in exactly 1 hour.
>
> So the problem must be the index is created in composite unique key
> C, D, because field D has a low selectivity and the table has so many
> rows, am I rigth?
>
> I may not add the unique key (although the file may have repeated
> values), but is there any another way of passing through this with
> import not taking 10 hours or there is no other solution?
>
> Thanks
>
> Diego
>
>
> --- In firebird-support@yahoogroups.com, "zimrilin2000"
> <drodriguez@a...> wrote:
>
>>Hi,
>>
>> I have enough space in the hard disk (about 300 Gb), and I did
>>commit after executing the stored procedure.
>>
>> Past nigth I have tested the insertion in other way: I have split
>>the file in 35 files with 1 million records in each file. Until
>
> file
>
>>number 26, files are imported in 2 minutes time (I insert a log
>>record with the times in a table between inserts, after commiting),
>>but file 27 is taking 20 minutes, and after that each file takes 5
>>more minutes than previous file. File 35 takes 1 hour to import.
>>
>>The table definition is
>>
>>CREATE TABLE TABLE1(
>>A VARCHAR(13),
>>B VARCHAR(10),
>>C VARCHAR(9),
>>D VARCHAR(32),
>>E SMALLINT,
>>DATE1 DATE,
>>FLAG SMALLINT,
>>);
>>
>>The primary key is C and D. The selectivity of field D is poor, but
>>it is no foreign key of any table, only composite key of this table
>>
>>Should I use a surrogate key for the table although is not used as
>
> a
>
>>foreign key?
>>
>>Am I reaching any limit I don't know?
>>
>>Any ideas/suggestions?
>>
>> Diego
>>
>>
>>
>>
>>
>>--- In firebird-support@yahoogroups.com, Helen Borrie
>
> <helebor@t...>
>
>>wrote:
>>
>>>At 03:58 PM 24/08/2005 +0000, you wrote:
>>>
>>>>So, which is the limit of the table size and why is failing the
>>>>import?
>>>
>>>Numbers as quoted - which is 2 BILLION not 2 million. Sorry
>
> about
>
>>that red
>>
>>>herring.
>>>
>>>
>>>
>>>>I just have tried to split the file in two 17 million rows
>
> files.
>
>>The
>>
>>>>first file is copied into internal file without problems in 45
>>>>minutes, but the second one has failed (the process is still
>>
>>running
>>
>>>>after 3 hours processing)
>>>
>>>How do you know it has failed?
>>>
>>>Did you hard-commit the first batch before commencing the second?
>>>
>>>./heLen
>
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>