cnn.com/2021/11/29/americas/…

The humanity just won't stop trying to kill itself... Nuclear weapons, self-replicating biorobots, regular expressions, MongoDB...

@PaulWhite wow how do you find those gems.

RegEx

@PaulWhite ikr

And definitely not be the subject of it

Yes sorry about that

A chairde - Morning all!

Has anyone noticed that "Regular Expressions" and "Beelzebub's coming" have the same number of syllables?

Plus, if you express the ASCII as hexadecimal and take the inverse log of the square root, the answer is 666 - it's obvious that those who worship at the altar of regexes are the spawn of the Devil.

@mustaccio Now they just need to make them turing complete and install BSD.

@EvanCarroll - what about putting a CTE - WITH cte AS NOT MATERIALIZED (SELECT n/m FROM param? That way, there's a fence around the table creation? See here.

But what about the fact that in real life, CTEs may well be materialised, and there's nothing wrong with that - in fact it's normally a good thing?

However, the measurements appear (at least to me) to be "better"?

Good news re. our other benchmark. Last night I fired up a machine upon which I had split coffee (and it's been living on the radiator for the last 4/5 weeks) and it worked. Previously, not a peep! So, it's a good machine on which to do bms - 32 GB of RAM, SSD (both NVMe and SATA), so I'll fire up a 16GB VM and see how that goes!

* SELECT n/m FROM param - not that benchmark - the other one (1, 'bar$foo$john$doe$xxx');! - the remove last $ question!

@EvanCarroll I just wish there was a way of putting the output of SELECT statements in for part of the regex itself... wait, flash of inspiration... another way to waste my employer's money (errr, I mean my time...)...

@Vérace-getVACCINATEDNOW Materialization is something to avoid unless needed. It's never a good thing. It means you're writing to disk.

It means the entire query must be buffered on the server too.

abcnews.go.com/International/wireStory/…

It's funny. Postgres CTEs were always materialized, and people complained. SQL Server CTEs were never materialized, and people complained.

There is no upside to forcing materialization, even if you want an optimization fence.

@EvanCarroll Yes, but isn't our problem that we have to be testing like with like - query performance under the same circumstances? However, one could argue that a query which makes (clever?) use of materialization is just as valid a way of showing good performance? I think I'll go onto the IRC thing you mention and see if I can make sense of any of this imbroglio! I'm also doing an obfuscated SQL answer - it demonstrates a couple of interesting things what I found on d'interweb...

OK, so my solution is out!

Back to the drawing board...

So long as you query the functions in the same fashion you can expect the same result.

@EvanCarroll Well a smart DBMS can acquire statistics on the materialized set and maybe even build an index if it seems useful for subsequent processing.

@EvanCarroll Not sure I understand - I was doing the same thing for all, no?

No.

OK - well then what should be done?

I think that maybe hardcoding the lot might be a good idea?

That way we're just looking at the query and no disk issues are involved. We can't know the state of any given disk that may be running a given query, what the state of the PostgreSQL and/or kernel buffer(s) is/are and what other activity is happening on the machine. Or maybe I'll simulate a few million records and see how that goes?

this is your link, dbfiddle.uk/…

Mine was faster because it was using constant folding.

Can you point out where I was "borking" myself... if you'll pardon the expression? :-)

it was faster because of the constant folding, you should just disable that by making it select from the table like the others.

I'm SELECTing from the table - how to change this query so?

It's getting to be a forest for the trees scenario... I'm starting to lose focus - not to mention the will to live!

that one is fine.

EXPLAIN (ANALYZE, TIMING OFF)
SELECT
  GENERATE_SERIES(1, 1000000),
  REVERSE(
  (STRING_TO_ARRAY(REVERSE((SELECT t_field FROM test)), '$'))[1]
  || '-' ||
  STRING_AGG(elem, '$' ORDER BY num))
FROM
(
  SELECT
    t.id, x.elem, x.num
  FROM test t
  LEFT JOIN LATERAL
    UNNEST(STRING_TO_ARRAY(REVERSE(t_field), '$'))
    WITH ORDINALITY AS x (elem, num) ON TRUE
) AS t
WHERE num > 1;

That one does not follow the pattern of the others.

That's because it's cheating.

(obviously)

I gotta pack my house though =(

got movers coming in 5 hours.

Morning

@EvanCarroll Is the problem this bit: UNNEST(STRING_TO_ARRAY(REVERSE(t_field), '$'))

Can you put it is as it should be?

Best of luck with your move...

Morning

Morning

@Vérace-getVACCINATEDNOW as already mentioned in the chat, you should fix that answer.

Your fiddle did not use FROM test in Carroll's query so it appeared to be faster than the others.

You can leave the "relatively simple". I disagree but it's your claim.

If the fiddle is edited so all queries use the test table, then E's answer becomes the slowest: dbfiddle.uk/…

@PaulWhite nice one!

@ypercubeᵀᴹ Does EXPLAIN ANALYZE produce the one million rows, just not return them to the client somehow? The documentation wasn't clear to me on that.

@PaulWhite yes, it is executed and the additional info from the execution is returned, not only the plan.

but the actual result set is not returned

If the query/statement has side effects (insert/delete/updates) they are performed as well.

should be: -> additional info from the execution is returned (added to the plan)

Understood

is my comment right here?

Not sure if I got it right, the answer has got -2 already in a hour

@ypercubeᵀᴹ Yes. Also it seems not to detect deletions as required. One could take the maximum update time across all rows, but that could take a while on a big table unless helpful indexes exist everywhere.

@ypercubeᵀᴹ My main concern is how we can be sure (aside from execution time) that the server actually performed the same operations on the string one million times. An engine that could detect the operation was deterministic might only perform it once?

@PaulWhite yes, I was thinking the same route.

We'll have to check all those functions and their definitions

@Vérace-getVACCINATEDNOW Just for fun dbfiddle.uk/…

nice

Benchmarking is famously hard to get right

So many variables and things to account for

@ypercubeᵀᴹ How do you find this benchmark? If it's OK, I'll write it up - but it'll be the last edition of that answer... see my remarks about losing the will to live above... :-)

yes, saw it

And the benchmark?

Looks good

@Vérace-getVACCINATEDNOW I have a challenge query in the plans. Not sure when I'll post it, probably a week or two before Christmas

Although I'm hesitant to post at dba, as you know I've "stopped" posting.

We got some appreciation:

SQL Server help and feedback (Microsoft | SQL Docs)

@ypercubeᵀᴹ - indeed, I've noticed your disciplined refraining from commenting and/or posting on the site - I just wish that I had your iron will! I'm just going to put the benchmark at the beginning. I have stuff to do - I'll get round to it tonight... or tomorrow morning or... after the next Ice Age...

@ypercubeᵀᴹ Happy to make the question CW if that helps

@Vérace-getVACCINATEDNOW I could motivate you with a downvote ;)

but you have had a rough time the last days

@PaulWhite yeah, that's one option, thnx

It would make all the answers CW too, but we've done that before

or I could make the q CW after a certain period - and donate the points to an answer with a bounty

Users can't make questions CW

ah

@ypercubeᵀᴹ Be careful! The loss of two whole points could tip me over the edge - depression, alcoholism, suicide...

@Vérace-getVACCINATEDNOW Downvotes don't cost rep on CW answers

oh, the stress of being downvoted at dba.se

It's not a CW!

Oh I thought it was. Must be thinking of a different Q & A.

Indeed!

You have put so many CW answers lately, you managed to confuse Paul

Yep

@ypercubeᵀᴹ How hard is that? :-)

Not as difficult as it used to be

And guess who's responsible for ~ 95% of my CW answers? The man who "no longer answers questions on this site..."!

Morning

asking the collective knowledge of TheHeap. what's the highest RAM you can have on a laptop these days?

64, 128GB? More?

More. I'm guessing.

Look at the Lenovo Portable Workstation

Noap 128 GB

lenovo.com/us/en/p/laptops/thinkpad/thinkpadp/…

thnx

I trust if there was more ram on a laptop, Lenovo would offer in that line.

They make the best spec'd mass produced stuff

@ypercubeᵀᴹ - @EvanCarroll - I've finished that eff#$%-ing benchmark on the last $ in the string answer... I NEVER want to see it again!

haha

I ordered a 64GB one today - a pal's coming from the States for Christmas - basically, I get a lovely machine 1/2 price!

@Vérace-getVACCINATEDNOW which one? link?

@EvanCarroll - this lad here - and please don't tell me that you can get it for $25 in your local hardware store! :-)

@Vérace-getVACCINATEDNOW changed my temptation from downvoting to giving a bounty ;)

I did a lot of work - I have to say though, it's well worth while taking about an hour out for a walk to think about the approach... for anybody who's interested, there's a deadly talk by Rich Hickey (inventor of Clojure and Datomic) about "Hammock driven development".

@Vérace-getVACCINATEDNOW where is the link to the benchmark you finished?

I SHOULD WRITE dba.stackexchange.com/q/303245/2639 IN C.

;)

@EvanCarroll - sorry, you're on 4th place on the fiddle but 3rd in the results - will change!

What do you think of the laptop?

You already ordered it. You're supposed to ask that before you order it. lol

It can be cancelled!

I'm also super biased on laptops, the only manufacturer that isn't dog shit today is Lenovo and to be honest I'm not entirely thrilled with theirs either. But if you can cancel that I'll tell you a trick. ;)

@Vérace-getVACCINATEDNOW can you really cancel that?

Not sure - my pal let me know that he'd sent me the order for my perusal - but I'd checked out the stuff - maybe they have a same business-day cancellation or something? It's possible my pal is a gold member or whatever...?

Well, you should always ask first.

What about the spec? 2 NVMe (4th gen) slots, + a SATA - have a 1TB one spare!

Do this.

I think this laptop is all around better, do you agree?

lenovo.com/us/en/p/laptops/legion-laptops/legion-5-series/…

I wouldn't go gamer laptop, but Lenovo has a pricematch guarenttee. And if you call up and hit that you need 15.6" form factor, and Intel 11th Gen i7 - GeForce RTX 3050Ti; 16GB DDR4. I bet they give you price match that Legion against the ACER.

So you'll get a laptop that's normally actually $1,549.99 for $899.99

If you don't want a gamer laptop, you can usually find a really good Sony VAIO or MSI and have them pricematch you into the thinkpad line. But it'll take an hour of work.

I got this laptop like 60% off. ;)

the kicker with the gamer laptops is you pay a lot for the GeForce RTX stuff because of the chip shortage.

How did you get the discount?

and they drink your battery life, but if you need the 3d accel well that's you're only option shy of a T-series or P-series.

@Vérace-getVACCINATEDNOW call up and ask.

I've looked here - seems OK-ish, but hardly stellar - for the same price more or less, the spec is a tiny bit inferior!

You need BOTH the URL (or a method to get to the laptop you're matching to) and the ITEM number of the Lenovo product you want matched.

I don't have the option to chat - and my friend knows nothing, but hey, good for you!

I don't know if they match custom orders. But they should offer you LEGION 82JK0002US against the ACER one you dropped the link to.