Saturday 12 July 2008

Parallel Data Loading using SQL Server 2005 partition techniques (BCP)

It is common in Enterprise scale projects to simulate the test environment with very large-scale databases. The preparation of real time data and loading consumes considerable amount of time during the environment setup. The SQL Server 2005 partition techniques allow parallel data loading into the tables using BCP / Bulk Insert statements. The data loading of 10 Million records into “Sales” tables using the partition and non-partition methods is explained below.

Data loading with out Partition

CREATE TABLE Sales
(
UniqueID BIGINT,
ItemName VARCHAR(100),
SaledAmount NUMERIC(10,3),
SalesDate DATETIME
)on [Primary]

The data loading for this table can be done using the BCP / Bulk Insert options. But we cannot load the data in parallel, as it will lead to table locking

Data loading with Partition

CREATE TABLE Sales(
ItemName VARCHAR(100),
SaledAmount NUMERIC(10,3),
SalesDate DATETIME,
PartitionID INT
)on fPartitionID(PartitionID)

The table “Sales” is partitioned using the column PartitionID into 10 different partitions (say PartitionID accepts value between 1.10)
As the table is split into 10 different partitions, data loading of this table can be done in parallel follows

  • Generate the data into to 10 X 1 Million files
  • Load data into the table using 10 BCP / Bulk Insert instances in parallel

Conclusion

The performance of the data loading can be improved up to 10 times if the table is partitioned. This option can be preferred only if there are sufficient hardware resources. As CPU usage of BCP is very high, the number of parallel instances can be reduced as per the resource availability

7 comments:

Anonymous said...

It is very interesting for me to read the blog. Thanks for it. I like such themes and everything connected to them. I would like to read a bit more on that blog soon.

Hilary Benedict

Anonymous said...

Pretty nice place you've got here. Thanx for it. I like such themes and anything connected to this matter. I would like to read a bit more soon.

Hilary Benedict

Anonymous said...

Pretty nice blog you've got here. Thanx for it. I like such topics and anything connected to them. I definitely want to read more on that blog soon.
Don't you think design should be changed from time to time?

Anete Kuree
asian escort girls

oakleyses said...

louis vuitton outlet online, kate spade handbags, ray ban sunglasses, kate spade outlet online, prada outlet, michael kors outlet, coach outlet store online, nike shoes, nike air max, jordan shoes, nike free, polo ralph lauren, christian louboutin shoes, longchamp outlet, michael kors outlet online, longchamp handbags, gucci handbags, longchamp outlet online, polo ralph lauren outlet, michael kors outlet store, michael kors outlet online, oakley vault, red bottom shoes, prada handbags, tory burch outlet, cheap oakley sunglasses, michael kors outlet online, burberry outlet online, chanel handbags, oakley sunglasses, burberry outlet online, coach purses, louis vuitton outlet, christian louboutin outlet, tiffany jewelry, coach outlet, nike air max, tiffany and co jewelry, ray ban outlet, louboutin shoes, louis vuitton handbags, michael kors outlet online, louis vuitton, coach outlet, louis vuitton outlet

Unknown said...

ninest123 16.03
ray ban sunglasses, cheap oakley sunglasses, replica watches, michael kors outlet, oakley sunglasses, jordan shoes, prada handbags, nike outlet, nike air max, nike free, louis vuitton, tiffany and co, ugg boots, louboutin outlet, ray ban sunglasses, louis vuitton outlet, burberry outlet online, michael kors outlet, chanel handbags, uggs on sale, tiffany jewelry, louis vuitton outlet, michael kors outlet, oakley sunglasses, oakley sunglasses, louboutin shoes, prada outlet, ugg boots, ray ban sunglasses, ugg boots, burberry, replica watches, michael kors outlet, tory burch outlet, longchamp outlet, michael kors outlet, louis vuitton, michael kors, polo ralph lauren outlet, longchamp outlet, louis vuitton, polo ralph lauren outlet, louboutin, oakley sunglasses, gucci outlet, ugg boots, christian louboutin outlet, nike air max, longchamp

Unknown said...

ugg,uggs,uggs canada, canada goose, ugg,ugg australia,ugg italia, canada goose, hollister, wedding dresses, moncler, louis vuitton, canada goose outlet, swarovski, pandora charms, pandora jewelry, moncler outlet, moncler, sac louis vuitton pas cher, replica watches, louis vuitton, marc jacobs, louis vuitton, vans, moncler, swarovski crystal, pandora jewelry, toms shoes, moncler, juicy couture outlet, doudoune canada goose, karen millen, converse, lancel, canada goose outlet, coach outlet, gucci, ray ban, thomas sabo, moncler, supra shoes, canada goose uk, juicy couture outlet, links of london, barbour jackets, louis vuitton, canada goose, barbour, ugg boots uk, pandora charms, bottes ugg, converse outlet, moncler, montre pas cher, canada goose, ugg pas cher, moncler
ninest123 16.03

jeje said...

nike shoes outlet
mbt shoes
off white shoes
oakley sunglasses
ralph lauren polo
ugg outlet
valentino shoes
red bottom shoes
jordan 4
ralph lauren polo