Very Computer

Hi,

I am using an application that does a lot of inserts. One stored
procedure is being repeatedly called to execute these inserts about
1000 times per second peak.

The application reuses only one connection and it looks like it cant
process more than about 500 calls per second. When additional
instances of this application are being run, then they will produce a
total throughput of up to an average of 1500 calls per second.

Does someone know where the bottleneck is? Should the applications use
a connection pool or is it maybe simply a matter of sql server 2000
configuration?

Thanks for any hint.

Regards

Willie

Is there anyway to batch your inserts?  It seems a little crazy for the
same app from the same computer to be doing 1000 inserts/sec.

Quote:> Hi,

> I am using an application that does a lot of inserts. One stored
> procedure is being repeatedly called to execute these inserts about
> 1000 times per second peak.

> The application reuses only one connection and it looks like it cant
> process more than about 500 calls per second. When additional
> instances of this application are being run, then they will produce a
> total throughput of up to an average of 1500 calls per second.

> Does someone know where the bottleneck is? Should the applications use
> a connection pool or is it maybe simply a matter of sql server 2000
> configuration?

Hi Brad,

Quote:> Is there anyway to batch your inserts?  It seems a little crazy for the
> same app from the same computer to be doing 1000 inserts/sec.

I am not sure. I guess with "batching" you are referring to a method
like DTS Import that will be executed in certain intervals. Since
every single insert is needed in realtime on the client's side, I
believe that batching won't do here.

I think I know why one connection won't perform as I wish, it is
simply because network latency and protocol overhead have to be added
to the execution time of the stored procedure, and since a single
synchronous connection is being used, it cannot be executed as often
as asynchronous execution with a connection pool would.

Regards

Willie