In Part I, we described how to overcome the simple but sometimes problematic issues of dbSeeChanges and code placement with respect to when a record get saved and defaults assigned. In this second article we look at problems relating to performance and application design.

It should be stressed that in general upsizing an application is a straightforward process and many applications only require very minor adjustments to be made to improve performance. However, on more complex applications after upsizing you could find that a complex report that took 5 minutes to run now takes 10 minutes or a batch job that took 10 minutes now takes 30 minutes, or maybe you have a query that just times out or never finishes. You maybe thinking, ..I thought you said SQL Server was faster than Access?. Well it is, but your design may need a few changes to reduce performance bottlenecks.

Although Access has problems when dealing with the combination of large data volumes and an increasing number of users, it is capable of undertaking some very complex operations suprisingly fast. With SQL Server you gain stability and robustness, eliminating issues of corruption and gaining scalability. But to take advantage of the potential improvement in performance you may need to change some of the approaches which you have used in Access. This article provides pointers to a number of areas where performance can be improved.

Query design

At the heart of the performance lies the question of how much data is being requested.

To analyse performance we use the SQL Server Profiler, a tool which can be used to monitor all the SQL being sent from Access to SQL Server when you do things like open a form, or search for data. In what follows we have extracted information from the profiler to analyse the query performance from the perspective of how the SQL is written in Access.

In this article the Access SQL is shown in bold and the trace output is not bold.

Consider the following Access query based on tables linked to SQL Server.

SELECT tblCustomer.Multiplier, tblCustomer.CustomerID, tblCustomer.*
FROM tblCustomer
WHERE (((tblCustomer.Multiplier)=1000) AND ((tblCustomer.CustomerID)="TRAIH"));

If we run the SQL Server profiler, we will see the following request being made by Access which is very efficient (The use of strange field called Multiplier is just a technique that we use for creating large sets of data for benchmarking performance).

SELECT "dbo"."tblCustomer"."Multiplier","dbo"."tblCustomer"."CustomerID" FROM "dbo"."tblCustomer"
WHERE (("Multiplier" = 1000 ) AND ("CustomerID" = 'TRAIH' ) )

Next consider the query

SELECT tblCustomer.Multiplier, Mid([CustomerID],1,5) AS Expr1, tblCustomer.*
FROM tblCustomer
WHERE (((tblCustomer.Multiplier)=1000) AND ((Mid([CustomerID],1,5))="TRAIH"));

Now the profiler shows the following, which is again very efficient, and we see how the Access Mid function gets translated into a SQL Server SUBSTR function by the ODBC drivers.

SELECT "dbo"."tblCustomer"."Multiplier","dbo"."tblCustomer"."CustomerID" FROM "dbo"."tblCustomer"
WHERE (("Multiplier" = 1000 )
AND ({fn substring("CustomerID" ,5 ,{fn length("CustomerID" )}-5 +1)}= 'TRAIH' ) )

Next consider adding a Format function into the where clause:-

SELECT tblCustomer.Multiplier, Format([CustomerID],"""AAAAA""") AS Expr1, tblCustomer.*
FROM tblCustomer
WHERE (((tblCustomer.Multiplier)=1000) AND ((Format([CustomerID],"""AAAAA"""))="TRAIH"));

The Format function has no direct equivalence in SQL Server, and the profiler displays the following.

SELECT "Multiplier" ,"CustomerID" ,"dbo"."tblCustomer"."Multiplier",
"dbo"."tblCustomer"."CustomerID"
FROM "dbo"."tblCustomer" WHERE ("Multiplier" = 1000 )

In the above, we see that more data is now being requested to transfer over the network, and the filtering using the format function (now missing from the above) is performed in Access; which runs in your computers memory (rather than the previous examples, where all processing executed in the servers memory in SQL Server).

If we had used the format function to only display information and not filter it as shown below :-

SELECT tblCustomer.Multiplier, Format([CustomerID],"""AAAAA""") AS Expr2,
Mid([CustomerID],1,5) AS Expr1, tblCustomer.*
FROM tblCustomer
WHERE (((tblCustomer.Multiplier)=1000) AND ((Mid([CustomerID],1,5))="TRAIH"));
>

Then the profiler displays the following, and we are back to having excellent efficiency.

SELECT "dbo"."tblCustomer"."Multiplier","dbo"."tblCustomer"."CustomerID"
FROM "dbo"."tblCustomer" WHERE (("Multiplier" = 1000 )
AND ({fn substring("CustomerID" ,1 ,5 )}= 'TRAIH' ) )

In the last example the format function will be run in Access to create the formatted output column, but because it is not used in the where clause it does not effect the efficiency in terms of the requested data travelling over the network.

So you can see that the more standard you SQL where clause is, the better performance you are going to get.

Top of Page

Form design

Consider a simple form bound to a table containing a large amount of data. When the form is opened we see the following in the profiler.

exec sp_prepexec @p1 output,N'@P1 int,@P2 varchar(5)',N'SELECT "Multiplier","CustomerID",
"CompanyName","ContactName","ContactTitle","Address","City","Region","PostalCode"
,"Country",
"Phone","Fax","TStblCustomer" FROM "dbo"."tblCustomer"
WHERE "Multiplier" = @P1 AND "CustomerID" = @P2',1,'ALFKI'

Then later on in the trace we see the following.

exec sp_prepexec @p1 output,N'@P1 int,@P2 varchar(5),@P3 int,@P4 varchar(5),
@P5 int,@P6 varchar(5),@P7 int,@P8 varchar(5),@P9 int,@P10 varchar(5),
@P11 int,@P12 varchar(5),@P13 int,@P14 varchar(5),@P15 int,@P16 varchar(5),
@P17 int,@P18 varchar(5),@P19 int,@P20 varchar(5)',N'SELECT "Multiplier",
"CustomerID","CompanyName","ContactName","ContactTitle","Address","City",
"Region","PostalCode","Country","Phone","Fax","TStblCustomer" FROM
"dbo"."tblCustomer" WHERE "Multiplier" = @P1 AND "CustomerID" = @P2
OR "Multiplier" = @P3 AND "CustomerID" = @P4 OR "Multiplier" = @P5 AND
"CustomerID" = @P6 OR "Multiplier" = @P7 AND "CustomerID" = @P8 OR
"Multiplier" = @P9 AND "CustomerID" = @P10 OR "Multiplier" = @P11 AND
"CustomerID" = @P12 OR "Multiplier" = @P13 AND "CustomerID" = @P14 OR
"Multiplier" = @P15 AND "CustomerID" = @P16 OR "Multiplier" = @P17 AND
"CustomerID" = @P18 OR "Multiplier" = @P19 AND "CustomerID" = @P20',1,'ANATR',
1,'ANTON',1,'AROUT',1,'BERGS',1,'BLAUS',1,'BLONP',1,'BOLID',1,'BONAP',
1,'BOTTM',1,'BSBEV'

So the form will operate fairly efficiently when opened and you can see how several rows are being sent to Access, this also explains why the recordcount showing the total number of records at the bottom of a form does not update in the Access record navigation buttons to show the total number of available records.

Now if try a Ctrl-f in one of the fields to do a search, this results in SEVERAL THOUSAND entries in this example we get lots of output like the following:-

Eventually we hit the record and find the following in the trace, where we see the read-ahead of several records taking place.

exec sp_execute 2,1000,'ALFKI',1000,'ANATR',1000,'ANTON',1000,'AROUT',1000,'BERGS',
1000,'BLAUS',1000,'BLONP',1000,'BOLID',1000,'BONAP',1000,'BOTTM'

So we can now see why it is important to filter all the data, and not allow users to do a Ctrl-F on a form with a table containing thousands of records! You will get miserable performance!

If we add an unbound text box to our form as shown below.

And add some code.

Private Sub txtMultiplierSearch_AfterUpdate()
If Not IsNull(Me.txtMultiplierSearch) Then
Me.Filter = "[Multiplier] = " & Me.txtMultiplierSearch
Me.FilterOn = True
End If
End Sub

Now when we look in the trace we see the following:-

SELECT "tblCustomer"."Multiplier","tblCustomer"."CustomerID" FROM "dbo"."tblCustomer"
"tblCustomer" WHERE ("Multiplier" = 1000 )

SELECT "Multiplier","CustomerID","CompanyName","ContactName","ContactTitle","Address",
"City","Region","PostalCode","Country","Phone","Fax","TStblCustomer"
FROM "dbo"."tblCustomer" "tblCustomer" WHERE ("Multiplier" = 1000 )

It is interesting to see how two requests are being made here, the first looks like it is being made to get the data keys, and the second to get data records.

And the performance gain compared to our Ctrl-F is astronomic!

Poorly performing forms are forms which allow users to search directly against large volumes of data, adding simple boxes for searching or opening forms with a where clause will make all these problems go away. People often suggest that you should use unbound forms, the main reason that this unbound approach gives good performance is that it helps designers to make users pick choices and then build the SQL which is specific to getting back the matched data, in short it encourages an efficient approach to design. However, if you ensure that your forms are always filtered to restrict the number of records to a manageable number, then unbound forms are just not required. You can still allow users to use the likes of Ctrl-F , but only on the resulting small subset of the data.

We could equally have used the following method for opening our form and restricting the data.

docmd.OpenForm "frmCustomers",,,"[Multipler] = 1000"

Top of Page

Importing data

Many systems import data, and they save the data in temporary tables. So your code probably empties the tables before the import, you will probably be using a delete query, or maybe some code. Either way you will be executing something like “DELETE * FROM Temp_SalesData”.

If the table is a temporary table, then the chances are it does not participate in any referential integrity, in which case you can use the “TRUNCATE TABLE” mechanism in either a pass-through query, or a stored procedure in SQL Server.

The SQL becomes “TRUNCATE TABLE Temp_SalesData”; expect an operation taking several minutes to drop to below 1 second on a large table when this method is used, the performance gain here is truly astronomic. This is always my first step in optimising performance in batch processes which stage data.

Another point to watch out for is attempting to join a local table of imported data to tables on the SQL Server as this can lead to a significant draw-down of data from the server to perform local processing. In these circumstances it is often better to push all the data up onto the SQL Server before performing processing.

Top of Page

Very slow queries, or queries that never complete execution

The problem in Access is that we build queries on top of queries on top of queries, and we often embed lots of special Access functions in them. More often than not a poorly performing query has several other queries sitting below the top level query.

The best solution here is to use MUST+SQL to translate a block of poorly performing queries into views and stored procedures in SQL Server.

When encountering a poorly performing query work through the layers of the queries starting from the bottom and working to the top, sometimes performance problems can be traced to a single culprit which can then be converted to a pass-though query or SQL Server View.

Top of Page

Performance problems after you have converted queries

If you are unfortunate in that after conversion you still have performance problems, then here are some addition suggestions.

• Avoid joining on calculated fields, even when a set of queries have been fully converted to SQL Server this can cause problems
• Consider creating a staging table which contains physical data, making data as part of the overall processing
• Use the SQL Server Management Studio to analyse the query execution plan, or the estimated plan if a query will not run, then look at how the indexes are being used on large tables and see if adding additional indexing helps with performance

Top of Page

Summary

Normally I am amazed at how well most applications convert and maintain good performance. It is really quite staggering when you look at very complex multi-tabbed forms which need very little re-design work to work efficiently with SQL Server. When they don’t, you need to take care in identifying exactly what is causing the performance problems and try some of the techniques we have suggested here to work around the performance bottlenecks.

Top of Page

MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or bringing together multiple databases and simplifying security. Click below to improve your development productivity.

Top of Page

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz