datatable vs custom List<MyClass> -- dotnet ado.net

paul
10/24/2007 5:16:01 PM

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,

Miha Markic
10/25/2007 12:00:00 AM

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

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

[quoted text, click to view]

paul
10/25/2007 2:34:00 PM

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.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about dotnet ado.net

dotnet ado.net : datatable vs custom List<MyClass>