I have an ASP.NET Web Forms app deployed to an Azure app service that utilizes an Azure SQL database. I upgraded from an S1 app service plan to S2 and seemed to get slightly faster page request times, but most pages are still painfully slow.
My website is [www.tenpindoctors.com], and the page most obviously slow is the "Store" page, at [www.tenpindoctors.com/Store.aspx]. That page uses an SQL query to display store merchandise on load. Sometimes it takes as long as three minutes to load. Testing the same query on my SQL Server connection, it usually takes no more than 20 seconds to load as opposed to. The average HTTP request is probably 75 seconds, so given the same amount of time for the query, the back-end coding would usually amount to 55 seconds of latency. For my database, I upgraded from an S1 pricing tier to S3, which went from 20 to 100 DTUs, along with 250 GB of maximum data size under geo-redundant backup storage. I feel like the database queries can be optimized a little, given the fact that the Store page only loads 17 records for store items. There are image BLOBs stored, and when I neglect that varbinary column for storing images, the query loads immediately. What are my options for improving my Azure SQL database plan without upping the price too much? Also, would standard database practices like using indexes and/or stored procedures make much difference for the performance of a query that fetched image BLOBs?
As for the performance of the HTTP requests, 55 seconds of delay is definitely a problem. The technique I use is an AJAX call to an ASP.NET Web method, which includes a return of an image's data URI in the JSON of the AJAX response. I'm not sure if the lengthy data URIs are causing significant delays, since I had to increase the maximum length of the returned JSON string. I'm open to ideas on how to return the image data for better performance.
I'm also not sure if upgrading my app service plan from a Standard plan to Premium would improve performance much. Even if it did, the price would increase by a substantial $300 per month. In this question, I used the Store page of the website as an example. However, it is certainly not the only page with lengthy delays. The only pages with lengthy delays are dynamic and data-driven. From what I've seen, though, the hindrances are mostly a result of the means of fetching the data into views, not just the queries themselves. Feel free to browse the site; I'm open to any recommendations.