locked
runtime index for Datatable RRS feed

  • Question

  • Hi All
    can anyone tell me how to create a run-time index on a datatable column something like the recordset.fields(0).properties("Optimize")=1
    I usually use this in ADO to create index on client side ADO recordsets and it signinficantly increase performance of quering the RS

    Thanks in advance
    Monday, August 15, 2005 1:31 PM

Answers

  • Hi Eisa,

    The same concept applies here. ADO.NET 1.0 does not utilize *true* indexes. You can create a simple or composite index by using the PrimaryKey property of the data table to assign columns of the data table to the PrimaryKey array. This should help performance. Also, I would suggest using DataViews in addition to your Primary Key index(es).

    Let me illustrate:



        /*
        This code sample illustrates assigning primary key columns to a data table
        and then selecting against the data table using the primary key column
        */

        DataSet ds = new DataSet("TestDS");
        SqlDataAdapter sda = new SqlDataAdapter("...", "...");
        sda.Fill(ds);

        DataTable dt = ds.Tables[0];
        dt.PrimaryKey = new DataColumn[] { dt.Columns[0], dt.Columns[1] };
        DataRow[] rows = dt.Rows.Find("<search for a value in your primary key columns>");
        foreach(DataRow row in rows)
        {
           Response.WriteLine(row["col1"] + " - " + row["col2"]);
        }

     


    This should yield some pretty quick results. Now, for even faster performance, if you can narrow down some common queries, you can create DataViews (sorted and filtered) from the data table and cache or store them in session. DataViews automatically create indexes on their columns, but do not allow you to modify those indexes.

    ***One thing to note is that when using the DataView, it is best to use the constructor that takes all the parameters. Each time you modify the filter, sort, or rowversion properties, the DataView recreates the index.

    HTH,
    Tuesday, August 16, 2005 5:32 PM

All replies



  • DataTable dt = new DataTable();
    dt.Columns.Add(new DataColumn("indexCol", typeof(string)));

    dt.PrimaryKey = new DataColumn[] { dt.Columns[0] };

     


    What this will do is create a primary key index on the columns you specify. The problem here is that you are thinking about client-side ADO vs. ADO.NET. ADO.NET DataTables do not run client-side and therefore usually will not benefit from having indexes placed upon it. If you are running into performance problems with your DataTables, then you need to optimize your stored procedures or queries on the database. Not in your code, per se.

    HTH,
    Monday, August 15, 2005 5:40 PM
  • Hi Bill
    thanks for you reply
    what I meant here is a datatable that T  use away from the database I query the database to fill the data table then I have nothing to do with tha database itself i use my copy of the datatable for filtering and querying . is there any way to create index on that table . I think that a very helpful feature that has been in ADO (which is the indexes that I mentioned before )cannot be removed in ADO.net , can I use another technique . I have a copy of a database table that I use to filter witha large amount of data .

    Thanks
    Tuesday, August 16, 2005 6:08 AM
  • Hi Eisa,

    The same concept applies here. ADO.NET 1.0 does not utilize *true* indexes. You can create a simple or composite index by using the PrimaryKey property of the data table to assign columns of the data table to the PrimaryKey array. This should help performance. Also, I would suggest using DataViews in addition to your Primary Key index(es).

    Let me illustrate:



        /*
        This code sample illustrates assigning primary key columns to a data table
        and then selecting against the data table using the primary key column
        */

        DataSet ds = new DataSet("TestDS");
        SqlDataAdapter sda = new SqlDataAdapter("...", "...");
        sda.Fill(ds);

        DataTable dt = ds.Tables[0];
        dt.PrimaryKey = new DataColumn[] { dt.Columns[0], dt.Columns[1] };
        DataRow[] rows = dt.Rows.Find("<search for a value in your primary key columns>");
        foreach(DataRow row in rows)
        {
           Response.WriteLine(row["col1"] + " - " + row["col2"]);
        }

     


    This should yield some pretty quick results. Now, for even faster performance, if you can narrow down some common queries, you can create DataViews (sorted and filtered) from the data table and cache or store them in session. DataViews automatically create indexes on their columns, but do not allow you to modify those indexes.

    ***One thing to note is that when using the DataView, it is best to use the constructor that takes all the parameters. Each time you modify the filter, sort, or rowversion properties, the DataView recreates the index.

    HTH,
    Tuesday, August 16, 2005 5:32 PM