January 13, 2020 at 7:51 pm
There is no difference in the execution plan. Just the number of rows estimated.
I found this in the second plan and not in the first. Interestingly, the second plan also carries a recommendation on a missing index, which the first one does not.
<Predicate>
<ScalarOperator ScalarString="[PaleoDataProPokusy].[dbo].[Podrobnosti].[TaxonAutoID] as [P].[TaxonAutoID] IS NOT NULL">
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[PaleoDataProPokusy]" Schema="[dbo]" Table="[Podrobnosti]" Alias="[P]" Column="TaxonAutoID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
January 13, 2020 at 7:58 pm
What happens if you create the suggested index? Do both queries use it?
January 13, 2020 at 8:13 pm
What happens if you create the suggested index? Do both queries use it?
Nope - nothing changed. The plan looks the same, the estimates are the same, and it even continues to suggest that I create that same index that I just created. (Yes, it's really there - I looked.) Seems a little fractured, but I've been warned before, by some heavyweights on this forum, that the suggestions SSMS makes in this regard are not to be take too seriously. I usually at least take a look at the suggestion, and sometimes I try it. Helps sometimes, not other times.
Also occured to me that the plans may be cached. I stuck DBCC FREEPROCCACHE at the beginning of the list of statements - still no change, in anything.
January 13, 2020 at 9:24 pm
Looking at the difference between plans:
The first plan utilizes an index scan using the index ix_TaxLok with no predicate. This results in an estimated row count of 124720 and an actual row count of 124720.
The second plan utilizes an index scan using the index ix_TaxLok with the predicate: [PaleoDataProPokusy].[dbo].[Podrobnosti].[TaxonAutoID] as [P].[TaxonAutoID] IS NOT NULL. This results in an estimated number of rows of 80447 and an actual number of rows as 78320.
For the second query - SQL Server pushes the check for null to the index scan, and in the first query that check is eliminated. The Hash Match (inner join) is then performed and would eliminate any NULL values anyways - so that check isn't actually needed.
Overall - I would say the first query looks to be the most efficient. If you removed the filter on P.TaxonAutoID you will get the same results and I think the second query would probably end up with the same plan.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 13, 2020 at 9:36 pm
Looking at the difference between plans:
The first plan utilizes an index scan using the index ix_TaxLok with no predicate. This results in an estimated row count of 124720 and an actual row count of 124720.
The second plan utilizes an index scan using the index ix_TaxLok with the predicate: [PaleoDataProPokusy].[dbo].[Podrobnosti].[TaxonAutoID] as [P].[TaxonAutoID] IS NOT NULL. This results in an estimated number of rows of 80447 and an actual number of rows as 78320.
For the second query - SQL Server pushes the check for null to the index scan, and in the first query that check is eliminated. The Hash Match (inner join) is then performed and would eliminate any NULL values anyways - so that check isn't actually needed.
Overall - I would say the first query looks to be the most efficient. If you removed the filter on P.TaxonAutoID you will get the same results and I think the second query would probably end up with the same plan.
You're right - removing that line from the Where clause made both give the incorrect large estimate, although the final results are still the same and correct for both. But since this version collects and discards 46,000 extra records, I'm curious why you would say that such a version is more efficient than one which does not do that extra work?
January 13, 2020 at 10:13 pm
Look at what the resulting values are that come out of the hash match - in the first query the number of rows is less than what you see from the second query.
The difference in the number of rows isn't going to cause much of an issue - since the size of that data is quite small. The estimated data size for the 124,000+ rows is 3410KB vs 2200KB from the second query and since SQL Server reads by page - either will result in the same level of work (1 scan 681 logical reads it appears).
So - the hash match reduces the number of rows in the first plan to 625 which makes the sort less costly and the merge join less costly. Looking at the final select the memory grant for the first plan is 1712 vs 1776 for the second plan. Granted - the differences are minor but the first plan appears to be a bit more efficient even though it appears to read more rows from the index.
Removing that filter should have resulted in the exact same plan...did that happen or did you get something different from the other 2 plans?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 13, 2020 at 10:45 pm
Look at what the resulting values are that come out of the hash match - in the first query the number of rows is less than what you see from the second query.
The difference in the number of rows isn't going to cause much of an issue - since the size of that data is less than a single page. The estimated data size for the 124,000+ rows is 3410KB vs 2200KB from the second query and since SQL Server reads by page - either will result in the same level of work (1 scan 681 logical reads it appears).
So - the hash match reduces the number of rows in the first plan to 625 which makes the sort less costly and the merge join less costly. Looking at the final select the memory grant for the first plan is 1712 vs 1776 for the second plan. Granted - the differences are minor but the first plan appears to be a bit more efficient even though it appears to read more rows from the index (again - it looks like all of those rows exist on a single page so they will all be loaded into memory regardless).
Removing that filter should have resulted in the exact same plan...did that happen or did you get something different from the other 2 plans?
I've been getting the same-looking plan all along - just different estimated and actual number of rows retrieved from P, and in the case of the first plan, much higher (cca 50%) than correct.
And what comes out of the hash match is 255 in both cases - the final and correct row count for the entire query (which makes sense - the last join to tL is a left join, and there are no conditions on that table).
The query runs quickly, though, in both versions, so it's not really a performance issue that I need to solve. But I could see this being a performance issue with similarly constructed queries in situations processing much more data, where badly incorrect row estimates might result in a seriously sub-optimal query plan being generated. So, I'm trying to figure out if there is something here that I can learn about performance tuning in general, like it's usually better to put inner joins first and left joins later, or something to that effect.
Testing large numbers of syntax variations can quickly get to be a major pain in the fundament, even when such testing is restricted to queries having noticeable performance problems. I'd like to have a better handle on what is likely to be a good syntax (and WHY a particular syntax is likely to be good or bad), instead of having to constantly assemble exhaustive series of tests.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply