DataTable Ordering Problem....

[axum]

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 ID's 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 it's doing a textual sort, it's 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 can't do this can you?

 

Has anyone had similar problems or know's of a way to speed things up?

 

Many thanks..

[Nerseus]

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? I've used Select in a lot of places and haven't 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

[axum]

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

 

But have no fear, i've 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

[Nerseus]

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

 

Glad you got it worked out, thought it's 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

[axum]

It's 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 can't believe that you have never encountered a slowdown due to this??!! It's one of the things NOT to do, use strings to do any kind of comparisons/sorting as it's just slower.

 

Of course the actual SELECT is quick, but it's the sort that's not! That's why i'm populating my Sorted List with the contents of my DataTable with the Select operation performed RATHER than sort the entire DataTable...

 

Any clearer?

 

Axum

[Nerseus]

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 shouldn't 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:

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

[axum]

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 it's rapid, as it works very similar to a Hashtable. Doesn't using the DefaultView sort depend on having primary keys setup or something?

 

The problem is when i'm sorting i'm sorting the WHOLE set of data and NOT the set of Data that is returned via the .Select method. I'm 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