The insert operation is a fairly simple one, compared to other provider
operations such as reading data, so it's hard for any one provider to exceed
another once it's been reasonbly optimized. If you want to compare overall
provider performance, whatever benchmark you choose would have to include a
fair measure of read operations, which is one of the areas where SqlClient
really outperforms the oledb managed provider.
That said, when I tried a simple test with 16000 distinct insert operations
(i.e. seperate round trip for each), I got results for SqlClient that were
1-2% better than the OleDb provider. I think it's fair to say that the two
providers are basically equivalent for many types of insert operations, but
overall for best performance you will be better off with SqlClient.
If you are only interested in insert performance, you will probably want to
check out the Whidbey release of .NET, where SqlClient supports a bulk load
api.
[quoted text, click to view] "Robby White" <RobbyWhite@discussions.microsoft.com> wrote in message
news:8A9CA873-B3B5-44B7-B2D9-296FFE988FC5@microsoft.com...
> Yes I mean the System.Data.SqlClient provider.
>
> Inserting 4000 records into SQL Server 2000
>
> using the System.Data.SqlClient took 14 seconds
> using the System.Data.Odbc took 13 seconds
> using the System.Data.OleDb took 12 seconds
>
> I have tried running these tests in different orders also and get the same
results. I realise the times are close but I am concerned about
scalibility.
[quoted text, click to view] >
> Robby White
>
> "David Schleifer [MSFT]" wrote:
>
> > By native provider, do you mean System.Data.SqlClient? In general
SqlClient
> > is a good deal faster than using the combination of System.Data.OleDb
and
> > the native oledb provider for sql server. To comment further, I'd need
to
> > know more about your scenario.
> >
> > Thanks,
> > Dave
> >
> > "Robby White" <Robby White@discussions.microsoft.com> wrote in message
> > news:04E120F8-5A6E-42F9-80E9-8BBD32B887C3@microsoft.com...
> > > Using a local instance of SQL Server 2000 and .NET I have managed to
> > insert 4000 rows across multiple tables using the OLE DB provider in 11
> > seconds but when using the native provider it takes 13 seconds.
> > >
> > > I was under the impression the native provider should enhance
performance
> > not decrease it? Why is this?
> > >
> > > Thanks, Robby
> >
> >
> >
