.Delete on DataRow effeciency problem

  • Thread starter Thread starter berandor
  • Start date Start date
B

berandor

I have two tables within a dataset. Each table has
roughly 200k rows in it each row is 8-10 columns.

In case A: I setup a DataRow[] rows = ...Select(null,
SortCriteria);

i then iterate the DataRow array and end up deleting
roughly 15k of the rows.

This happens very quickly (a second or two)

In case B: I setup a DataRow[] rows = ...Select
(SelectionCriteria, null);

I then iterate this array and delete roughly 4k rows.

this happens quite a bit more slowly (about 35 seconds)

In neither case am I saving this data back to the
database and I have eliminated all additional code so
that I am simply deleting.

Is this common? I have another approach for solving my
overall business problem and it will be fast, it
unfortunately will not be as elegant as using delete.
 
bill,

I don't want to delete these items from the database,
rather I want to delete them from the table within the
dataset. I have found ado.net to be quite sufficient for
handling 200k rows of data very efficiently.

further, if you read the original post you notice a
rather marked difference between 14k deletes occurring in
under 2 seconds and 4k deletes requiring over 35 seconds.

The only difference that I can identify is in how the
DataRow arrays were originally populated. The first was
populated with a select using only the sorting feature,
the second actually had a filtering clause.

My suspicion is that the implementation of
DataTable.Select has caused one approach to be more
effecient than another.

I was hoping for an answer that confirmed this suspicion
or provided other insite to the effecient utilization of
a DataTable object.
-----Original Message-----
If I was that concerned about performance, I sure wouldn't bring 200K rows
to the client. I would write a smart DELETE statement that deleted them in
place.

--
____________________________________
Bill Vaughn
MVP, hRD
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 have two tables within a dataset. Each table has
roughly 200k rows in it each row is 8-10 columns.

In case A: I setup a DataRow[] rows = ...Select(null,
SortCriteria);

i then iterate the DataRow array and end up deleting
roughly 15k of the rows.

This happens very quickly (a second or two)

In case B: I setup a DataRow[] rows = ...Select
(SelectionCriteria, null);

I then iterate this array and delete roughly 4k rows.

this happens quite a bit more slowly (about 35 seconds)

In neither case am I saving this data back to the
database and I have eliminated all additional code so
that I am simply deleting.

Is this common? I have another approach for solving my
overall business problem and it will be fast, it
unfortunately will not be as elegant as using delete.
Click to expand...


.
Click to expand...
 
Back
Top