DataView speed (versus DataTable.Select("filter")) 3

[StevenK]

I'm currently writing an application that makes use of DataTables / DataViews generated from data from a SQL Server 2000 database.
One of the important factors of the application is processing speed (the time in which in takes a method to complete).
Due to this fact I've run some testing.
What's suprised me is the speed taken when dealing with DataViews.
I've got an initial DataTable which hits the database for the first 4000 records from a database table (for example).
I then allow myself to run one of two processes (and timed to compare them).

The first process has a DataView (derived from the first DataTable).
I then do a loop.
For each entry in my first DataTable I get the key piece of information (i.e. Company Code) and then use this to set a RowFilter against the DataView, cycling this to count the records in the DataView (with the RowFilter applied then they'll only be one).
For 4000 iterations I clock up about 50 seconds (give or take).

My second process runs a similar iteration but makes use of a DataRow array which is generated from the DataTable.Select("CompanyCode = " + companyCode) concept.
I then cycle this DataRow array and count the records again (with this Filter applied then they'll only be one).
For 4000 iterations I clock up about a second or two.

Which begs the question - should I only be using DataView's if I'm wanting to make use of their additional talents (i.e. Sort, etc.) if all I'm looking at doing is creating a subset of data against the DataTable and working with this - be it just counting the entries in the subset or editing the subset.

Anyone shed any light on this ?
I truly didn't think I'd be looking at this kind of difference in speed when changing my tactics.

Steve

 

[obislavu]

First of all a DataView is not derived from a DataTable.
A DataView is a dynamic view of a single set of DataTable object to which different sorting and filtering criteria could be applied.
For some points of view a DataView is similar to the view object provided by a database but are different.
A DataView is different from the DataTable.Select() which which returns a DataRow array from that table while relationship and orders are static.
A DataView is ideal for data-bindings applications.
There are two schemas:
1.
- open connection if its State is not open
- get a qunatity of records from the database into a DataTable object
- close connection
- use DataView/DataViewManager to present different views of the same DataTable object.
- use DataTable.Select() if the number of returned records is small.

2. One difference between a DataView and a view objects created in a database is the DataView cannot have new column, computed column or joins.
There are application where the following schema is applied:
- create view objects or stored procedures in the database
- define user functions at database level and use them in the view objects and stored procedure
to do there some comptational work instead when using Select() or Filter.
- get records into a DataSet or a DataTable from the view objects as you do from tables or by executing stored procedures.
-obislavu-

 

[StevenK]

Thanks for the information.
Which method should I be using in instances when I'm concerned with :
- just the count of records in the 'filtered' subset ?
- reading / editing the subset of records in the 'filtered' subset ?
Can I use either / or (of DataView / DataTable.Select() ) ?

Steve

 

[obislavu]

1.
When using Select() to filter a DataTable :

DataTable dt=new DataTable();
//...
string strExp = "Comments = 'X'";
string strSort = "GUID";
DataRow[] drArray;
drArray = dt.Select(strExp, strExp);
// The number of returned records:
int count=drArray.Length;
[code]

2. Every table has a default data view called DefaultView.
Use this DataView to filter and sort only in code.
Use this DefaultView as DataSource in a DataGrid in order to edit, for example.
[code]
 DataGrid m_dataGrid1=new DataGrid();
 //
 DataTable dt = new DataTable();
   
 // Populate dt with data.

 m_dataGrid1.DataSource = dt;

// Change the rowfiletr at run time

 DataTable grd = (DataTable) m_dataGrid1.DataSource;
// Set the RowFilter property to show the salary > 30000..
 grd.DefaultView.RowFilter = "Salary > 30000";

-obislavu-

 

[stsuing]

- just the count of records in the 'filtered' subset ?
int count = dt.Select(strExp, strExp).Length



- reading / editing the subset of records in the 'filtered' subset ?

Depends:
If you are binding to a datagrid for editing then the dataview is good.

If you are doing rowset processing then the select() is good. EX:

foreach (DataRow dr in DataTable.Select())
{
//Change values of the dr here.
}

 

[StevenK]

Thanks for the help / pointers with this one guys.
I would have blindly gone ahead and dealt with DataViews if I hadn't invested a little time in running some tests of my own and asking for the opinion of other developers ...
Thanks again
Steve