Wednesday, November 10, 2010

C#/SQL: The Record Viewer: Quick Access To 3,000,000,000 Data Records

Build Viewer Control Panel
The products offered by Melissa Data are personal and business contact lists intended for use by marketing or search organizations. Melissa Data combines billions of partial records received from several sources into a smaller set of current and complete records. Marketers can use the high quality Melissa Data product to reach customers who are more likely to become actual purchasers and avoid wasteful effort and postage costs associated with attempting to contact previous or duplicate addresses. A search business can learn and provide the latest updates concerning the contact information for people or businesses.

There are far too many records in play for us to accurately realize: i) the effects of modifications to our development process; ii) changes in source and types of input; and, iii) software that converts the raw data to the finished output. One of my purposes at Melissa Data is to produce applications that make retrieval and viewing of records practical at any stage of their assimilation.

One application I wrote for those purposes was a C# Winform record viewer that interfaced with both XML and mySQL backends, the MD Build Viewer. The Build Viewer allows list, view, search, and jump access to every one of the billions of records at each step in their production, their heirarchy, and changes resulting from modifications in a preceding phase. The final version of the records are read from a mySQL database and the parent records are read from a very large flat text, proprietary format text file.

Handling that amount of data reflected on the Build Viewer requirements in the form of performance goals, such as: The UI loads access to the data (approx. 3 billion records) in less than 15 seconds so the user experiences minimal delay (On that count, consider that it takes far longer than 15 seconds for the comparatively simple Linux wc shell command to count the number of lines in a 3 billion line file); Switching between records, presenting parent records, and jumping to records should be rendered in either a standard or cascading sidebar form; Clicking on any record field will jump to the breakout of that record component; Right-clicking on a street address will pop up a Google map with the address shown in the map. Here are a few views:




The view above shows the treeview record list and the detail of the selected record.

The next shows the details of one of the individuals in that record. The yellow tree nodes are those that have been reviewed and the red ones have been read and marked via a right click menu selection for later review. The user can suppress all but the red nodes or store them to a serialized list that can be loaded in a later session. A mnemonic key sequence provides a means to jump to the next or previous red node or to a specified record number.

Here are views of the sidebar view that shows the contents of the parent records of the one selected in the treeview. The upper will replace the parent record present in the sidebar with the one just selected from the list or reached via the up or down arrow key.
The lower shot shows the cascade option where the records overlap as the user clicks or accesses them via the arrow keys.




And here is one that show a Google Map inset resulting from right-clicking on an address in the record field and a sidebar view showing the same.