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!
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;