Query gone haywire
-
I have a view and the view already was pulling data from quite a few tables but it was still executing around 20 seconds.....which was acceptable for my purposes.
After adding 2 more tables to the query, the performance tanked and is now above a minute and that is not acceptable.
I decided to check the Execution Plan to see if I had some Table Scans going on that might be the cause of the performance issues. I've coursed through the entire plan a few times and there is not one single Table Scan in the whole thing.
The step that the Plan is reporting as causing the highest cost is a Zip Code join. The Zip Code table itself has about 42000 rows. The table it's joining onto has 2100. Both Zip Code columns have an index on them.
Doing a SELECT * from either table produces a Clustered Index Scan and doing a SELECT ZIP_CODE from either table produces an Index Scan.
Why am I all of a sudden getting this performance hit?
Notes:
[*]The zip code join was not one of the 2 tables I just added to the view.
[*]That same zip code table is used someplace else in the view joined onto a different table...that has more rows....and no index on its ZIP_CODE column
[*]When I remove that one join from the view, it executes just fine...trims a whole minute off the processing time
I can supply detailed information for whatever people might need to be able to help.
Thanks in Advance. I'm really lost at this point.
-
The optimizer may decide to do an index scan if it feels it is going to need a high percentage of the records. This may simply be the case, or your statistics may need to be updated - so try that first.
If that does not help, post the query and the execution plan and we may be able to help you diagnose the issue.
-
Made a few more advances in my troubleshooting....
The Zip Code table that it is joining to is actually a View which includes a State table.
That state table is joined, itself, in the view.
If I just use certain joins within the query, I can keep the time manageable. However, once I have the State table joined via the Zip Code view, the table itself and then try to join another table onto it, the performance tanks.
That might be a bit crazy to follow so I'll whip up an example
TableA
TableB
TableC
TableD
TableE
Let's say we create View1 that joins TableA, B and C.
Then, let's create View2 which does the following....
SELECT *
FROM
TableD D
INNER JOIN View1 V1
ON D.ID = V1.ID
INNER JOIN View1 V1a
ON D.OTHER_ID = V1a.ID
INNER JOIN TableB B
ON B.ID = D.ID
INNER JOIN TableE E
ON E.ID = D.ID AND E.ID = B.ID
I know that last join is superfluous but just trying to show what's happening. I can do that whole query right up until the last join and then, as soon as I include that, it all blows up.
So, it's not just joining a table that's already joined via a view, it's then referencing that same table while joining a totally different table later on.
-
So... are you required to use the ZipCode/State view? If not, call the tables directly, instead. Also, Views of Views generally suck the life out of code... avoid them if you can... especially views that use the same tables that you need to join to for other reasons.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Can you perhaps post the execution plan in its xml format? (save as .sqlplan, zip and attach to your post). The base tables and index definitions would also be nice.
Regarding table scans...
A table scan in an exec plan doesn't always mean slow, and the absence of table scans doesn't mean fast. In addition, if the tables have clustered indexes, you'll never see a table scan. It shows up as a clustered index scan instead.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Here's the Exec Plan in XML form as requested.
I have also taken all references to the other view from the query and it still dies on that last join.
SELECT *
FROM
(SELECT * FROM AID_AGENCY_MIGRATOR WHERE ID IN (56643, 56644, 56648, 56649, 56650, 56651, 56652, 56653, 56654, 56655, 56659, 56660, 56661, 56767, 56639, 56640, 56641, 56642, 56768, 56769)) AM
LEFT OUTER JOIN AID_LICENSE_EXTENDED X
ON(X.LICENSE_ID IS NULL OR X.LICENSE_ID = AM.LICENSE_ID) AND (X.AGENCY_LICENSE_ID IS NULL OR X.AGENCY_LICENSE_ID = AM.AGENCY_LICENSE_ID)
LEFT OUTER JOIN AID_LICENSES SL
ON AM.LICENSE_ID = SL.ID
LEFT OUTER JOIN AID_AGENCY_LICENSES AL
ON AM.AGENCY_LICENSE_ID = AL.ID
INNER JOIN AID_AGENCY AG
ON AG.ID = COALESCE(SL.AGENCY_ID,AL.AGENCY_ID)
INNER JOIN FFG_ZIP_CODES AAZ
ON AG.ZIP_CODE = AAZ.ZIP_CODE
INNER JOIN FFG_STATES AAS
ON AAZ.STATE_ID = AAS.STATEID
INNER JOIN FFG_CITIES AAC
ON AAZ.CITY_ID = AAC.ID
LEFT OUTER JOIN AID_STAFF SF
ON SL.STAFF_ID = SF.ID
LEFT OUTER JOIN FFG_STATES LS
ON LS.STATEID = COALESCE(SL.STATE,AL.STATE)
LEFT OUTER JOIN FFG_ZIP_CODES SAZ
ON SF.ZIP_CODE = SAZ.ZIP_CODE
LEFT OUTER JOIN FFG_STATES SAS
ON SAZ.STATE_ID = SAS.STATEID
LEFT OUTER JOIN FFG_CITIES SAC
ON SAZ.CITY_ID = SAC.ID
INNER JOIN FFG_STATES RES_ST
ON X.RESIDENT_STATE = RES_ST.STATEID
LEFT OUTER JOIN AID_TERMINATION_REASONS TRM
ON X.TERMINATION_REASON = TRM.ID
LEFT OUTER JOIN AID_LICENSE_TYPES LICTYPE
ON LICTYPE.ID = X.LICENSE_TYPE
LEFT OUTER JOIN AID_SIRCON_COMPANY_IDS SCO
ON SCO.COMPANY_ID = COALESCE(SL.COMPANY_ID,AL.COMPANY_ID)
INNER JOIN COMPANY_NAMES CN
ON CN.COMPANYID = COALESCE(SL.COMPANY_ID,AL.COMPANY_ID)
LEFT OUTER JOIN AID_CORPORATE_STATE_LICENSES CSL
ON SCO.COMPANY_ID = CSL.COMPANY_ID AND CSL.STATE_ID = LS.STATEID
INNER JOIN AID_SIRCON_EXCEPTIONS EXC
ON EXC.STATE_ID = COALESCE(SL.STATE,AL.STATE) AND RTRIM(X.ENTITY_TYPE) = RTRIM(EXC.LICENSE_TYPE)
It's that last join.
If I run the query all up to that last one, 3-4 seconds. If I add that last one - 1 min 3 sec.
If I only do part of the last join (either the state join or the license type join), it obviously does not display proper data, but it still executes quickly.
I believe that almost every column being used to join in this query has a index of some sort on it. (including a unique key on AID_SIRCON_EXCEPTIONS STATE_ID and LICENSE_TYPE columns......query performed identical before and after creating that index)
-
The execution plan is warning of missing join predicates in a couple places. It thinks you have cartesian products forming
From the way the actual row count is increasing at points in the plan (estimated 1008960000 actual rows 5456880)
I'd say it's maybe right.
How many rows are returned at the end? 26?
If so, SQL's generating work tables of millions of rows, then filtering that down to 20 or so.
I'm not familiar with your structure, so can't help with finding the joins. The exec plan's complaining about these two bits though
INNER JOIN COMPANY_NAMES CNON CN.COMPANYID = COALESCE(SL.COMPANY_ID,AL.COMPANY_ID)
and
INNER JOIN FFG_STATES AAS
ON AAZ.STATE_ID = AAS.STATEID
My guess is that the table you added was the last straw that broke the query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I had also considered that but the weird thing in my mind is that when I modify that join to only use 1 of the columns, the total output switches to 52 or many thousand rows (depending on which on is used) and the query runs very quickly.
So, the question is.....what is happening when both columns are used that is throwing things into the gutter?
-
Also....COMPANY_NAMES has 3 rows....
FFG_STATES has about 52......
which is why I don't understand why it's complaining about those 2 particular joins.
If any join should be throwing fits it seems like it should be one of the FFG_ZIP_CODES joins since that table is in the 45000+ range.
-
It's not the number of rows. The query processor thinks there's a couple of cross joins happening.
Suggestion: Go carefull through the joined tables and make sure that all tables are properly joined.
Make sure that there are no groups of tables that are joined to each other but not the outer query.
Paper and pencil is probably best for that.
Could you post the execution plan of the query when one of the joins is removed and is running fast?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Current attachment has....
goodGrief - original
goodGrief_halflast - last join is only joined on the first column (STATE_ID)
goodGrief_1less - last join is totally removed
I'll work on pencilling out the design and see if something pops out that using both parts of that last join might be doing.
-
hehehe...
true blue genius that I am, forgot to attach the new file
yeah....that's how I roll!
-
Started pencilling it out and noticed some things but nothing super serious....
I noticed that the second table I was joining (EXTENDED) was LEFT OUTERed when it really should be INNERed so I made that change but still no change.
When I got to the bottom, the last query was being INNER JOINed.....which is not really wrong, logic-wise but I decided to play around with it and LEFT OUTERed it and BAM.....back to 3 seconds.
So....after about 6 hours and bashing my head over this, that was all it turned out to be.
My question to all ye MS SQL Gods (and godesses) would be.......in the Execution Plan, why was it pointing to things that seemingly had nothing to do with actually solving the problem? Doesn't instill much confidence in me to use the tool when what it tells you doesn't seem to help :unsure:
-
Different join means a different execution plan. SQL probably generated one that doesn't have the same massive internal tables.
Can you post the plan with the outer join? I'd like to see what it's figured out.
I also sketched out the joins. 1 question: Is there any chance of X.LICENSE_ID been NULL and X.AGENCY_LICENSE_ID been NULL. If there's any chance of that, then the join between X and AM has no predicate. Since the last table you added in joins both to X and to SL (which is joined to AM), there's a chance that could have changed the plan sufficiently to really slow stuff down.
The coalesce joins with AL and SL look like the're adding to the problem. Is there any way you can get around the need to coalesce the joins to those two tables?
The exec plans with the half joins are also showing joins without predicates. It's just that the plan is sufficiently different that the estimated and actual rows are diffent (46143000 estimated and 2492400 actuall on 1st half, 2017900000 estimated and 10913760 on last half)
As a long term fix, if I may suggest.
Create 2 temp tables with appropriate indexes. Populate the one with the portion of the query that affects the tables X, RES_ST, TRM and LIC. Populate the other temp table with the portion of the query that affects all the other tables. Then join the two resulting temp tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
For the X table, AGENCY_LICENSE_ID and LICENSE_ID work like a 2 column key and one of them will always be not null. The table is only ever populated via a trigger on AID_AGENCY_LICENSES or AID_LICENSE so it would take some really weird situation to ever jeopardize that.
Just so I understand your suggestion correctly.......I should create a stored proc that creates a couple dummy tables and then joins those and return them? That would probably work for where this view is used just as well as anything else.....but the performance of the view right now is really quite spectacular so I'm not sure that any overhaul is necessary at the moment. The moment I do start having problems with it again, I will definitely consider that option.
Here's the Execution Plan you wanted......the query has been modified slightly. When I was trying to find the problem with the query, I had replace the first table in the join with that sub query you saw. The actual view uses the whole table. Using the whole table makes the query take quite a long time just because of the number of rows involved that way so I changed the table into a sub-query again....I think this one only has 20 rows but you should still be able to see the difference in the Plan.
Again, thanks for all your help.
I made this just for you.......
...hope you like it 😉
(Edit = changed cake to one that wasn't mega huge :P)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply