BULK IMPORT the same data multiple times require very different time. Why?

Question

I'm testing the speed of BCP utility. I'm using the same database, table, data file (10 million lines), format file etc. in different experiments. After each experiment, I drop table and create the new one (exactly the same). When BCP utility doesn't work my HDD performance loaded on 1-5% (I use Task Manager to check it). When it works HDD loaded on 99%.

BUT.

I had 6 experiments now. The best is 178 sec the worst is 292 sec!!!

I will need to compare BCP to other methods, figure out what influences on speed (for example indexes, constraints, recovery settings etc) and how big is this influence. But I can't do it now because of such big differences between same experiments.

Why it happens and can I fix it?

UPD: I changed recovery model to "full" from "simple" and made more experiments. Now the best time is 173 sec the worst is 435 sec :)

For all these tests i think your are making sure source IO, destination IO and processing capabilities are similar... Because primarily in destination IO a small change in INDEX in destination table will provide different results — Kannan Kandasamy, May 19 '17 at 10:17
@KannanKandasamy I'm not trying to find I need to have my own results. I'm writing graduate work — zer_ik, May 19 '17 at 10:20
@KannanKandasamy I can't compare different methods when the same method gives random results — zer_ik, May 19 '17 at 10:23
During both the execution is there change in the target table structure? — Kannan Kandasamy, May 19 '17 at 10:24
You should check your database statistics to see where the difference is. In Oracle an AWR report would help, in this environment I do not know what the proper tool is, but there must be some. — Galcoholic, May 19 '17 at 10:27

Su Jun-Ming · Answer 1 · 2017-05-19T10:41:00.597

0

Maybe you need to test Nonlogged BULK INSERT

Recovery mode set to SELECT INTO/BULK INSERT
Add TABLOCK

BULK INSERT TestDB
FROM 'C:\TestDB.txt'
WITH
(
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '\n',
    TABLOCK
)

edited May 19 '17 at 10:41

answered May 19 '17 at 10:30

Su Jun-Ming

89
7

BULK IMPORT the same data multiple times require very different time. Why?

1 Answers1