none
does DataTable has index concept? RRS feed

  • Question

  • Hello everyone,

    Not sure whether there is concept like index in DataTable (the same as index in SQL Server physical tables)? If no index concept, how do we improve query performance (e.g. order by) by using DataTable?

    thanks in advance,
    George
    Sunday, July 19, 2009 12:28 PM

Answers

  • Internally DataTable build indexes based on code actions, but there is not direct way to create indexes for the DataTable. Those indexes do not affect query performance, since queries managed by database server, not by DataTable and DataTable is a kind of cached storage of retrieved data on client side.
    Val Mazur (MVP) http://www.xporttools.net
    Monday, July 20, 2009 10:02 AM
    Moderator
  • All Val is trying to say is the DataTable internally will create indexes to improve DataTable.Select performance.  These indexes have nothing to do with the indexes on the SQL Server, they are client side indexes used to improve performance of DataSet/DataTable/DataView operations with respect to the local in memory representation of the data.
    • Marked as answer by George2 Tuesday, July 21, 2009 4:25 AM
    Monday, July 20, 2009 6:02 PM
    Moderator

All replies

  • Internally DataTable build indexes based on code actions, but there is not direct way to create indexes for the DataTable. Those indexes do not affect query performance, since queries managed by database server, not by DataTable and DataTable is a kind of cached storage of retrieved data on client side.
    Val Mazur (MVP) http://www.xporttools.net
    Monday, July 20, 2009 10:02 AM
    Moderator
  • Hi Val, 1. I am confused what do you mean "queries managed by database server, not by DataTable and DataTable is a kind of cached storage of retrieved data on client side." -- my confusion is DataTable is disconnected from database server after I retrieve all the data and all the query I make further on DataTable should be not connecting to DB server any more. Why do you think query to DataTable will still be rely on DB Server? Please correct me if I am wrong to understand your words.

    2. If I need to manipulate DataTable in offline mode as I mentioned in (1), from ADO.Net developer point of view, no matter that is the data access pattern on DataTable, there is no need to care about creating index inside DataTable and we just rely on DataTable internal optimization?
    Monday, July 20, 2009 11:48 AM
  • All Val is trying to say is the DataTable internally will create indexes to improve DataTable.Select performance.  These indexes have nothing to do with the indexes on the SQL Server, they are client side indexes used to improve performance of DataSet/DataTable/DataView operations with respect to the local in memory representation of the data.
    • Marked as answer by George2 Tuesday, July 21, 2009 4:25 AM
    Monday, July 20, 2009 6:02 PM
    Moderator
  • To answer more specifically:

    1. Query to DataTable does not rely on server at all.
    2. In most cases you should not care, just do operations on DataTable etc and internally it will create the indexes for you.   Note however you do have to be careful about using DataSet/DataTable over multiple concurrent threads, you need to serialize writes.
    Monday, July 20, 2009 6:04 PM
    Moderator
  • If speed is the main issue when querying a DataTable also try bring back the smallest query results you can get from the server to populate your DataTable and refresh it if you need to get a different (larger) set from the server.

    Monday, July 20, 2009 7:03 PM
  • Thanks Matt, question answered!
    Tuesday, July 21, 2009 4:24 AM
  • Thanks Michael, question answered.
    Tuesday, July 21, 2009 4:25 AM