Datatable: Use other keys than primary key for lookup?

  • Thread starter Thread starter Ty
  • Start date Start date
T

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)
 
Do you have an database index on that particular field?

Ken
Click to expand...

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!
 
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)
Click to expand...

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
 
any other suggestions?
Click to expand...

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
 
Back
Top