IMPORT timing with and w/o index

IMPORT timing with and w/o index

Post by Sathyar » Fri, 15 Jun 2001 05:25:33



Hi,

I tried doing IMPORT of roughly 1.7 million rows with and without
index on the following table . With index it went through in 15 mins
and the same IMPORT without the index took 13 mins.

Is that all the performace difference we can expect from DB2 ??

We are at the base level of DB2 7.1 on Solaris.

Thanks in advance.

Cheers

Sathyaram

CREATE TABLE TAB1
(
    C1     CHAR(10),
    C2           INTEGER,
    C3      CHAR(6),
    C4     CHAR(2),
    C5 CHAR(3),
    C6        INTEGER,
    C6    INTEGER,
    C6  CHAR(26),
    C7       INTEGER,
    C8     INTEGER,
    C9 TIMESTAMP,
    C10  SMALLINT NOT NULL DEFAULT 1024,
    C11     TIMESTAMP NOT NULL WITH DEFAULT
) in TS1 index in TS2 ;

CREATE INDEX IND1 ON TAB1(C1, C3,C4
, C5) CLUSTER

 
 
 

1. Drop Index, BCP Import and Create Index (Ver 6.5)

Hi All,

I have a 6.5 database and I import about 120,000 records each week.  At the
moment, I do not delete any records, however I intend to delete all records
over two months old on a regular basis.  The table I import into has three
indexes, one primary key (non-clustered) and two foreign keys (both
non-clustered).

The process I use at the moment, is as follows
1) I do not use the Select Into / Bulk Import Option (performance is fine
without this additional risk)
2) I drop the three indexes on the table
3) I import the file using BCP
4) I recreate the indexes

My question is what do you think of this process, specifically,
1) the practice of having no clustered index, and
2) the drop index (including Primary Key), Import and Create Index routine.

On the initial timines I ran, if I use a Clustered Index it took a long time
to perform the import and also if I did not drop the indexes, it took a long
time to import the data.

I really just want to get your (Expert) opinions on the procedure I am
using.

Thanks once again, I do appriciate your help.

--
Best Regards

Mark L. Breen
Solution Providers Ltd
Ireland

2. Suggestions to convert from Alpha 4 ???

3. Error importing time field data to SQL7

4. Oracle newbie

5. DTS Import Wizard and Time field

6. DataEnvironement & Closed object

7. Importing Date/Time with DTS (newbie)

8. MDX to define a Cell to OLAP Action (Client Side)

9. DTS import - syntax for combining date and time columns into one datetime column

10. Import Times From Access Problem

11. DTS import with variable time

12. Date/Time After Importing From FoxPro

13. DTS Date/Time Import???