SetColumnError Takes a Long Time to Execute

[Charles Law]

I have a data table with ~1000 rows. I go through the rows one at a time,
checking the contents. If I find a cell with an error, e.g containing null,
I use SetColumnError on the data row to flag it.

Here's my code:

For Each dr As DataRow In MyDataTable.Row s
Dim MyValue As Integer

If dr("ID").Equals (DBNull.Value) Then
dr.SetColumnErr or("ID", "ID cannot be null") ' this take 1 to 2
seconds each time
Else
MyValue = CType(dr("ID"), Integer)
End If

...
Next

The problem is each call to SetColumnError takes about 1-2 seconds to
execute. This is an ice-age, and if I have 100 cells to flag it takes 100
seconds to go through the data.

Does anyone know why it takes so long for this method to execute?

I am using VS2008 SP1 on XP SP3. I am targeting V3.5 of the framework,
although I have V3.5 SP1 installed. This was a problem before I installed
SP1 though. The back-end is SQL Server 2005, but the data are disconnected,
so I can't see that being an issue.

TIA

Charles

[Cor Ligthert[MVP]]

Hi Charles,

Takes in Brittain a second the same time as at the continent?

:-)

\\\
Module Module1

Sub Main()
Dim a As New Stopwatch
a.Start()
Dim myDataTable As New DataTable
Dim ID As New DataColumn("ID" )
myDataTable.Col umns.Add(ID)

For i As Integer = 0 To 100
Dim dr = myDataTable.New Row
dr("ID") = DBNull.Value
myDataTable.Row s.Add(dr)
Next

For Each dr As DataRow In myDataTable.Row s
Dim MyValue As Integer

If dr("ID").Equals (DBNull.Value) Then
dr.SetColumnErr or("ID", "ID cannot be null") ' this take
1 to 2 seconds each time
Else
MyValue = CType(dr("ID"), Integer)
End If
Next
Console.WriteLi ne(a.ElapsedMil liseconds / 1000)
Console.ReadLin e()
End Sub

End Module
///

Cor

"Charles Law" <bl***@nowhere. comschreef in bericht
news:%2******** ********@TK2MSF TNGP04.phx.gbl. ..

>I have a data table with ~1000 rows. I go through the rows one at a time,
checking the contents. If I find a cell with an error, e.g containing null,
I use SetColumnError on the data row to flag it.

Here's my code:

For Each dr As DataRow In MyDataTable.Row s
Dim MyValue As Integer

If dr("ID").Equals (DBNull.Value) Then
dr.SetColumnErr or("ID", "ID cannot be null") ' this take 1 to 2
seconds each time
Else
MyValue = CType(dr("ID"), Integer)
End If

...
Next

The problem is each call to SetColumnError takes about 1-2 seconds to
execute. This is an ice-age, and if I have 100 cells to flag it takes 100
seconds to go through the data.

Does anyone know why it takes so long for this method to execute?

I am using VS2008 SP1 on XP SP3. I am targeting V3.5 of the framework,
although I have V3.5 SP1 installed. This was a problem before I installed
SP1 though. The back-end is SQL Server 2005, but the data are
disconnected, so I can't see that being an issue.

TIA

Charles

[Charles Law]

Hi Cor

Good to hear from you. I tried your test, and it takes only a fraction of a
second, so there is obviously something about my data table that is causing
the problem.

I've just had another thought: I am using the data table as the data source
for a grid. I wonder if the fact that the grid is bound to the data table is
what is making it so slow. I'll have to look into that.

Cheers

Charles
"Cor Ligthert[MVP]" <no************ @planet.nlwrote in message
news:1F******** *************** ***********@mic rosoft.com...

Hi Charles,

Takes in Brittain a second the same time as at the continent?

:-)

\\\
Module Module1

Sub Main()
Dim a As New Stopwatch
a.Start()
Dim myDataTable As New DataTable
Dim ID As New DataColumn("ID" )
myDataTable.Col umns.Add(ID)

For i As Integer = 0 To 100
Dim dr = myDataTable.New Row
dr("ID") = DBNull.Value
myDataTable.Row s.Add(dr)
Next

For Each dr As DataRow In myDataTable.Row s
Dim MyValue As Integer

If dr("ID").Equals (DBNull.Value) Then
dr.SetColumnErr or("ID", "ID cannot be null") ' this take
1 to 2 seconds each time
Else
MyValue = CType(dr("ID"), Integer)
End If
Next
Console.WriteLi ne(a.ElapsedMil liseconds / 1000)
Console.ReadLin e()
End Sub

