Problem with a lot of inserts in short time - SQL Server 2000

[edi]

Hi!

We have SQL Server 2000 on virtual machine (slowly). We run a pair of
stored procedures from ADO.NET 2.0 (WebService):
- first procedure: insert into table A a data row with correlationID
- next procedure: search for inserted record in tab A by
correlationID and insert value into tab B with constraint to tab A.

The problem is when we try to do this in short time (few minutes)
thousands times (5000/6000 pairs in 5/6 minutes) in 100 parallel
threads:

query in second procedure cannot find inserted record in first
procedure.

Everything is dirty read. no transactions.

Have you any ideas?

edi

[Erland Sommarskog]

Re: Problem with a lot of inserts in short time - SQL Server 2000

edi (edward.weinert @gmail.com) writes:

We have SQL Server 2000 on virtual machine (slowly). We run a pair of
stored procedures from ADO.NET 2.0 (WebService):
- first procedure: insert into table A a data row with correlationID
- next procedure: search for inserted record in tab A by
correlationID and insert value into tab B with constraint to tab A.
>
The problem is when we try to do this in short time (few minutes)
thousands times (5000/6000 pairs in 5/6 minutes) in 100 parallel
threads:
>
query in second procedure cannot find inserted record in first
procedure.

Can you take that again slowly, and please include your code? And please
explain more clearly what is your problem. Is it a performance problem
only, or is there a malfunction?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx