March 11, 2019 at 9:22 pm
Hi,
I have a query that is not performing well. The execution plans starts with an index seek on the transaction table (VENDTRANSOPEN). It reads and returns 90 000 rows to the next operator. It would be much more effective if the execution plan would start with the base table (VENDTABLE) with the PARTY predicate.
I would believe that the issue is the join order and the solution is rewriting the query. However, I would prefer putting a solution on the SQL side if possible. If it possible to hint the optimizer to start with the PARTY predicate ?
SELECT *
FROM VENDTRANSOPEN T1
CROSS JOIN VENDTRANS T2
CROSS JOIN VENDTABLE T3
LEFT OUTER JOIN DIMENSIONATTRIBUTEVALUECOMBINATION T4 ON (
(T4.PARTITION = 5637144576)
AND (
(T1.CASHDISCOUNTLEDGERDIMENSION = T4.RECID)
AND (T1.CASHDISCOUNTLEDGERDIMENSION = T4.RECID)
)
)
LEFT OUTER JOIN HCMWORKER T5 ON (
(T5.PARTITION = 5637144576)
AND (
(T2.APPROVER = T5.RECID)
AND (T2.APPROVER = T5.RECID)
)
)
LEFT OUTER JOIN LOGISTICSLOCATION T6 ON (
(T6.PARTITION = 5637144576)
AND (
(T2.REMITTANCELOCATION = T6.RECID)
AND (T2.REMITTANCELOCATION = T6.RECID)
)
)
CROSS JOIN VENDTABLE T7
WHERE (
(T1.PARTITION = 5637144576)
AND (T1.DATAAREAID IN ('MATT'))
)
AND (
(T2.PARTITION = 5637144576)
AND (
(
(T2.PROMISSORYNOTESTATUS = @P1)
OR (T2.PROMISSORYNOTESTATUS = @P2)
)
AND (
T1.REFRECID = T2.RECID
AND (T1.DATAAREAID = T2.DATAAREAID)
AND (T1.PARTITION = T2.PARTITION)
)
)
)
AND (
(
(T3.PARTITION = 5637144576)
AND (T3.DATAAREAID IN ('MATT'))
)
AND (
T1.ACCOUNTNUM = T3.ACCOUNTNUM
AND (T1.DATAAREAID = T3.DATAAREAID)
AND (T1.PARTITION = T3.PARTITION)
)
)
AND (
(
(T7.PARTITION = 5637144576)
AND (T7.DATAAREAID IN ('MATT'))
)
AND (
(T7.PARTY = @P3)
AND (
T2.ACCOUNTNUM = T7.ACCOUNTNUM
AND (T2.DATAAREAID = T7.DATAAREAID)
AND (T2.PARTITION = T7.PARTITION)
)
)
)
ORDER BY T1.REFRECID
OPTION (FAST 9)
Thank you
March 12, 2019 at 12:06 am
SQL Optimizer generates a good execution plan if I do T6.PARTITION = 0. Once the plan is in cache, I run the query with T1.PARTITION = 5637144576 and it is fast.
What would be the correct approach to ensure SQL generates the correct plan when I use T1.PARTITION = 5637144576 ?
March 12, 2019 at 2:58 am
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 12, 2019 at 6:04 am
Gamleur84 - Tuesday, March 12, 2019 12:06 AMSQL Optimizer generates a good execution plan if I do T6.PARTITION = 0. Once the plan is in cache, I run the query with T1.PARTITION = 5637144576 and it is fast.What would be the correct approach to ensure SQL generates the correct plan when I use T1.PARTITION = 5637144576 ?
That sounds like a statistics distribution problem. Look up the values in the statistics of the index to see why you're getting different suggestions. If these were parameters instead of hard coded values, you could use the OPTIMIZE FOR hint to have it use T6.PARTITION=0 as the compile value. It's possible a simple statistics update could help.
By the way, this:OPTION (FAST 9)
Is absolutely going to affect the plan produced. The optimizer will favor loops joins over other joins that more likely to perform better overall. I'd strongly suggest testing without that hint.
Just so we're really clear, query hints are not hints. They are absolute commandments to the optimizer. You will be taking away optimizer choices. Sometimes, this is necessary, but it's EXTREMELY rare (except in the case of bad parameter sniffing). I absolutely lean towards never using hints until I've exhausted other options (again, except in the case of bad parameter sniffing, although there, with 2016+ and Azure SQL Database, I'm leaning on Query Store to solve a lot more of those problems).
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 12, 2019 at 10:52 pm
Thank you for the help. I am always amazed by the skills on this forum.
The reason of poor performance was due to OPTION (FAST 9).
The query is automatically constructed by the application based on X++ language. It means that all those joins were not explicitly written but rather generated by the application. Making a modification to the code means I would need to go to the vendor (Microsoft) or a third party vendor. For that reasons, it is always preferable for me to find a solution in SQL like a missing index or statistics.
In this case, it seems the developer put the FAST hint and that would cause SQL to generates a bad execution plan. In my opinion, the solution is to fix the code since I would like to avoid forcing a plan to the query.
I am also disappointed that I totally omitted the hint during my analysis and focused too much on statistics. Live in learn I guess !
Thank you
March 13, 2019 at 5:58 am
Gamleur84 - Tuesday, March 12, 2019 10:52 PMThank you for the help. I am always amazed by the skills on this forum.The reason of poor performance was due to OPTION (FAST 9).
The query is automatically constructed by the application based on X++ language. It means that all those joins were not explicitly written but rather generated by the application. Making a modification to the code means I would need to go to the vendor (Microsoft) or a third party vendor. For that reasons, it is always preferable for me to find a solution in SQL like a missing index or statistics.
In this case, it seems the developer put the FAST hint and that would cause SQL to generates a bad execution plan. In my opinion, the solution is to fix the code since I would like to avoid forcing a plan to the query.
I am also disappointed that I totally omitted the hint during my analysis and focused too much on statistics. Live in learn I guess !
Thank you
Thanks for reporting back what worked. Frequently we don't get that, so it's appreciated when we do. Not only do we know what the solution was, but others who find this thread will know what fixed the issue in the event that they're hitting it too.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply