It’s time to convert my Access query to a SQL view, what do I do now?
We have a client who runs a system ePolk created many years ago that uses an Access front end attached to a SQL backend. As with many of our systems, we link to SQL tables and then build local queries for reports and data processing. Over the years, as our client’s business grew, so did their data. And since their data has grown, so does the processing time. They contacted us to let us know that a form that originally opened instantly (or at least within a few seconds) was now taking a really long time. I timed it and it took almost two minutes! By stepping through the code, I found the culprit. The process was slowing down due to an append query which included three grouping sub queries.
To resolve this, I moved all four queries to SQL views. Converting the Access queries to SQL views didn’t present a challenge. However, between the two systems there are some syntax differences, and there is a good Access to SQL cheat sheet for functions found in Access. The SQL version of the append query became a Select View which was then linked as a table. I was then able to create a local append query based on this “table”. With SQL doing the heavy processing, the form now opened within six seconds!
To recreate the scenario, I decided to use the default Northwind databases. In the Access Northwind mdb, I linked to the SQL Northwind database tables- Customers, Orders and Order Details. I then added three grouping queries to the “Order Details Extended” query Average Unit Price per Order, Count of Items per Order and Total Orders by Customer. I created matching views in SQL. As a side note, I did change the structure just a bit to use single, numeric primary keys in the Customer and Order Details tables.
Based on these queries/views, I built a few reports. The first report uses the local “Order Details Extended” query with the added fields from the grouping queries and the Customer Name. The second report is based on the linked SQL view that I modified to include the fields from the grouping queries and the Customer Name.
However, a more comprehensive comparison required a larger amount of data so I wrote a stored procedure in SQL to add about a million order detail records and corresponding orders (three items per order). With this amount of data, we’d be able to see noticeable performance differences between the two reports.
Once our 1MB test image completes loading on the user’s browser, jQuery calls our remote page test.aspx. Our variable string is also passed to our remote page as a querystring. The value of this querystring is set to the current date and time when the server initially compiles the page to send to the visitor. So, string is essentially the date and time at which the user requested the page.
Our remote page, test.aspx, calculates the connection speed and passes the value back to the page that initiated the call. We will take a look at those calculations below.
The first report all customer orders using the local queries took 4:33 to run. The second report- all customer orders based on the SQL view took 1:22. Although in reality, date ranges or individual customers would be used to filter the data, this demonstration shows how a SQL view linked as a table provides a significantly faster processing time than local queries.