April 4, 2023 at 12:51 pm
This is a big stored procedure which has multiple operations SELECTs, INSERTs within it and bunch of tempdb operations? The temp table error was something related to another operation within that stored proc which when throw ed an error when I clicked on Estimated plan.
If I can one question, here in the query, does table join happen first or filtering is done first?
It depends. No other way to put it. The preference, is for filtering to be done, at the point of reading the data, the earlier filtering occurs when processing, the better (usually, not 100% of the time). However, it's not automatic. Depending on the query, the indexes, statistics, server settings, database settings, you certainly can see joins done before filtering operations. In many circumstances, this actually works well. The only way to know what's going on here is to get the plan, even the estimated plan will do. Do you have Query store enabled on the database because you can just get the plan there.
How can the join operation become a problem in this case. Is it 1-1 or 1-many relationshio can cause the issue? Could you pls provide more insight with numbers how the cartesian product is being an issue? I am not able to visualize how this query is has bad performance impact w.r.t I/Os it is doing ? Can you please unfold in a little detailed manner?
Row count information:
select count(ROWID_OBJECT) from C_B_PARTY_COMM; --11924789
select count(ROWID_OBJECT) from C_B_PARTY_COMM_XREF; --17767184
select count(REF_PHONE_VLDTN_STATUS) from C_B_LU_PHONE_VLDTN_STS; --6
select count(SOURCE_SYSTEM) from MDM_TRUST_SCORE; --26Any tips to optimize this query where you can a direct performance impact?
or changing the join order will help ? or filtering anything first would help?
what kind of indexes might help in the case.
Looking for some advise.
Thanks!
Bob
Beyond what's already been suggested for indexes, I honestly can't make much more in the way of guesses without seeing how the query is played out within the execution plan. While an actual plan would be useful, since they're just estimated plans plus runtime metrics, we can actually tell a lot about the behavior without the run time metrics. Yes, having those is preferable, but not the be-all, end-all that we can make zero estimates on poor behavior. We can actually tell quite a lot from estimated plans.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 4, 2023 at 2:48 pm
If you can share the Stored Procedure in its entirety I can comb through it and see if I notice any issues and/or ways to streamline what you have. If the code is documented at all then that will be easier but I am pretty good at picking apart Stored Procedures and isolating issues as I have been doing just that for the last several months now as I inherited a mess of inefficient stored procedures.
If you cannot publically share it then copy/paste into a text file and email it to me djensen765@gmail.com I keep all things confidential.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply