|
|
|
|
Note: This article was previously published on VB/MySQL.com.
Introduction
This is the second article intended to illustrate professional programming techniques using Visual Basic and MySQL. The first article discussed why we should avoid data binding. It can be viewed here. This article discusses writing software that helps the user effectively work with large amounts of data. The approach that I will describe is called filtering. I have expanded on my sample program from the previous article to exemplify the techniques that are described herein. You can download it here.
Why use Filtering?
When working with a small amount of data it is often adequate to load an entire table into a grid or listview control. MySQL is quick and you can load several hundred records in under a second. When the dataset gets larger we start running into performance problems. More important than a slight delay though is the usability of the data. Long before the user gets upset at a load delay he will go nuts trying to find the one record he needs to work with. The purpose of data filtering is to assist the user in finding that one necessary piece of information. Any increase in processing speed this may yield is merely a pleasant side effect.
Find Patterns in your Data
Most data may seem rather random but you can usually find patterns. Based on these patterns we can build structures that will let a user answer a few simple questions. With this information we can then narrow down our data to return a small result set. The user can then quickly scan through this small set of data to find the one piece that they need. The perfect example of this is a search engine on the Internet. The Internet contains a truly massive amount of information but with a quick search at google.com you can find the pages that interest you.
Building and Using a Filter
In my example program we are collecting addresses. I broke the variously fields into their smallest components to facilitate filtering. As the user keys records, some of the values in these fields will repeat (city names or states). We can use the DISTINCT keyword in a SELECT query to give us all the unique values in a particular field.
For example:
Select Distinct State From Addresses
We can then populate a combo box from this recordset. The user can then choose a State from this list. We would then use this State selection in a Where clause to return only the records that have the selected State.
For example:
Select * From Addresses Where State = 'AK'
By asking one simple question we have significantly reduced the number of records a user will have to review. There are a lot of cities in Alaska though so we could further filter our data by asking for a City. We could simply repeat the steps above and provide a list of cities. If we want to get fancy we could even let the results of one filter affect the contents of the next filter list. I didn't go that far in my example program.
As you can see in the screen shot above, the user answered four filter questions and narrowed the returned records to 17 out of a possible 1234. This is about 2 pages of data since our listview is tall enough to display 8 rows.
In the sample program I provide a generic combo box loading routine that you can use to produce filter combos from your data. See the cmdRefresh_Click event to see how we build a where clause to return our records.
Conclusion
Providing filtering mechanisms can drastically increase the usefulness of data entry screens. By identifying patterns in our data we can reduce the amount of work required to locate a given piece of information. This not only helps the user but reduces the load on the server because it doesn't need to return all the records every time.
Sample Code #1
Sample project showing how to apply user-defined filters to your data
