CODEWITHSUNDEEP
sqlsql-server
brianc
brianc

Reputation: 475

Is there a better, more optimized way to perform this SQL query?

I have a SQL query where we are selecting a list of products but also need to query an orders table to find how many have been sold. Is there a more efficient way to retrieve the numberSold sum without a subQuery? 

SELECT tProducts.ID as ID,
tProducts.Name,
tProducts.Description,
Highlights=Replace(Replace(tProducts.Highlights, '##productdeliverydate##', convert(varchar, @ProjectReleaseDt, 101)),'##productltdedition##', @productltdedition),
tProducts.isLocked as isLocked,
tProducts.isVisible as isVisible,
tProducts.ImageID as ImageID,
tProducts.ShippingYN as ShippingYN,
tProducts.LastDateUpdate as LastDateUpdate,
tProducts.BaseUnitPrice as Price,
    FileExtension = case  tProjectImages.FileExtention
    WHEN tProjectImages.FileExtention then tProjectImages.FileExtention
    ELSE '.none'
    END,
    @ImagePath as ImagePath,
    @ImageServerPath  as ExternalServerPath,
    @ThumbExt as ThumbnailExtension,    
    tProducts.SalesContainerTypeID,
    tProducts.ListOrder,
    tProducts.isParticipantListed,
    tProducts.LimitQuantity,
    tPRoducts.isFeature,
    numbersold=(SELECT sum(quantity) 
                  from tOrderDetails 
                  JOIN tOrders 
                    on tORders.OrderID=tORderDetails.ORderID 
                 where productID=tProducts.ID 
                   and tOrders.isTestOrder=0),
FROM tProducts
LEFT JOIN tProjectImages ON tProducts.ImageID = tProjectImages.ID
WHERE tProducts.ProjectID = @projectID  
and tProducts.isVisible=1 
and tProducts.SalesContainerTypeID = 6 
and tProducts.langID=@langID
ORDER BY tProducts.BaseUnitPrice ASC

Upvotes: 2

Views: 58

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

If you are having performance problems, then perhaps indexes would help.

The best indexes for the subquery are: tOrderDetails(productid, orderid, quantity) and tOrder(orderid, isTestOrder).

The best indexes for the outer query are: tProducts(ProjectId, isVisibleId, SalesContainerTypeID, langID, BaseUnitPrice, ImageID) and tProjectImages(Id).

Upvotes: 2

sgeddes
sgeddes

Reputation: 62831

I'm not sure if it would be more efficient (you'd want to check the execution plan). But here's an alternative version with an outer join to a subquery:

SELECT ...
    numbersold=t.qty,
FROM tProducts
    LEFT JOIN tProjectImages ON tProducts.ImageID = tProjectImages.ID
    LEFT JOIN (
        SELECT sum(quantity) qty,  productID
        FROM tOrderDetails 
            JOIN tOrders on tORders.OrderID =tORderDetails.ORderID 
        GROUP BY productID
    ) t ON t.productID=tProducts.ID and tOrders.isTestOrder=0
WHERE tProducts.ProjectID = @projectID and 
     tProducts.isVisible=1 and 
     tProducts.SalesContainerTypeID = 6 and 
     tProducts.langID=@langID
ORDER BY tProducts.BaseUnitPrice ASC

Upvotes: 1

Related Questions