@Gonsalu Ahem.

@MarkStorey-Smith Yes, I think something must have compromised my btinternet account. They had locked the web mail address.

@PaulWhite I've the cleaned up plans ready

@PaulWhite I'm getting an unknown error registering in sqlperformance.com

@PaulWhite What's the recommended way to post execution plans in dba.se?

@Gonsalu Uploading directly from Plan Explorer? Not heard of that issue before.

@Gonsalu However you like. Some people put the XML on a file-sharing site. Whatever's easiest. I generally recommend the Plan Explorer route because it avoids that issue (normally - not sure what's going on in your case).

At this stage I'd just say post the thing somewhere so we can get a look, I don't care much where tbh.

I did think of one way a hint would affect plan selection - hints in some versions prevent simple parameterization and go on to full optimization where a trivial plan isn't available. Probably not the cause in your case, but it is one scenario.

I'll get the latest Plan Explorer then

The one I have doesn't have the upload feature

That would help no end I'm sure :)

Yeah, now that I think of it, I'm not able to install it here

Remember guys, don't delete winsxs contents to save space on a SSD

I'll just upload the XML on a file-sharing site

OK, here are the plans: gist.github.com/gonsalu/55fce6509ee6fa9e49d9

A cool feature for Plan Explorer would be side-by-side query plan comparison

@PaulWhite, Hmmm I'm seeing that it uses more parallel threads on the NOLOCK version

@PaulWhite, can that be the reason to generate a different plan?

@Gonsalu Already exists in PE Pro.

@PaulWhite, What does?

@Gonsalu Side by side comparison.

@PaulWhite, ah cool :)

@Gonsalu That's just a consequence of the different plan shape. More parallel branches = more threads.

@PaulWhite, OK.

@PaulWhite, There's no OPTION(RECOMPILE) on those queries, but I can assure you it made no difference

@Gonsalu Ok.

@Gonsalu The queries are semantically different due to the confused approach with table aliases. Your NOLOCK query aliases both tables as I. The other query aliases only one as I. Write queries with explicit and clear aliasing to prevent unexpected results.

oh crap.

that's it

I feel ashamed haha

Yes, yes it is.

And I was doing the cleaning of the plans and didn't even notice

Too close to the trees no doubt.

Anyway, mystery solved.

Yes, sorry on creating this supposedly interesting problem

The intent of the query isn't 100% obvious to me either. It seems odd to do both a correlated TOP (1) join and the MAX aggregates.

because I'm grouping on grouper field

Yes I understand why the MAX is required, it's just an odd construct.

grouper_field is part of a composite key

Usually I see that pattern expressed differently.

If I set up a test DDL, would you mind telling me how you would do it?

Perhaps if you fix up the aliases so everything is qualified the logic will be clearer.

Feel free to share the query again in case there is a better approach we might suggest.

Or not :) Either is good.

the query is correct without the NOLOCK

the outer table is aliased and the inner one isn't

You're right, it's a habit I have to change

sqlfiddle.com/#!6/5cacf/1

The idea is to have a unique grouper_field with related attributes

but since grouper_field is part of a composite key, it's not unique

so I use the ORDER BY to declare rules of preference of records

and if there are several records which pass all the rules, I don't care which one gets chosen

sqlfiddle.com/#!6/88a1d/1

this is a better example, with more records

Interesting query. All else being equal, each of the GROUP BY grouper_field calls into CROSS APPLY should result in the same X record, which then gets... displayed once (MAX should arbitrarily be selecting from replicas of the same row)... (from my quick inspection)

@孔夫子 What sort of name is that?

yes, MAX() is just sugar, because it's not doing anything, really

@PaulWhite, would you write a query to do this in a different way?

@Gonsalu Quite likely. Just working on something else for a moment, and I'll take another look.

@PaulWhite Hi Paul. It's RichardTheKiwi. Those are Chinese characters for Confucius

sqlfiddle.com/#!6/88a1d/4/1

@孔夫子 Oh hey! Didn't recognise you with the untypable characters :)

Perhaps it's actually Chinese for "confuse-us" ;c)

@PaulWhite It does throw people off :) Good to see you back!

Thanks. Very glad to see the back of air travel for a while.

@PaulWhite I'm not due one until summer, but then I travel for family, not for work

@孔夫子, Yes, that's definitely more readable and does a single scan, too

@Gonsalu I think those types of queries are tagged Top-N-per-group on StackOverflow

Now that I've made the DDL example I don't know why I made the query that way

I think it's because I wasn't thinking of the data as TOP n per group, but more like, please try to remove this crap rows from the results

sqlfiddle.com/#!6/88a1d/5

@孔夫子 Holiday? Destination?

Oh I see we both wrote a window function example.

@PaulWhite, isn't the option 1 better, performance-wise?

@PaulWhite Holiday, 5 full weeks away from NZ and travelling across 3 countries in Asia.

@Gonsalu It depends.

@PaulWhite Yes, but I'm sure your version will run faster :)

Actually there is another interesting alternative...

sqlfiddle.com/#!6/8833d/1

It's not quite suitable here because the Top is With Ties

But it's a nice transformation to a single sweep of the table

Yes, this one doesn't do what I need

I know. It's just interesting.

@PaulWhite, but when you say it depends, is it because the sort of the single table scan requires more space?

Or even the third option here (modified from Paul's fiddle) sqlfiddle.com/#!6/88a1d/11/0

that one is the same as the one I did

but it's grouping on a sub-select instead of on the outer table

@Gonsalu That's the distinction. If we were going purely by the query plan costs (which you should never put too much weight into) and line up all the options so far, that last option shows the least cost from adding the percentages

it does push the stream aggregate down before the nested loop

@Gonsalu The ROW_NUMBER often comes out on top because it only scans the index once. The APPLY can be faster when a distinct list of keys is available without doing an explicit DISTINCT (e.g. where the keys exists in another table) and the inner side of the correlated join is optimally indexed, and where there a relatively few, large groups.

To be honest, for day to day usage, I only use ROW_NUMBER for Top-N-Per-Group queries; it performs well for typical use cases and rarely if ever performs the worst.

@PaulWhite, I understand, thanks for the explanation

I'll have to go now

@PaulWhite, again sorry for the hyped up problem

@Gonsalu No worries.

@孔夫子 Yes it is simple and has quite predictable performance, especially if a full sort can be avoided.

@孔夫子, thanks as well

@孔夫子 Lucky you!

you should really change your nickname, doesn't help for the nickname tab-completion

haha

See you later @Gonsalu

A friend of mine thinks that it's a good idea to store Python code in the database.

My first thought is that it's not a good idea but is there any valid reason to do this?

@ypercube where is the python code executing, on the client or server?

on the server.

He thought of this as a way to make changes in the program dynamically.

@ypercube I can see how that might be abused horribly, but it may also be legitimate. It's not much different to having stored procedures and changing them dynamically?

Hi. Is the sql server full-text indexing process quick or does it take time to create the indexes? If its the second case how to we know its completely built?

@deostroll You might get a quicker answer on the main site than in here at the weekend

@deostroll I think you need:

SELECT FULLTEXTCATALOGPROPERTY('the_Catalog_Name-here', 'PopulateStatus') ;

From here: technet.microsoft.com/en-us/library/ms190370.aspx

@ypercube I can't see how you would change the running code in the interpreter with it. You would still have to import the module somehow and you can just do that off the filesystem anyway.