We help IT Professionals succeed at work.
Get Started

Optimize SQL Query: SQL vs. Access

165 Views
Last Modified: 2020-01-26
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
Comment
Watch Question
IT Supervisor
CERTIFIED EXPERT
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE