UtterAccess.com
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
> Awa Database Tuning, Access 2013 Web App    
 
   
ryan996
post Sep 26 2017, 04:37 AM
Post#1



Posts: 72
Joined: 16-June 13



I'm trying to tune my AWA to improve query and general db performance as the amount of data in my app has begun to cause performance issues. The Access client is useless for performance tuning, so it's best to resort to SSMS. Unfortunately, many of the tools available to SQL Azure developers in SSMS are denied for AWA developers due to user permissions. We can't even see the query execution plan, find missing indexes, fix fragmented indexes, etc, etc. The only thing that works is to use the 'display client statistics' and use trial and error in rewriting queries/formulating indexes.

1) What are the tricks you use to optimize your queries?
2) Is there any chance of convincing the Access development team to expand the permission levels for developers to use query optimization tools in SSMS?
3) Does anyone know what the technical resource level capacity is set in Azure for AWA? I'm guessing it's the basic level, but does it scale depending on the number of users on O365? I've found a huge variance in performance from day to day, which hasn't been easy to explain.

I plan to migrate my AWA app to a SP Foundation/SQL server platform before it's unceremoniously removed from O365. Are there better tools available for database tuning with an on-premises solution?

There's a great article on Azure DB tuning here:

https://docs.microsoft.com/en-us/azure/SQL-...e#batch-queries
This post has been edited by ryan996: Sep 26 2017, 04:49 AM
Go to the top of the page
 
RobKoelmans
post Sep 26 2017, 05:09 AM
Post#2



Posts: 427
Joined: 25-November 14
From: Groningen, Netherlands


Hi Ryan,
You can't use foundation, Access Web Services is not available in there and you wouldn't be compliant to licensing rules if you could get it running somehow. You could just as well go not compliant with SharePoint Enterprise then. But I don't think you can install Access Web App services in Foundation anyway. Keep in mind that using SQL-Server Express as AWA-database (to repel SQL-licensing) is useless because of that: SharePoint itself needs SQL-Standard or Enterprise anyway. And SQL-server is pretty expensive per user until you have enough to pay by core.

Within your own platform, there are no limitations on SSMS as far as we know. We didn't encounter any. Big issue is that the database has to stay under AWA-management (as long as you go for that). Bright side is that you magnificently can optimize within AWA. A popup with 10 items out of half a million rows, is responding instantly as long as indexing is properly active.

What did you already apply? You're aware of combined row indexing in table management? Another technique we extensively use is indexed calculated fields (combined lookup id's enlarged to 10 characters each with preceding zeros in concat expressions. This way, calculation of the expression is being done on record entry, not on the query performing. This also makes indexed querying possible in scenarios that can't be indexed otherwise.

We have a list of other tricks but those are on hierarchic structures (self referencing tables that support fully qualified entities that come from XML or JSON-messages) but those are actually appliances of the structures described above.
Hope this helps,
Rob
This post has been edited by RobKoelmans: Sep 26 2017, 05:21 AM
Go to the top of the page
 
GroverParkGeorge
post Sep 26 2017, 06:46 AM
Post#3


UA Admin
Posts: 30,723
Joined: 20-June 02
From: Newcastle, WA


Rob has outlined the facts of the situation nicely.

With regard to the future of AWAs, don't forget that your existing Azure based databases WILL be removed from Microsoft's servers. To me, pouring resources into an existing AWA is not the wisest choice anyway, unless you are able to migrate your AWAs to a service like that Rob's company offers. So far as I am aware, his organization is unique in doing so. However, you'll need to communicate with him privately and directly to see what options you have in that direction.

With regard to whether or not someone could convince the Azure team -- not the Access team -- to offer you MORE services in the face of total deletion of your AWA's databases from their servers six months from now -- I don't see that happening. But you might get a definitive answer by contacting support on the Azure team. The Access team doesn't control that kind of thing. In my conversations with them, it's clear the Access team is totally engaged in future enhancements to the core Access product and have no appetite for picking over AWA issues. I suspect that some of them, on a personal level, share our disappointment, but they are there to manage the future of the Access product, not to drag out the end game here.

I am pretty sure that resources for existing AWAs on SQL Azure servers is set to the lowest possible levels; the most recent issue we saw was the sudden throttling of queries to no more than 50 records. That was relaxed after some complaints, but the trend is clear, I think.

In short, if you want to continue using the AWA tools, your best option, IMO, is someone like Rob, whose organization has the skillset and resources needed to carry them forward. I'll leave details of that to him and you, though.

I personally just finished moving my primary personal AWA to a traditional Access/SQL Server (Azure, actually) architecture and am enjoying the benefits of the enhanced functionality I can achieve in the standard Access desktop development environment, along with the ability to write and optimize Stored Procedures and views directly in SSMS.

One last word, I am leading an online Access User Group devoted to AWAs. Our meetings recently have been devoted to AWA alternatives. In October, we'll hear from an ASP.net developer on some things you can accomplish with MVC framework to create a custom web app. We've seen other alternatives in previous months. I've asked Rob's team to present and that's scheduled for November of this year.
This post has been edited by GroverParkGeorge: Sep 26 2017, 08:36 AM

--------------------
Go to the top of the page
 
ryan996
post Sep 27 2017, 02:09 AM
Post#4



Posts: 72
Joined: 16-June 13



Thanks Rob and George.

Yes, I do use both combined row indexing as well as calculated field indexing in my tables. I'm getting data from on-premise ERP systems (Oracle) and entering that data into AWA through SSMS. One table requires deleting all the records and entering new records twice a week (about 20K records each time) since some of these records have updated values. As there aren't any tools to prevent index fragmentation in tables with frequent create, update and delete operations, deleting and recreating the indexes is a workaround to eliminate index fragmentation and speed up queries (if this is an issue).

The fact that MS is reducing all the performance levels in Azure explains a lot about what's been going on with my app. I wish they wouldn't mess with the resource settings as this is causing stored procedures etc to fail randomly.

We are definitely looking to move the app into SharePoint Enterprise through a 3rd party service provider. I think that it's a good suggestion not to spend any significant development time on the app until this has been accomplished. It sounds like there will be more freedom and flexibility in SQL Server anyway. Has anyone created a step-by-step guide on moving an AWA from O365 to a SharePoint Enterprise? My understanding is that AWAs weren't designed to be moved from SQL Azure to SQL Server, but I also know that a number of people like Rob have been successful in the transition.



Go to the top of the page
 
RobKoelmans
post Sep 27 2017, 06:04 AM
Post#5



Posts: 427
Joined: 25-November 14
From: Groningen, Netherlands


We succeeded in that but it wasn't trivial because Microsoft never did configure AWA-service in SP2016. It took us approx 2,5 months with their help. I can't exactly tell how many man hours it actually took in that period, but my estimation is two weeks of actual time.

Important is that you use exactly the same version of SQL-Server that Azure is using for AWA (SP2014SP1). Otherwise you won't be able to import your .app files after you set up everything.

We can replicate hour system and have a second platform running under our spla. If it's sharing hardware, some licensing can be under cores we already pay for. If you run your own full system, spla licensing wil in the order of 2000 euro's a month, if I recall well. Our point of view is that we don't have to pay per client on top of that because of the external connector being included since SP2013 and besides because we're using form based authenticated users instead of domain users. Microsoft would also be competing falsely against their own partners with O365 if otherwise. We offered another customer to set up a complete replica in four man days (at 720 euro per day including documentation etc.


But - reading about your situation - I don't understand why you should use AWA. AWA is strong at the database end but you're exporting weekly from Oracle. Why not use some client that has connection to Oracle. PowerApps and Flow are much more suitable for that and provide modern connectivity to typical mobile entities like gpr, accelaration, camera, recorder, graphing etc.
What exactly (still) makes you choose for AWA in this scenario?
Rob
Go to the top of the page
 
RobKoelmans
post Sep 29 2017, 05:20 AM
Post#6



Posts: 427
Joined: 25-November 14
From: Groningen, Netherlands


Ryan,
As George mentioned, we run AWA's multi-tenantly. You're free to experiment on our platform if you want to.
If we don't charge you anything, we don't have to pay for you to microsoft either for three months max under SPLA-regulations
Rob.
Go to the top of the page
 
ryan996
post Today, 04:40 AM
Post#7



Posts: 72
Joined: 16-June 13



Hi Rob,

Yes, I would be interested to discussing a hosting agreement with your company for our AWA. We like AWA for the reasons you mentioned, the primary one being that the SQL Server back-end is robust and the security of SharePoint. AWA is a nice way to sandbox relevant organization data for our business users and quickly build automated business processes, reports etc through the other Power(x) stack of tools without involving the IT department. AWA allows power users who don't have a deep development background to design databases and build applications that tie together disparate data that may not be stored in ERP systems (Probably the reason so many organisations still rely heavily on Excel).

When you look at the PowerApps website, the first line says "Connect to your Systems and create new data." AWA would be the perfect database to connect to since most IT departments in organizations aren't too pleased giving out access to their databases. It is much better to just sandbox the relevant data using AWA and perhaps automate the data transfer between the two systems. This is why I felt that PowerApps would complement not replace AWA.

I realise that the front-end of AWA can be unstable and is probably obsolete since it isn't mobile friendly, but our users are mainly interacting with the application on a desktop client. Lots of features can't be easily replicated or created in PowerApps such as downloading reports with thousands of lines into Excel.

Anyway, I will drop you a PM soon so we may begin a discussion on how to get started. BTW, I read somewhere that MS updated the SQL Server version used in Excel when the Sharepoint online sites were updated, but I could be wrong. Is there any way to find out the SQL Server version used in the metadata of the .app file?
Go to the top of the page
 
GroverParkGeorge
post Today, 06:19 AM
Post#8


UA Admin
Posts: 30,723
Joined: 20-June 02
From: Newcastle, WA


Rob, Ryan.

I am wondering if this scenario supports creation or modification of Stored Procedures or Functions inasmuch as the SQL Server instance is not subject to the same limitations as the Azure SQL database behind an O365-based AWA.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th October 2017 - 11:47 AM