What to use: RowFilter or Query?

[Frederik]

Hi all,

I have a database that contains almost 10,000 records. It's an Access
DB that's only being used on a single computer (no access from multiple
users at the same time). The front-end is a windows form app. It has a
search textbox, that updates the query whenever the text in that
textbox has changed (like the iTunes app). 2 Questions:
- I fetch new records with a TableAdapter (VS2005 / .NET 2.0). Should I
use a RowFilter instead for performance?
- Does someone have experience with using a timer to filter/fetch
records? If a user is entering text in the search textbox, it should
probably be more efficient to only fetch after the user did not change
the textbox for ??? milliseconds. Please let me know if someone has
been dealing with this before.

Thanks in advance,
Frederik

 

[W.G. Ryan - MVP]

Frederik:

Comments inline:

Hi all,

I have a database that contains almost 10,000 records. It's an Access
DB that's only being used on a single computer (no access from multiple
users at the same time). The front-end is a windows form app. It has a
search textbox, that updates the query whenever the text in that
textbox has changed (like the iTunes app). 2 Questions:
- I fetch new records with a TableAdapter (VS2005 / .NET 2.0). Should I
use a RowFilter instead for performance?

--It depends. If you search/filter locally, you don't have to open a
connection, take the hit on the db for the query and hit associated with
network traffic. So yes, it's probably more performant to filter locally
although RowFilter isn't the only way to filter things

- Does someone have experience with using a timer to filter/fetch
records? If a user is entering text in the search textbox, it should
probably be more efficient to only fetch after the user did not change
the textbox for ??? milliseconds. Please let me know if someone has
been dealing with this before.

--One thing I typically do is create a public property that corresponds to
lookup data so that I don't have to requery for it, I can just use the
property. Depending on the nature of the data though, I'll often set a
LastAccessedTime and if the interval between then and now is greater than x,
I'll requery. However I'm not following you about the textbox, do you mind
explaining a little more about it? I can probably be of more help then.

Cheers,

Bill

 

[frederik12]

Thanks for your response Bill,

About the search textbox. It's in the toolbar of my windows form.
Below, there's a datagrid. When the form is loaded, all 10,000 rows are
shown in the datagrid. When the user enters text in the search textbox,
the datagrid is updated on every change in the textbox. For this, I use
a "LIKE '%' & <textbox content> & '%'" query.

Just one more question about the RowFilter. Are the indexes (and there
advantages) in my database table also used in the DataTable's that are
in memory?

Regards,
Frederik

 

[W.G. Ryan - MVP]

Frederik:

Each time you set the Rowfilter you create a new dataview so in this case,
with that many rows, you're probably incurring a potentially large
performance hit. 10,000 rows is a lot of data. You're probably best off
though doing it client side like you are, making a trip to the db each time
is a waste. I haven't done a performance test for Find, dataTable.Select
etc but i believe I remember seeing apost about it and that .Select is the
way to go. Let me double check before I say anything for sure though
(although I believe Sahil Malik was the one that's tested it , he frequents
this group so he may be able to chime in).

As far as the DB indexes vs the DataTable's indexes, they do not necessarily
equal each other so you can't count on that.