DataTable Ordering Problem....

A

axum

Active member
Joined
Mar 28, 2003
Messages
40
Hi, i have a TreeView that has the usual Parent > Child relationship.

I populate my Treeview by using a DataAdapter to fill a DataTable by running a Stored Proc.

This Stored Proc returns a list of IDs Ordered by their Title, so they will appear alphabetically. But when i insert the records into the DataTable it seems to order them by ID and lose their original ordering.

I know i can use the Sort method on the DataTable :

dRowColl = dTable.Select(selectSql,"Title");

but using this has slowed down my program immensely! Obviously as its doing a textual sort, its running about 5 times as slow.

My selectSql above is the usual "parentID=CurrentParent and childID <> CurrentParent" to select the Parent > Child relationships. What i really would like to do is to add an "ORDER BY" bit to the end but you cant do this can you?

Has anyone had similar problems or knows of a way to speed things up?

Many thanks..
 
Are you saying the Select(...) is slow? How do you get the "5 times" as slow part - is it the actual Select or part of some looping code? Ive used Select in a lot of places and havent noticed a slowdown, but my data is relatively small (maybe a few hundred rows).

Can you show us some code used when filling the TreeView and anything you think might be related?

-Nerseus
 
no, if you read on i explain that its the Textual Sort - i.e the .Select(selectSQL, "TITLE") bit - the actual "TITLE" sort is whats slowing it down.

But have no fear, ive sorted this by just omitting the "TITLE" bit out, then looping through the records and adding them to a Sorted List, this works MUCH faster :)

Cheers

Axum
 
I understand your issue - but Ive never encountered it. I have sorted by strings, dates, and ints but never saw anything that was so slow Id worry about it. Meaning, all the selects were under 50ms which is fine by me.

Glad you got it worked out, thought its hard to believe that you could loop through and fill a Sorted List in faster time than having the Select do it... very strange.

-Nerseus
 
Its obviously due to the fact that the DataTable holds my ENTIRE dataset, thus when you elect to do a Sort it has to perform a TEXTUAL sort on all the records, and i cant believe that you have never encountered a slowdown due to this??!! Its one of the things NOT to do, use strings to do any kind of comparisons/sorting as its just slower.

Of course the actual SELECT is quick, but its the sort thats not! Thats why im populating my Sorted List with the contents of my DataTable with the Select operation performed RATHER than sort the entire DataTable...

Any clearer?

Axum
 
Actually, sorting on a string is more the norm than sorting on other data types (maybe date coming next). It sounds like the sort might be happening before the filter (which would seem odd since a filter shouldnt depend on a sort) when you specify both on your Select. I wonder if creating a DataView with a filter THEN applying a sort would work faster? Then you could avoid using a Sorted List.

Maybe try something like:
C#: 
ds.Tables["Table1"].DefaultView.RowFilter = "...";
ds.Tables["Table1"].DefaultView.Sort = "Title";
// Now either loop or bind to DefaultView
foreach(DataRowView row in ds.Tables["Table1"].DefaultView)
{
    // do something
}

-Nerseus
 
Nope, i tried the DataView route and no improvement whatsoever.. i understand sorting on a string is more the norm, but using strings to do anything is gonna be slower than handling numbers surely?

The Sorted List automatically sorts alphabetically, believe me it works fine and its rapid, as it works very similar to a Hashtable. Doesnt using the DefaultView sort depend on having primary keys setup or something?

The problem is when im sorting im sorting the WHOLE set of data and NOT the set of Data that is returned via the .Select method. Im sure i could reassign the DataTable to a new one after the Select then sort on that, but why not just use a Sorted List that is specifically designed to sort?

Axum
 
Back
Top