DefaultView.RowFilter

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

Joe

Hello all,

I'm having a performance problem using the DataTable.DefaultView.RowFilter.
When setting the filter to MyField = "Something" this takes about 12 seconds
(this isn't the issue). Next if I change the filter to MyField In
('SomethingElse') it takes about 1 second (still not the issue), Next I set
the filter to "". This takes about 5 seconds (still not the issue). Finally
if I set the filter back to MyField = "Something" or MyField In
('SomethingElse') it takes like 30-45 seconds. This is the issue.

My DataTable is bound to a single control but I remove the binding prior to
changing the RowFilter and it doesn't make a difference.

In a test app the 1st filter takes 12 seconds, the 2nd filter takes 468 ms,
no filter takes 6 seconds and back to the 1st takes 453 ms.

I cannot find a way to debug what it's doing in my actual app. I tried
stepping into RowFilter = "....." but nothing happens (thought maybe there
was an event I subscribed to and forgot) and as far as I can tell there are
no other controls bound to this DataTable.

Are there any tricks out there that may help me narrow this down?

Thanks for any help,
Joe
 
Every time you change the filter, you're creating a new object. If that
object is big, it can get really sluggish really quickly. Can you use Find
or Select on the DataTable?
 
I realize that the performance can get bad by changing the RowFilter. My
issue is that my test app has the exact same data and the results are
completely different. This is why I think it has something to do with a
binding. My test app doesn't bind to anything.
 
After further testing in my test app it turns out that once I assign my
table to the DataSource of a DataGridView the performance gets really bad.
Here's what I'm trying now:
dataGridView1.DataSource = null;
table.DefaultView.RowFilter = myfilter;
dataGridView1.DataSource = table;

If I never do the assignment to the DataGridView the RowFilter is pretty
quick. But once I assign it, it doesn't matter what I do I cannot get the
RowFilter to perform correctly.
 
Are you filtering it for display on a form, like based on some
criteria on the form? Is it databound?

Are you using VB2005?

If so, you can use a BindingSource in between your data and your
bound controls, and apply the filter to that instead of to your
datatable. I think that will be faster.

Robin S.
 
Here's a post I just posted for someone over in
microsoft.public.languages.vb.data that explains how
to use the BindingSource to do filtering. This is version 2005;
I don't think it was available in 2003.

------------------------------------
You can create the BindingSource in code, although I
would just add one to the form and name it, then set the
properties in code. It's in the Data components. Either way,
it should work.

You bind your controls to the BindingSource, and the
BindingSource to the Data Source.

Then you can use all of the features of the Binding Source,
like Filter, Sort, etc. Any change you make to the binding source
is immediately displayed on the screen. So even if you changed
your data source, the screen updates w/o any other effort on your
part. And you can filter and sort w/o impacting the original
data source.

When you load your form:

'load myDataSet.Tables("myTable")

'set up the binding source
Dim myBindingSource As BindingSource = New BindingSource()
myBindingSource.DataSource = myDataSet.Tables("myTable")

'set up the bindings for the listview
myListView.DataSource = myBindingSource
'set whatever properties the ListView needs in order to bind it,
' using the BindingSource instead of the dataset

When you want to filter your listview:

myBindingSource.Filter = "EmployeeName = 'LazyGuy'"

It's like magic!

Robin S.
--------------------------------------------------
 
Robin,

Yes this is for display purposes and I'm using 2005.

I tried using the BindingSource but it didn't help at all.

Thanks,
Joe
 
You mean it didn't help with the performance, or it didn't
work or you couldn't get it to work?

Robin S.
-----------------
 
Bummer. I'll ruminate on it, and if I can think of anything
else, I'll post back. Just out of curiousity, how are you filling
the datatable that you're using?

Robin S.
--------------------------------
 
RowFilter = "MyField = 'SomeValue'";

I actually called MS. They don't know what the reason is for this
performance issue. They're looking into it.

I'll keep this thread posted with my results.

-Joe
 
Actually, I wondered how you were filling the original
data source, if it was something about that that was
causing the issue.

Good luck; let us know what it is when/if they figure it out.

Thanks,
Robin S.
------------------------------------------------
 
Joe,

Are you sure that you did not bind any other control to the default.view, if
you change it, it changes all controls or whatever you have binded this
table to.

Mostly it is easier to create a new dataview, which is only for the
datasource that you want to use.

dim dv as New DataView(DataTable)
DataView dv = new DataView(DataTable);

dv.rowfilter = etc

Cor
 
Hi Cor,

Yes. My test app only has 1 control which is a DataGridView. MS has been
able to reproduce the problem.

I may end up creating a new DataView each time I need a new filter (which is
very fast) but I end up with a memory problem. It seems that once a binding
is done, not all references to it is released which causes the problem.

-Joe
 
Back
Top