Avatar of newknew
 asked on

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
DatabasesMicrosoft AccessASPWindows OSSQL

Avatar of undefined
Last Comment
John Tsioumpris

8/22/2022 - Mon
John Tsioumpris

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

Are you running the query as Pass-Thru?
Dale Fye

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?

Anders Ebro (Microsoft MVP)

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Pavel Celba

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...

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 Tsioumpris

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

Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.