Results 1 to 6 of 6
  1. #1
    theviking is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Northern New Jersey
    Posts
    15

    Migrating back end to Azure effectively

    We currently have a customer application processing work orders which operates efficiently against a SQL back end. The application uses a number of ad-hoc search screens to allow users to find/filter data easily. However when the back end was migrated to Azure we are seeing very inconsistent performance results retrieving data. It's not a bandwidth issue from our location and there isn't any obvious issue with the execution plan. So if anyone has further suggestions for tuning queries specifically against Azure please let me know. Thanks!
    Click image for larger version. 

Name:	WOMScreen1.jpg 
Views:	15 
Size:	136.4 KB 
ID:	37611
    The SQL query corresponding to this particular search is as follows:
    SELECT tblWorkOrder.PrimaryID, tblWorkOrder.CustID, tblWorkOrder.RequestedBy, tblWorkOrder.WOStatus, WOStatus.ValueName AS Status, tblWorkOrder.Phone, tblWorkOrder.Cell, tblWorkOrder.FirstContactDate, tblWorkOrder.Renter, tblWorkOrder.InfoBy, tblWorkOrder.TypeID, WOType.ValueName AS WOType, tblWorkOrder.Area, tblWorkOrder.ProjectDescription, tblWorkOrder.ProjectNotes, tblWorkOrder.GeneralLocation, tblWorkOrder.[Acct#], tblWorkOrder.[Page#], tblWorkOrder.[Section1/4], WOSection.ValueName AS [Section], tblWorkOrder.[Section#], tblWorkOrder.[Pole#], tblWorkOrder.EstDescription, tblWorkOrder.EstAmount, tblWorkOrder.WhatsNext, WOWhatsNext.ValueName AS NextStep, tblWorkOrder.HpOption, tblWorkOrder.HpSize, tblWorkOrder.ConfirmedDate, tblWorkOrder.ApplicationNeeded, tblWorkOrder.ApplicationSent, tblWorkOrder.ApplicationDate, tblWorkOrder.ROWNeeded, tblWorkOrder.ROWSent, tblWorkOrder.ROWDate, tblWorkOrder.PayNeeded, tblWorkOrder.PaySent, tblWorkOrder.PayDate, tblWorkOrder.PayAmt, tblWorkOrder.RateClass, tblWorkOrder.StakingDone, tblWorkOrder.CountyAppNeeded, tblWorkOrder.CoAppSent, tblWorkOrder.CoAppDate, tblWorkOrder.StAppNeeded, tblWorkOrder.StAppSent, tblWorkOrder.StAppDate, tblWorkOrder.[WO#], tblWorkOrder.WODesc, tblWorkOrder.[Cust#], tblWorkOrder.Legal, tblWorkOrder.ServLoc, tblWorkOrder.ReleaseDate, tblWorkOrder.StoresDate, tblWorkOrder.StoresDateDone, tblWorkOrder.OneCallDate, tblWorkOrder.[OneCall#], tblWorkOrder.WhoOptID, WOWhoOpt.ValueName AS WhoGetsIt, tblWorkOrder.WorkDate, tblWorkOrder.BuiltDate, tblWorkOrder.ReturnToEngineering, tblWorkOrder.MappingDate, tblWorkOrder.CprDone, tblCustomer.LastName, tblCustomer.FirstName
    FROM (((((tblWorkOrder LEFT JOIN (SELECT ValueID, ValueName FROM tblDomainVal WHERE (TypeName = 'WO_TYPE')) AS WOType ON tblWorkOrder.TypeID = WOType.ValueID) LEFT JOIN (SELECT ValueID, ValueName FROM tblDomainVal WHERE (TypeName = 'WHATS_NEXT')) AS WOWhatsNext ON tblWorkOrder.WhatsNext = WOWhatsNext.ValueID) LEFT JOIN (SELECT ValueID, ValueName FROM tblDomainVal WHERE (TypeName = 'SECTION_QTR')) AS WOSection ON tblWorkOrder.[Section1/4] = WOSection.ValueID) LEFT JOIN (SELECT ValueID, ValueName FROM tblDomainVal WHERE (TypeName = 'WO_STATUS')) AS WOStatus ON tblWorkOrder.WOStatus = WOStatus.ValueID) LEFT JOIN tblCustomer ON tblWorkOrder.CustID = tblCustomer.CustID) LEFT JOIN (SELECT ValueID, ValueName FROM tblDomainVal WHERE (TypeName = 'WHO_OPT')) AS WOWhoOpt ON tblWorkOrder.WhoOptID = WOWhoOpt.ValueID
    WHERE (((tblWorkOrder.PrimaryID) Is Not Null))


    and tblWorkOrder.[FirstContactDate]>='2/1/2019' and tblWorkOrder.[WOStatus]=1
    ORDER BY tblWorkOrder.PrimaryID DESC;

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I don't see anything obvious, but double check all fields used in joins, criteria/filters and sorts/orderby are indexed - although I don't see the benefit ordering by ID or the benefit of using subqueries e.g.

    LEFT JOIN (SELECT ValueID, ValueName FROM tblDomainVal WHERE (TypeName = 'WO_STATUS')) AS WOStatus ON tblWorkOrder.WOStatus = WOStatus.ValueID

    I would have thought

    LEFT JOIN tblDomainVal AS WOStatus ON tblWorkOrder.WOStatus = WOStatus.ValueID

    would work just as well unless ValueID is not unique?

    Other options would be to use a passthrough query or a stored procedure with parameters

    With regards azure - depends on the package you are using - if sharing the server with other azure users, they may be making high demands resulting in a slowdown in your performance - not sure what options you have for getting MS to investigate and perhaps tune the server.

    And you might need to rebuild indexes on a regular basis - similar to compact and repair. Just because it is an 'enterprise' rdbms doesn't mean you don't need to maintain it

  3. #3
    theviking is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Northern New Jersey
    Posts
    15
    Thanks for your feedback Ajax. The tblDomainVal table used to store (relatively) static lookup values for combo box entries. This allows us to avoid creating lots of lookup tables but avoid having to hard code value list entries in the form(s). However as such the Value_ID is only unique within a given ValueName which is then filtered by the TypeName.

    We typically avoid combo box controls on the continuous forms for performance reason, but as a result need to do a lookup on the display values for various field entries. The OrderBy on the ID is only used because it typically relates to the chronological entry of data, but this could be omitted without much push-back. We do have indexes in place on all the relevant joins/sorts but I will double check, along with the rebuild schedule.

    Example of the tblDomainVal table entries if that helps explain its organization and layout better.

    Click image for larger version. 

Name:	tblDomainVal.jpg 
Views:	14 
Size:	159.7 KB 
ID:	37616

  4. #4
    theviking is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Northern New Jersey
    Posts
    15
    Migrating the Access queries to SP's was discussed as a possible next step. I was trying to avoid this option if possible as it increases the level of effort & cost for up-sizing the application. But we will likely do a POC with an SP running on the Azure DB and see if that stabilizes the performance. Which I suspect it will.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    using the sub query may have an impact on performance so personally I would change your valueID in tblWorkOrder to store the ID value instead. If not ensure both typename and valueID are indexed, although I guess it is a small table so you may not see any noticeable benefit. You would have to try it and see.

  6. #6
    theviking is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Location
    Northern New Jersey
    Posts
    15
    Well we created an SP and that worked extremely well. All the searches came back with basically the same speed we would expect of native queries/tables. The only downside is it's a more substantial rewrite of existing code. We need the new SP (obviously). Then the code was rewritten to tie the form to a recordset instead of a native Access query so we could retrieve data from the SP. In order to make a datasheet view available to the user we also wrote a function to generate a pass-through query. Which basically just calls the original SP with the same parameters but creates the query instead of a recordset. And finally all of our OrderBy statements needed to be rewritten to call a recordset sort function instead.

    So lessons learned we will probably just use recordsets behind our search forms now to simplify migration of local databases. I'm hoping the Azure performance remains stable as it opens up a lot of options.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 02-24-2017, 06:25 PM
  2. General discussion for MySQL, PHP, Azure
    By MrRuz in forum General Chat
    Replies: 2
    Last Post: 10-29-2014, 02:06 AM
  3. Unable to use WithEvents effectively like we use in Excel
    By vikasbhandari2 in forum Programming
    Replies: 1
    Last Post: 02-19-2013, 09:22 PM
  4. Access 2010 and SQL Azure
    By drexasaurus in forum SQL Server
    Replies: 2
    Last Post: 09-20-2012, 08:58 AM
  5. Access 2010 to SQL Azure migration
    By Aurelius7 in forum SQL Server
    Replies: 6
    Last Post: 06-18-2012, 10:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums