A lot of inserts

Hi,
I am using Visual Studio 2005 and C# to do a lot of inserts into SQL Server
2000. I have tried to use either DataTable/DataAdapter, stored procedures and
plain SQL to do the inserts. I have encapsulated everything in a transaction
because I found that the performance was better.
However, I can only do 10000 inserts in about 9 seconds on Pentium III 1
GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored
procedure etc.). Can I get better performance using another technique?
I really want to do about 10000 inserts in 0.01 seconds which my binary
files can handle on the same machine. Is this possible?
Why is the performance higher when using transactions?
Best regards,
Chris
Chris
Do you have any indexes define on the table ?
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...
> Hi,
> I am using Visual Studio 2005 and C# to do a lot of inserts into SQL
> Server
> 2000. I have tried to use either DataTable/DataAdapter, stored procedures
> and
> plain SQL to do the inserts. I have encapsulated everything in a
> transaction
> because I found that the performance was better.
> However, I can only do 10000 inserts in about 9 seconds on Pentium III 1
> GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored
> procedure etc.). Can I get better performance using another technique?
> I really want to do about 10000 inserts in 0.01 seconds which my binary
> files can handle on the same machine. Is this possible?
> Why is the performance higher when using transactions?
> Best regards,
> Chris
|||Hi,
I am using a clustered index:
"ALTER TABLE LOG WITH NOCHECK " +
"ADD CONSTRAINT LOG_key PRIMARY KEY CLUSTERED(A,B,DATE,MSEC)";
A is a uniqueidentifier, B is a int, DATE is datetime, msec is a int.
/Chris
"Uri Dimant" wrote:

> Chris
> Do you have any indexes define on the table ?
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...
|||Well, try to drop the indexes during the insertion and later recreate them.
This is costly operation as the users will not be able to get the table
while SQL Server build the indexes but it might speed up the query.
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:DBABDA80-7758-42A6-B3CE-34DDF5B5C956@.microsoft.com...
> Hi,
> I am using a clustered index:
> "ALTER TABLE LOG WITH NOCHECK " +
> "ADD CONSTRAINT LOG_key PRIMARY KEY CLUSTERED(A,B,DATE,MSEC)";
> A is a uniqueidentifier, B is a int, DATE is datetime, msec is a int.
> /Chris
> "Uri Dimant" wrote:
>
|||Hi,
I have tried to drop the indexes (not creating them) and it does not give me
any performance boost.
/Chris
"Uri Dimant" wrote:

> Well, try to drop the indexes during the insertion and later recreate them.
> This is costly operation as the users will not be able to get the table
> while SQL Server build the indexes but it might speed up the query.
|||> I really want to do about 10000 inserts in 0.01 seconds which my binary
> files can handle on the same machine. Is this possible?
So you need to do one million rows per second? This isn't going to happen
using traditional insert techniques. You can improve performance by using a
bulk insert technique. MDAC 9 introduces new methods that allow you to
perform bulk copy operations directly from managed code. However, that
still won't come close to your performance objective, especially on a single
processor 1GHz box using a beta version of SQL Server and .Net. High-volume
loads are generally performed in parallel from multiple clients using a bulk
insert method.

> Why is the performance higher when using transactions?
Without an explicit transaction, each insert is in an individual transaction
and thereby requires a synchronous log i/o for each insert. Only the last
log i/o (during COMMIT) is synchronous when you perform the inserts in a
transaction.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E8702FC9-26A0-49CA-AE6F-6F7C58C9F60D@.microsoft.com...
> Hi,
> I am using Visual Studio 2005 and C# to do a lot of inserts into SQL
> Server
> 2000. I have tried to use either DataTable/DataAdapter, stored procedures
> and
> plain SQL to do the inserts. I have encapsulated everything in a
> transaction
> because I found that the performance was better.
> However, I can only do 10000 inserts in about 9 seconds on Pentium III 1
> GHz, XP SP2, 512 MB RAM. This is for all combinations (datatable, stored
> procedure etc.). Can I get better performance using another technique?
> I really want to do about 10000 inserts in 0.01 seconds which my binary
> files can handle on the same machine. Is this possible?
> Why is the performance higher when using transactions?
> Best regards,
> Chris
|||Thanks for the replies. You have been helpful.
|||On Wed, 7 Sep 2005 03:50:03 -0700, Chris wrote:

>Hi,
>I am using a clustered index:
>"ALTER TABLE LOG WITH NOCHECK " +
> "ADD CONSTRAINT LOG_key PRIMARY KEY CLUSTERED(A,B,DATE,MSEC)";
>A is a uniqueidentifier, B is a int, DATE is datetime, msec is a int.
Hi Chris,
If you can pre-sort the data to be imported to match this sequence,
you'll probably get the best possible performance with your setup. Of
course, if you generate the uniqueidentifier values for column A during
the import, you're out of luck.
For what you're trying to do, uniqueidentifier is one of the worst
candidates for the first value in the clustered index. New rows get
inserted in all parts of the table, and SQL Server will have to spend
much of it's time splitting pages.
The best bet is a clustered index key that will keep increasing for new
rows. A datetime value would be good, if the new rows are isnerted in
order of increasing datetime. Or, if you need a surrogate key, you could
consider using IDENTITY instead of uniqueidentifier. Since each new row
has a higher IDENTITY value than the preceding rows, it will be inserted
at the end of the index. You'll have no page splits at all, just the
occasional addition of an extra index page.
If you can do without surrogate key, that might be better still. The
less bytes you write to each row, the faster your inserts will go.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)