Databases Reference
In-Depth Information
If you have a large amount of data to insert into a database table and your data
provider supports bulk load, also known as bulk copy, using bulk load can be
even faster than using arrays of parameters. You use bulk load functionality
through
the
xxxBulkCopy
class
(for
example,
or
SqlBulkCopy
OracleBulkCopy
), which many data providers support.
Using bulk load, rows are sent from the database client to the database in a
continuous stream, without making extra network round trips. In addition,
when a bulk load operation is performed, the database can optimize the way
rows are inserted.
However, you should be aware that using bulk load can have negative side
effects. For example, data inserted with bulk load may ignore referential integrity,
causing consistency problems with data in the database.
Using 100%
managed code
allows your .NET assemblies to run inside the CLR.
When the data provider bridges into
unmanaged code
, or code that is outside
the .NET CLR, it adversely affects performance. The overhead associated with
each call made outside the CLR is a subject that is highly debated, with various
sources claiming that performance can degrade anywhere from 5% to 100%
when compared to calls into managed code. In general, the performance hit is
greater if the machine running the application is busy.
Be wary when choosing a data provider that advertises itself as a 100% or a
pure managed code data provider. Many ADO.NET data providers make this
claim yet use an architecture that bridges into native Windows code, as shown in
Figure 7-1. For example, these data providers may call into the DB2 Call Level
Interface (CLI) or Oracle SQL*Net.
Performance Tip
Using unmanaged code can significantly impact performance depending
on how busy the machine running your application is. The bottom line is
this: If a “managed” data provider requires unmanaged database clients
or other unmanaged pieces, it's not a true managed data provider. Only
a few vendors produce true managed data providers that work as a
100% managed component.
