I work in an environment in which I am frequently asked questions about database platforms we support and provide general advice relating to troubleshooting queries and optimisation, as the in house guru.
I've started to put together a set of key points and advice for including on my intranet pages to give people a chance to try things out for themselves before approaching me.
So far, I've got:
To effectively troubleshoot slow running queries and database access code, you need to understand the data structure that the database uses and the tables/views that retrieve the data within the query.
The exact steps that you need to take will be specific to your application, but below are a few common problems I've seen that can be fairly easily resolved. The General points apply to any database engine.
1. There are nearly always multiple ways to write a query (eg using joins vs correlated subqueries). Try to write it in such a way that it can be covered by indexes rather than needing to go to the table directly. This is sometimes referred to as its sargability. (Sargeable is a term used by database professionals that refers to the Search Arguments of any given query).
2. Ensure that every table has a primary key and, if your database engine supports it, referential integrity between related tables. This will also help maintain data integrity within your application.
3. If you wrote the application (rather than written by an outsider and brought in), ensure the schema is normalised.
4. Only retrieve the data that you need to for a particular query. The more specific you can be with your criteria, the better the query optimiser in the database engine will be able to optimise. Be as direct as possible, specifying where fields are not equal to a particular value is almost certain to slow it down.
5. Avoid using "Select *" in queries. Instead, list the exact field names.
6. Minimise trips between the database server and the client. If you can retrieve everything you need in one retrieve then do it.
7. Don't over index. Whilst they are beneficial for searching and sorting, they slow down insert, update and delete operations. Producing the right balance of indexes to keep it running quickly without hurting performance is a matter of trial and error. Generally though, any fields that get used for searching or sorting will see some sort of benefit in being indexed.
8. Never assume that your data will be retrieved in a specific order, unless you include an ORDER BY clause in your query. Remember that SQL results are treated as a set, and the order of results is not important unless you force the database server to sort them.
Can anybody think of anything else worth including?
The people who generally ask me are partially database literate, ie they know some things about it (data comes from tables) but not as much as I do.
If anybody would like me to post the SQL Server and Access/Jet specific sections (plus another one for VB/VBA code in a different forum) I'm quite happy to.
John
I've started to put together a set of key points and advice for including on my intranet pages to give people a chance to try things out for themselves before approaching me.
So far, I've got:
To effectively troubleshoot slow running queries and database access code, you need to understand the data structure that the database uses and the tables/views that retrieve the data within the query.
The exact steps that you need to take will be specific to your application, but below are a few common problems I've seen that can be fairly easily resolved. The General points apply to any database engine.
1. There are nearly always multiple ways to write a query (eg using joins vs correlated subqueries). Try to write it in such a way that it can be covered by indexes rather than needing to go to the table directly. This is sometimes referred to as its sargability. (Sargeable is a term used by database professionals that refers to the Search Arguments of any given query).
2. Ensure that every table has a primary key and, if your database engine supports it, referential integrity between related tables. This will also help maintain data integrity within your application.
3. If you wrote the application (rather than written by an outsider and brought in), ensure the schema is normalised.
4. Only retrieve the data that you need to for a particular query. The more specific you can be with your criteria, the better the query optimiser in the database engine will be able to optimise. Be as direct as possible, specifying where fields are not equal to a particular value is almost certain to slow it down.
5. Avoid using "Select *" in queries. Instead, list the exact field names.
6. Minimise trips between the database server and the client. If you can retrieve everything you need in one retrieve then do it.
7. Don't over index. Whilst they are beneficial for searching and sorting, they slow down insert, update and delete operations. Producing the right balance of indexes to keep it running quickly without hurting performance is a matter of trial and error. Generally though, any fields that get used for searching or sorting will see some sort of benefit in being indexed.
8. Never assume that your data will be retrieved in a specific order, unless you include an ORDER BY clause in your query. Remember that SQL results are treated as a set, and the order of results is not important unless you force the database server to sort them.
Can anybody think of anything else worth including?
The people who generally ask me are partially database literate, ie they know some things about it (data comes from tables) but not as much as I do.
If anybody would like me to post the SQL Server and Access/Jet specific sections (plus another one for VB/VBA code in a different forum) I'm quite happy to.
John