Datatable's Select and Compute methods

[Guest]

I want to use a Datatable's SELECT and COMPUTE methods but I suspect that the
app will not scale well if I do.

If I have a key defined on a datatable and it is not the PrmaryKey, how do I
force the app to use that key when executing a SELECT or COMPUTE method?

 

[Miha Markic [MVP C#]]

Hi Mike,

I want to use a Datatable's SELECT and COMPUTE methods but I suspect that
the
app will not scale well if I do.

Why not? It is an operation as any other.

If I have a key defined on a datatable and it is not the PrmaryKey, how do
I
force the app to use that key when executing a SELECT or COMPUTE method?

Select or Compute doesn't care about pk or any other key. Just use the right
filter statement.

 

[Guest]

Example: A DataTable has 2 columns; KEY and COL_A. Column KEY is the
PrimaryKey. A secondary key is also defined for COL_A. The keys are defined
using the dataset designer.

If I execute a SELECT method with filter: "COL_A = 'This'", will a lookup be
performed using the secondary key or will every record in the table be
searched for a match?

I suspect the later which means the app will not scale well at all.

 

[Steven Cheng[MSFT]]

Hi Mike,

I agree with Miha, the Select or Compute method can do filtering based on
single or multi-columns' value in your datatable(not restricted to primary
key). For example:

string strExpr;
strExpr = "Date > '1/1/00'";
DataRow[] foundRows;
foundRows = myTable.Select(strExpr);

For detailed reference on filtering expression, you can view the following
MSDN doc:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatadatacolumnclassexpressiontopic.asp

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Miha Markic [MVP C#]]

Example: A DataTable has 2 columns; KEY and COL_A. Column KEY is the
PrimaryKey. A secondary key is also defined for COL_A. The keys are
defined
using the dataset designer.

If I execute a SELECT method with filter: "COL_A = 'This'", will a lookup
be
performed using the secondary key or will every record in the table be
searched for a match?

I suspect the later which means the app will not scale well at all.

Yes, very probably Select doesn't utilize keys.
However, if you need to search values by keys, you might use use
DataTable.Rows.Find method or DataView.Find or FindRows methods.
And finally, there is a third way: create an indexing/searching mechanism by
yourself.

 

[Guest]

I understand what Select will do, I am more concerned as to how it does it.

If, in your example Date is a non-primary key to the table and the table
contains 1,000,000 records, will the entire 1,000,000 records be searched
when the Select method is executed?

Or is an ISAM or other key based retrieval technique used which would
drastically reduce the number of records to be searched?

 

[Miha Markic [MVP C#]]

I understand what Select will do, I am more concerned as to how it does it.

If, in your example Date is a non-primary key to the table and the table
contains 1,000,000 records, will the entire 1,000,000 records be searched
when the Select method is executed?

Why would you hold so many records in memory in first place?
It is normally not a good practice.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Or is an ISAM or other key based retrieval technique used which would
drastically reduce the number of records to be searched?

Hi Mike,

I agree with Miha, the Select or Compute method can do filtering based
on
single or multi-columns' value in your datatable(not restricted to
primary
key). For example:

string strExpr;
strExpr = "Date > '1/1/00'";
DataRow[] foundRows;
foundRows = myTable.Select(strExpr);

For detailed reference on filtering expression, you can view the
following
MSDN doc:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatadatacolumnclassexpressiontopic.asp

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Steven Cheng[MSFT]]

Hi Mike,

Thanks for your followup. Yes, you're right. Since the DataTable only
maintain a local set of records in memory, its Select Method is just a
simple interface which will loop through all the records so as to find the
required ones. If the datatable we use "Select" on contains large number of
records, that 'll certainly reduce the application's performance.

If you have any other questions, please feel free to post here. Thanks.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)