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?

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?

Question about Identity generation and issues..

Has anyone run into this issue before?
I'm creating test scenarios doing Deletes/Updates/Inserts and after the test
scenario is completed need to remove any and all changes to the Database
that were made. The connection is made and many statements are run and all
are encapsulated in a single transaction. When all transactions are
completed a rollback is performed.
The problem arises here:
Table A has an identity column defined as the primary key on it.
A process (run in a .Net transaction) executes and performs an insert into
Table A to generate an Identity but before the transaction is completed the
process runs other statements using that generated value in other tables for
reference purposes. During the time these queries run, another instance of
the same process performs the same insert allowing the table to auto
generate its identity value. A certain percentage of the time, I'll get an
error whereby a duplicate primary key violation occurs.
Has anyone encountered anything like this and is there a way around it?
I've tried setting differing Isolation levels, minimizing the transaction to
only the section that is actually changing data, unique transaction
names...?
Thanks
DAre you using a column with IDENTITY property or are you generating the
sequencial value?. If you are generating it, can we see the code used?
AMB
"news" wrote:

>
> Has anyone run into this issue before?
>
> I'm creating test scenarios doing Deletes/Updates/Inserts and after the te
st
> scenario is completed need to remove any and all changes to the Database
> that were made. The connection is made and many statements are run and al
l
> are encapsulated in a single transaction. When all transactions are
> completed a rollback is performed.
>
> The problem arises here:
>
> Table A has an identity column defined as the primary key on it.
>
> A process (run in a .Net transaction) executes and performs an insert into
> Table A to generate an Identity but before the transaction is completed th
e
> process runs other statements using that generated value in other tables f
or
> reference purposes. During the time these queries run, another instance o
f
> the same process performs the same insert allowing the table to auto
> generate its identity value. A certain percentage of the time, I'll get a
n
> error whereby a duplicate primary key violation occurs.
>
> Has anyone encountered anything like this and is there a way around it?
> I've tried setting differing Isolation levels, minimizing the transaction
to
> only the section that is actually changing data, unique transaction
> names...?
>
> Thanks
>
> D
>
>|||The column has been defined as an INT IDENTITY(1,1) NOT NULL
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E07EDB1D-6F14-4F87-9D77-13E58115A473@.microsoft.com...
> Are you using a column with IDENTITY property or are you generating the
> sequencial value?. If you are generating it, can we see the code used?
>
> AMB
> "news" wrote:
>
test
all
into
the
for
of
an
transaction to|||Is there a way to reproduce the problem in our computer?
AMB
"news" wrote:

