datatable vs custom List<MyClass>

[Guest]

Let's say I want a read-only copy of a simple query that joins 2 tables. When
I compare a DataTable (adapter.fill) and a custom List + DataReader, I see
that the custom sol'n is about 6x faster and takes 5x less memory. For my
test I read about 100K rows and then bind to a grid so the timing takes into
account both getting the data and binding the data. From profiling the app I
see one thing that really stands out - the internal DataTable routine to
build the (default?) index takes up most of the time - it clearly is doing
linear scans of a red-black tree and the hit count for traversing parts of
the tree ending up in the 100's of millions. Can I give the DataTable some
hint (via a property) to tell it not to perform this extra work? In the end I
may go w/ a custom sol'n but I first want to check if the built in classes
can solve my problem.

btw, this is .net 2.0, c#, and I'm just setting the datasource w/out any
other binding code.

thanks,
Paul.

 

[Miha Markic]

How does your table look like?
Did you try pre-create the table without any key?

 

[Guest]

Well for the test, the select performs an inner join on two simple tables
that have no indexes. The tables have ints and bigints and the result set
obviously won't have any indexes as it will be tempdb w/ about 100K rows.
When I step in the debugger the data retrieval from sql server is pretty fast
- around 1 - 2 sec for both the DataTable and a custom list. BUT, when I step
over the code that sets the datasource, the DataTable does the extra work.
The call stack during the extra work is something like:

IListSource.GetList
dataTable.DefaultView
defaultView.SetIndex2 <- this can't be indexing on any specific column?
UpdateIndex
GetIndex
Index.ctor
InitRecords
RBtree<>.Insert
RBTree<>.Left/Right/Parent <- called 100's of millions of
times?

The RBTree methods are called 100's of millions of times while this index is
set. I really don't want any indexes because I want the user to be able to
sort/group/filter by any combination of columns. I was wondering if there was
a way to give the DataTable some extra knowledge so that the list inside of
DataRowCollection is maintained more efficiently.

thanks,
Paul.