If you simply memorize the examples showed here, you can already improve many queries you will encounter in your work. This text and the accompanying video guide you the first steps along the way. Query tuning is a complex topic and requires experience to master it. A query that is optimized to handle 10 million of records will probably also be the best query if it needs to tackle 10 billion of rows. Then at least generate at least enough data to make the database really work to process your queries. This might not be feasible for extremely large (SQL Server) databases. Then create sample data matching the expected production data in volume. Ask you client about the amount of data he expects to store in your applications database. Whenever you want to realistically assess the performance of your application you need a realistic volume of data. This will always run blazingly fast, but it will not provide any meaningful performance metrics. It is a very common mistake to develop and test application performance only with a small number of sample records. SELECT * FROM Customers WHERE StrComp (, 'MadMax', 0 ) = 0 AND = 'MadMax' - Execution Time: 0.87 seconds Using the individual criteria values on the raw table columns will produce much better performance.
Using any VBA function to process the data in the table and then comparing the results to the criteria will once again prevent the use of any index in addition to requiring a lot of computing power to process the data from each row in the table. Public Function FormattedAddress ( ByVal Country As Variant, _ ByVal State As Variant, _ ByVal PostalCode As Variant, _ ByVal City As Variant, _ ByVal StreetAddress As Variant ) _ As String Dim retVal As String Select Case Country Case "United States" retVal = Nz ( StreetAddress, "" ) & vbCrLf & Nz ( City, "" ) & ", " & Nz ( State, "" ) & " " & Nz ( PostalCode, "" ) & vbCrLf & UCase ( Country ) Case "Germany" retVal = Nz ( StreetAddress, "" ) & vbCrLf & Nz ( PostalCode, "" ) & " " & Nz ( City, "" ) & vbCrLf & UCase ( Country ) Case Else retVal = Nz ( StreetAddress, "" ) & vbCrLf & Nz ( PostalCode, "" ) & " " & Nz ( City, "" ) & " " & Nz ( State, "" ) & " " & vbCrLf & UCase ( Nz ( Country, "" )) End Select FormattedAddress = retVal End Function This prevents the database engine from using an index to find the matches. Often queries with LIKE are written to match any part of the data in the searched column. When searching for partial matches of text we use the LIKE operator in SQL. If you apply the following recommendations to non-indexed columns the optimizations will have much less or no effect at all. This is generally recommended for most columns you frequently use in query criteria. Keep in mind that these differences will be exponentially bigger in a real production environment with multiple users all using the same database and infrastructure at the same time.Īll the criteria columns in the following examples are indexed. Nevertheless, in this scenario the differences between the variations of the queries are only seconds or even milliseconds. To make the optimizations have an actual effect, I loaded up the database with a significant number of records (100K Customers, 1.5M Orders).
To eliminate side effects such as network latency and bandwidth fluctuations, concurrent server load, and database and operating system caching, I use a copy of the Northwind sample database on my local computer.