stop the merry-go-round

@ErikDarling The previous link was to an Adam Machanic post on sqlblog.com which has stopped working. I replaced it with a working link. While I was there, I made the post prettier. All this in response to a flag from another user.

Easier to see in side-by-side markdown mode

Erik Darling

3:09 AM

Yeah who can find that

Paul White

4:34 AM

Only experts

jadarnel27

12:43 PM

> I have a 1 TB string of digits

😱

Paul White

12:59 PM

Some possible context:

5

Q: Longest string with all unique substrings

All substrings of length 2 of the binary string $1011$ are unique, as well as all 2-substrings of $10011$. 5 seems to be the longest possible string with all unique 2-substring. $000101100$ is a string where all 3-substring are unique Given an alphabet A with $|A|=n$ and a substring-length m wh...

(same author in case it's not immediately apparent)

There's something very scary about professional mathemeticians

Research level I mean

jadarnel27

Wow, mathjax in comments. It's like art.

@PaulWhite yeah, I agree. Their minds have to go to some crazy places.

Paul White

1:14 PM

Well there is that too

jadarnel27

2

Q: Fastest way to split/store a long string for charindex function

I have a 1 TB string of digits. Given a 12-character sequence of digits I want to get the start-position of this sequence in the original string (charindex function). I have tested this with a 1GB string and a 9-digit-substring using SQL Server, storing the string as a varchar(max). Charindex ta...

sql-server sql-server-2017 string-searching physical-design string-splitting

(for posterity)

I wonder what the application for that research is.

Probably something to do with biology.

Paul White

I thought that too. Genes and whatnot.

> Working at PRIMITIVE - Private Institute of Mathematics and Information Technology in a Virtual Environment

Ah so it is another VM problem

jadarnel27

LOL

Paul White

To clarify my earlier remark: I find it scary in the same way I would find it scary if a Nobel Prize winner were to ask a SQL question. There's almost no answer we could give that wouldn't seem desperately unworthy.

Anyone dealing with 1TB of digits has to be into some pretty serious shit.

You'd think they'd give them a bit more than 16GB RAM.

jadarnel27

I actually laughed out loud at that VM problem message while feeding my 1 y/o breakfast. She then laughed at me laughing. It's a pretty good morning in the Darnell household.

Paul White

1:20 PM

sweet

Though I can't imagine a year-old breakfast tastes very nice

jadarnel27

Well I brought that on myself I suppose.

Paul White

little bit

jadarnel27

@PaulWhite It's like you asking any of the rest of us a SQL question, really.

😁

Paul White

I was thinking more of Richard P. Feynman popping in.

jadarnel27

1:38 PM

My statement stands.

Also there are too many xkcd comics to choose from for that.

Erik Darling

2:14 PM

@PaulWhite > 8GB per core would be a much better starting point, with more desirable

Not sure where you were going with that or I'd edit

Paul White

@ErikDarling 8GB per core with 16 cores would be 128GB RAM

Erik Darling

Someone got all nervous typing to the math wiz

Yeah I get the math but you didn't finish the sentence

Paul White

Well that was the end of the thought

Erik Darling

@_@

Paul White

With more (than 8GB per core) desirable.

Erik Darling

2:18 PM

Oh I see

Paul White

I'm not at all sure the whole thing's going to work out well anyway.

Erik Darling

Doesn't sound like a good fit for SQL Server.

Paul White

2:39 PM

1TB is certainly a lot of numbers

Joe Obbish

3:00 PM

where's the repro to create the 1 TB string?

VTC

Erik Darling

3:17 PM

Harsh

I'm sure you can come up with one

Joe Obbish

3:30 PM

I think I got a good answer

Erik Darling

Does it involve a Heap?

Joe Obbish

that's an option that I'll probably rule out

Erik Darling

Does it involve 1tb of RAM?

Joe Obbish

no

I seem to get results in 0 ms though

22 TB of disk space

eh

Erik Darling

3:54 PM

what are you on about now

how did you make a 1tb string 22x worse

Joe Obbish

did you read Paul's answer?

Erik Darling

yes and no

Joe Obbish

Paul's answer calls for 1000000000000 - 11 rows to be inserted into a table

that's how you make a 1 TB string worse

Erik Darling

doesn't sound like a very good answer

someone should have a talk with him about databases

Joe Obbish

question asks for fast

like I said, I can get 0 ms with 22 TB of storage

that's fast

Erik Darling

4:06 PM

is it 22tb with the archival compression?

Joe Obbish

12.3 TB

Erik Darling

4:47 PM

that seems a bit less oppressive

Joe Obbish

I don't know about that

Erik Darling

how not?

jadarnel27

5:16 PM

waits for Joe's answer

Joe Obbish

it's almost done

jadarnel27

Oh nice. I saw your comment on the q and thought you'd reconsidered answering.

Erik Darling

this isn't a bad idea

1

Q: Vote to accept answer?

There are a number of great answers out there that have never been accepted as the answer for whatever reason. Would a new feature that allows high-reputation users to vote to accept the answer after a certain time period has elapsed be helpful? I'm not going to argue for any specific reputat...

feature-request

Joe Obbish

@jadarnel27 up

columnstore isn't that great for compressing unique integers

that's the main issue here

the columnstore table is almost impossible to populate without delta store activity

which means that the rowstore table probably takes up less space practically

jadarnel27

5:37 PM

@ErikDarling pretty sure that's a dupe of something on MSE. Haven't looked yet though.

@JoeObbish Very cool idea.

Joe Obbish

thank you

I prefer to store my data in the table names

Erik Darling

is that the database version of writing your email in the subject line?

Joe Obbish

is sql server even the right tool for what he wants to do?

Erik Darling

well

i don't think database choice matters a lot if you're only thinking of it as data storage

but perhaps the actual search should be done elsewhere

elasticsearch might be a better choice, now that i think about it

Joe Obbish

for example?

does elasticsearch store stuff in ram?

Erik Darling

5:46 PM

see above

as long as you have ram

:D

Joe Obbish

so you're screwed for this

there's probably some annoying postgres answer where you create your own data type

Erik Darling

it's a big data problem

Joe Obbish

for u

I have no idea how he's going to populate the tables that we're suggesting

if you gave me flat files and 96 cores I could probably do it

no clue how long it would take

it's easy to write code that would take forever

would he do a trillion substring calls?

Erik Darling

he could make a numbers table with all the split positions

or something

Joe Obbish

a trillion row numbers table?

Erik Darling

5:58 PM

yeah, why not?

Joe Obbish

how do you create that? that's also > 10 TB?

probably the way to do it is to use in memory OLTP as shock absorbing tables

if you can insert 4k rows per second per core then you're done in a month on a 96 core server

I think I'd ask for 1k flat files and start from there

Erik Darling

row compression might help

but yeah populating it would be painful

Joe Obbish

I wonder if you can get anywhere with native procedures

pass in 1k characters at a time or something to the procedure

Erik Darling

ask ned

Joe Obbish

no thanks

that would be fun code to write

a 1 GB string is sufficient as a test, I think

could just split the 1 TB string up into 1000 flat files

Erik Darling

6:07 PM

that's a lot of copy and paste

Joe Obbish

my question is "Given a 1 GB string in one row of one table, what's the fastest way to populate T0000_Q229892_PAGE_COMPRESSION through T9999_Q229892_PAGE_COMPRESSION?"

that's only 20 GB of data split out

Erik Darling

ssis

Joe Obbish

this guy

Erik Darling

though this is where a smaller numbers table might make sense

Joe Obbish

TALLY OH

I have no idea how to create a 1 GB string in sql server

Erik Darling

6:12 PM

loop

Joe Obbish

doesn't scale

maybe I need the wacky .WRITE syntax

DECLARE @append_count INT = 0,
@big_string VARCHAR(MAX) = CAST('' AS VARCHAR(MAX))

SET NOCOUNT ON;
WHILE @append_count < 10000
BEGIN
	SET @big_string = @big_string + CAST(ABS(CHECKSUM(NEWID())) AS VARCHAR(10));

	SET @append_count = @append_count + 1;
END;

Erik Darling

why does it have to scale?

you're just doing it once to populate a row?

Joe Obbish

doesn't scale with length

that code takes < 1 s

if I go to 100k then I don't know how long it takes

it's much worse than 10 s

I'll let it run for a bit

108 s for 100k

< 1 s for 10k

that's no good

especially because I need to go up to 100 M

yeah

can do 100k in < 1 s with .WRITE

Erik Darling

CREATE TABLE #append
(
    id INT IDENTITY NOT NULL PRIMARY KEY,
    a VARCHAR(10) NOT NULL
);

INSERT #append (a)
SELECT TOP 10000
CAST(ABS(CHECKSUM(NEWID())) AS VARCHAR(10))
FROM sys.messages AS m

DECLARE @append_count INT = 1,
@big_string VARCHAR(MAX) = CAST('' AS VARCHAR(MAX))

SET NOCOUNT ON;
WHILE @append_count < 10001
BEGIN
	SELECT @big_string += a
    FROM #append AS a
    WHERE a.id = @append_count;

	SET @append_count = @append_count + 1;
END;

SELECT @big_string

oh yeah, that sucks for 100k rows

i wonder if there's something useful for xml here

Joe Obbish

I think I can get it into the table in under 100 s

Erik Darling

6:25 PM

1:57 for 100k, hahaha

Joe Obbish

12 mins ago, by Erik Darling

why does it have to scale?

THAT'S WHY

looks like my time estimate was wrong

string length of 1001371946 in 116 seconds

😎😎😎😎😎

sql server does some interesting things with IO here

CHECKPOINT;
DBCC DROPCLEANBUFFERS;

set statistics io on;
SELECT SUBSTRING(FOR_U, 500000000, 1000)
FROM dbo.BIG_STRING;

that's 4 logical reads

Erik Darling

how many lob reads?

Joe Obbish

Table 'BIG_STRING'. Scan count 1, logical reads 1, physical reads 1, read-ahead reads 0, lob logical reads 3, lob physical reads 3, lob read-ahead reads 0.

Erik Darling

me: i'm gonna have a productive day of rehearsing for sqlbits and grab an early dinner

joe: starts talking

Joe Obbish

you want the code to create the table?

it's OP's fault

yeah this can be done

I can grab 10k consecutive bigints from the middle of the table and throw them into a BIGINT temp table in 68 ms

Erik Darling

6:36 PM

nice

Joe Obbish

wait, this plan makes no sense

ok I was being dumb

3 ms for 6k strings

I can do this without in memory OLTP

Erik Darling

most people can

Joe Obbish

can... what?

10 ms to throw 6k rows into this table:

CREATE TABLE #t(
TABLE_ROUTING VARCHAR(4),
STRING_PIECE_AS_INT INT,
TABLE_POS BIGINT
);
CREATE CLUSTERED INDEX CI ON #t (TABLE_ROUTING);

Erik Darling

do things without in memory oltp

i'd say it's the default state of the world

Joe Obbish

it's attitudes like that which prevent in memory oltp from taking its rightful place as king of all technologies

Erik Darling

6:53 PM

lead the people, joe

Joe Obbish

the problem is latch contention

2 oltp 4 me

Erik Darling

you say that about everything

Joe Obbish

that's because if your problem isn't latch contention then you aren't trying hard enough

my first attempt would be to load 7889 rows into a temp table indexed on the target table

then to pick a random number between 0-9999 and insert into target tables in sequential order

so a cursor

Erik Darling

why 7889?

Joe Obbish

and inserting 1-2 rows at a time

@ErikDarling 8k string split into 12 character pieces

might by a of by one error there

Erik Darling

7:01 PM

gotcha

Joe Obbish

if that was too slow I'd try something else

maybe go up to a million characters at a time, still parse the strings so you avoid VARCHAR(MAX) as much as possible, then insert on average 100 rows at a time into the target tables

Erik Darling

this photo has such an odd tone

Paul Kienitz, Napa, CA

106 2

csv import sql-server

Joe Obbish

that guy is going to make you wish you had insurance

Erik Darling

i do have insurance specifically because of him

Joe Obbish

I wonder how much I'd know about the internals of this stuff if I processed strings for a living

I think it's a really interesting problem, shame it requires so much hardware

Erik Darling

7:08 PM

string processing?

Joe Obbish

1 hour ago, by Joe Obbish

my question is "Given a 1 GB string in one row of one table, what's the fastest way to populate T0000_Q229892_PAGE_COMPRESSION through T9999_Q229892_PAGE_COMPRESSION?"

Erik Darling

1 hour ago, by Erik Darling

ssis

Joe Obbish

incorrect

Erik Darling

in memory ssis

Joe Obbish

coming CTP 2.3

NYC heating up

does 50 cent have the greatest street cred of all time, or what?

"NYPD Officer Tells Fellow Cops Shoot 50 Cent on Sight"

Erik Darling

7:17 PM

holy shit he got weird looking

tmz.com/2019/02/17/…

Joe Obbish

this 12 seconds says it all:

www.youtube.com/watch?v=5D3crqpClPY&feature=youtu.be&t=233

Erik Darling

how did you manage to post a link as not a link?

Joe Obbish

got skills you can't even comprehend

Erik Darling

7:33 PM

this is clearly not for me

Joe Obbish

what did weiner do now?

Erik Darling

got out of jail

i was wondering why i stopped hearing about jussie smollett

guess that's why

what a fucked up thing to lie about

I applied cumulative update 13 and the error message did not change. :( — Paul Kienitz 8 mins ago

REEEEEEEEEEEEEEEEEEEEEEEEEEEEE EEEEEEEEEEEEEEEE EEEEEEEEEEEEEE

EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE

just for good measure

welp

plus.codes

Joe Obbish

8:43 PM

it's so horribly slow

78 ms to do everything but the singleton inserts

4.6 seconds to insert 7989 rows across 10k tables

1 s to insert 7989 rows into the same table

Erik Darling

i thought you went to watch anime

Joe Obbish

1.2 s to insert across 1k tables

4s to insert across 10k tables

-_-

page compression has something to do with it

there's a lot of variance

Erik Darling

why is that bad?

you should be happy it doesn't take 1.2 * 10k

Joe Obbish

I'm inserting the same number of rows

testing with empty tables isn't a good idea

I'll try inserting a million rows

2 minutes to insert 1006614 rows across 10k tables

looked like WRITELOG was the biggest wait

with... 1 concurrent process

Erik Darling

9:04 PM

are your logs minimal?

Joe Obbish

singleton inserts

probably shittons of plage splits

Joe Obbish

9:39 PM

why does this code have 15 minutes of ASYNC_NETWORK_IO?

DROP TABLE IF EXISTS dbo.BIG_STRING;

CREATE TABLE dbo.BIG_STRING (FOR_U VARCHAR(MAX));
INSERT INTO BIG_STRING VALUES ('');

DECLARE @outer_loop INT = 0,
@append_count INT = 0,
@big_string VARCHAR(8000);

SET NOCOUNT ON;

WHILE @outer_loop < 132000
BEGIN
	SET @big_string = '';
	SET @append_count = 0;

	WHILE @append_count < 800
	BEGIN
		SET @big_string = @big_string + CAST(ABS(CHECKSUM(NEWID())) AS VARCHAR(10));

		SET @append_count = @append_count + 1;
	END;
	UPDATE dbo.BIG_STRING SET FOR_U.WRITE(@big_string, NULL, NULL);

Erik Darling

send table def

Joe Obbish

I already did

Erik Darling

...

be a pal

don't make me scroll

Joe Obbish

CREATE TABLE dbo.BIG_STRING (FOR_U VARCHAR(MAX));

this is so dumb

I connected to the VM with RDP

opened SSMS there

it's about 5X faster

what the hell does it have to send to the client?

yeah

04:12 when run in the VM

Erik Darling

1:30 total run time

Joe Obbish

9:52 PM

why is there async wait time at all though?

I'm pretty sure what's happening is that it does a ton of very short round trips

and this VM adds a lot of lag to each one

on my local computer or within the VM guest OS it's fine

maybe I should just ask a question

Erik Darling

maybe it's something to do with the loop?

Joe Obbish

I'll MCVE it

in other news, the code to load to the tables doesn't seem practical

I have a 1:2 CPU time to WRITELOG ratio

for one session on a 48 core server

Erik Darling

that seems odd

is it on cpu0?

Joe Obbish

no

finished after 11 minutes

379869 ms of WRITELOG wait

Erik Darling

i hope someday i can hire you

Joe Obbish

10:01 PM

I don't seem like a very good hire

I find problems and can't solve them

I guess you could say "run the code in SSMS in the VM" is solving it

but...

Joe Obbish

10:19 PM

anyway, at the current rate (assuming perfect scaling) it would only take 80 days on a 96 core server

Erik Darling

10:40 PM

dumb idea

try running it from an agent job

Joe Obbish

I had a similar thought. that would probably fix it too

Erik Darling

👍

Joe Obbish

have an MCVE

problem is people might start asking me about the VM

you get some ASYNC waits for this code, right?

DECLARE @outer_loop INT = 0,
@append_count INT = 0,
@big_string VARCHAR(8000);

SET NOCOUNT ON;

WHILE @outer_loop < 10000
BEGIN
	SET @big_string = '';
	SET @append_count = 0;

	WHILE @append_count < 800
	BEGIN
		SET @big_string = @big_string + CAST(ABS(CHECKSUM(NEWID())) AS VARCHAR(10));
		SET @append_count = @append_count + 1;
	END;

	SET @outer_loop = @outer_loop + 1;
END;

Erik Darling

I'm not home right now, but it looks enough like the last batch

Joe Obbish

yeah, just got rid of the DML

huh

I just realized my approach makes no sense

Erik Darling

11:03 PM

Why?

Joe Obbish

REEEEEEEEEEEEEEEEEEEEEEEEEEEEEE

EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEe

I thought I was being slick

how the hell is that the bottleneck

this would be like a week long project to get right

my answer is useless for that guy

I turned query store to read only

now 34% of CPU is allocating IAM pages

it did go from 2:51 to 2:13 by switching query store to read only

which is pretty amazing

Forrest

11:20 PM

What is this madness?

Joe Obbish

you'll need to scroll for a while

was trying to figure out how to practically implement my answer

4

A: Fastest way to split/store a long string for charindex function

I recommend using a flavor of method 2 and splitting the search range into many target tables. 10000 tables is a good first attempt. For example, if you search for "012345678901" then your query would look at the table associated with data that starts with "0123". You would still have about a tri...