Today we have a guest editorial from Mike
Recently, I had a job writing and/or enhancing add-ons to a major software development company’s Accounting package for my employer. My employer sold the add-ons as part of a total package that included the basic software. The experience caused me to question the wisdom of filling a software development team with generalists instead of specialists. The original software did not have enough functionality to adequately manage manufacturing problems, and the add-on for which I was responsible made the software more useful to manufacturers. The add-on had to generate production orders from Bills of Manufacturing (BOM), a requirement needing recursion. The tool it used was VB .NET and the database engine was SQL Server 2005.
One day I noticed that it was taking four minutes to create a production order containing 387 line items from a BOM that had only six line items and that was much too long. The reason for the explosion from six to 387 was that recursion was required for four of the six line items in the BOM. The VB code, which I inherited, used a recursive VB function to produce production orders. It turned out that for this BOM the process required over 100 calls to the database.
I believed, and still do, that for data-centric applications, it is best to do as much of the processing as possible right in the database. This principle was not being followed in this case. In order to reduce the number of calls to the database to one, I created a recursive stored procedure containing all the data needed to create the production order. I then changed the VB code to invoke the stored procedure and the result was that the original four minutes was reduced to one. I could have made it faster than that by having the stored procedure also create the production order but the larger company didn’t want any objects created unless policed by their SDK. Without the SDK requirement, the probable result would have been a reduction from the original four minutes to only a few seconds. Wow!
However, certification by the larger company prohibited the use of stored procedures, and I had to convert to a recursive common table expression (CTE). Since I had never created a recursive query prior to this, it was for me a major intellectual workout. Had I been more astute at database development, I would not have had to take three or four days to get the query correct. Given the data structure, this approach actually took longer than the stored procedure, but the larger company didn’t care.
This experience led me to the notion that organizations are being foolish by looking for developers who can do everything. The knowledge required to create major applications is too great. Companies end up getting jacks of all trades and masters of none, and the resulting applications are not all that great. I think there needs to be a greater division of the effort and a major part is coding required within the database. I think that all the data retrieval should be handled by people who are familiar with all features of databases while allowing the desktop and ASP developers do their coding without distraction.
There is also the great effort necessary to become competent at Desktop .NET, ASP .NET, and database processing. For SQL Server one must read approximately 2,000 pages. ASP .NET requires a total page count of 3,500. But since the code samples are often in C# and our department was using VB .NET, there would been the additional effort needed to translate them to VB. VB .NET desktop requires 1,900 pages. ADO .NET 4 requires 700 pages. A total of 8,000 pages must be devoured. There is also a good chance that the reading involved would be merely a starting point on a journey of discovery toward expertise. I only offer this thought as a metaphor to show the great effort required to become proficient in all three of these tools. Due to the overwhelming demands on them, generalist developers tend to handle similar problems with the same tools outdated or not. However, if only one aspect of an application’s architecture is assigned to a specialist, I believe it should be the database programming. The type of thinking required for maximizing performance is not the same for database programming as that needed by the other tools.