Hi

I need to insert 250 millions records or 6GB Fixed with positional File into
SQL
Server 2000. The information must be inserted 1.5 Hours.


Currently we have SQL scripts which import all data into ONE COLUMN Table
using bulk Insert statement and then from there we use SUBSTRING Function to
sort data into our main tables.

Performance is the main concern now.


I have following questions:-

1. Is there any thing else I can do to reduce the insert or import time?
(currently its taking very much )
2 What will be the ideal time for such kind of huge import?
3 What will be the ideal programming for such kind of huge import?

Thank you for your help.

Macisu

;)

Out of curiosity, why can't you bulk insert into a table with the proper
column structure? Is the data in the file malformed or something?

Show quoteHide quote

I need Sort the input file.

The structure has identifier fields for record Above Savings Account.

The entrance order is the same one of exit.
The objective is to give a file to print. The Order cannot be modified.

Thanks for your help

Show quoteHide quote

1. Is there any thing else I can do to reduce the insert or import time?
(currently its taking very much )

Import:
BULK_INSERT is as fast as you can get. You can speed up import time by
spreading your insert across procs on the machine by dividing insert into
multiple files (see the Rosetta document in the white papers section of
www.microsoft.com/sql)

INSERT (I would call this step normalize):
You can get HUGE perf benefits by actually dividing the data out into
columns prior to running normalize. The best is to actually import into a
columnar structure. By removing the need to substring while normalizing, you
will see huge gains.

If this is impossible, consider adding a step to break out the data before
moving into the normalization step. This may still be too slow as you have
FUDed up the data by moving it in one big blob.

One option to move into columns, if this is fixed width and there is no way
"other" than substring is to create a BCP mapping file and use BCP.exe to
bulk load. It is a bit slower than BULK INSERT, but it will kick the snot out
of SUBSTRINGing every time.

2 What will be the ideal time for such kind of huge import?

Depends on the methodology. With Rosetta, they were importing about 850k
rows per second. On our system, we were doing about 1/4th that speed.
Normalization can still take quite a bit of time.

3 What will be the ideal programming for such kind of huge import?

I would find a way to break things out prior to your import. That will give
you a great perf benefit.

NOTE: At work, I had to architect a system, with three other developers,
that would import a customer file into our normalized structure. We
accomplished everything offline, using .NET (C#) and walking files.
Presently, a monthly run creates 36 million member records with about 5
million transactions and 12 million detail lines. Initial load is 24 months
of data, so it is comparable to the number of rows you are talking about,
although the data size is much bigger (about 750 GB of data once normalized).

I am not sure if this type of system would help you. When we get to load,
tables load, in production, between 3000 and 30000 rows per second, depending
on the table and the load on the server. If we can load on weekends
(maintenance load), we can load the entire month in a few minutes (a month of
data is about 30+ GB of data).

Show quoteHide quote

Why is Count(*) slow?
Bad datetime values stored by SQL Server 2000
Bitwise operations on BigInt
Upsized SQLServer app 100x slower than MsAccess
Where clause in variable?