DataView.Rowfilter performance

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

I have a RowFilter which is doing a not in (). The not in contains 600 items
for a single column. For example RowFilter = "ADate not in (600 DateTimes)".
This takes several seconds to run.

Is there a better way to handle this?
 
Joe,

You might be able to improve performance if you also sort on the AData
field, i.e.

..Sort = "ADate"

If not, you're better off keeping the 600 DateTimes in a list and using a
loop to delete non-matching rows in the view, for example,

dataview.Sort = "ADate"
For each row as DataRowView in dataview
Dim date as DataTime = Row("ADate")
Dim found as Boolean = False
For each val as DateTime in DateList
if date = val then
found = true;
exit for
end if
next
if not found then row.Delete
Next

Of course, storing the 600 DateTimes in a sorted list or indexed view could
speed this algorithm.

Hope this helps
Ad.
 
Adrian,

I doubt that this will speed up in version 1.x. Deleting is terrible slow in
that version.

Deleting was one of the goals to improve for 2.0

An anlternative from your routine can be to create a new table in 1.x
because that has no performance isues.

Just as addition.

Cor
 
Hi Joe,

Welcome to MSDN newsgroup!

For your current issue, I agree with Adrian's suggestion. The performance
mainly is influenced by the indexing. So if you call sort method before
using RowFilter, I think maybe the performance will be improved. The below
sample from MSDN document demonstrate above description:
view.RowFilter = "City = 'Berlin'";
view.RowStateFilter = DataViewRowState.ModifiedCurrent;
view.Sort = "CompanyName DESC";
// Simple-bind to a TextBox control
Text1.DataBindings.Add("Text", view, "CompanyName");

If you have any concern, please let me know.

Regards,

Yuan Ren [MSFT]
Microsoft Online Support
 
I'll give it a shot.
BTW - It seems the performance is much worse when the values are DateTime.
If I was doing the same with a regular string column and values, the
RowFilter is much quicker.
 
Hi Joe,

Thanks for your reply!
"If I was doing the same with a regular string column and values, the
Click to expand...
RowFilter is much quicker."
Based on my understanding, if you change the condition to string value, the
performance has been improved obviously. My meaning is changing "RowFilter
= ADate not in DateTime object" to "RowFilter = "ADate not in string
object". If I have anything misunderstood, please let me know.

As far as I know, the DateTime object needs higher cost than string object
on the aspect of comparison. And then, I suggest you consider changing the
condition if you need high performance.

If you have any concern, please feel free to let me know, I'm looking
forward your reply!

Regards,

Yuan Ren [MSFT]
Microsoft Online Support
 
Back
Top