I occasionally get invited to present at some of the developer-oriented conferences that are put on over the course of a year. While I don’t get paid for these presentations, it’s a free trip to somewhere interesting, access to some of the industry gurus who are also presenting, a chance to see some friends, and an opportunity to talk about something that I find interesting. At the last conference at which I presented, I realized that one of the parts I enjoy most is the question-and-answer sessions that follow a presentation.
I get a lot of satisfaction from being a consultant. It’s a grand life if you don’t weaken, as my mother-in-law frequently says. For me, part of the satisfaction comes from actually helping people. I’ve been doing an intranet project for one of Canada’s major banks, and, because of my schedule and their short deadline, it’s meant being on their site until ridiculous hours in the morning. We’ve accomplished a lot together–more than we thought possible–and I’m going to miss working with the team when we implement the project. The question-and-answer sessions that follow a presentation are that experience of helping someone distilled to its essence. At my last conference, I was presenting on two topics near and dear to my heart (and the hearts of all Access developers): using SQL and creating effective database designs. Since I’ve developed a reputation as someone who can speed up applications, I begin both sessions with a simple claim. Your application is guaranteed to run faster if you replace your VBA data-processing code with SQL. This is a broad claim, and it’s sure to be disputed by developers with any experience. I make the claim for two reasons: 1) It gets the audience’s interest; and 2) It’s true.
Sure enough, after the sessions, one or two people usually come up to explain, more in sorrow than anger, that the SQL solution isn’t always faster than the code solution. They’ll admit that it frequently is, maybe even usually is, but not that it always is. My reply is simple: I’ve never seen an application where replacing code with SQL didn’t produce a faster program.
I do go on to explain that there’s one exception. If the database design is wrong, then, yes, you might be forced to use code to get the performance that you want. Doctors bury their mistakes, architects cover them with ivy, and database designers write code around them (programmers cover their mistakes with documentation).
However, in these situations, if you redesign the database, you’ll find that a simpler, faster, SQL-based solution becomes possible. I described a typical application in my article “Working SQL Case Study: Configuring Data” in the October 1998 issue of Smart Access.
One conference attendee actually had a sample application on his laptop, which he proceeded to walk me through. The data model was huge, but the particular area that he wanted me to look at involved just four tables. The first table had a primary key that consisted of one field. That table was linked to two other tables that had primary keys that consisted of the primary key from the first table and a field unique to the table. These two tables were linked to the fourth table. This final table had a primary key that consisted of three fields. The first field in the fourth table’s primary key was an AutoNumber field. The next two fields in the primary key were the unique fields from the two previous tables.
Reports based on this final table were a nightmare to produce because they needed to draw information
from the first table in the series. By the time a query linked back to the first table through the two intermediary tables, so many duplicate records were generated that the computer just ground to a halt. If you’ve read some of my Working SQL columns, you know that I object strongly to generating arbitrary data to use as a key field (for instance, AutoNumber fields). My feeling is that your primary keys should always be based on real data in the table. If you’ve read those columns, the solution that I suggested probably won’t be a surprise to you. After looking at the data model for a couple of minutes, I suggested that we replace the AutoNumber field in the fourth table with the primary key from the first table. Now the fourth table could be linked directly to the first table in the series. I don’t know if all of the reporting problems went away, but, by the time we parted, we were pretty confident that the problem was a lot simpler than it used to be.
I went back to my hotel room quite pumped. I had the satisfaction of feeling that I’d actually helped someone out with a problem (plus, I’d avoided all of the real work of implementing the solution). Not only did I realize how much that meant to me, I also realized that this is one of the reasons I enjoy editing Smart Access: I have the opportunity to present articles that will help readers find solutions to their problems. Who could ask for more?