Benckmark. Inserting records.

How may inserts can SQL execute in a second?
The table that's inserting into has 3 fields (numeric, char(15) and
datetime)
and no other kind of SQL statements are running against it.
Hardware: quad proc, 2GB RAM, RAID.
TIA,
Nicthis is one of those 'it depends' issues...
depedning on the speed of your disks, number of indexes, other users on the
system, blocking, etc...
you could easily do hundreds or a few thousands per second on high end
hardware.
On 2 procs... I'd be thinking more in the range of hundreds... but only
testing will know for sure.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"BN" <nc@.abc.com> wrote in message
news:e9hUkZcXDHA.652@.TK2MSFTNGP10.phx.gbl...
> How may inserts can SQL execute in a second?
> The table that's inserting into has 3 fields (numeric, char(15) and
> datetime)
> and no other kind of SQL statements are running against it.
> Hardware: quad proc, 2GB RAM, RAID.
> TIA,
> Nic
>|||First, paralellism is the key to optimizing data loading performance.
Create your insert routine so that it can easily be partitioned and
"paralellized".
Depending on your application, you might also consider using bulk insert,
bcp, or DTS - it should be possible to achieve 10's of 1000's of rows per
second with a table that narrow. I would estimate with a midrange server
you could easily go 50K/sec with bulk insert into an empty heap with only a
couple streams.
----
The views expressed here are my own
and not of my employer.
----
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:esoc4fcXDHA.3924@.tk2msftngp13.phx.gbl...
> this is one of those 'it depends' issues...
> depedning on the speed of your disks, number of indexes, other users on
the
> system, blocking, etc...
> you could easily do hundreds or a few thousands per second on high end
> hardware.
> On 2 procs... I'd be thinking more in the range of hundreds... but only
> testing will know for sure.
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "BN" <nc@.abc.com> wrote in message
> news:e9hUkZcXDHA.652@.TK2MSFTNGP10.phx.gbl...
> > How may inserts can SQL execute in a second?
> >
> > The table that's inserting into has 3 fields (numeric, char(15) and
> > datetime)
> > and no other kind of SQL statements are running against it.
> >
> > Hardware: quad proc, 2GB RAM, RAID.
> >
> > TIA,
> >
> > Nic
> >
> >
>|||on a 2x2.4, using individual stored proc calls per single
line insert, i can get > 7K/sec using 10 separate threads
by consolidating more than 1 single row insert statement
into each stored procedure, >18k single row inserts/sec is
possible,
>30k rows/sec on multi-row inserts,
if you are a doing more than one single row insert in a
single stored proc., try using BEGIN/COMMIT TRAN even if
it is not required, this consolidates the transaction log
writes
go to the next sql server magazine connections conference
for more info, brian is there as well
www.sqlconnections.com
>--Original Message--
>How may inserts can SQL execute in a second?
>The table that's inserting into has 3 fields (numeric,
char(15) and
>datetime)
>and no other kind of SQL statements are running against
it.
>Hardware: quad proc, 2GB RAM, RAID.
>TIA,
>Nic
>
>.
>