The economy is a wreck and things will likely get worse before they improve. Unemployment is even worse; almost 600,000 jobs were lost in January 2009, sending the unemployment rate to 7.6%, the highest it has been in 16 years. So many data professionals are out there looking for their next challenge … and more probably will be job hunting before the year is out.

Before we even begin this month’s column I better define what I mean by a “black box.” If I plan to recommend avoiding them we better both understand what it is we are talking about proscribing.

Simply put, a black box is a database access program that sits in between your application programs and the DBMS. It is designed so that all application programs call the black box for data instead of writing SQL statements that are embedded into a program. The general idea behind such a contraption is that it will simplify application development because programmers will not need to know how to write SQL. Instead, programmers call the black box to request data. SQL statements become calls – and every programmer knows how to code a call, right?

This approach is commonly referred to as a “black box” approach because the data access interface shields the developers from the “complexities” of SQL. The SQL is contained in that black box and programmers do not need to know how the SQL works – just how to call the black box for data. But there are a number of reasons why this approach is not sound. Let’s examine them.

Ignorance (of SQL) is not a Virtue: The basic premise behind implementing a black box is that it is better for programmers to be ignorant of SQL. This means that you will be creating database applications using developers having little to no understanding of how SQL works. So what may seem like simple requests to a non-educated programmer may actually involve very complex and inefficient SQL “behind the scenes” in the black box. This can result in innocuous requests for data sometimes performing quite poorly. Knowledgeable SQL programmers understand the complexity of their requests and will formulate better performing queries.

It is a good idea to train your development staff in the proper usage of SQL. Develop and publish SQL guidelines in a readily accessible place (such as your corporate intranet or portal). These guidelines should outline the basics elements of style for SQL programming.

Shortcuts Make for Poor Performance: The programmers in charge of writing the black box code will inevitably introduce problems into the mix. Most developers will choose to simplify and take shortcuts. But shortcuts can lead to degraded performance. How so? For example, assume there are three programs and each one of them needs to retrieve customer information by area code. Program 1 needs the customer name and address, program 2 requires customer ID, name, and phone number, and program 3 requires customer ID, name and type. This is properly coded as three different SQL requests (each one in its own program).

But all of these SQL requests are quite similar. The black box programmers will notice this and usually will try to condense all of them into a single SQL statement with all of the columns needed in the SELECT-list. When program 1 calls the black box we execute the query and return just the customer name and address; for program 2 we return just customer ID, name, and phone number; and for program 3 the black box returns only customer ID, name and type. This shortcut saves coding time but but it is bad for performance. Best practice SQL coding retrieves only those columns required; never more. Additional columns retrieved means additional work is required to send additional columns from the DBMS to your programs. Minimizing the number of columns in your SELECT statements will improve application performance. By coding shortcuts such as these into the black box you are designing poor performance into your database applications by design. Not a good idea, right?

Extra Code Means Extra Work: When using a black box an application requires more lines of code to be executed than without the black box. The call statement in the calling program is extra and the code surrounding the statements in the black box that directs execution is extra. None of this is required if you just code your SQL right into your programs. This extra code must be compiled and executed. When extra code is required – no matter how small or efficient it may be – extra CPU will be expended to run the application. More code means more work, meaning degraded performance.

Synopsis

There are other performance arguments against black boxes, including the fact that a black box is a data access method, but SQL is already an access method, so it duplicates a service already available.

When a black box is used, performance inevitably suffers. Black boxes deviate from proper SQL development guidelines, require additional work and additional code, and promote ignorance. Avoid black boxes – instead, train your programmers to code efficient SQL statements right in their application programs. Your users will thank you for it!