Question about performance

I'm using a tableadapter for my update operations.

The application i'm using typically creates 2500 dirty rows for a single table and then updates using the tableadapter.Update(). The tableadapter.update() method takes about 50 seconds to complete, which is way too long.

This is the only query running agaist the sql server 2000 database ( as this is a test server only), so i'm not sure if it is a problem with sql server or a problem with the tableadapter. Can anyone recomment how to improve performance for updating with the tableadapter and sql 2000?

Thanks in advance.

How big is each row? Was the CPU fully utilized? Is the client remote or local? For performance related issue, if you can describe your configuration and share out the code, it will be helpful.|||

The cpu isn't fully being utilzed on the sql server machine.

Here is the exact process:

There are 3 tables for this process. I basically create the information inside the dataset, and then postback to the server with all changes. Some of those changes are creating new rows and retrieving identities as well.

Example schema:

Table1 ( ID1 (identity, pk), 3 misc rows)

Table2 ( ID2 (Identity, pk,), ID1 (ForiegnKey)

Table 3 ( ID2 (ForigneKey) )

So, Table1 is parent to Table2 and Table3 is parent to table 2.

-

The user will create a row in Table1. The ID of Table1 is set to 0 as a temp Primary Key until it can resolve the identity when it hits the db. The user will then create on average 300 rows in table 2. Table2's ID is set to 0 as a temp Primary Key until it can resolve the identity when it hits the db. Next, the user creates 6 rows (on average) for every ID in Table2 ( 6 * 300).

So, then when you update this dataset, you're basically udpating Table1, getting the ID's from the database and cascading them down to Table2. Table2 will update and recieve it's true identities and then pass it down to table 3. Or something like that. So, it takes about a minute for this to update... Is my methodology fuzzy?

Please let me know if you need anymore detail!

|||

I still haven't solved this problem and maybe there is no solution. But I'll try one more time to clarify.

http://www.condoresorts.com/sample.jpg

Ok, so here is the process:

1. A user will create a time span that will be entered into the RoomInfo table. The start date is stored in Arrival column and end date stored in Departure column.

2. The time span which is stored in RoomInfo (Departure date - Arrival Date) will get expanded for each day and will be stored in Room_days.

3. Then the individual days for a timespan can then be assigned one to many people in the RoomDay_Customer table.

* RoomInfo and Room_Days primary key is an identity.

The problem is if you enter a timespan that spans more then 6 months, the update is really slow using the tableadapter.Update method.

A six month time span creates the following data:

1 entry in RoomInfo

180 entries in Room_Days ( on average)

540 entries in RoomDay_Customer ( for 3 people for example)

The only bottleneck I can think of is the identities. Is there a more efficenet way to update a typed dataset aside from calling its update method?