Relational Artist: Intro

The project's further development

This project is in the process of being subsumed into The Freestyler Toolkit.

Paging and performance

All options for automatically paging recordsets in ADO.NET suffer from serious performance drawbacks, however, when confronted with large query results.

The ASP.NET documentation says:

"If you are working with a large dataset, recreating the entire dataset each time users navigate to a new page can degrade performance. By using custom paging, you can retrieve data in page-size "chunks" — that is, retrieve just a page worth of records at a time. To do that, you turn off the automatic paging feature of the grid so that it doesn't assume that it is working with the entire dataset. You then get the correct number of rows to fill the grid."

A generic paging solution

The question that comes to mind is: "Can't we solve this problem once and for all?" In other words, can we create a library that will help page efficiently through a recordset? Without recreating the same logic every time? Therefore, I started working on a re-usable solution.

Fortunately C# came along too, with its impressive composition methods as it elaborates on the brilliant concepts initially synthetized by the Java team from state-of-the-art practices and programming patterns.

The RelationalArtist library helps paging efficiently through a recordset. One strand of logic optimizes paging when the results are sorted by primary keys. Another strand allows you to specify non-primary sort keys. It's performance tends to degrade in line with the amount of duplication in your sort keys.

Supporting multiple vendors

The library supports out of the box: MsSQLServer, MsAccess, and MySQL. For MsSQLServer, it uses the System.Data.SqlClient logic. For MsAccess and MySQL it uses the System.Data.OleDb logic. It is certainly not difficult to add support for other vendors, like Oracle or Sybase. By the way, feel free to send me patches and your logic to improve the library or to add support for more vendors.

The library generates, transparantly, SQL in the dialect appropriate for the database of choice. Of course, no one could prevent you from using vendor-specific SQL dialect if you felt the urge to do so.

Multi-processing versus multi-threading

The responsibility for managing the allowable number of instances of the database connections is relegated to the application. Under process isolation, like in IIS5.0, a (static) singleton will certainly do the job. If you are dealing with a multi-threaded environment, like in II4.0, you will probably need to create a connection for each page invokation, or else serialize access to the singleton connection. You should in any case prevent situations in which two data readers are simultaneously reading from the same connection. ADO.NET will insist on throwing exceptions.

As a general guideline, you are probably better off with multi-processing, optimized with process recycling and pooling, than with multi-threading, when executing the stateless part of your logic. It buys you full memory separation and a forteriori: thread safety. Sharing static memory and resulting contention issues are best left to the database server or other backend persistence logic.

Automated test battery

For each namespace, there is an NUnit namespace, ending in NUnitTest, which contains an automated test battery. I guess this namespace will grow bigger in the future. The idea is to add future trouble tickets there.

Windows and other operating systems

Now the $36,000-question. Does it run on Linux? Well, I hope it will. That depends on when the efforts of DotGNU and/or Mono will have produced runnable results. Work-in-progress is impressive already.

This library is licensed under the Library General Public License.

Erik Poupaert
erikpoupaert@users.sourceforge.net
Brussels, Belgium
May 2002