Avoid Context Switching for More Readable Code

Context switching is when your brain has to jump between unrelated tasks.

It's easier to manage a large number of related tasks, than a smaller number of unrelated ones.

But what does context switching have to do with writing software applications?

Juggling Concepts

Writing code is kind of like juggling.  Each related procedure is a ball.  You add balls as you work through implementing the logic of the task at hand.  

Writing action queries can also be like juggling.  If you are working through a process that requires updates to multiple tables, you will have multiple queries.  Each related query is a bowling pin.  

Juggling balls is hard.

Juggling bowling pins is hard.

Juggling balls and bowling pins at the same time is darn near impossible.

A Microsoft Access Example

In Microsoft Access, this commonly occurs when you execute saved query definitions from within your code.  

Here's a common example of voiding a check in an accounting application.

Sub VoidCheck(CheckID As Long)
    If MsgBox("Are you sure?", vbOKCancel) = vbOK Then
    
        CurrentDb.Execute "VoidPart1", dbFailOnError
        Forms!Global!VoidReason = InputBox("Why?")
        CurrentDb.Execute "VoidPart2", dbFailOnError
        If DCount("*", "MyTable", "CheckID=" & CheckID) > 0 Then
            CurrentDb.Execute "VoidPart2a", dbFailOnError
        End If 
        CurrentDb.Execute "VoidPart3", dbFailOnError
    
        MsgBox "Void complete."
    Else
        MsgBox "Void cancelled."
    End If
End Sub

In this contrived example, we've got a mix of code and action queries.  In a real application, you would probably have even more code mixed in, but I wanted to keep this simple for demonstration purposes.

You may be wondering what "VoidPart1" does.  If so, you're already starting to see the problem.

If this were a real application, you would have to leave the VBA editor and open that query in Design View to figure out what it's doing.  And by the time you've done that, you will likely have forgotten what's going on with the code.

So after figuring out what's going on in "VoidPart1", you come back to the code and notice that you are inside of an If-Then statement.  You can't remember what the condition was that put you inside the If-Then statement, so you scroll up to the top of the procedure to refresh your memory.

Now you're getting some input from the user and storing it on a global form.  You can probably infer that the data is being used by "VoidPart2" and/or "VoidPart3."  But how?  Back to the query designer to figure it out.

By the time you reach the end of this very simple procedure, you've switched back and forth between the code and the query designer and the code and the query designer and the code and the query designer so many times that you don't know which way is up and which way is down any more.

You're juggling balls and bowling pins and even a running chainsaw (did I forget to mention that managing changes to global state is like juggling chainsaws?).

Better Naming is Not Enough

I can already hear people protesting.  

"The problem is not the context switching, it's the horrible naming convention. Just change the names of the queries to something more descriptive, like so:"

• VoidPart1 => Void_MarkOriginalTransAsVoided
• VoidPart2 => Void_CreateReversingTransaction
• VoidPart2a => Void_DeleteRelatedDetails
• VoidPart3 => Void_CreateNewUpaidInvoice

Now, there's no question this is a big improvement.  But what is actually happening inside the Void_CreateReversingTransaction query?  What date is being filled in?  Today's date? Or the date of the original transaction?  The only way to know for sure... is to go back to the query designer.

In other words, you're still juggling balls and bowling pins.

Restoring Sanity

To make things easier on your brain, you need to reduce the amount of context switching you are asking it to do.

One way to do that is to avoid saved "action" (INSERT/UPDATE/DELETE) query definitions.  Instead, you can write and execute the query SQL definitions in code.  I wrote a class module to simplify the SQL generation and execution, but you could also just write the raw SQL strings and execute them directly.

By doing it this way, you can follow the entire logical process in one place with no context switching required.

It may mean juggling a few extra balls, but I'd rather do that than have to juggle bowling pins and balls at the same time.  Plus, it means I never have to juggle chainsaws.

A Notable Exception

One important exception to this general rule is stored procedures in SQL Server (and other RDBMS products with similar capabilities).

There are several reasons for this:

• The performance difference is often 10x or greater
• Multiple steps can be combined into a single stored procedure
• Improved security

For example, we could write a single stored procedure named VoidCheck and pass it the CheckID and VoidReason.  All the updates, deletes, and inserts could be combined into that single stored procedure.  We could even set up role-based access control so that only admins can void checks.

To be clear, the context switching is still an issue with stored procedures.  However, the benefits of stored procedures are so potentially massive that the tradeoff is usually worth dealing with the cons of context switching.

Image by Theodor Moise from Pixabay