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

[Ryan W]

Gustav,
  The kicker there is my $10/mo "Shared Windows Hosting", with a Shared SQL
Server with MANY MANY users on it is faster. I realize Azure is a "shared"
nature too.. but with SOME resources that are for your account only (like a
mini virtual machine, of sorts)... where on the shared hosting that's not
the case.. anyone can cause the server you are on to bottleneck and cause
grief.

  I would have thought that the S0 or S1 Azure plan would exceed the shared
windows hosting plan I have over at HostGator...    I was only testing to
see if my 1-2 minute "insert into" time with HostGator was atypical or
not.  Turns out I'm not doing as bad as I could be, I suppose.



On Thu, Oct 19, 2017 at 3:34 PM, Gustav Brock <gustav at cactus.dk> wrote:

> 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.
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>