End Module
///

Cor

"Charles Law" <bl***@nowhere. comschreef in bericht
news:%2******** ********@TK2MSF TNGP04.phx.gbl. ..

>>I have a data table with ~1000 rows. I go through the rows one at a time,
checking the contents. If I find a cell with an error, e.g containing
null, I use SetColumnError on the data row to flag it.

Here's my code:

For Each dr As DataRow In MyDataTable.Row s
Dim MyValue As Integer

If dr("ID").Equals (DBNull.Value) Then
dr.SetColumnErr or("ID", "ID cannot be null") ' this take 1 to 2
seconds each time
Else
MyValue = CType(dr("ID"), Integer)
End If

...
Next

The problem is each call to SetColumnError takes about 1-2 seconds to
execute. This is an ice-age, and if I have 100 cells to flag it takes 100
seconds to go through the data.

Does anyone know why it takes so long for this method to execute?

I am using VS2008 SP1 on XP SP3. I am targeting V3.5 of the framework,
although I have V3.5 SP1 installed. This was a problem before I installed
SP1 though. The back-end is SQL Server 2005, but the data are
disconnected , so I can't see that being an issue.

TIA

Charles

[Andrew Morton]

Charles Law wrote:

Good to hear from you. I tried your test, and it takes only a
fraction of a second, so there is obviously something about my data
table that is causing the problem.

I've just had another thought: I am using the data table as the data
source for a grid. I wonder if the fact that the grid is bound to the
data table is what is making it so slow. I'll have to look into that.

Maybe .SuspendLayout and .ResumeLayout on the grid would help?

Andrew

[Charles Law]

Hi Andrew

I did try that and it didn't improve things. It seems that it is the fact
that the data table is the data source for the grid. I set the data source
to Nothing and the SetColumnError takes a fraction of a second. So, I create
a copy of my data table to work on and then use the copy as the data source
at the end. I couldn't really think of a better way.

Cheers

Charles
"Andrew Morton" <ak*@in-press.co.uk.inv alidwrote in message
news:6j******** ****@mid.indivi dual.net...

Charles Law wrote:

>Good to hear from you. I tried your test, and it takes only a
fraction of a second, so there is obviously something about my data
table that is causing the problem.

I've just had another thought: I am using the data table as the data
source for a grid. I wonder if the fact that the grid is bound to the
data table is what is making it so slow. I'll have to look into that.

Maybe .SuspendLayout and .ResumeLayout on the grid would help?

Andrew

[Steve Gerrard]

Charles Law wrote:

Hi Andrew

I did try that and it didn't improve things. It seems that it is the
fact that the data table is the data source for the grid. I set the
data source to Nothing and the SetColumnError takes a fraction of a
second. So, I create a copy of my data table to work on and then use
the copy as the data source at the end. I couldn't really think of a
better way.
Cheers

Charles

Instead of SuspendLayout on the grid control, which really only effects the
positioning on the form, you might try BeginLoadData and EndLoadData on the data
table. This should turn off notifications during your modifications, which might
cut down on all the grid updating until you are done.

Rather than makiing a copy, I would also consider just setting the grid
datasource to nothing, modifying the table, and then reassigning it as the
datasource.

[Charles Law]

Hi Steve

I tried the Nothing thing before, which did work, but decided to make a copy
instead to avoid the grid going blank during the process, but I appreciate
it will take longer when the data table has many rows.

I will try the BeginDataLoad though; that sounds like it could work.

Cheers

Charles
"Steve Gerrard" <my********@com cast.netwrote in message
news:Zp******** *************** *******@comcast .com...

Charles Law wrote:

>Hi Andrew

I did try that and it didn't improve things. It seems that it is the
fact that the data table is the data source for the grid. I set the
data source to Nothing and the SetColumnError takes a fraction of a
second. So, I create a copy of my data table to work on and then use
the copy as the data source at the end. I couldn't really think of a
better way.
Cheers

Charles

Instead of SuspendLayout on the grid control, which really only effects
the positioning on the form, you might try BeginLoadData and EndLoadData
on the data table. This should turn off notifications during your
modifications, which might cut down on all the grid updating until you are
done.

Rather than makiing a copy, I would also consider just setting the grid
datasource to nothing, modifying the table, and then reassigning it as the
datasource.