ADO.NET performance at deleting records

  • Thread starter Thread starter Abra
  • Start date Start date
A

Abra

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
 
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.... I would _expect_ direct SQL to be faster. The RDMS is optimized
for
set based data manipulation. Your code is programatically doing row
based
logic and then constructing 4000 to 5000 row based delete statements
using
optimistic concurrency generating complex row based WHERE clauses.

Regards,
Jeff
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
!
 
I have a similar performance problem at the start of my application,
when the dataset is loaded for the first time from the database, I hope
someone helps me to improve it.

The code looks like this :

Code: 
public void LoadTable(string tableName, DataSet actualDataset)
myAdapter.SelectCommand = conn.CreateCommand();
myAdapter.SelectCommand.CommandText =  "SELECT * FROM " + tableName;
myAdapter.FillSchema(actualDataset, SchemaType.Source, tableName);
myAdapter.Fill(actualDataset, tableName);
}

After that the dataset is assigned to a listbox :

Code: 
myList.DataSource = actualDataSet.Tables["myTable"].DefaultView;

If there are some thousands of records in the dqtabase, it takes very
long to fill the listbox, and the user sees nothing during this time.

I thought then to load only some 50 records from database, the user
would already see this part on screen, and then create a thread that
would read in background the rest into the dataset and implicitly into
the listbox.

So I first modified the function that calls Fill() and added a second
function that only fills the rest.

Code: 
// first function
public void LoadTable(string tableName, DataSet actualDataset, int
startRecord, int nrRecords)
{
myAdapter.SelectCommand = conn.CreateCommand();

myAdapter.SelectCommand.CommandText =  "SELECT * FROM " + tableName;

myAdapter.FillSchema(actualDataset, SchemaType.Source, tableName);
myAdapter.Fill(actualDataset, 0, 50, tableName);
}

// second function
public void LoadTablePart(string tableName, DataSet actualDataset, int
startRecord, int nrRecords)
{
myAdapter.Fill(actualDataset, startRecord, nrRecords, tableName);
}

After calling the first function, I assigned the listbox to the dataset
(as the first time) and then created the thread :

Code: 
----
actualDataset.Clear();
dbAccess.LoadTable("myTable",actualDataset, 0, 50);
....
// assign to listbox
myList.DataSource = actualDataSet.Tables["myTable"].DefaultView;

// create thread
Thread tViewer = new Thread(new ThreadStart(FillRestViewerThread));
tViewer.Start();

The thread code looks for example like this :

Code: 
public void FillRestViewerThread()
{
int startRecord = 51;
int nrRecords = 0;
dbAccess.LoadTablePart("myTables",actualDataset, startRecord,
nrRecords);
}

When I start the program, the 50 rows are loaded into the litsbox which
can already by seen on screen, and then it continues refreshing in
background. The problem is that it never stops, as apparently (tested
with the debugger) the thread does not terminate, although I thought
that it would terminate by itself. Does anybody see where the problem
could be ? Do I have to wait for some event regarding the listbox,
before starting the thread that fills further the dataset assigned to
the listbox ? Or is there any other, more elegant solution for my
problem ?

Thanks in advance for your help.
Regards,
Abra
 
Back
Top