Updates from a DataGridView

[Bill Schanks]

VB.NET 2005
SQL Server 2000

I have a datagridview that users will update. When there are only a
few records the update is fine. However when they update 100-200 of
the rows the udpate can take 2+ Mins.

Is this normal performance, as the datagridview was not meant to
update this many records at once?

Here is the code I am using to save the data:
<<Snip>>
Me.Validate()
Me.Spoc_ev_resultsBindingSource.EndEdit()
Dim iUpdate As Integer = _

Me.Spoc_ev_resultsTableAdapter.Update(Me.RCT_EV_QADataSet.spoc_ev_results)
<<Snip>>

Here is the Stored Procedure that gets called:
ALTER procedure [dbo].[spoc_ev_results_update]
@ItemID int,
@Status_ID int,
@UserID int,
@Notes varchar(1000) = NULL
AS

UPDATE dbo.t_EV_ExcepVal_Accts
SET
Status_ID = @Status_ID,
Notes = @Notes,
LastMaintBy_ID = @UserID,
LastMaintDate = getdate()
WHERE
ID = @ItemID

 

[Cor Ligthert[MVP]]

Bill,

It seems that you do something terrible wrong, a dataadapter is updating
only updating (datasets, datatable, datarow(s)).

Those rows have all a rowstate which should be have not a value unchanged,
it seems to me that you are doing something where you ignore the rowstate.

Cor

 

[Michel Posseth [MCP]]

Well this might give you some extra performance

<<Snip>>
Me.Validate()
Me.Spoc_ev_resultsBindingSource.EndEdit()



if Me.RCT_EV_QADataSet.spoc_ev_results.haschanges then
Dim iUpdate As Integer = _
Me.Spoc_ev_resultsTableAdapter.Update(Me.RCT_EV_QADataSet.spoc_ev_results.getchanges)

end if
<<Snip>>

what this small change does
Only perform the update when there are changes in the rowset , if there are
updates only send the changed records

hth

Michel

 

[Cor Ligthert[MVP]]

Michel,

As in past too thought that the get changes was needed, was this in my idea
a mistake from me.
The dataadapter itself is normaly only handling changed records.

In this case is at least a dataset.acceptchanges needed, as the getchanges
is a copy of the set, and the rowstates in the origianl dataset are not
changed by this.

Cor

Well this might give you some extra performance

<<Snip>>
Me.Validate()
Me.Spoc_ev_resultsBindingSource.EndEdit()



if Me.RCT_EV_QADataSet.spoc_ev_results.haschanges then
Dim iUpdate As Integer = _
Me.Spoc_ev_resultsTableAdapter.Update(Me.RCT_EV_QADataSet.spoc_ev_results.getchanges)

end if
<<Snip>>

what this small change does
Only perform the update when there are changes in the rowset , if there
are updates only send the changed records

hth

Michel

VB.NET 2005
SQL Server 2000

I have a datagridview that users will update. When there are only a
few records the update is fine. However when they update 100-200 of
the rows the udpate can take 2+ Mins.

Is this normal performance, as the datagridview was not meant to
update this many records at once?

Here is the code I am using to save the data:
<<Snip>>
Me.Validate()
Me.Spoc_ev_resultsBindingSource.EndEdit()
Dim iUpdate As Integer = _

Me.Spoc_ev_resultsTableAdapter.Update(Me.RCT_EV_QADataSet.spoc_ev_results)
<<Snip>>

Here is the Stored Procedure that gets called:
ALTER procedure [dbo].[spoc_ev_results_update]
@ItemID int,
@Status_ID int,
@UserID int,
@Notes varchar(1000) = NULL
AS

UPDATE dbo.t_EV_ExcepVal_Accts
SET
Status_ID = @Status_ID,
Notes = @Notes,
LastMaintBy_ID = @UserID,
LastMaintDate = getdate()
WHERE
ID = @ItemID

 

[Bill Schanks]

Ok, it's only updating the rows that have changed. My problem is when
the user changes a lot of the rows (100-200 of them), then it takes a
long time to update.

The datagridview could have 800 records on it, but if they only change
2 of them everything is fine. My question is: Is it normal for it take
2+ Mins to update 200 rows?

Michel,

As in past too thought that the get changes was needed, was this in my idea
a mistake from me.
The dataadapter itself is normaly only handling changed records.

In this case is at least a dataset.acceptchanges needed, as the getchanges
is a copy of the set, and the rowstates in the origianl dataset are not
changed by this.

Cor

Well this might give you some extra performance

if Me.RCT_EV_QADataSet.spoc_ev_results.haschanges then
Dim iUpdate As Integer = _
Me.Spoc_ev_resultsTableAdapter.Update(Me.RCT_EV_QADataSet.spoc_ev_results.getchanges)

end if
<<Snip>>

what this small change does
Only perform the update when there are changes in the rowset , if there
are updates only send the changed records

hth

Michel

VB.NET 2005
SQL Server 2000
I have a datagridview that users will update. When there are only a
few records the update is fine. However when they update 100-200 of
the rows the udpate can take 2+ Mins.
Is this normal performance, as the datagridview was not meant to
update this many records at once?
Here is the code I am using to save the data:
<<Snip>>
Me.Validate()
Me.Spoc_ev_resultsBindingSource.EndEdit()
Dim iUpdate As Integer = _
Me.Spoc_ev_resultsTableAdapter.Update(Me.RCT_EV_QADataSet.spoc_ev_results)
<<Snip>>
Here is the Stored Procedure that gets called:
ALTER procedure [dbo].[spoc_ev_results_update]
@ItemID int,
@Status_ID int,
@UserID int,
@Notes varchar(1000) = NULL
AS
UPDATE dbo.t_EV_ExcepVal_Accts
SET
Status_ID = @Status_ID,
Notes = @Notes,
LastMaintBy_ID = @UserID,
LastMaintDate = getdate()
WHERE
ID = @ItemID

 

[Bill Schanks]

Yea, I think the slowless is from the multiple round trips. How would
I go about doing one round trip? Say I have 100 Updates on the grid.
How would I update multiple rows with different values with one update
statement?

 

[Jason Keats]

Yea, I think the slowness is from the multiple round trips. How would
I go about doing one round trip? Say I have 100 Updates on the grid.
How would I update multiple rows with different values with one update
statement?

Here's some reading:

http://msdn.microsoft.com/en-us/library/aadf8fk2.aspx

http://devcenter.infragistics.com/Support/KnowledgeBaseArticle.Aspx?ArticleID=8270

http://www.asp.net/learn/data-access/tutorial-63-cs.aspx

HTH