I'm moving an application from a dedicated Windows Server / IIS to Azure with SQL Database and App Service. The existing application runs on MS Access "server" with Classic ASP pages and it is pretty speedy.
The following query on existing server for example returns all records in about 3 seconds. There are 36,720 records in the "loads" table.
Now that I've moved the DB to Azure SQL the records are returned in about 35 seconds to the Azure App. My current Azure config is Serverless Gen5, 2-8 vCores, 6GB-24GB memory. After increasing the compute power I noticed the results returned faster, so I suspected poor query design.
Question: Can the following query be optimized further? I expected much better performance on SQL Server vs. MS Access "Server"
SELECT dbo.loads.ID, dbo.loads.CustomerID, dbo.loads.CustomerContactID, dbo.loads.RequestDate, dbo.loads.Equipment, dbo.loads.Commodity, dbo.loads.Stops, dbo.loads.Miles, dbo.loads.CommentsPublic, dbo.loads.GrossQuoted,
dbo.loads.Weight, dbo.loads.Length, dbo.loads.Width, dbo.loads.Height, dbo.loads.CargoValue, dbo.loads.CommentsPrivate, dbo.loads.UserID, dbo.loads.Created, dbo.loads.forfeitload, dbo.loads.voidload, dbo.loads.shipperid,
dbo.loads.shippercontactid, dbo.loads.shippercomments, dbo.loads.consigneeid, dbo.loads.consigneecontactid, dbo.loads.consigneecomments, dbo.loads.usforwarderid, dbo.loads.usforwardercontactid,
dbo.loads.usforwardercomments, dbo.loads.billtoid, dbo.loads.billtocontactid, dbo.loads.billtocomments, dbo.loads.mxforwarderid, dbo.loads.mxforwardercontactid, dbo.loads.mxforwardercomments, dbo.loads.mxcarrierid,
dbo.loads.mxcarriercontactid, dbo.loads.mxcarriercomments, dbo.loads.mxcarriertrucknumber, dbo.loads.mxcarriertrailernumber, dbo.loads.shipperdate, dbo.loads.shipperdateeta, dbo.loads.shipperdateetaasof,
dbo.loads.shipperdateactual, dbo.loads.consigneedate, dbo.loads.consigneedateeta, dbo.loads.consigneedateetaasof, dbo.loads.consigneedateactual, dbo.loads.usforwarderdate, dbo.loads.usforwarderdateeta,
dbo.loads.usforwarderdateetaasof, dbo.loads.usforwarderdateactual, dbo.loads.mxforwarderdate, dbo.loads.mxforwarderdateeta, dbo.loads.mxforwarderdateetaasof, dbo.loads.mxforwarderdateactual, dbo.loads.billingcarrierid,
dbo.loads.billingcarrierloadnumber, dbo.loads.bookingcarrierid, dbo.loads.bookingcarriercontactid, dbo.loads.bookingcarriercomments, dbo.loads.bookingcarrierloadnumber, dbo.loads.haulingcarrierid,
dbo.loads.haulingcarriercontactid, dbo.loads.haulingcarriercomments, dbo.loads.haulingcarrierloadnumber, dbo.loads.haulingcarriertrucknumber, dbo.loads.haulingcarrierdriver, dbo.loads.haulingcarrierdrivercontact,
dbo.loads.haulingcarriertrailer1, dbo.loads.haulingcarrierdatedispatch, dbo.loads.haulingcarrierfuelcard, dbo.loads.haulingcarrierdrivercomments, dbo.loads.interchangelocationid, dbo.loads.interchangelocationdatein,
dbo.loads.interchangelocationdateineta, dbo.loads.interchangelocationdateinetaasof, dbo.loads.interchangelocationdateinactual, dbo.loads.interchangelocationdateout, dbo.loads.interchangelocationdateouteta,
dbo.loads.interchangelocationdateoutetaasof, dbo.loads.interchangelocationdateoutactual, dbo.loads.interchangelocationcomments, dbo.loads.loadpaid, dbo.loads.loadpaidcomments, dbo.loads.shipperreferencenumber,
dbo.loads.consigneereferencenumber, dbo.loads.interchangelocationcontactid, dbo.loads.bookingcarrierloadentrycomplete, dbo.loads.haulingcarriertrailer1preplan, dbo.loads.loadconfiguration, dbo.loads.statustims,
dbo.loads.statusavailableloads, dbo.loads.statusleadspreplan, dbo.loads.statusleadsfinal, dbo.loads.statuspaperworkyard, dbo.loads.xloadcomments, dbo.loads.haulingcarriertrailer2, dbo.loads.statusdispatchready,
dbo.loads.consigneedatefinal, dbo.loads.shipperdatefinal, dbo.loads.usforwarderdatefinal, dbo.loads.leadsloadid, dbo.loads.bookingcarrier_s1, dbo.loads.bookingcarrier_s2, dbo.loads.bookingcarrier_d1,
dbo.loads.bookingcarrier_d2, dbo.loads.BookingCarrierBookingCode, dbo.loads.BookingCarrierReferenceNumber, dbo.loads.billtoreleased, dbo.loads_totalcharges.totalcharges, dbo.loads_totalcharges.totalrevenue,
dbo.locations.Name AS customername, locations_1.Name AS shippername, locations_1.City AS shippercity, locations_1.State AS shipperstate, dbo.states.Country AS shippercountry,
dbo.states.country_mapcode AS shippercountrymapcode, CONCAT_WS(', ', dbo.locations.City, dbo.locations.State) AS shippercitystate, locations_2.Name AS consigneename, locations_2.City AS consigneecity,
locations_2.State AS consigneestate, dbo.states_1.Country AS consigneecountry, dbo.states_1.country_mapcode AS consigneecountrymapcode, CONCAT_WS(', ', locations_2.City, locations_2.State) AS consigneecitystate,
locations_3.Name AS haulingcarriername, dbo.locations4.Name AS billtoname, dbo.bookingcodes.BookingCodeName
FROM dbo.loads LEFT OUTER JOIN
dbo.locations ON dbo.loads.CustomerID = dbo.locations.ID LEFT OUTER JOIN
dbo.locations4 ON dbo.loads.billtoid = dbo.locations4.ID LEFT OUTER JOIN
dbo.locations3 AS locations_3 ON dbo.loads.haulingcarrierid = locations_3.ID LEFT OUTER JOIN
dbo.loads_totalcharges ON dbo.loads.ID = dbo.loads_totalcharges.LoadID LEFT OUTER JOIN
dbo.states_1 RIGHT OUTER JOIN
dbo.locations2 AS locations_2 ON dbo.states_1.Code = locations_2.State ON dbo.loads.consigneeid = locations_2.ID LEFT OUTER JOIN
dbo.states RIGHT OUTER JOIN
dbo.locations1 AS locations_1 ON dbo.states.Code = locations_1.State ON dbo.loads.shipperid = locations_1.ID LEFT OUTER JOIN
dbo.bookingcodes ON dbo.loads.BookingCarrierBookingCode = dbo.bookingcodes.BookingCodeID