Kevin,

We aren't using typed DataSets, mostly because they are cumbersome to maintain when your database tables change. While I appreciate the benefits they provide for avoiding boxing and unboxing and the convenience of exposing columns as properties, our database changes so frequently that it makes their maintenance impractical.

I am looking forward to switching over to VS.NET 2005 and the improved performance that it provides, but could you specifically address which method of identifying a specific row incurs the most processing work? Performance impacts our business a lot, so succinct code is less important to us. I have wrapped most of our database access and DataSet manipulation in wrappers, but I am hoping to eke a bit more performance out of what is inside my data access classes.

I want to know which method is the most efficient use of hardware resources, rather than the most elegant code. I try to support elegant coding by wrapping the most efficient (often more convoluted) code in my data accessor class.

I am glad that Microsoft is making ADO.NET more performant better across the board, but I am looking to optimize my app within the options Microsoft provides.

Is substantial cost is incurred to set a PK column? Is an index created? Is it just a hashtable?

Is there a rule of thumb that uses the number of rows in a table and the number of times an individual row would need to be looked up by its unique ID to determine if it is a better use of hardware resources to use Find or Select?

I appreciate your suggestions, but I guess I am still not sure if when Find is a more efficient use of hardware resources than Select.

David

>Hi, David,
>
>Several things:
>
>First, I noticed you posted this under C# 1.1. ADO.NET 2.0 (in VS2005) is generally at least twice as fast as ADO.NET in VS2003. Some operations are more than ten times as fast when you get into larger datasets. Microsoft re-wrote the indexing engine in ADO.NET 2.0.
>
>Second, I mainly used typed datasets and define a primary key for each datatable - which means the typed DS class provides a nice FindBy(Key) method for me. When I generally need a single row based on a PK, I use the findby method for that pk. It makes for some nice clean syntax:
>
>

>decimal nAmount = MyTypedTable.FindByOrderPK(11111).Amount;    // or you could do it as two lines
>

>
>Third, I use a DataTable Select (well, actually, I create a dataview and specify the rowfilter syntax) when I need a subset of rows where the criteria can't be handled by a PK.
>
>

DataView myview = new DataView(dtMyTable);
>myview.RowFilter = " Amount > 1000 and overdue = true ";
>

>
>And in case you weren't aware....if you have parent/child relations defined in a dataset, you can specify Child() and Parent() columns in the RowFilter syntax. This has often helped me in more complicated filter scenarios.
>
>I would expect if I was pulling 10K records and only needed to find one row, setting a primary key and using DataTable.Rows.Find would be less efficient than simply using Select.
>
>Fourth...actually, I would expect that use a FINDBY based on the PK would be the most effecient.
>
>Kevin