X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Sql Azure - Database Performance, Any Version    
post Sep 15 2015, 07:47 AM

UtterAccess Editor
Posts: 9,938
Joined: 7-December 09
From: Staten Island, NY, USA

(with approval from Gord and the crew - thanks!)

We've been doing a fair bit of work with Access FEs connected to SQL Azure BEs for a while now (technically called "SQL Database"). From both the client and the developer end, one of the most often heard concerns is performance.

Acceptable performance against a remote backend can be obtained, but it takes a different approach than what many of us are used to in terms of desktop application development in a local client/server environment.

Attached is a document that explains much of how we tackle the problem. Below is a generalized ToC describing the contents:

Foreword: why this was written, common concerns, and how we’ll attempt to address the problem.
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.
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.
List/Static Data: how to handle data that’s more or less “just there” and isn’t expected to change often.
Primary Data: core recordsets, form binding, basic CRUD operations on non-complex data.
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.
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.

At 44 pages long, you might want to grab a coffee if you intend to read it...

I appreciate any feedback on the document. Thanks!

Attached File  05___Database_Performance__v1.0_.pdf ( 1.39MB )Number of downloads: 138
Go to the top of the page

Posts in this topic

Custom Search

RSSSearch   Top   Lo-Fi    18th December 2018 - 08:17 PM