Datatable: Use other keys than primary key for lookup?

[Ty]

Hi all

we have a situation where we have a very big datatable with more than
one unique key. lookup using the primary key is very fast (with
table.rows.find). but if i want to lookup rows with other columns,
wich happen to be unique too, (with table.select), it's about 10 times
slower.

is it possible to use additional unique constraint for similarly fast
lookup as if it where a primary key? any other suggestion?

thanks!



(to circumvent the problem i made a class wich creates a hashtable
with the additinal key pointing to the primary key. it's fast but
cumbersome since i have to keep the hashtable up-to-date manually)

 

[Ty]

Do you have an database index on that particular field?

Ken

Hi Ken,

i'm not shure what you mean. There is no SQL Server Backend, the data
is fed into a System.Data.DataTable via other means. So my question is
all about this System.Data.DataTable.

We have a primary key on one column. Looking up rows with
DataTable.Rows.Find(key) is very fast. But we also have to lookup rows
using column-combination which are also unique. looking up rows with
DataTable.Select(filter) is about 20-30 times slower.

so my question is, since it's possible to define unique constraint
(also named "Key" in Dataset-Designer), is it possible to use these
additional keys for fast lookup?

any other suggestions?

thanks!

 

[Chris Dunaway]

Hi all

we have a situation where we have a very big datatable with more than
one unique key. lookup using the primary key is very fast (with
table.rows.find). but if i want to lookup rows with other columns,
wich happen to be unique too, (with table.select), it's about 10 times
slower.

is it possible to use additional unique constraint for similarly fast
lookup as if it where a primary key? any other suggestion?

thanks!

(to circumvent the problem i made a class wich creates a hashtable
with the additinal key pointing to the primary key. it's fast but
cumbersome since i have to keep the hashtable up-to-date manually)

I think you need to create a DataView on the column in questions.
Please see this link:

http://www.dotnet247.com/247reference/msgs/24/123218.aspx

Chris

 

[Ken Foskey]

any other suggestions?

Don't know enough about this, however. My thinking would be to inherit
the datatable and put the maintenance of the hash inside that so that it
is encapsulated inside one class not throughout the application.

Ta
Ken

 

[Ty]

I think you need to create a DataView on the column in questions.
Please see this link:

http://www.dotnet247.com/247reference/msgs/24/123218.aspx

Chris

That is the solution! Works very well and just as fast as looking up
with the primary key.

Thanks a bunch Chris!