@bbaird Ah, what does a similar CROSS APPLY look like in performance?

FYI folks, the /Review/Close queue needs a little love. Ok, more than a little.

A lot of sites are struggling for reviewers hence the three votes to close experiment. User engagement has definitely dropped off in recent times. There were fewer highly active users keeping sites going than was widely appreciated I think

Not that anyone was ever particularly keen to cast the final vote

Morning

@bbaird the difference is AND ThingyProp.IsApplied = 1, right?

Today I saw this. It is possibly the most very best error message of all time ever.

@ypercubeᵀᴹ Yes

@HannahVernon I'm doing my part!

@bbaird cheers

@PaulWhite Two words: Monica Cellio...

quite

@CadeRoux Also, not to mention you can't use CROSS or OUTER APPLY since the criteria is to return the most recent row only if IsApplied = 1. So you just get into the whole "use the broke-ass IIF thing to fake the left join" situation.

@bbaird Are you ok with repeating the IsApplied predicate?

SELECT
    TV.ThingyId,
    TV.UpdateDtm,
    TP.Property
FROM dbo.ThingyVersion AS TV
LEFT JOIN dbo.ThingyProperty AS TP
    ON TP.ThingyId = TV.ThingyId
    AND TP.IsApplied = 'true'
    AND TP.UpdateDtm =
    (
        SELECT
            MAX(TP2.UpdateDtm)
        FROM dbo.ThingyProperty AS TP2
        WHERE
            TP2.ThingyId = TV.ThingyId
            AND TP2.UpdateDtm <= TV.UpdateDtm
            AND TP2.IsApplied = 'true'
    );

It may not be immediately apparent but the underlying mechanism is as described in sql.kiwi/2010/07/the-segment-top-query-optimisation.html

For the group by apply pattern to match, the two blocks must be identical

I do wish people would ask questions on the main site. It adds a lot more value.

@PaulWhite That gives the wrong result - it will return the last value where IsApplied was true, not the latest value only if IsApplied is true.

@bbaird Yes well I'm not familiar with your data model. It might have been valid. Anyway, that's the underlying issue.

@PaulWhite I can type it up and post it as a question if we feel it would add value. For me it's more of an annoyance/an edge case miss on the optimizer, not something I need solved.

Like, I've got a workable fix and even if it occurs the performance impact isn't noticeable because the cost of physical reads is so much greater than logical reads. IIRC, SQL Server (at least as of 2008R2) would always do the two seeks + inner join method as opposed to TOP.

No, SQL Server 2008 R2 generates the TOP plan (just tried it)

Perhaps you find this less clunky:

SELECT
    TV.ThingyId,
    TV.UpdateDtm,
    TP.Property
FROM dbo.ThingyVersion AS TV
OUTER APPLY
(
    SELECT TP2.Property
    FROM
    (
        SELECT TOP (1)
            TP3.IsApplied,
            TP3.Property
        FROM dbo.ThingyProperty AS TP3
        WHERE
            TP3.ThingyId = TV.ThingyId
            AND TP3.UpdateDtm <= TV.UpdateDtm
        ORDER BY
            TP3.UpdateDtm DESC
    ) AS TP2
    WHERE
        TP2.IsApplied = 'true'
) AS TP;

An IIF or CASE on the outer would be closer to a direct translation of the desired plan

@PaulWhite Yes, this gives the right plan, although I'd be more likely just to stick to my current IIF/CASE option as it's less code to produce the same plan

Since Paul recommended it for one of my other issues, I have found myself using the APPLY noticeably more often (rarely) than in the past (never). It definitely makes some things a little more readable and explicit in terms of what you expect the best way to carry out the operation will be.

Paul, is essentially the requirement for the TOP operator to be used for the left join w/correlated subquery is that conditions are only placed on the primary key column(s)? And just by default the behavior is to produce two seeks if there is any other column(s) in the join condition?

This site is such a cluster fuck.

How can I not ban-hammer this question on another site which shouldn't even host it... askubuntu.com/questions/50621/…

such a stupid idea to limits SME's to the domain of one site, while simultaneously allowing questions to be asked on totally unrelated sites.

Not one of those answers is specific to Ubuntu, and that question is too vague to keep open, that's 500k people we've wasted the time of.

@bbaird The 'two seeks' is the earlier plan that that sort of T-SQL is likely to produce. The transform from that two-table pattern to a single Top (+ Segment) is Join(x, GbAgg(x)) -> GbApply(x). You can have extra predicates inside the group by, but any on the outside must be matched on the inside.

@EvanCarroll Is the CF dba.se or askubuntu? - and where's askredhat/fedora...? I had to do a doubletake on version 8.4 of PostgreSQL... I'm not exactly clear on your point here.

@bbaird For example, your queries generally pass through an intermediate stage like this (if the optimizer dealt with T-SQL, which it does not):

SELECT
    TV.ThingyId,
    TV.UpdateDtm,
    OA.Property,
    OA.IsApplied
FROM dbo.ThingyVersion AS TV
OUTER APPLY
(
    SELECT TP.Property, TP.IsApplied
    FROM dbo.ThingyProperty AS TP
    JOIN
    (
        SELECT MAX(TP2.UpdateDtm) AS Expr1004
        FROM dbo.ThingyProperty AS TP2
        WHERE TP2.ThingyId = TV.ThingyId
        AND TP2.UpdateDtm <= TV.UpdateDtm
    ) AS J
        ON J.Expr1004 = TP.UpdateDtm
    WHERE TP.ThingyId = TV.ThingyId
) AS OA

The bit inside the apply is the pattern that might be matched by GbApplySimple

SELECT TP.Property, TP.IsApplied
FROM dbo.ThingyProperty AS TP
JOIN
(
    SELECT MAX(TP2.UpdateDtm) AS Expr1004
    FROM dbo.ThingyProperty AS TP2
    WHERE TP2.ThingyId = TV.ThingyId
    AND TP2.UpdateDtm <= TV.UpdateDtm
) AS J
    ON J.Expr1004 = TP.UpdateDtm
WHERE TP.ThingyId = TV.ThingyId

So the (x) there is ThingyProperty

Join(x, GbAgg(x)) -> GbApply(x)

I think I have it - want me to type up the question so you can throw this all in an answer?

Not this time. It would just be double effort for me

@PaulWhite Thanks for diving into how the internals approach this

no worries

@Vérace the CF is on the network.

The point here is that that question is tapping into the SME here, not there. It shouldn't be there. The most upvoted answer isn't just wrong, it's insecure. And that question should be rejected for being vague but they don't have the expertise to even know that.

Sounds like someone might be wrong on the internet

GRANTED.

mostly stackexchange leadership.

Let's create a bunch of overlapping sites that split the community and expertise along boundaries that have nothing to do with subject matter....

BRILLIANT.

@PaulWhite shurely shome mishtake? I'm shocked!

@EvanCarroll So, you're saying that there should be more moderation of the sites?

@Vérace I don't have a problem against moderation in theory. I think we can do better on addressing who is moderating and how it's done.

@EvanCarroll Indeed... although I have to say that there, they aren't too bad...

I mean that here, they're not too bad...

zzzzzzzzzzzzzzzzzzzzzzzzzzzz