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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Sql Azure - Database Performance, Any Version    
post Sep 15 2015, 07:47 AM

UtterAccess Editor
Posts: 9,870
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: 111
Go to the top of the page
post Sep 15 2015, 08:22 AM

Posts: 1,426
Joined: 5-February 06
From: Ohio, USA

I just skimmed over it for now, will get into heavy reading later, maybe. I don't use Azure, most of our data is Access or ODBC, though, so I found some useful things, and some validation on what I do already.

For instance, I have a 'common tables' database that is blank, and when the database starts, it is copied to the user's local drive and populated with data that rarely changes, to make things faster. Initial loading of the database and the tables takes a bit, but after that...

As for readability and skimming, I like the style. Just one minor nit-pick, and that is the diagrams of relationships on page 21, I think it is... the lines don't match up field to field, like they would in an Access relationship window. (I do like the 'key' image instead of '1' to 'infinity'.)

Enjoyable skim. Thank you, Jack!
Go to the top of the page
post Sep 15 2015, 08:30 AM

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


I usually include the "sideload" database (as I tend to call it - a term I picked up from Brent I believe) as part of the application package itself, and the startup procedures dump the cache tables and reload them. It's very effective - as explained within, one of the most effective (and easy) things to do.

Most of the performance implications tend to lie in how to treat the ODBC driver correctly, and the concept is held to throughout the writeup. It's written as if we were using Access to Azure, but the underlying concepts tend to apply to any client/remote server setup (as well as LAN setups as well, for that matter).

For the ERD snippets, that's how SSMS gave it to me! shrug.gif

Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    17th February 2018 - 04:03 PM