November 8, 2022 at 9:05 am
Hello,
I have the below query working properly by but very slow, the sub-query is the cause of slow but I don't what is the best way to replace it.
SELECT TblStyle.StyleCode,TblColor.Ename + '('+TblColor.Code+')' Color,
ISNULL (TblQCDecisionStatus.Ename, 'Need Sample') Status,TblQcbrandManagerColorDecision.CreationDate,
TblQcbrandManagerColorDecision.Comment,
supplier.Ename Supplier,
SUM( TblQCPackingList.Quantity) TotalQuantity, --Sum of all sizes quantity of the same color
CAST(TblQCInspectionRequest.Iserial AS NVARCHAR(20)) QCReport
FROM TblQCInspectionRequest
LEFT OUTER JOIN TblQcbrandManagerColorDecision ON TblQcbrandManagerColorDecision.TblQCInspectionRequest = TblQCInspectionRequest.Iserial
LEFT OUTER JOIN TblQCDecisionStatus ON TblQCDecisionStatus.Iserial = TblQcbrandManagerColorDecision.TblQCDecisionStatus
LEFT OUTER JOIN TblQCNeedSample ON TblQCNeedSample.TblQCInspectionRequest = TblQCInspectionRequest.Iserial
INNER JOIN TblStyle ON tblstyle.Iserial = TblQCInspectionRequest.TblStyle
INNER JOIN TblColor ON TblColor.Iserial = TblQcbrandManagerColorDecision.TblColor
OR (TblColor.Iserial = TblQCNeedSample.TblColor
AND TblColor.Iserial NOT IN (SELECT tblcolor FROM TblQcbrandManagerColorDecision WHERE TblQcbrandManagerColorDecision.TblQCInspectionRequest = TblQCNeedSample.TblQCInspectionRequest))
INNER JOIN TblQCPackingList ON TblQCPackingList.TblQCInspectionRequest = TblQCInspectionRequest.Iserial
AND ((TblQCPackingList.TblQCRequestColors=TblQcbrandManagerColorDecision.TblColor AND TblQCPackingList.TblQCInspectionRequest = TblQcbrandManagerColorDecision.TblQCInspectionRequest)
OR (TblQCPackingList.TblQCRequestColors = TblQCNeedSample.TblColor AND TblQCPackingList.TblQCInspectionRequest = TblQCNeedSample.TblQCInspectionRequest))
INNER JOIN TblContractHeader ON TblQCInspectionRequest.ContractCode = TblContractHeader.Code
INNER JOIN retaildb.ccnew.dbo.TblSupplier supplier ON supplier.Iserial = TblContractHeader.SupplierIserial
WHERE
(( TblQcbrandManagerColorDecision.CreationDate >= dateadd(dd, 0, datediff(dd, 0, getdate()))
and
TblQcbrandManagerColorDecision.CreationDate < dateadd(dd, 0, datediff(dd, 0, getdate()) + 1)
)
OR TblQCNeedSample.CreationDate >= dateadd(dd, 0, datediff(dd, 0, getdate()))
and
TblQCNeedSample.CreationDate < dateadd(dd, 0, datediff(dd, 0, getdate()) + 1))
-- AND TblQCInspectionRequest.Iserial = 2531
GROUP BY TblColor.Iserial,
TblStyle.StyleCode,TblColor.Ename,TblQCDecisionStatus.Ename,TblColor.Code ,TblQcbrandManagerColorDecision.CreationDate,
TblQcbrandManagerColorDecision.Comment,
supplier.Ename, TblQCInspectionRequest.Iserial
November 8, 2022 at 10:38 am
Using short aliases would make this easier to read.
To make reasonable suggestions about indexes, stats etc we would really need to see the ACTUAL query plan.
https://www.brentozar.com/pastetheplan/
At a guess, NOT EXISTS is quite often better than NOT IN:
INNER JOIN TblColor
ON TblColor.Iserial = TblQcbrandManagerColorDecision.TblColor
OR (
TblColor.Iserial = TblQCNeedSample.TblColor
AND NOT EXISTS
(
SELECT 1
FROM TblQcbrandManagerColorDecision CD1
WHERE CD1.TblQCInspectionRequest = TblQCNeedSample.TblQCInspectionRequest
AND CD1.tblcolor = TblColor.Iserial
)
)
November 9, 2022 at 7:26 am
I agree with Ken that NOT EXISTS should run faster than NOT IN.
Imagine you have a room full of people wearing various colour shirts, and you want to see if your shirt is a unique colour. A NOT IN requires you to check the colour of all shirts in the room before deciding if your shirt is unique. A NOT EXISTS will give its decision as soon as it finds a matching colour - it only checks all shirts if there are no matches.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 9, 2022 at 7:51 am
The first thing you should do is use the query plan to see if new indexes might help. This often helps. My best win on this was when I reduced a query from taking over 12 hours to taking seconds just by looking at the query plan and adding the right index.
If indexes do not do all that is needed, then try to reduce the complexity of the final query. A good way to reduce complexity is to do some pre-aggregation. Not all queries need pre-aggregation, but joins using NOT or OR can benefit from this. The query plan will help you decide if pre-aggregation might be of use.
Look at how you can split the query into smaller units, with each unit populating it's own table. These tables can then be indexed to improve the performance of your main query.
One way to package the pre-aggregation is to get whatever runs your query to instead call a stored procedure. You can then do all the needed pre-aggregation in the SP. There are many other ways to package pre-aggregation, such as indexed views, triggers, or separate table builds. You have to choose what is best in your situation.
When I have done pre-aggregation, I have sometimes got sub-second response from queries that previously took minutes or hours. There have also been situations where pre-aggregation was no help at all.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply