none
Dynamically generated search in datatable (datacube) RRS feed

  • Question

  • Hi,

     

    I have a datatable with 9 columns, lets say a,b,c,d,e,f,g,h,i. Values in every column are only integers (if integers are a problem all could be changed to be only characters). The table can contain several million rows. The combination of entries in a row appears only once, i.e. every row is different to all others rows.

     

    In my programm I would like to select with dynamically generated search conditions, for instance

     

    Select single * from datatable where a=1, c=2, f=3, h=4 or

    Select single * from datatable where b=1, c=2, g=3, h=4, i=5 or

    Select single * from datatable where a=1, b=2, c=3, d=4, e=5, f=6, g=3, d=4 or

    Select single * from datatable where a=1, c=2, f=3, h=4

     

    etc.

     

    Any idea how to implement this?

     

    Thanks in advance

    Uwe

    C#, VS Standard 2005

     

    Monday, October 29, 2007 11:12 PM

Answers

  • If you create a DataView on the DataTable you can "select" via RowFilter.  RowFilter syntax is slightly different from SQL but you can use boolean OR (and AND).  Once the filter is applied the only rows available will be those satisfying the filter.

    Thursday, November 1, 2007 9:32 PM
  • A composite index (made up of several keys) may be more helpful.  Put the most frequently queried fields earlier in the index.  I believe if you create a DataView with a Sort expression specified then the view will be indexed on the Sort expression.

     

    You may need to take a non-relational approach.  If your integer values are in a small enough range you could create a 9 dimensional array using the 9 column values as indexes into the array (which would store a reference to the entire row).  Each dimension would need as many spaces as are in range for the corresponding column.  This array might be very sparse and total memory might become an issue if the range of values for each column is large.

     

    Another approach would be to sort the rows by all 9 columns (easy enough w/ an IComparer and Sort()) then use a  BinarySearch to programmatically find rows matching your query values.  Wikipedia has a stock implementation of BinarySearch.

     

     

     

    Monday, November 5, 2007 10:57 PM

All replies

  • If you create a DataView on the DataTable you can "select" via RowFilter.  RowFilter syntax is slightly different from SQL but you can use boolean OR (and AND).  Once the filter is applied the only rows available will be those satisfying the filter.

    Thursday, November 1, 2007 9:32 PM
  • Thank you Arnshea,

     

    in the meantime I implemented it your way and it works fine with a small datatable with indexes in all columns

    But I read, that the select statement is not performant enough.

    I tried the statement find, but it needs a primary key and is not flexible to select with different filters.

     

    If you or somebody else have another hint ...

     

    Regards

    Uwe

     

     

    Friday, November 2, 2007 5:23 PM
  • A composite index (made up of several keys) may be more helpful.  Put the most frequently queried fields earlier in the index.  I believe if you create a DataView with a Sort expression specified then the view will be indexed on the Sort expression.

     

    You may need to take a non-relational approach.  If your integer values are in a small enough range you could create a 9 dimensional array using the 9 column values as indexes into the array (which would store a reference to the entire row).  Each dimension would need as many spaces as are in range for the corresponding column.  This array might be very sparse and total memory might become an issue if the range of values for each column is large.

     

    Another approach would be to sort the rows by all 9 columns (easy enough w/ an IComparer and Sort()) then use a  BinarySearch to programmatically find rows matching your query values.  Wikipedia has a stock implementation of BinarySearch.

     

     

     

    Monday, November 5, 2007 10:57 PM