Database Performance

Gaining acceptable performance with client applications against a remote backend involves somewhat of a paradigm shift compared to how we’re used to developing desktop applications in local client/server environments. This latest post in the Azure Series covers the techniques and practices required to do so.

At 44 printed pages, this document’s a bit too large for a blog post, so you can find the PDF download below.

The Database Performance article is written for desktop developers who are new to working with remote backends and need some guidance on techniques for making the application perform well: a formidable task for the uninitiated.

The PDF can be downloaded here:

Below is a generalized table of contents that should help describe some of what you will find within the document.

1. Foreword: why this was written, common concerns, and how we’ll attempt to address the problem.
2. A Context and a Goal: This describes an example company that we’ll use throughout the article – a service company with some fairly demanding requirements.  We’ll also define the ultimate goal that we’re attempting to achieve in terms of performance.
3. Getting Grounded: This discusses the basis of concerns when working with remote databases.  Namely the ODBC driver and how it effects performance.  This section also gives us some insight as to how we can recognize certain types of data and handle them accordingly, as there’s different methods that may work better than others depending on what “type” of data we’re working with.
4. List/Static Data: how to handle data that’s more or less “just there” and isn’t expected to change often.
5. Primary Data: core recordsets, form binding, basic CRUD operations on non-complex data.
6. Handling Complex Queries: the real fun… how to efficiently work with highly complex datasets for more sophisticated processes and views.  This section starts by a detailed look at the example company’s schema, defines an example query, discusses passthrough queries in some depth, how to manage updates to complex data and finally some other design considerations for working with “heavy” datasets.
7. Tips, Tricks and Other Considerations: Here, topics on threading, timers and events, async processing, service queues, remote polling, denormalization, XML/JSON and stored procedure/bind parameters should give us some food for thought.

Feedback is appreciated!

Cheers,
-jack