We help IT Professionals succeed at work.

Optimize SQL Query: SQL vs. Access

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

Software & Systems Engineer
Distinguished Expert 2019
Commented:

If you are using the SQL as Linked table then its natural to be slower...have you run your query via SSMS to give some kind of indication if your SQL is slower.

Take a look at my post for my utility for benchmarking Cloud BE from Access

Top Expert 2014

Commented:
Are you running the query as Pass-Thru?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:

This query doesn't do anything special, other than return a lot of fields.  Do you really need all of those fields in the result set?

The query looks simple enough. If your getting poor performance, my guess is its because you have not done proper indexing.

Its not perfect, but a good starting point is to index any column used in a foreign key. Other then that, fire up your query in SSMS, and hit Ctrl-L and the query plan will be shown along with any suggestions on it thinks might improve the query.
SQL Server and Azure optimization level depends on existing indexes at first. Then the optimizer uses statistics to decide about the query plan.
So if you provide list of indexes on your tables and also the query plan from your query then we may provide some optimizations hints.

Basically if all columns used in joins are indexed then we don't have many more options for optimization...

Author

Commented:
No linked tables.  The suggestions that the query is not all that complex and the suggestion to run the query directly in SSMS (much faster than the webpage output) led me to look at other sources of the slowness.  Since this is a port from dedicated server to Azure I was only looking at SQL being the culprit.  SSMS was returning the result set within 3-5 seconds.   However, the webpage (Classic ASP) was taking 35+ seconds.  Looking more closely at the webpage, it had additional code that took the SQL result recordset and looped through it executing further SQL for EACH record of the recordset.

Why? Because each "load" record has an unspecified number of associated records in a "loads_commissions" table for all the sales people participating in that "load".  These commission records were looped through to create a delimited string value to represent all persons to be paid for that load.  This string value is used in an unrelated accounting software.
John TsioumprisSoftware & Systems Engineer
Distinguished Expert 2019

Commented:

Since the culprit is string concatenation for unknown records you can use the Power of SQL to do the dirty work

There many ways to accomplish that : https://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/

 And also Concat_ws ... unfortunately I never had a case that needed such kind of output..but with some googling or question here you will get a solution that will get you lightning fast