Fog Creek Software
Discussion Board

Conditional Compiling of it worth it?

I have an application that I want to make two versions of. One is for JET (existing) and another that will run on sql server.

This is a ms-access app with about 26,000 lines of code, and 160 forms. Not a large application by any means. It only has a few class objects.  It was written by me, so I do think is it well written!

I am going to use ODBC via DAO for sql server. While one should use ADO (ole DB), I have in my experience not seen any bandwidth difference between using ADO OlE db, and DAO via ODBC anyway. Ie: if you know what you are doing, then both DAO and OLE DB perform the same if you use stored procedures and passthrough quires to sql server anyway.

So, I plan to keep the bulk of the code as DAO. I can then simply attack this change to sql server on a case by case approach. In cases where I have some VB functions and sql code processing occurring I plan to switch that code to some stored sql procedures.  I will not do this for all cases, but JET does have the luxury of calling VB code from expressions in SQL, and you can’t do that with sql server. (at least you can’t if you want any performance here, and I don’t believe that a 3 tired design is a workable right now solution here).

The problem here is that I don’t really want to maintain two separate versions. So, I have two choices here:

choice #1
Use conditional compiler options for the code that will be different between the sql and JET version.

choice #2
Try and abstract the data routines into two code versions and set some flag for what routine to be called at runtime. (perhaps I will use a mix of both conditional compiling and some runtime vars to make this work).

So should I abstract all the data handling routines out to another layer and then have to only change the stuff behind this layer?

Any comments on the concept of using conditional compilation here is welcome.? I am not restricting this to VB/ms-access....but any general insights on this issue is welcome.

I have written a lot of software on a lot of different platforms. I don’t even recall using conditional compiling when I was writing assembler stuff.

Since I have NEVER used conditional compiling in my projects, I can’t speak from a general “instinct” point of view if this is a good idea?

Is there any maintenance points I should know?

Is, or Was conditional compiling of code worth the trouble for your last project?

I mean, do large projects that supposedly run on both the Mac and the PC get away with just conditional compiling?

Albert D. Kallal
Edmonton, Alberta Canada

Albert D. Kallal
Monday, June 16, 2003

So Albert, is this port to MS SQL is just the beginning of many?    Did you see this coming when you started the project?

The personal problem I've had with 'putting it in one place' in an abstraction layer is that is a functional ordering instead of a logical one.  The best place for the 'list employees reporting to me' logic is in the 'manager' object, not farmed out to some abstract library.  When you are maintaining code, it seems that you just visit little problem bits of code.  You won't enjoy having to play 'join the dots' to find how this your logic interacts with the DB abstraction layer.  DAO is all the abstraction layer you're going to need!

Assuming you stick with DAO (if it ain't broke don't fix)  then you can get away with runtime-selection of implementation.  The overhead of that switch statement will be nothing compared to the actual sending of the request either way.

I am guessing the amount of stored procedures you'll need is pretty low.  You can have 90% in very safe portable SQL and then just put an if or a select around code that is performance critical.  And it goes without saying that you find your performance critical bits to tackle in a backend-specifc-way from profiling, not guessing :-)

good luck

Monday, June 16, 2003

Forgive me, but I can't wrap my head around what you're saying - are you for or against stored procedures?


Monday, June 16, 2003

"...and 160 forms. Not a large application by any means."

Yikes! I knew that most of you guys were working on more hardcore systems than I do, but from my frame of reference an app with 160 forms (And I'm assuming that you mean windows forms or dialog boxes) is gargantuan. I'd even be surprised if Word has more than that. I'm going to go finish reading the message because I can't imagine how large and complex an app of that size is.

On second thought, 26K loc isn't that bad, these forms in an access app (which I have zero experience with) must mean something different. Right?


Monday, June 16, 2003

I wouldn't use conditional compilation unless you're REALLY concerned about the size of your executable.

Instead, define an interface that contains all the database features you need and write two different classes that implement that interface, one for SQL server and one for Access.

In your case it seems like the most common difference between the SQL Server and the Access versions will be syntactic differences in SQL. In that case your interface might just consist of simple functions which return the appropriate SQL statement for each database. In fact if the only difference is the text of the SQL statement, why not have two big arrays of strings, one with SQL statements in Access form and the other with SQL statements in SQL Server form, and decide which array to use at the last minute.

Joel Spolsky
Monday, June 16, 2003

If you can get away with a runtime switch, do it that way. That way you never ship out the "wrong" version to a customer.
We have a database shim layer that sits between our code and the database and lets us swap from Pervasive to MS-SQL. In practice we've found around 3 or 4 places in our code base that we needed to optimise the SQL version (This is across a 56Mb code base)
If we were looking for serious speed we could optimise in many more places but we tend to wait for our trainers to come back with complaints.
One final thought. Do some measurements, some of the stuff that we thought would need optimising would only produce a 20% improvement in speed post optimisation on most customers data so we never bothered. (To be fair this meant an operation took 120 ms rather than 100ms, so the user is very unlikely to complain)

Peter Ibbotson
Monday, June 16, 2003

>I wouldn't use conditional compilation unless you're REALLY concerned about the size of your executable.

Hum, ok. I am concerned, but it is not really MAJOR. It is good and noble to keep things small when one should. The 7meg code mdb right now can actually be zipped onto a single floppy 1.4 meg disk as a mde.  (p-code is small!) A few more features, and that will NOT be possible any more.

However, the goal is a version that functions with both platforms, and I will without a doubt trade executable size. Executable size is not a issue right now, but it most certainly is NOT going to be a issue if I did use compiler options to solve this problem.

As for different sql syntax, you are right, much of the difference here is not going to be code. That is a good observation.

I do however want to move a good number of those small code and processing routines from VB to t-sql. That certainly can be done without compiler options.

Thanks...this does help me.

And,  Peter also just gave a great reason as to not use the compiler. It means the same version can switch...and I don’t have to send out new code. Peter, your advice was *exaclty* the kind of reasoing I was looking for to tips the scales one way or another...thanks...

Albert D. Kallal
Edmonton, Alberta Canada

Albert D. Kallal
Monday, June 16, 2003

>>So Albert, is this port to MS SQL is just the beginning of many? Did you see this coming when you started the project?

I kind thought about it, but no, I did NOT design the application with migration to sql server as a goal.

I designed the application to run very well with JET. Since much of the design was to keep bandwidth down to a real min, then that kind of design tends to work very well with sql server also. I NEVER for example just load up a ms-access form attached to some big table. That is dumb in ms-access, and it is complete un-workable in sql server. So, forms in general are loaded to a requested record, and nothing more is transferred. It is a huge myth that bound forms are bad in ms-access. They are just fine, but the real trick is to restrict the form to the one record you need.

Hence, a LOT of the design I have will work well with sql server.

I will explain a bit why I want a sql version. goes...

My plan here is for 5 or 6 user systems to use the free MSDE sql server in place of jet. (but, I still need, and want two versions).

The application right now has about 55 tables (lots of relations...good normalized design). The tables are usually quite small, only around 30,000 records. With 5, or 6 users on a standard network, this ms-access application right now responds instantly. I can’t think of a screen or form that holds up the user. There is only one form that that does take about 1.5 to seconds to load. Microsoft used to rate JET for 50 users before they started selling SQL server. The performance of JET is more then what I need most of the time.

Note that my design does NOT store results like inventory and things like the number of people booked into a hotel. This means that a rather large performance penalty takes place for a lot of general queries. However, with good designs, performance is more then adequate with JET (you can’t tell the difference with 1 user, or 6 users in the system now). However, with that many tables, and a design that does NOT store results like inventory then one index corruption can cause incorrect results. I don’t even store invoice totals, nor even the number of people booked on a tour bus. All those numbers are calculated on the fly in real time (but with only 5 or 6 users in the system...that is nothing). However, this on the fly approach means that I relying heavily on the database engine to get me results in a hurry, and much of those results are going to rely on indexes. One minor problem in a index, and my results will be wrong. (that is NOT the case in a design that stores those values).

Anyone who has used products like JET or FoxPro will of course nod their heads in agreement that they know someone who has experienced some form of index corruption.  (fire up google and type in “FoxPro index corruption” or “JET index corruption” and see what you get. Products that are run in a file share on a small office network are vulnerable to index corruption. However, the risk vs cost has usually been a reasonable compromise here. When no network is to be involved, then JET is rock solid. When you need multi-users, then using JET means we don’t need a database server here.  Now that MS has a free sql server engine, I want to be able to offer it as a option. (it looks good to promote a product that runs on SQL server and state that we have a  100% sql server compatible product!)

Use of sql server is several orders of magnitude in terms of reliability here as to compared to file share products like JET and FoxPro. I have not yet experienced a damaged file (JET) for this application,  but I want something ready to go for any client that does.

In addition, with sql server, a proper written form can run EASILY function via VPN connection. This means that I can offer clients a remote connectivity option without them having to install windows terminal server (which I what I suggest and use now). Sql server can even reduce bandwidth requirements to even less then TS, but ONLY if you write for SQL server. I mean, how much data gets sent to a form that needs one record with sql server? (hint: very low). You can’t even consider running a file share like JET across a vpn even with high speed net, let alone low speed! With sql server, then adsl, and high speed net is just fine. Heck, 56k might even work if I put in the effort.

So, I have several goals in mind with the sql server version, and one of those goals is to allow good connectivity without having to resort to windows terminal server. I also get more reliability. I also get to tell clients that a free copy of sql server is included! This list is quite long. There is also the issue of web connective.

Albert D. Kallal
Edmonton, Alberta Canada

Albert D. Kallal
Monday, June 16, 2003

>>Forgive me, but I can't wrap my head around what you're saying - are you for or against stored procedures?

Is that above question is directed to me, or the other person? Yes, I most certainly do believe in using stored procedures. In fact, I * want * to use some stored procedures (t-sql) to reduce bandwidth here. I have some fairly complex quires (those nasty calc on the fly stuff) that need to be views, and some will be come stored procedures.

There is also some sql that I have right now that uses VB functions, and to allow those VB functions to run via a linked table to sql server is going to be a real poor design (poor AND slow..yikes!). I do want this app to be real zippy. Further, I want to be able to run this app on a low speed connection. In fact, some stored proces will reduce load times for several forms by a good deal when done right. (that means I can get rid of that 1.5 second delay in one spot!).

I see weekly posts in the sql server group about how some JET application was migrated to sql server and it then runs SLOWER!. Of course it does!, you have to design your software for sql server to reap the benefits of sql server here.

Albert D. Kallal
Edmonton, Alberta Canada

Albert D. Kallal
Monday, June 16, 2003

>>these forms in an access app (which I have zero experience with) must mean something different. Right?

No, actually they are much the same as VB forms, but offer a LOT more functionality for the same amount of code. That is why the form count is high, but the code count is low. In straight VB, the number of forms here would not change..but the amount of code would go up by a good deal. (a very large amount in fact). Those ms-access forms save tons of code.  You can open a form to a particular record with ONE line of code in ms-access from a pick list for example. If you are curious, here are some screen shots:

I suppose I could consider re-writing the whole thing as a web service in .net and be done with this problem...and I am actually thinking of that too!

Albert D. Kallal
Edmonton, Alberta Canada

Albert D. Kallal
Monday, June 16, 2003

In general, I would default against conditional compilation by default

However, having worked on large multi-database apps, one thing I would say:  eventually the versions will probably diverge far more than you currently realize.  What tends to happen, over time, is you want to add special features to exploit one database or another (unless you are sticking to totally lowest common denominator), sometimes database issues can have a knock on effect on user interface etc.

S. Tanna
Monday, June 16, 2003

Hi Albert ,

That's interesting, I looked into it a bit more and those ms-access forms have some interesting capabilities.

That sounds like a huge app to me, the sheer number of forms blows my mind. It seems on first blush that an app with that many forms would be very hard for "typical" and especially beginner users to understand, managing the various forms and functions, unless a lot of the forms are variations around a core set of specific functions. Like maybe seperate forms for different ways of sorting or filtering info instead of those functions working within one seperate form.

I just took a count, and the current app I'm just finishing up has a grand total of less than 20 forms in total, including common dialog bocxes and what messageboxes I've used. This is a fairly straightforward shrink-wrap app used to create and edit documents using two different formats. Part of this upgrade project was to apply some interaction and UI design to the process. This resulted in streamlining a number of wizard style dialog boxes into unified dialogs, dropping the total form count from *maybe* 40.

Anyways, that's interesting how large some systems can get.


Monday, June 16, 2003

Interesting question for me, since our application does both.  We have what's evolved into an application framework, along with several client plugins.  (And yes we built a successful plugin first, and it has then evolved into a framework).

We always build the framework, but conditionally build the plugins for different clients.  The framework enables or disables certain functionality based on querying the plugins.  A couple of things we have learned along the way ...

- if the code isn't there, it can't be the source of a bug.  Include as little code as possible.

- make the logic that enables the functionality in the framework very, very simple.  It is easy to get wrong, and is tough to provide workaround for in the field.

- Ant has been our choice for builds (we're java :) )  It seemed complex at first, but it as our needs have grown the functionality has been there.  We couldn't do conditional compilation without it.  You will need to find a similar make tool with similar capabilites if you go down the conditional compilation path.

Hope that helps ....

Monday, June 16, 2003

*  Recent Topics

*  Fog Creek Home