> The column has been defined as an INT IDENTITY(1,1) NOT NULL
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:E07EDB1D-6F14-4F87-9D77-13E58115A473@.microsoft.com...
> test
> all
> into
> the
> for
> of
> an
> transaction to
>
>|||>> The problem arises here: Table A has an identity column defined as
the primary key on it. <<
That is a problem! You have hired someone who does not know better
than use IDENTITY as a key in a schema. The right thing to do is
re-design that table with a proper relational key. This is a much
better idea than any of the kludges you are going to be given.|||Instead claiming that the sky is falling like senor Joe and give up identity
columns, there are a couple of more practical ideas. The most important
solution is to reproduce the problem. What version of SQL Server? How many
records in the database? How hard is the table being hit? What do the
inserts look like?
For example, one solution would be a simple retry mechanism (which btw, Joe,
would be needed with any sort of sequencing mechanism).
Thomas
"news" <DavidP> wrote in message
news:ugrawntOFHA.2144@.TK2MSFTNGP09.phx.gbl...
>
> Has anyone run into this issue before?
>
> I'm creating test scenarios doing Deletes/Updates/Inserts and after the
> test
> scenario is completed need to remove any and all changes to the Database
> that were made. The connection is made and many statements are run and
> all
> are encapsulated in a single transaction. When all transactions are
> completed a rollback is performed.
>
> The problem arises here:
>
> Table A has an identity column defined as the primary key on it.
>
> A process (run in a .Net transaction) executes and performs an insert into
> Table A to generate an Identity but before the transaction is completed
> the
> process runs other statements using that generated value in other tables
> for
> reference purposes. During the time these queries run, another instance
> of
> the same process performs the same insert allowing the table to auto
> generate its identity value. A certain percentage of the time, I'll get
> an
> error whereby a duplicate primary key violation occurs.
>
> Has anyone encountered anything like this and is there a way around it?
> I've tried setting differing Isolation levels, minimizing the transaction
> to
> only the section that is actually changing data, unique transaction
> names...?
>
> Thanks
>
> D
>|||In a schema where you have several branches to a snowflake describing say a
insurance distributor model, I needed a way to create a unique identifier
for each instance without having a three part key to carry to each relating
table.
The server is SQL Server 2000 with latest service packs and patches et al.
The table is being hit pretty hard as this data is being loaded to load up
the insurance distributor plans and such.
Currently the table has only in the neighborhood of 100039484 records
Inserts are simple inserts where there is one value set being put in i.e.:
INSERT INTO InsuranceDistributorPlan
(DistributorID, PlanPeriodId, PlanTypeID, Name, Source)
-- VALUES (-90000, -2004, 1, 'Davids test category', 4);
SELECT -90000, -2004, 1, 'Jeffs test category', 4
This creates and unique identifier for me to then relate other data items
to.
This type of query is being run from a .Net application (explicit
transactions don't appear to affect the issue).
Identities are reset when a rollback occurs so why would this issue arrise?
"Thomas" <thomas@.newsgroup.nospam> wrote in message
news:%23X7fSKyOFHA.1932@.tk2msftngp13.phx.gbl...
> Instead claiming that the sky is falling like senor Joe and give up
identity
> columns, there are a couple of more practical ideas. The most important
> solution is to reproduce the problem. What version of SQL Server? How many
> records in the database? How hard is the table being hit? What do the
> inserts look like?
> For example, one solution would be a simple retry mechanism (which btw,
Joe,
> would be needed with any sort of sequencing mechanism).
>
> Thomas
>
> "news" <DavidP> wrote in message
> news:ugrawntOFHA.2144@.TK2MSFTNGP09.phx.gbl...
into
transaction
>|||I presume that SQL has been patched to service pack 3a? What
indexes are on the table? Script them using the QA and post
them to them to group if you can. (Change the column names
and/or index names if you like)
There is a knowledge base article on an identity problem,
however it is old and has presumably been fixed in one of
the service packs. (http://tinyurl.com/46yme)
Thomas
"news" <DavidP> wrote in message
news:%23P$Hwh5OFHA.3512@.TK2MSFTNGP15.phx.gbl...
> In a schema where you have several branches to a snowflake
> describing say a
> insurance distributor model, I needed a way to create a
> unique identifier
> for each instance without having a three part key to carry
> to each relating
> table.
> The server is SQL Server 2000 with latest service packs
> and patches et al.
> The table is being hit pretty hard as this data is being
> loaded to load up
> the insurance distributor plans and such.
> Currently the table has only in the neighborhood of
> 100039484 records
> Inserts are simple inserts where there is one value set
> being put in i.e.:
> INSERT INTO InsuranceDistributorPlan
> (DistributorID, PlanPeriodId, PlanTypeID, Name, Source)
> -- VALUES (-90000, -2004, 1, 'Davids test category',
> 4);
> SELECT -90000, -2004, 1, 'Jeffs test category', 4
> This creates and unique identifier for me to then relate
> other data items
> to.
> This type of query is being run from a .Net application
> (explicit
> transactions don't appear to affect the issue).
> Identities are reset when a rollback occurs so why would
> this issue arrise?
> "Thomas" <thomas@.newsgroup.nospam> wrote in message
> news:%23X7fSKyOFHA.1932@.tk2msftngp13.phx.gbl...
> identity
> Joe,
> into
> transaction
>|||On Thu, 7 Apr 2005 10:36:42 -0700, news wrote:
(snip)
>Identities are reset when a rollback occurs
(snip)
Hi news,
While I must admit that I don't really understand the proble you
describe, I do know that this statement is incorrect. A rollback will
not reset the identity.
CREATE TABLE Test (Ident int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Descr varchar(60) NOT NULL)
go
INSERT Test (Descr)
VALUES ('First row')
go
BEGIN TRANSACTION
INSERT Test (Descr)
VALUES ('Second row - will disappear after rollback')
SELECT * FROM Test
ROLLBACK TRANSACTION
go
INSERT Test (Descr)
VALUES ('Third row, to prove that IDENTITY value 2 is not reused')
SELECT * FROM Test
go
DROP TABLE Test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||sorry, that was a typo..
My statement was meant to say that
"Identities aren't reset"
Thanks,
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:am2b51t2qil494ud2uc4nsmrd7glqe0d4m@.
4ax.com...
> On Thu, 7 Apr 2005 10:36:42 -0700, news wrote:
> (snip)
> (snip)
> Hi news,
> While I must admit that I don't really understand the proble you
> describe, I do know that this statement is incorrect. A rollback will
> not reset the identity.
> CREATE TABLE Test (Ident int IDENTITY(1,1) NOT NULL PRIMARY KEY,
> Descr varchar(60) NOT NULL)
> go
> INSERT Test (Descr)
> VALUES ('First row')
> go
> BEGIN TRANSACTION
> INSERT Test (Descr)
> VALUES ('Second row - will disappear after rollback')
> SELECT * FROM Test
> ROLLBACK TRANSACTION
> go
> INSERT Test (Descr)
> VALUES ('Third row, to prove that IDENTITY value 2 is not reused')
> SELECT * FROM Test
> go
> DROP TABLE Test
> go
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)