Adding record to disconnected DataTable slow

  • Thread starter Thread starter Turtle MacDermott
  • Start date Start date
T

Turtle MacDermott

I have an ADO.NET datatable which I created from scratch - just defined its
columns and started adding data.
I find that when I step through adding a row in the debugger, it's quite
slow - up to several seconds - at the point where I actually add the row to
the table.

Any ideas?

TIA
- Turtle
 
How many rows are you adding?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I'm only adding one row at a time.
The table is the datasource for a datagrid.
It has 8 fields.

Currently, the first row is added and displays correctly.
The second one throws a NullReferenceException which I can't seem to trap,
even though I have a Try - Catch around the line of code which adds the row
to the table and generates the error.

I would be incredibly grateful for any insights you might be able to offer!

TIA
- Turtle
 
Turtle,

We probably would need to see what else you have going on with your
datatable besides 8 columns. 8 columns in itself is not such a big deal - it
certainly shouldn't throw an exception in most simplistic cases.

But there could be constraints on the datatable that might take time. There
could be weird datatypes specified, there could be relations, there could be
firing events .. who knows what else.

The reasons for the long inserts could be manifold, but let me assure you, a
simple datatable with 8 simple columns and a new row added into it shouldn't
be slow.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
 
I'm just getting the hang of ADO.NET, so I'm probably doing something unwise
here.
I've got other very similar code which seems to work fine.

Of my 8 columns, the first is a date/time type; the rest are integers and
doubles, about half each.
I have no relations defined.
I'm not at all sure what a firing event would mean in this case;
it's just a completely disconnected datatable; I create it using the New
keyword and add the columns one by one.

Would it help if I used a primary key?

- Turtle
 
How MANY rows are you adding? How many rows does the DataSet have? How many
do you plan to read into the DataSet?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Gracious!
How can I state it more clearly?
I am using a Data Table, not a DataSet.
When I create the table, it's empty.
I add one row -
this is slow, but it works.
Then I try to add another row,
and get an untrappable NullReferenceException.

That's as far as I've gotten.
Each row represents the results of a testing cycle for an attached machine.
I'd like to let my users run the machine as long as they like (even
overnight, or several days), but if you feel it's important to limit the
number of records, perhaps you could advise me on a suitable limit.

TIA
- Turtle
 
Okay, you should be able to add rows to the DataTable quite quickly. The
reason I was asking about the number of rows is that when you add a lot of
rows (thousands), the DataTable structure gets bogged down. Can we see the
code that's failing? How do you build the rows to add to the DataTable?
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Hey Turtle,

Are you adding rows - with the datatable associated as the datasource for
the datagrid?

Can you try a quick experiment ? Disassociate the DataTable with any
Databindings it might be a part of. Also any dataviews that might be built
upon it.

Now add the row ... is it still slow?

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
 
Here's some of the code:
(The application is extensive; I hope I've gotten the relevant parts.)

At the top of the form declaration:
Private dtLoading As DataTable

When I start the test:
dtLoading = New DataTable

dtLoading.Columns.Add("Time",
Type.GetType("System.DateTime"))

dtLoading.Columns.Add("Position",
Type.GetType("System.Int32"))

dtLoading.Columns.Add("TimeInPosition",
Type.GetType("System.Double"))

dtLoading.Columns.Add("Count", Type.GetType("System.Int32"))



dtLoading.Columns.Add("Centroid",
Type.GetType("System.Double"))

dtLoading.Columns.Add("FWHM", Type.GetType("System.Double"))

dtLoading.Columns.Add("CPM", Type.GetType("System.Double"))

dtLoading.Columns.Add("Approach",
Type.GetType("System.Int32"))

Me.dgLoadingResults.DataSource = dtLoading



When I add a row:

(This is one especially simple aberration I've tried.

I've also tried using a DataRow.)

Dim DataStuff(7) As Object

DataStuff(0) = Now

DataStuff(1) = 8

DataStuff(2) = 0

DataStuff(3) = 0

DataStuff(4) = 0

DataStuff(5) = 0

DataStuff(6) = 0

DataStuff(7) = 0





Try

Me.dgLoadingResults.DataSource = Nothing

dtLoading.Rows.Add(DataStuff) '<- ******

Me.dgLoadingResults.DataSource = dtLoading

Catch ex As System.Exception

BVA.WriteError("Adding New Row" & vbCrLf & ex.Message)

End Try





When the line I've marked with the ***** is executed, the whole thing stops
with a NullReferenceException.

Execution does not move to the BVA.WriteError... line.



Thanks so much for looking at this!



- Turtle
 
I think I've posted what you're asking for in a reply to William Vaughn
below.

Thanks so much for looking at it!

- Turtle
 
Yes, as you can see in the code I posted, I'd tried disassociating it for
the moment that I added the row -
although I'd certainly rather not have to do that.

That particular work-around was not effective.

But when I commented out enough code to remove the binding to the table
completely, the problem did seem to go away.

So -
where do we go from here?

I'd really like my users to be able to see the data in the grid as it comes
in...

- Turtle
 
Okay so now that we have the problem zero'ed down to databinding being
slow - what do we do next?

Two solutions ---

a) If you must need updateable datagrid - keep using binding, but bind
differently, instead bind with an array returned by datatable.select (i.e.
bind a subset), not only that fill the datatable on a seperate thread, do
the select on that thread, and then use form.invoke to switch control to the
main UI for binding. That way, your binding and filling goes in parallel -
it'd work faster.

b) If you can get around by creating a readonly view of the data (like
asp.net does - everything is read only until you hit "edit"), so in a
winforms app it could be a double click popping up another modal dialog
maybe? If you can do that .. screw databinding and use table events to add
rows to a custom visible display.

I think solution b is better.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
 
I like the sound of B better, too!
I'd actually prefer to have the data read-only.

Unfortunately, I'm too much of a beginner to know how to implement your
suggestion.
Would you happen to know of some sample or a tutorial somewhere which could
help me learn these techniques?

TIA
- Turtle
 
Turtle,

It's actually quite simple, just use the Table.TableNewRow event to display
every newly added row in a control. Which control? It could be a datagrid by
why use the hyperbole of complexity - just use a listview instead.

As a matter of fact, for a simple test/proof of concept, just use a
multiline textbox. Once that works, and the time taken to add the row is
reasonable, then you can use any other control to acheive the same.

As far as sample goes - there is no canned sample (well there might be, but
not that I know of), but it's quite simple .. do 2 things ---

a) Play with TableNewRow event in a simpler app.
b) Look up a sample of ListView on the internet.

Combine a & b .. and you just parked your car.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
 
Many thanks!
It's working now.
I'm seeing that there is a great deal I don't understand yet about what
does and doesn't work with ADO.NET, but it's a great relief now to have a
model that does work!

- Turtle
 
And I'm awfully glad to have found this newsgroup!

Having acquired some degree of competence with Access programming (so that
I'm now often the one others are coming to with questions), I feel like a
"fish (turtle) out of water" in .NET. What you've provided is just the kind
of mentoring I've been missing.
I'll be back, I'm sure!

- Turtle
 
Back
Top