[AccessD] Pushing data to Azure SQL database VERY VERY slow

[Gustav Brock]

Hi Ryan

We have some small Azure SQL instances running for testing purposes, and they are very slow. Straight reading is relatively fast, but anything else is painfully slow. Except for some simple views, we don't run anything else on the Azure SQL engine.

The positive aspect of this is, that it is a very effective method to pinpoint bottlenecks. If you can run a form off the Azure SQL at good speed, it will run blazingly fast off a local SQL Server. And what you think you can away with using a fast local server, will kick you hard when moved to Azure SQL.

I guess that using a larger instance would prove much better results, but the costs rise quite quickly. I miss a small, fast, and free instance.

/gustav

________________________________________
Fra: AccessD <accessd-bounces at databaseadvisors.com> på vegne af Ryan W <wrwehler at gmail.com>
Sendt: 19. oktober 2017 20:39:34
Til: Access Developers discussion and problem solving
Emne: [AccessD] Pushing data to Azure SQL database VERY VERY slow

Back story:

We have an Access FE with a SQL Server back end.  We push up a relatively
small dataset (6000 rows total) up to our website for some updated
'statistics' on client work.

Right now my SQL server is on a shared windows host and pushing those 6000
rows takes anywhere from 1m30s to 2 minutes, usually!

I'm also testing a snapshot of our SQL database in Azure (S1 plan) for some
devs we have in Denmark to query off, it doesn't have to be updated so it
doesn't do any log shipping or anything (not that azure supports it, from
what I can tell).


Anyway those same tables on my shared Windows hosting plan were created in
my azure instance and those very same queries take over 6 minutes!

First off, it seems highly suspect that azure would be 3 times slower?
Secondly aside from WAN latency and such why would it even be taking 2
minutes to insert 6,000 rows across the WAN?  Is there a way I can speed
that up in either the shared server or the Azure server?


When I check the Azure DB statistics and "DTU usage" it's barely a blip on
the radar, so I'm not pegging my DTU allocation by any means.


When I query that information back out of Azure I pull down those 6,000
rows in less than one second.. so it doesn't seem like my WAN is
necessarily the culprit.