I have been accepted to present a paper at this year's EMEA PUG Challenge on query tuning techniques. I will publish my guide here after the event, but for now, here is my abstract:
How heroes navigate the query minefield
We’ve all been there. We create a clever piece of Progress code only for the users to hate it because the particular combination of options they want to choose results in a query that takes ages to return a few results.
Query writing can be a minefield.
- Programmers design for what they anticipate;
- Users don’t necessarily correspond to that design;
- Users don’t have clear understandings of what they need;
- Trend is toward richer and more flexible screens;
- Table sizes are growing;
- Legacy code was written by programmers who didn’t think things through.
The combination of these things can lead to frustrated users who create what they think is a simple request which takes a long time to run, and can even impact other users too.
This session will demonstrate the use of tools such as ProTop and client logging to examine a query’s efficiency. In particular we will look at how to see how many records a query reads vs how many it returns to the user, and what indices it is using for the job.
We will then look at some tips and tricks on query design to help you get the most out of your application with a little bit of forethought, and hopefully without the necessity to add new indices (assuming sensible ones are already in existence).
This is not a hyper-technical session or a complete solution to a problem. It is a set of practises that any developer can put into action on a daily basis to produce better queries and become the heroes of their customers.
Now that the PUG Challenge in Copenhagen is over I can make my slides and my talk available. Feel free to take a look, and give me a shout if you need any further help or have questions or comments.
Thanks to everyone who came to listen to the session.