@JoeObbish - after an admittedly cursory look at the plan xml, it looks like the coalesce is the cause of the problem. I know this is not particularly helpful since you've already identified that as a problematic item. It looks like that is causing the loop join between the derived table and the X_LAST_TABLE to estimate a maximum of 94025 rows for each row in the derived table. This matches your hypothesis of the join acting like a cross join.
Why removing the link table fixes the issue is unknown to me. I'm certain Paul will provide the answer since this question is exactly the kind of thing he excels at, as you know!
@JoeObbish I've had problems with Coalesce against different LEFT-JOINed tables, too. These are things I've done that have helped, some, in various (perhaps not identical, but similar) situations.
1. Make each successive LEFT JOIN after the first one add a condition WHERE PreviousTable.Value IS NULL
2. Don't compose the final value of the Coalesce from "left-to-right" but from "top-to-bottom" using some form of aggregation, ala Min() or SELECT TOP 1 FROM A UNION B UNION C
3. Strategic use of CROSS APPLY seems to alleviate situations like this sometimes
And just a wild idea, an indexed view that UNIONs the three tables could do wonders potentially. (Is that possible? I am ambivalent about this and don't have time to look it up. My instinct says indexed views can't use UNION but I can't remember.)
I was just checking my answer/reply history and noticed this became Closed as 'Off Topic'. Tag reads "this could be because your code has a typo, basic error, or is not relevant to most of our audience" In the case of this question that doesn't happens.
If you can understand what the user is ask...
> Additionally, if you offer multiple bounties on the same question, the minimum spend doubles with each subsequent bounty (50 reputation on the first bounty, 100 reputation on the second, 200 on the third, and so on).
Would that mean that if I start out at 500 I can't add an additional bounty?
Or for example when starting at 250 only 1 additional bounty can be added, but when starting at 100 3 additional bounties can be added?
I've used the code below to query and got the output shown. Now, I would like to query as describe below. How should I do it?
Find code 2 of an ItemID, check if code 1 comes after code 2. If yes, compare the time difference. If time difference is less than 10 seconds, display the two compared...
> ...if your first bounty was worth 50 reputation, your second bounty on the same question will have to be for at least 100, your third for at least 200 and so on. If you've already offered a bounty for more than 250, you can still offer more bounties for 500 (the maximum amount) as long as you like (or as long as you have the rep). This doubling applies only to bounties by the same user on the same question.
I think it was considered too localized because the problem was a missing join condition.
The scope of the site states
dba.se is for those needing expert answers to advanced database-related questions [...]
Doing a Cartesian product instead of an inner join is hardly a topic that I woul...
don't forget - databases include little more than MySQL or PostgreSQL, for example MongoDB is also database and it not support foreign keys, and inside relation databases realisations could be different, so developer could decide for some reasons move this logic outside of database and use database with most universal set of commands - SELECT, UPDATE, INSERT. I not tell - it good or bad, but this is often practice. — a_vlad2 hours ago
Tags pgadmin-3 and pgadmin-4 were created a while ago.
pgadmin-1.18 also exists.
Is there any need for these, or should they be made synonyms of pgadmin and merged?
Related meta Q & A:
PgAdmin Tags should be redone
@PaulWhite I just thought it was funny you said "morning" this time, we all know it's not morning where you are, and you sometimes jokingly say "evening" when somebody says "morning" but didn't this time. That triggered me to star your message, and apparently some people followed suit. Your asking "why" just added to the fun so now you have 6 stars
Well, pgadmin-1.18 is definitely useless (it refers to a specific pgAdmin III version).
At the same time, pgAdmin III and pgAdmin 4 are two very different products. If the question is tagged rightly with pgadmin, it completely makes sense to indicate which one it is about. That the users don't...
@ypercubeᵀᴹ I'll tell you what prompted this (aside from the other meta question on the subject). I noticed earlier than an existing question had it's pgadminreplaced with pgadmin-4.
There is no way to inject a cardinality estimation directly into the optimizer but depending on what you want to achieve there are a few options.
You could use an OPTION (FAST N) hint to introduce row goals, and possibly rewrite your query using CTE's or subqueries to inject your row goals in di...
@PaulWhite yes but the vast majority of questions with pgadmin are also tagged with postgresql. I follow postgresql, so I hadn't bothered to tag with pagdmin
Potentially, all questions about pgadmin-4 will have the pgadmin tag as well. So if I want to follow all pgadmin questions, I follow the pgadmin tag. If I want only the pgadmin-4 question, the pgadmin-4 tag.
And I trust my fellow dba.se users to retag appropriately the ill-tagged questions ;)
I don't know. Do we gain anything from having sql-server and sql-server-2008, etc tags? Aren't there questions that fall through the gaps and are not tagged right?
So the cost is maintaining a new version hierarchy and applying it consistently. What's the corresponding gain?
If the answer is there is no gain, that's fine. I just want people to understand they're supporting something that adds nothing, but costs a little bit of someone's time.
@TomV I added the TOP thing because FAST hints can only be used at the top level (not in a CTE or subquery/derived table). If that's not what you meant, feel free to edit/rollback as always.
Grammar Translator by Colin Daley. Converts e.g. Transact-SQL syntax BNF to Oracle-style railroad diagrams.
@TomV In case you wanted more feedback, it might be helpful to clarify that TOP can only be used to lower a cardinality estimate and not to increase it
Halsman's found that by taking people staged portraits, they always had their "face" on. When he asked them to jump, they'd let their guard down and you'd see the real them
if you have any time, can you have a look at this question, please? I ask my intern to see what he can do to improve the perf dba.stackexchange.com/questions/168776/…
@MaxVernon Thanks for the answer. I probably won't mark it as accepted because what I really want to know is why the bad CE is happening. It's still a quality answer though
Is that final join more or less a cross join with some predicate filtering in the join operator? I don't like the look of that spool
@JoeObbish I don't expect it to be accepted. It's really just an attempt to learn. Thanks!
Interestingly, in 2008R2, the original query goes parallel. Also, in v.Next, the CE estimates 1 row will be returned.
@Lamak Your query with ISNULL(ISNULL(...)) instead of the COALESCE(X,Y,Z) is certainly far superior in my book. Of course without testing it on actual data it's pretty hard to say for sure.
@MaxVernon It puts 87385 rows into a table spool and does a full scan of that table spool 539141 times. So 47112836285 rows would be processed by the join to produce 8 rows at the end :)
@JoeObbish Yeah just to avoid nesting. It just seems more like the sort of thing that would make some sort of sense.
The other nice thing about ISNULL that can be important is that it simplifies away if the expression is known not to be nullable. COALESCE can't do that, because it is implemented with the generic CASE.
I need to look at it more but my attempt at a rule would be "use COALESCE if you sometimes need to return NULL or if you need to do funny business with data types. otherwise use ISNULL"
for some reason I thought NULL wasn't appropriate for the second parameter
my new attempt at a rule would be "use COALESCE if you need the expression to be considered NULLable or if you need to do funny business with data types. otherwise use ISNULL"
In a thousand years, when people have uploaded themselves to machines, the useless professor stereotype won't be the person who can read Old English, but ANSI-92.
@MaxVernon IMO an important point was lost in your answer. Because JOIN_ID is the primary key of X_LAST_TABLE, it shouldn't matter how much obfuscation I do in the definition of dm.PRIMARY_ID
for each row in dm the join to X_LAST_TABLE must return either 0 rows or 1 row
I think that it's fair to say that ISNULL could be more accurate, but not fair to say that CASE should result in this kind of behavior
the number of rows returned by the query must be between 0 and 481577 due to the constraint on the table
SQL Server is inconsistent with itself here. It estimates 481577 rows for the outer table and one row per iteration for the inner loop
I agree, @Joe that the calculation should be easy, since that column is a primary key. However, nothing surprises me with "estimates" in SQL Server. They are, after all, called that for a reason.
It's funny. I thought that as I learned more about SQL Server I would be able to glean more information from query plans. In reality it feels like as I learn more there's actually less useful information in query plans...
@JoeObbish It is possible, yes. Any part of the tree, down to a single node, can be substituted and a new estimate derived. When the lowest cost parts are stitched together, the result can be "inconsistent" when viewed from the highest level.
@JoeObbish I'm not trying to blow your mind here. All the things you know are still useful, it's just that there's always more going on. An execution plan that exposed all possible details and lineages would be unfeasibly large and hard to work with, not to mention resource intensive to generate.
@JoeObbish More or less, yes. It is time-consuming, though.
One reason I haven't even looked at the stats-copy db for that quesiton.
@PaulWhite At one point I was going to ask our MS contacts if there was anything out there that we could get under NDA that would help with some of this stuff
since some of the newer TFs don't work for the legacy CE
apparently their answer would have been to link me to some videos about query optimization
SELECT
SOH.CustomerID,
SUM(OrderQty)
FROM Sales.SalesOrderHeader AS SOH
JOIN Sales.SalesOrderDetail AS SOD
ON SOD.SalesOrderID = SOH.SalesOrderID
WHERE
SOD.ProductID BETWEEN 711 AND 718
GROUP BY
SOH.CustomerID;
It's a tough thing to write about, so it just features as a sort of aside in that article.
Replacing part of a tree with an indexed view is easier for people to grasp than some abstract idea of alternatives that only exist in the memo for a short time.
In class I talk about writing the same logical query a dozen different ways in SQL and why it is unrealistic to expect the final cardinality estimate to be the same in all cases.
@JoeObbish Oh. Sorry about that then.
Unless you enjoy that sort of thing. In which case: you're welcome.
It seems that despite the implication in BOL that the left hand operand will be implicitly cast to float that this is not the case. The output of POWER() is cast to the type of the left hand operand, which is DECIMAL if you use 10.0. Using an explicit float works fine.
SELECT POWER(1e1, 38);
SEL...
Results are the same between testing all of the above, including work arounds, on Microsoft SQL Server 2014 (SP1) 12.0 and Microsoft SQL Azure (RTM) - 12.0. I think the Azure database is the equivalent of 2016 or newer. — Triynko17 mins ago