Why does it take really long to delete DataRows?

  • Thread starter Thread starter Tolga Erdogus
  • Start date Start date
T

Tolga Erdogus

Hi,

I am looping through 800 DataRows and calling Delete on them. It takes
almost a minute even though I call BeginLoadData and EndLoadData. Any ideas
why this happens and how I can make it happen in less than a second?

Thanks
 
Sure. Execute a smart DELETE query that deletes rows in the database that
qualify based on a WHERE clause. It should take under a second to delete 800
rows with a good index.

--
____________________________________
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.
__________________________________
 
Bill,

that's already part of what I do. My issue is that after I do that in a web
service, I still have to make my client side dataset reflect those changes.
If I do a complete clear and fill on my client side dataset, it takes very
long because there is a lot of data. I want to just make a part of the
client side dataset (in a particular table) dissapear, because I know I just
deleted those rows directly from the db.

Any ideas?

Thanks
 
Tolga Erdogus said:
Bill,

that's already part of what I do. My issue is that after I do that in a web
service, I still have to make my client side dataset reflect those changes.
If I do a complete clear and fill on my client side dataset, it takes very
long because there is a lot of data. I want to just make a part of the
client side dataset (in a particular table) dissapear, because I know I just
deleted those rows directly from the db.

Any ideas?
Click to expand...

I haven't tried this out for performance, but here's an idea. If you use a
DataSet on the server side, why not call GetChanges on it and return the
changes to the client side, which could then Merge them into its own
DataSet.

I don't know whether this would perform better, or worse than simply
looping. It could perform better, since ADO.NET has the opportunity to
optimize the operation.
 
The DataTable has much more than 800 rows. I don't want to delete them
all - I only want to delete the 800 that pertain to a master reocrd.

Thanks
 
Hello !

How much records do you have in DataTable ? Let say,
if Y x 10000 of records, then - yes, it will be slow to delete
records. Because records are stored in array and deletion
of each record requires to move records in array and reallocate
memory.

So, as a solution may be possible just to "mark" records in
memory as "deleted". So your code will know - records are
"deleted". And delete records from server using single
"DELETE" command.

Regards,
Dmitry
 
How about using a DataView to hide the deleted rows?

--
____________________________________
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.
__________________________________
 
hmm,

thanks very much for your reply.

While this is a good idea, I am surprised that there isn't a built-in way to
delete a subset of the records in a client side datatable in an acceptable
timeframe.
Your recommendation will need additional client side filtering to omit the
datarows that are marked deleted. This could be either through the
datatable or a seperate dataview as William suggested.

I guess I am just surprised that a delete method on the datatable that
exists for exactly the reason I need it does not run in a real-life
timeframe. There are MANY ways one can get around speed problems stemming
from memory deallocation and index adjustments...

Thanks
 
The DataTable was designed around dealing with far fewer rows that what
you're expecting it to handle. In a Web application (at least those you
expect to scale) you can't return 800 rows for each client and expect the
central server to manage them for you. In a client/server application, you
also limit scalability and performance as these rows have to be transported
to the client and managed locally while the data gets stale. Ordinarily we
create "views" on current data and execute bulk changes on the server where
you don't have to return rows so you can delete them. These views show just
the rows the user can view in a page or two (about 50 rows) and while this
takes a bit more thought and planning (and code), the result is a far faster
and more scalable system.

hth

--
____________________________________
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.
__________________________________
 
Bill,

don't take this wrong, but I am well aware of good design paradigms for
n-tier applications. You are disregarding the fact that the lowest common
denominator of what our users need to see (per the business requirements)
amounts to about 800 detail rows per master row. This information all
together is an information unit and can not be logically split.

Ideally I would have loved to have split the information in to smaller
chunks and provided some other filter criteria that governs which "chunk" to
display, but in this case the only way of doing this is really through a
virtual dataset technology such as ComponentOne's data objects, which
automatically brings rows on demand for large datasets.

Just because a paradigm seems nice and logical doesn't necessarily mean it
will fit everybody's needs. Nor does it mean that anybody whose needs are
not met by the paradigm must have designed their architecture wrong. Thin
client database development is not a must and does not fit absolutely every
situation. I would argue that, if Microsoft indeed has designed ADO.NET to
be only a thin-client approach with no support for large client-side
datasets, they really did their homework wrong. Obviously when large chunks
of data don't need to be brought down to the client, you can do your 3rd
tier processing for the data on a server, but there are many instances when
the client would need medium to large datasets, for example to perform some
visualization on it.

DataSets and their related classes (such as DataTable, DataView, etc) seem
to be presented by Microsoft as not just a thin-client WEB approach, but
also as a client server approach (whether two tier or n-tier) This is
evident from the fact that they support both web forms as well as winforms.
Additionally, to my knowledge, in .NET, there is really no other
out-of-the-box way than ADO.NET to design database based winform
applications. So I am stuck with ADO.NET for my winform application that
has large client side data requirements.

Thanks,

Tolga
 
DataSets and their related classes (such as DataTable, DataView, etc) seem
to be presented by Microsoft as not just a thin-client WEB approach, but
also as a client server approach (whether two tier or n-tier) This is
evident from the fact that they support both web forms as well as winforms.
Additionally, to my knowledge, in .NET, there is really no other
out-of-the-box way than ADO.NET to design database based winform
applications. So I am stuck with ADO.NET for my winform application that
has large client side data requirements.
Click to expand...

Yes, but ADO.NET doesn't equate to datasets, datatables and dataviews.
You are perfectly free to, for instance, write an SQL query to retrieve
the data you want to use on the client, define a structure/class to hold
each data row and use a generic collection to store them on the client.

It sounds like for your application you'd be better off going that
route. You'll have to build some infrastructure, but you'll end up with
something tailored exactly to what you need with no extraneous
(performance-sapping) frills.

-- Rick
 
Yes, but there are ways to do intelligent sub queries on the detail rows. No
human I know can deal with all 800 rows at a time. Yes, I expect you'll have
to use a third-party tool to automatically scroll through the detail rows
but the code to do so is not that complex--you could write your own class to
do so fairly quickly.

--
____________________________________
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 agree with you, but we have some visualisation tools that represent the
800 rows in a way that allows them to do visual pattern matching... You
would be suprised as to how much a well designed data-visualizer can help...

I don't want to prolong this thread too long, but just out of curiosity, in
the age of 3.X GHz Pentium 4 computers, how can it possibly take 1 minute to
delete 800 rows from memory. We're not even talking about commiting to disk
here. There has got to be some room for improvement on the performance of
the delete method for a DataRow...

My understanding is that memory deallocation happens asynchronously through
the garbage collector, so the only remaining synchronous to-do would be the
readjustment of the indexes for the array. I know i am probably
oversimplifying, but I think I have a general idea of what needs to be done
and a minute to do that kind of processing is way to much...

Thanks
 
I understand - thanks for your input.

Tolga

Guinness Mann said:
Yes, but ADO.NET doesn't equate to datasets, datatables and dataviews.
You are perfectly free to, for instance, write an SQL query to retrieve
the data you want to use on the client, define a structure/class to hold
each data row and use a generic collection to store them on the client.

It sounds like for your application you'd be better off going that
route. You'll have to build some infrastructure, but you'll end up with
something tailored exactly to what you need with no extraneous
(performance-sapping) frills.

-- Rick
Click to expand...
 
Back
Top