Many people assume that simply upsizing a backend database from Microsoft Access to SQL Server will solve all of their performance problems.
The reality is more complicated. Upsizing alone usually leads to worse performance, at least initially. For example, queries that rely on VBA functions to calculate values often run much slower when the data is stored in SQL Server.
The best way to turbocharge those queries is to migrate the VBA functions to equivalent SQL Server scalar functions.
SQL Server Scalar Functions
A SQL Server scalar function is a user-defined function (UDF) that returns a single value.
SQL Server UDFs can also return tables; these are commonly referred to as table-valued functions (TVF). A scalar function, then, is roughly equivalent to a typical VBA function. It accepts one or more parameters and returns a single result.
I can't tell you in one article how to convert any arbitrary VBA code into its equivalent T-SQL. Instead, I can do the next best thing.
I can provide you with a common set of steps to ensure you can safely convert any VBA function to a functionally-equivalent SQL Server scalar function.
The 3 Words that Make Risk Go Away
Rewriting critical business logic is a high-risk situation.
All that risk can be eliminated with these 3 words: Test. Driven. Development.
Test Driven Development
Test driven development minimizes risk in complex software endeavors.
Here's the high-level overview:
- Write tests and confirm they fail
- Write code until all the tests pass
The biggest challenge with TDD is providing adequate test coverage.
It takes time to write each test. You need enough tests to cover all the edge cases (the known knowns). You need creativity to imagine the corner cases (the known unknowns). And, of course, there are always those cases we don't anticipate, those scenarios that lie beyond our capacity for human aforethought (the unknown unknowns). (Unfortunately for us, those obscure scenarios are not beyond Ned-in-accounting's capacity for creative software use.)
We can use Microsoft Access to overcome the test coverage challenge without even breaking a sweat.
VBA to T-SQL via TDD
There are 10 steps to convert a VBA function into a fully-tested SQL Server scalar function.
The first five steps are all about overcoming the test coverage challenge. The next five steps are the basic steps in any test driven development scenario. We will explore these steps in more depth in future articles.
- Identify or create test data in Microsoft Access
- Build a "test table population query" in Microsoft Access
- Create a temporary test table in SQL Server
- Use the results of your Access query to populate the test table
- Create a dummy scalar function in SQL Server
- Build a test query in SQL Server to return failing tests
- Execute the test query in SQL Server to verify it fails
- Alter the scalar function to address failures
- Execute the test query in SQL Server
- Repeat steps 8 & 9 until all tests pass
UPDATE [2021-09-10]: Switched steps 5 and 6 to avoid errors when creating a test query that would be calling a non-existent SQL Server scalar function.