The Heap™ – Consultancy ©®

John aka hot2use

6:47 AM

Morning

I read guns

John aka hot2use

7:17 AM

@MaxVernon added my 2 cents for future users

McNets

7:32 AM

Morning

Paul White

7:45 AM

Morning

Tom V

Morning

Andriy M

8:05 AM

Morning

Tom V

8:57 AM

0

A: can I write the below query using Over()in mysql?

You know how an owl can turn its head almost completely around? That's what happens to me when I see a RIGHT JOIN. Is this a correct rewrite of your query, using LEFT JOIN? SELECT (fs.follow_start_date <= ds.start_date) AS running_total , ds.onboarded_date FROM ( SELECT di...

> You know how an owl can turn its head almost completely around? That's what happens to me when I see a RIGHT JOIN

McNets

10:53 AM

@TomV 😂

ypercubeᵀᴹ

11:13 AM

RIGHT JOIN = LEFT 270 JOIN ;)

Andriy M

That's assuming that RIGHT JOIN = RIGHT 90 JOIN

Paul White

LEFT 180 JOIN seems more natural to me

Perhaps it should be NOIJ THGIR

I'm sure that would help Rick's owl problem

Andriy M

@PaulWhite More natural than NATURAL JOIN?

Paul White

I always thought NATURAL JOIN was a little presumptive of it

Like all other joins are somehow unnatural

Andriy M

Fair point

Paul White

11:23 AM

IMPLICIT JOIN perhaps

@AndriyM How are you enjoying your new chat buddy?

They seem to like you

Andriy M

I'm only now starting to notice the impatience, to be honest

Paul White

My senses are highly tuned 😀

Also I am fairly cynical these days

No idea why

Also who reinstated @Lamak as a room owner

He lakhs the qualifications

4

Andriy M

What are the requisite qualifications these days?

Paul White

Not being called Lamak

No special relationship with herbs and spices

Those are two main ones

Andriy M

11:42 AM

Their impatience appears to have helped them find a solution on their own.

Paul White

So I see

What a great investment of time and energy that was

I hope we see more questions of that sort in the near future

Andriy M

So sarcastic

Paul White

It's a gift

Use it or lose it

Max Vernon

11:56 AM

@PaulWhite lol it was the rookie mod.

Paul White

Yeah I know

banter

APPROX_COUNT_DISTINCT is pretty amazing

Max Vernon

@Johnakahot2use awesome!

@PaulWhite presumably, by amazing, you mean terrible?

(just reading the room lol)

Paul White

@MaxVernon Nope, genuinely amazing

Max Vernon

ahhh interesting. In what way(s)?

Paul White

Fast, minimal memory, accurate

Max Vernon

12:07 PM

when you say accurate, you mean sometimes? or ?

I guess there must be circumstances when it is more accurate than others?

Paul White

> The function implementation guarantees up to a 2% error rate within a 97% probability.

Max Vernon

oh well that's 3 standard deviations (approximately) which is excellent

gotta love statistics

when you say fast, like an order of magnitude faster than COUNT(DISTINCT...)?

Paul White

SO 2013 example

SELECT
    COUNT_BIG(DISTINCT C.CreationDate),
    COUNT_BIG(DISTINCT C.PostId),
    COUNT_BIG(DISTINCT C.Score),
    COUNT_BIG(DISTINCT C.UserId)
FROM dbo.Comments AS C;

SELECT
    APPROX_COUNT_DISTINCT(C.CreationDate),
    APPROX_COUNT_DISTINCT(C.PostId),
    APPROX_COUNT_DISTINCT(C.Score),
    APPROX_COUNT_DISTINCT(C.UserId)
FROM dbo.Comments AS C;

Both queries execute in 5 seconds on a 24.5M row table in 8GB

First has a memory grant of 3,020,784KB
Second has 12,392KB

Results 1

24515257    8614788    448    893479

Results 2

24238063    8890773    448    887006

1 min ago, by Max Vernon

that is amazing. I'm running it now on Erik's big SO database. still waiting for results lol

Max Vernon

for anything requiring sampling, not 100% accurate, that is a great choice. Will stow that one away for future.

Paul White

On really large tables, the question is how accurate does one actually really need

Max Vernon

12:14 PM

the plans for those two queries are quite incredibly different too

@PaulWhite for sure. Most of the time, you just want to get a feel for the number of distinct values.

Paul White

Visually anyway

SQL Server 2019

Max Vernon

ya

Paul White

They're the same for me on SO 2013

Max Vernon

interesting

for those following along who don't want to run it:

Paul White

@MaxVernon Your database isn't set to 150 compatibility is it

Max Vernon

12:18 PM

yes it is

Paul White

Ah well you have too many CPUs or too much memory then

Max Vernon

sorry 100

Paul White

Uh huh

Max Vernon

every other database is 150 lol

Paul White

Well that's the important thing

I don't know why SQL Server considers row mode plans for big queries on 150

Well I do, but I wish it wouldn't sometimes

@MaxVernon Ha!

Max Vernon

12:22 PM

sneaky hey

I have a couple of SO databases and was looking at the wrong one

Paul White

It happens to me too

That's why I guessed that

It's v. easy to do when reverting/restoring

Max Vernon

for sure

Paul White

Do you see better plans under 150?

Max Vernon

the time taken to show that plan image was almost all spent in trying to copy-and-paste into the chat window. Is that broken? I mean didn't you used to be able to just paste an image in here?

Andriy M

You may be thinking The Heap 2.0

Max Vernon

12:25 PM

yah probably

Paul White

@MaxVernon No that has never worked in chat, only on main

Heap 1.0 really does feel a bit clunky these days

Max Vernon

I agree

Paul White

@MaxVernon That's more like it!

The case in point today was a 2TB table hosted on Azure something or other

With the usual sucky I/O perf

Andriy M

1.0 is full screen, so it gives you more info without extra clicks. Ability to easily paste pictures is a major advantage of 2.0 over 1.0 though.

In my eyes anyway

Paul White

@AndriyM You know you can move the divider on 2.0?

Between Q & A and chat I mean

Andriy M

12:32 PM

I can, but... Then the question area will be smaller.

What's with my fingers today

Paul White

You need one of those ultra wide monitors

I find it quite useable at 1920x1080

But I hear what you say

Andriy M

Actually that's a good point. SE doesn't use the entire width. TA does. So I should be able to find the right balance.

I mean, the way SE uses the screen space makes total sense to me, when it's too wide, it's not very readable.

Paul White

Right

Lamak

@PaulWhite I'm actually not called lamak

Paul White

@Lamak Sounds like something a camel would say

Lamak

12:45 PM

chile lakhs camels though, so I wouldn't know

Paul White

true

@JoshDarnell Should be a room owner so the italics make his surname Darneli

Lamak

he should legally change his name

Paul White

Right!

He'll always be an Italian living in the UK to me

Max Vernon

@PaulWhite in that plan, it's an aggregate of 4 streams (since my maxdop setting is 4) aggregated into one value-per-column. Would the 2nd aggregate be just running a traditional count(distinct on the output from the 4 stream aggregates? Do I have that nearly correct?

Paul White

25 mins ago, by Max Vernon

@MaxVernon Second aggregate?

Max Vernon

12:50 PM

the one closest to the SELECT, in the 2nd plan. The plan using the APPROX_COUNT_DISTINCT

but, this plan:

Paul White

No, the serial Stream Aggregate is computing expressions like:

[globalagg1006] = Scalar Operator(APPROX_COUNT_DISTINCT_MERGE([partialagg1005])), [globalagg1008] = Scalar Operator(APPROX_COUNT_DISTINCT_MERGE([partialagg1007])), [globalagg1010] = Scalar Operator(APPROX_COUNT_DISTINCT_MERGE([partialagg1009])), [globalagg1012] = Scalar Operator(APPROX_COUNT_DISTINCT_MERGE([partialagg1011]))

Max Vernon

that's under the 140 compat btw. Oh, APPROX_COUNT_DISTINCT_MERGE. interesting.

Paul White

Whereas the per-thread parallel partial aggregates are:

[partialagg1005] = Scalar Operator(APPROX_COUNT_DISTINCT_ACCUM([StackOverflow2013].[dbo].[Comments].[CreationDate] as [C].[CreationDate])), [partialagg1007] = Scalar Operator(APPROX_COUNT_DISTINCT_ACCUM([StackOverflow2013].[dbo].[Comments].[PostId] as [C].[PostId])), [partialagg1009] = Scalar Operator(APPROX_COUNT_DISTINCT_ACCUM([StackOverflow2013].[dbo].[Comments].[Score] as [C].[Score]))

HyperLogLog

HyperLogLog is an algorithm for the count-distinct problem, approximating the number of distinct elements in a multiset. Calculating the exact cardinality of a multiset requires an amount of memory proportional to the cardinality, which is impractical for very large data sets. Probabilistic cardinality estimators, such as the HyperLogLog algorithm, use significantly less memory than this, at the cost of obtaining only an approximation of the cardinality. The HyperLogLog algorithm is able to estimate cardinalities of > 109 with a typical accuracy (standard error) of 2%, using 1.5 kB of memor...

> The HyperLogLog has three main operations: add to add a new element to the set, count to obtain the cardinality of the set and merge to obtain the union of two sets.

Max Vernon

@PaulWhite 1.5 KB of memory lol

Paul White

Yeah it's pretty amazing maths

Max Vernon

1:02 PM

that's a dense wiki entry

(for me lol)

Cool that they split the algorithm across multiple operators in the plan.

ypercubeᵀᴹ

> Max Vernon has added Lamak to the list of this room's owners.
>
> billinkc
> Rookie mod
>. Who even elected this n00b

Paul White

1 hour ago, by Paul White

Yeah I know

Max Vernon

yah yah :-)

This is pretty awesome, I must say:

101

Q: Stack Overflow Extras (SOX)

SOX v2.7.0 Stack Overflow Extras (SOX) is a project that stemmed from the Stack Overflow Optional Features (SOOF) project. The SOX userscript adds a bunch of optional features to all sites in the Stack Exchange network. These can be toggled on or off from an easy to use control panel (see scree...

Paul White

1:32 PM

Oct 6 at 15:42, by Paul White

chat markdown ~~strikes~~ again

Paul White

1:56 PM

—

huh

24

A: How to type a dash (— or –) instead of a hyphen(-) in Stack Exchange?

The HTML entities – and — work on all systems: in the rendered post, they will be shown as – resp. —. On a Mac (and on iOS devices connected to a physical keyboard), you can use ⌥ + - for an en-dash and ⇧ Shift + ⌥ + - for an em-dash. (⌥ is the Option or Alt key). On iOS, you can ho...

Andriy M

—

Paul White

guess it doesn't work in chat

@AndriyM How did you get that? The hard way?

Andriy M

Alt+0151. Is that the hard way?

Paul White

Yeah, one of them

Andriy M

I just remember a few that I sometimes like or need to use

Paul White

2:03 PM

It's two or three hyphens in chat 2.0

tad more convenient

Andriy M

Nice, didn't know that, thanks

John aka hot2use

3:02 PM

ALT + 0153

ALT + 0169

Max Vernon

ALT + 0176 is a nice one for quoting American Weather System temperatures

aka Freedom Units

John aka hot2use

That's SHIFT + § on my keyboard. :-)

Or keep 0 pressed on an iPhone

Max Vernon

(and I suppose it works equally well for sane temperature units too)

@Johnakahot2use on my Android, I have the Hacker's Keyboard installed, and it's wonderful for things like that. But it doesn't support the ALT + xxxx methodology though, sadly.

John aka hot2use

I once had an Android until it got hacked.

Have to admit I had a 2G iPhone that I ~~jailbraked~~ jailbroke, that was then hacked by some Russian guys.

Max Vernon

interesting

👍

John aka hot2use

3:08 PM

yeah, that's the problem when you're on multiple sites.

Max Vernon

so confusing lol

it would be so great if everyone just supported <s></s>

John aka hot2use

It's CTRL + SHIFT + X on Slack.

😬

Be leaving in 5 to catch ~~Covid~~ the train.

Enjoy your morning(s) wherever you are. See you later in the morning.

@MaxVernon It's a start.

Max Vernon

have a good evening!

Max Vernon

4:05 PM

anyone have any thoughts about this:

0

Q: SSMS cannot determine if remote computer is connected to domain

I've recently set up a new server with an SQL instance running on it (2017), I am able to connect to the server remotely using SSMS, but when I try to add a new login, I receive the attached error. Both machines are on the same domain. I have already done some prior research and opened up port...

ssms azure active-directory domain

seems to be firewall related.

Josh Darnell

@PaulWhite 🤣

Transcript for

The Heap™ – Consultancy ©®