Compare BULK INSERT vs INSERT

Hello,
I am wondering is the Transaction Log logged differently between BULK INSERT vs INSERT? Performance speaking, which operations is generally faster given the same amout of data inserted.

Sincerely,
-Lawrence

I don't know anything about the transactions logs.

Bulk insert is generally much faster.

Jonathan

|||

BULK INSERT can be a minimally logged operation (depending on various parameters like indexes, constraints on the tables, recovery model of the database etc). Minimally logged operations only log allocations and deallocations. In case of BULK INSERT, only extent allocations are logged instead of the actual data being inserted. This will provide much better performance than INSERT. You can start with the links below for more information:

http://msdn2.microsoft.com/en-us/library/ms190421.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx

Compare BULK INSERT vs INSERT

Hello,
I am wondering is the Transaction Log logged differently between BULK INSERT
vs INSERT? Performance speaking, which operations is generally faster given
the same amout of data inserted.
Sincerely,
-LawrenceIf conditions are right (e.g. destination table is not replicated), BULK
INSERT can exploit minimal transaction logging for performance advantage.
Linchi
"Lawrence" wrote:
> Hello,
> I am wondering is the Transaction Log logged differently between BULK INSERT
> vs INSERT? Performance speaking, which operations is generally faster given
> the same amout of data inserted.
> Sincerely,
> -Lawrence
>|||I should add that in SQL2005, INSERT can make use of the bulk rowset provider
in the OpenRowset function. But I have not tested the performance difference,
if any, between BULK INSERT and INSERT ... SELECT FROM OpenRowSet(bulk...).
Linchi
"Linchi Shea" wrote:
> If conditions are right (e.g. destination table is not replicated), BULK
> INSERT can exploit minimal transaction logging for performance advantage.
> Linchi
> "Lawrence" wrote:
> > Hello,
> > I am wondering is the Transaction Log logged differently between BULK INSERT
> > vs INSERT? Performance speaking, which operations is generally faster given
> > the same amout of data inserted.
> >
> > Sincerely,
> > -Lawrence
> >|||"Lawrence" <Lawrence@.discussions.microsoft.com> wrote in message
news:097C24AA-AE0A-45DC-9C2D-B09AAD9A4539@.microsoft.com...
> Hello,
> I am wondering is the Transaction Log logged differently between BULK
INSERT
> vs INSERT? Performance speaking, which operations is generally faster
given
> the same amout of data inserted.
>
BULK INSERT can be incredibly fast compared to INSERT, simply because of how
it can handle the logging.
> Sincerely,
> -Lawrence
>

Compare BULK INSERT vs INSERT

Hello,
I am wondering is the Transaction Log logged differently between BULK INSERT
vs INSERT? Performance speaking, which operations is generally faster given
the same amout of data inserted.
Sincerely,
-LawrenceIf conditions are right (e.g. destination table is not replicated), BULK
INSERT can exploit minimal transaction logging for performance advantage.
Linchi
"Lawrence" wrote:

> Hello,
> I am wondering is the Transaction Log logged differently between BULK INSE
RT
> vs INSERT? Performance speaking, which operations is generally faster giv
en
> the same amout of data inserted.
> Sincerely,
> -Lawrence
>|||I should add that in SQL2005, INSERT can make use of the bulk rowset provide
r
in the OpenRowset function. But I have not tested the performance difference
,
if any, between BULK INSERT and INSERT ... SELECT FROM OpenRowSet(bulk...).
Linchi
"Linchi Shea" wrote:
[vbcol=seagreen]
> If conditions are right (e.g. destination table is not replicated), BULK
> INSERT can exploit minimal transaction logging for performance advantage.
> Linchi
> "Lawrence" wrote:
>|||"Lawrence" <Lawrence@.discussions.microsoft.com> wrote in message
news:097C24AA-AE0A-45DC-9C2D-B09AAD9A4539@.microsoft.com...
> Hello,
> I am wondering is the Transaction Log logged differently between BULK
INSERT
> vs INSERT? Performance speaking, which operations is generally faster
given
> the same amout of data inserted.
>
BULK INSERT can be incredibly fast compared to INSERT, simply because of how
it can handle the logging.

> Sincerely,
> -Lawrence
>