Re: ADO.NET performance at deleting records

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Alvin Bruney [ASP.NET MVP] (www.lulu.com/owc)
Date: 03/18/05 

Date: Fri, 18 Mar 2005 14:55:54 -0500

Not necessarily long, it's an unfair comparison. You most likely have an
index on the database. The dataset doesn't so you examine each record. As a
workaround, you can first filter the dataset by using the select method or
call the getchanges method of the dataset to get just the culprit rows to
delete. that should be much faster than iterating the dataset. btw, the
dataset does use an index for search but only if you use the search method 

-- 
Regards,
Alvin Bruney
[Shameless Author Plug]
The Microsoft Office Web Components Black Book with .NET
available at www.lulu.com/owc
_________________________
"Abra" <nospam@devdex.com> wrote in message 
news:u%23TqqJ%23KFHA.1392@TK2MSFTNGP10.phx.gbl...
> My C# application has a list (ListBox object), connected over a DataView
> to a dataset which corresponds to a table from a MySql database. I want
> to delete for example 4000-5000 rows from the table, which correspond to
> a certain filter.
> If I iterate the Rows from the table and check for each one the filter
> condition and, if true, I call the Delete() method, it takes several
> minutes till the respective rows are deleted. I tried also to create a
> second DataView having as RowFilter the condition for the delete. If I
> iterate over this second DataView and also Delete() the corresponding
> rows, it also takes several minutes to perform.
> But if I use a OdbcCommand object (initialized with a "DELETE FROM ...
> WHERE (...)" string), call ExecuteNonQuery() and then clear the dataset
> and reload it again from the table, it takes only 2-3 seconds to be done
> !
>
> Can somebody please explain why the first 2 methods take so long ?
>
> Thanks in advance.
> Abra
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: OO vs. RDB challenge
    ... > Layer 1. ... If you filter your data after you retrieve it, ... possible to redesign the database schema to enable SQL to do its job. ... A good RDBMS could integrate an external authentication server. ...
    (comp.object)
  • Re: Access module does not continue past the Filter Method
    ... If you get this problem in any database at all, ... the subroutine went back to not working again. ... Public Sub Apply_Tech_Filter ... Debug.Print "Past the Filter" ...
    (microsoft.public.access.modulesdaovba)
  • Re: Lots of Bits
    ... and performance is perhaps increased for the database. ... purist might frown upon it for more theoretical purposes as well as the many ... > match customer filter values with campaign filter values. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Access module does not continue past the Filter Method
    ... I have only seen the problem of "no runtime error messages" in this database. ... Public Sub Apply_Tech_Filter ... Debug.Print "Past the Filter" ...
    (microsoft.public.access.modulesdaovba)
  • Re: Apply filter macro
    ... property to True to make sure the filter is actually being applied. ... > I have two separate database applications with similar ... I have placed a command button on the form ... >>Bryan Reich ...
    (microsoft.public.access.macros)