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

[The Smiley Coder]

Curious Ryan, what is the ping time for both servers? I've found that with
a bit of work, and some design principles, I can get my local access app to
run against SQL azure so fast you can't tell it is not local.

Best regards
Anders Ebro // TheSmileyCoder <http://www.thesmileycoder.com>
Access MVP 2014-2018
<http://mvp.microsoft.com/en-us/mvp/Anders%20Ebro-5000469>

On Thu, Oct 19, 2017 at 10:38 PM, Ryan W <wrwehler at gmail.com> wrote:

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