Bad performance doing a DataTable.Select()

Question

_{Friday, May 20, 2011 1:39 PM}

Hi,

I have a strongly typed datatable. It holds approximately 200 records from the database. I would like to do an in memory search because i will do this search one time for each row in a data table and I believe that it is (or at least should be) faster than querying the database. The table and of course also the datatable looks like this:

Column1: Table nvarchar(50) PK
Column2: Column nvarchar(50) PK
Column3: Language nvarchar(2) PK
Column4: Value tinyint PK
Column5: Descriptionnvarchar(50)

There are four primary keys making each record unique (marked with PK). I am doing this search on the datatable to get the DataRow[] (always one):

DataRow[] dr = dt.Select("Table = '" + table + "' AND Column = '" + column + "' AND Language = '" + language + "' AND Value = '" + value + "'");

This search takes forever so my question is, how can I improve performance?

Br, JÅ

All replies (5)

_{Tuesday, May 24, 2011 2:45 AM ✅Answered}

>> Would linq be performing well for this kind of task. Using the dt.Select() in my original post is taking about 40 seconds to display 200 rows.
It is weird as here in my test project, only several milliseconds are taken to fulfill the same task, besides, here I have more than 2000 records in my test table, and the structure of the table is not that simply. Please check if most of your time is taken to fetch data from the database.

Here I come up with several approaches:

• Directly search through the DataTable.
• Use DataTable.select(String filterStr) method.
• Search through the DataView using DataView.RowFilter.
• Use Index to improve the performance of querying data from the database.

Let’s check them out respectively, I used a simple Sql statement to test through these ways to see the used time.
The database used: Northwind sample database
Selection Condition: ProductID > 30
The amount of selected rows is 1358 out of 2122.

1> Directly search through the DataTable. The average used time is : 1.894 milliseconds.

      //Directly search through the DataTable.
      foreach (DataRow row in ds.Tables[0].Rows)
      {
        if (Convert.ToInt32(row["ProductID"]) > 30)
          targetDt.ImportRow(row);
      }
 

2> Use DataTable.select(String filterStr) method. The average used time is : 3.487 milliseconds.

      //Use DataTable.select(String filterStr) method.
      foreach (DataRow row in ds.Tables[0].Select("ProductID > 30"))
        targetDt.ImportRow(row);

Analysis of causes: The Select method encapsulates the logics in the first approach, and makes some extensions.

3> Search through the DataView using DataView.RowFilter. The average used time is : 8.410 milliseconds.

      //Search through the DataView using DataView.RowFilter.
      DataView view = new DataView(ds.Tables[0]);
      view.RowFilter = "ProductID > 30";
      targetDt = view.ToTable();

Analysis of causes: The data source of a DataView is originally a DataTable, all operations to the DataView ultimately are converted to the operations to the DataTable. So it is not hard to imagine that DataView.RowFilter is more complex than DataTable.Select().

4> Use Index to improve the performance of querying data from the database. This is realized at the database server side. I think it doesn’t matter much about your requirement, as you want to manipulate the data object which has the data got from the database and consists in the memory.

Well we can easily see that if you simply pursue improving the performance, use the first approach.

You can download my test project here to check out(The Northwind database files are involved in the package):
http://cid-63c0c5f1723a3dc0.office.live.com/self.aspx/For%20Customer/TestWinForm%5E_DataProviderEffeciency.zip

Leo Liu [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

_{Friday, May 20, 2011 2:59 PM}

Hi,

Using DataTable.Select() is not good practice and it performs slower than querying the database. Using this means in most cases you are filtering the data which could have been also done in the database. If you have to use select() method in client side (after feching it from db) i rather recommand using linq. 

_{Friday, May 20, 2011 4:18 PM}

Hi,

Thanks for the reply. There are a few problems with retrieving the data from the database. I am displaying one table per datagridview and the tables has a reference to other tables and some columns has a number representing a text, such as e.g. type or status. If I would join in the text representation that column would not be strongly typed and the query would also be useless in other user interfaces using the same query.

Would linq be performing well for this kind of task. Using the dt.Select() in my original post is taking about 40 seconds to display 200 rows.

My plan (but I might have to replan) was to loop for each row in the datagridview to look up the text value for the number represenation and add it to the grid on the client. All I really need is to have a datatype in memory that has a good search performance (obviously not a datatable). Can you recommend such a data type, I really only need at a maximum 4 columns, most often 2. Or do you have another suggestion to my problem?

Best regards, JÅ

_{Thursday, May 26, 2011 1:24 AM}

Hi JÅ,
Is any progress made? Thanks.Leo Liu [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

_{Friday, June 3, 2011 5:41 AM}

Hi,

Thanks for all replies. and the statistics. I will use step 4 to improve performance at database side.

Best regards, JÅ