The Heap™ – Consultancy ©® - 2017-11-28 (page 1 of 2)

Joe Obbish

3:23 AM

@sp_BlitzErik both!

a friend tells me there are 25k tables

Joe Obbish

5:38 AM

3000 words later: orderbyselectnull.com/2017/11/28/containment-for-the-common-man

Chris Travers

5:49 AM

@EvanCarroll We do fun things like write foreign data wrappers for things like Kafka, and I am currently writing a distributed transaction coordinator that plugs into the back end for managing data copied between pg versions.

@dezso In Berlin.

So a lot of things require both a knowledge of C and an understanding of Pg internals and in particular memory management.

hot2use

Morning or evening :-)

@ChrisTravers up early

Chris Travers

yeah up early :-)

hot2use

:-) Must be just one of those days. Got up at 5 and sitting in office now.

Chris Travers

@deszo, I think our office is a block from your office, if you are at the Prenzlauerberg Allee office.

(we are at the Backfabriken)

You work with Oleksii?

@Evan

@EvanCarroll, we also write our own data types in C but most of those are simple except for istore.

Andriy M

7:06 AM

@ChrisTravers Didn't know iStore was actually a custom data type in Postgres ;)

Morning

hot2use

7:29 AM

O.k. hands up: Who created the homework tag?

ypercubeᵀᴹ

7:53 AM

@hot2use dba.stackexchange.com/review/suggested-edits/98316

hot2use

8:05 AM

Do we need a homework tag? Isn't that a precursor to VtC as too localized/too basic?

Andy K

8:25 AM

evening and morning

hot2use

Found an interesting repository on Github: BurntSushi/erd

> Translates a plain text description of a relational database schema to a graphical entity-relationship diagram.

example pdf

@ypercubeᵀᴹ Hmm. Read How do I ask and answer homework questions? (Meta.SO) and Is DBA a place for homework questions? (DBA.meta). Agree, but do we need the tag?

Chris Travers

8:41 AM

@AndriyM We wrote istore as an integer equivalent to hstore with arithmetic support.

McNets

morning

dezso

@ChrisTravers yeah, with Oleksii. We moved to another office, though. What you say sounds interesting otherwise, too bad I have no idea about C.

@hot2use we don't, and no, it's not, respectively

Chris Travers

@dezso we are looking to fill two positions. One doesn't need C knowledge to my knowledge actually.

dezso

any good question is a good question, even if it is homework. On the other hand, the tag is really useless (and we (well, we) cleaned it up once.

@ypercubeᵀᴹ aha. The user profile still says Jakarta :D

hot2use

@dezso Agree on the questions being good if they are asked in a professional manner, and thanks that you cleaned up once.

spooky repeating patterns: knowledge is knowledge, good is good, create index to create index

Chris Travers

10:08 AM

@dezso I need to fix that

Fixed

And by the way our data environment is 400TB for our primary analytics environment in PostgreSQL.

Andriy M

@ChrisTravers I knew it would turn out to be something as mundane and boring as that. The real-world iStore as a Postgres datatype would seem much more fun :) Oh well, can't have it all

Chris Travers

10:24 AM

@AndriyM Just remember how it is to name things. If I have a function called fmterror, is that fmt error? or fm terror?

Andriy M

10:35 AM

@ChrisTravers The former is more likely, of course, but even in that case I guess nothing prevents you from amusing yourself by imagining the latter :)

Chris Travers

@AndriyM If it segfaults does it suddenly become the latter?

Andriy M

Ha, indeed, so you don't even need to imagine things

Chris Travers

@dezso by the way, programming in C is actually fairly simple: 1. you think about your data structures. 2. You think about your memory allocation. 3. you test/debug your code. The steps I have skipped over are largely insignificant in terms of time and effort.

Chris Travers

11:01 AM

(I find writing C code takes less time than writing Python or Perl code, but mostly because thinking about C code takes more time.)

gbn

@ChrisTravers "the sooner you start coding, the later you'll finish"

7

community.spiceworks.com/topic/… others

Chris Travers

Yeah. I wrote a two phase commit module for one of our Pg extensions in C in basically a day (200 lines of C code). It took me about 2 hours to debug and fix it the next day and most of that was makefile framework stuff.

Next I am writing a background worker to handle what happens if the remote connections cannot be cleanly resolved during transaction commit. I will probably do that this week. But not rushing it.

I don't get to code today, which may be a good thing for this project.

hot2use

11:51 AM

> There is no such thing as scope creep, only scope gallop

> The nice thing about not planning is that failure comes as a complete surprise rather than being preceded by a period of worry and depression.

Hilarious.

@gbn Made my day

gbn

@hot2use we can all relate to most points there

hot2use

12:08 PM

(graphical version for windows users)

Chris Travers

12:53 PM

@hot2use If only we managed scope of requirements the same way we manage scope of variables.

Tom V

@ChrisTravers We would have global variables everywhere?

Chris Travers

@TomV Or we would have well managed expectations. One or the two.

*of the two

hot2use

1:31 PM

@ChrisTravers True, but you don't see a lot of "project managers | stakeholders" wanting to do proper requirements engineering

Tom V

@hot2use "We'll do agile"

gbn

@hot2use The big banks in Zurich have Requirement Engineers for that very purpose. Especially in private/retail banking

hot2use

Hmmm. That's why I have to install 200+ applications a week because they have "improved performance".

This whole App business is way too agile IMHO.

Crap in = Crap out

gbn

@hot2use what kind of firm do you work for? 200 per week?

hot2use

FIFO = Fast In Fast Out

gbn

1:34 PM

Fit In or Fack off

hot2use

Oh I'm generalising. It's when I open up my iPhone or iPad to download updates.

gbn

Therein is your problem. An iThingy

hot2use

Just gets on my nipples

Android is the same.

Andy K

2:04 PM

2

ConcernedOfTunbridgeWells

2:23 PM

@TomV Ah, yes - the 'We don't need to do requirements' school of Agile project management.

Andriy M

The new Review top bar icon looks much better than the previous one.

Lamak

@AndriyM how did you notice the change so fast?

and yes, quite better

dezso

@Lamak still should show the queues on hover, not on click

Andriy M

@Lamak If it was very recent, then I guess I was lucky to refresh the page at the right moment.

ConcernedOfTunbridgeWells

@gbn A little risk management saves a lot of fan cleaning.

Lamak

2:31 PM

@dezso you think so?

I don't really like auto expanding things on hover

McNets

SET @nomFitxer = N'DELFOR_FIRME_COPRECI_20171127_143701.xml';
SET @pathFitxer = N'C:\Copreci\' + @nomFitxer;

SELECT @xmlData = BulkColumn
FROM OPENROWSET(BULK @pathFitxer, SINGLE_BLOB) x;

Is there a way to use a variable with BULK?

I get a Syntax error

dynamic SQL

@Lamak thanks

dynamic Lamak

thanks

2:39 PM

dynamic thanks?

SQL

or Lamak

yeah, exactly

4:10 PM

I wonder if this is part of where the (now obsolete) 'page life expectancy should be >300 seconds' guideline for SQL Server started

sp_BlitzErik

i think it came from an old internals book

2005 or 2008 maybe

Andy K

cya

James L

4:30 PM

@sp_BlitzErik I think I remember hearing it before 2005, but don't remember for sure.

Some of the Wikipedia content about data modeling is really bad

"These databases are more scalable and easy to query with as they do not involve expensive operations like JOIN."

2

sp_BlitzErik

maybe, but it persisted until... well... i suppose it still does

Lamak

@sp_BlitzErik you can still remember those years?, thankfully the booze hasn't completely destroyed it

sp_BlitzErik

@Lamak i can only remember things about sql

Lamak

that's....sad

sp_BlitzErik

memory optimized

James L

4:33 PM

my earliest memory is the slammer worm

sp_BlitzErik

is that from jail or from tequila?

good question

both?

both and this

yes. i remember that well

Lamak

4:36 PM

what well?

swasheck

the sql slammer worm

Lamak

ah, yeah....I mean...

swasheck

speak clearly and directly. i'm old (just turned 40)

sp_BlitzErik

happy halfway to death

swasheck

depends on if my liver has a say.

sp_BlitzErik

4:42 PM

lots of people in this chat don't drink, we should start harvesting them

Lamak

lots?

swasheck

great idea.

a plethora, even

sp_BlitzErik

@Lamak you're not getting my donor list

Lamak

well, I don't drink

swasheck

@sp_BlitzErik that statement deserves qualification
"lots of people in this chat don't drink (... as much as we do ...), ..."

Lamak

4:44 PM

but I'm taking myself out of your list for that comment

sp_BlitzErik

no backsies

Lamak

was there a givesy?

swasheck

it was implied. TOS of The Heap

sp_BlitzErik

"there may someday be a takesy of whichever organ another member of the heap deems necessary"

Lamak

I always forget about that

sp_BlitzErik

4:51 PM

thankfully i have nothing to worry about because all my innards are useless

swasheck

i need a good and interesting data set. i need a large one that would be "batchy" and i need another that would be more transactional. the stackexchange set is not quite what i'm looking for

sp_BlitzErik

there are a bunch here

opendata.cityofnewyork.us

the turnstile data might be okay for your batchy one

also this: data.gov

Joe Obbish

confirmed that JOINs are slow and that you shouldn't do them

swasheck

5:09 PM

thanks @sp_BlitzErik

Joe Obbish

5:38 PM

I got a really dumb question on foreign keys

just looking for confirmation on the answer

sp_BlitzErik

go on

Chris Travers

5:51 PM

@TomV But Agile only works when you are starting on a solid foundation.

Joe Obbish

@sp_BlitzErik so I got my fact table

with a PersonKey column or something

that points to PersonDim

and I define a foreign key

I need an index on PersonKey on PersonDim

that seems reasonable

sp_BlitzErik

glad you think so

Joe Obbish

but an index on the fact table has nothing to do with the foreign key, right?

unless I wanted to delete rows from the dimension table?

sp_BlitzErik

what do you mean 'has nothing to do with'?

Joe Obbish

start with "not required"

move to "is it a good idea"

or rather, what do normal people do?

sp_BlitzErik

5:57 PM

well, personkey in your fact table would have to be a candidate key column in a pk or unique constraint anyway, no?

or do i have your relationship backwards?

Joe Obbish

I'm too dumb to understand that

I require that every PersonKey value in the fact table have a matching row in PersonDim

but not the other way around

sp_BlitzErik

correct

Joe Obbish

so an index on the fact table wouldn't be required?

sp_BlitzErik

do me a favor? pastebin your script to set this up.

Joe Obbish

writing the script was going to be the next step

sp_BlitzErik

6:01 PM

you don't need to populate the tables

Joe Obbish

this feels like a trap

sp_BlitzErik

valid

Joe Obbish

oh

I didn't think about the CASCADE stuff at all

sp_BlitzErik

careful with them there cascades

Joe Obbish

I don't need any cascading

sp_BlitzErik

6:02 PM

dandy

Joe Obbish

@sp_BlitzErik pastebin.com/UkhpKngx

I think that's correct

Tom V

@JoeObbish are you only processing and/or reporting on that design or are you looking for incremental loads/updates /deletes in your dwh?

Joe Obbish

I would like to have the foreign keys exist during ETL

sp_BlitzErik

dimperson is an outlandishly good table name for you

5

Tom V

Or aren't the fact and dim used as the basis of a cube?

@JoeObbish too broad

Joe Obbish

6:09 PM

no cubes

cubes are the worst

Tom V

Why? Indexes are often disabled during load

Joe Obbish

@TomV will do incremental updates

sp_BlitzErik

on a cci?

madman

Joe Obbish

@sp_BlitzErik don't get me started

@TomV elaborate on "often"?

didn't MS make foreign key stuff way more efficient in 2016?

Tom V

When bulk loading foreign keys are often disabled

sp_BlitzErik

6:11 PM

yeh when you use bulk insert you have to tell it to use triggers iirc

wait no

now i have to look

Tom V

Depends on the order you are loading and things you are updating obviously hence the question 'why'

Joe Obbish

not using bulk insert on fact tables

the dims get updated first

nothing is "obvious" here, I'm really dim on this subject

sp_BlitzErik

i dunno why i thought about triggers when we're talking about FKs

oh well

Joe Obbish

@sp_BlitzErik the tables are in the same database

sp_BlitzErik

anyway, this all sounds bad, and you should hire someone to straighten you people out

Joe Obbish

6:13 PM

you can see that in the pastebin you asked for

@sp_BlitzErik what is "this"?

updating the dims first is bad? why?

sp_BlitzErik

i don't care about that part

Joe Obbish

are you building suspense?

Tom V

@JoeObbish hire a professional

Joe Obbish

that's not up to me

Lamak

@JoeObbish exactly

Joe Obbish

6:20 PM

I try to keep things like this very simple

sp_BlitzErik

no, i'm working and feeling smug about my new monitor setup

Joe Obbish

I figure that since I've answered 250 questions here that I'm entitled to the occasional dumb question

if you don't want to help, fine, but don't activity non-help

Lamak

sounds like a good ratio

sp_BlitzErik

i'm not sure what your question is anymore

Lamak

@sp_BlitzErik he doesn't need an index on that fact table

Joe Obbish

6:23 PM

if you don't know what the question is then it's even more ridiculous to suggest hiring a professional

sp_BlitzErik

no, you don't need it, but doesn't that help with join elimination?

from the last time i messed with it i seem to recall that the constraint on a non-nullable column wasn't quite enough

but with an index where the fk was the leading key i got join elimination

Lamak

it should, but I thought the question was about needing not needing

sp_BlitzErik

well, need for what?

shrug

Lamak

@sp_BlitzErik I read that question as "need it" to make the whole foreign key work

Tom V

@JoeObbish I probably wouldn't create the index on the fact table unless you need it to delete stuff from the fact table ie when loading incremental

sp_BlitzErik

6:28 PM

@Lamak yeah no, you don't need an index just to have a foreign key, but you may need it for additional optimizer goodies

Tom V

But my experience in that setup is mostly cube related not for direct queries

Joe Obbish

well

the implementation of this sucks

it's not optimized for CCIs

"this" meaning making sure that the constraint is valid when inserting data into the fact table

Tom V

But surely you are smart enough to understand what the possible impact (both positive and negative) could be

Lamak

@JoeObbish ah, yeah, that's true

that sucks

Joe Obbish

@TomV On this? no

Tom V

6:32 PM

@JoeObbish unless the source system already validated that and you could be fairly sure creating the key after loading won't fail

Joe Obbish

on this I'm in a bubble, so I'm trying to break out of that bubble

if I need to do a bunch of reading instead that's completely reasonable feedback

only a mother could love that plan

Tom V

I still don't see what the end result should be

sp_BlitzErik

that sort >_>

Joe Obbish

@TomV end result of what?

Tom V

You have a fact and some dims

And you're going to run ad hoc queries then relying on CCI?

Joe Obbish

6:36 PM

yes

oh I think I need that WITH CHECK CHECK CHECK thing

sp_BlitzErik

did sp_blitz warn you about untrusted foreign keys?

:D

Joe Obbish

no, but I created the constraint in 0 s

Tom V

Exactly. That's what I meant when saying 'if you can be reasonably confident it won't fail'

Joe Obbish

query optimizer won't use untrusted FKs, right?

sp_BlitzErik

correctamundo

Tom V

6:38 PM

got me a real keyboard

Lamak

@JoeObbish exactly

Tom V

@JoeObbish what is your main concern? query time or loading time?

sp_BlitzErik

it would be like create maybe kinda unique constaint uq_u

Joe Obbish

@TomV my concern is that we removed all foreign keys

now we no longer have any

I'm seeing some really unfortunate things happen to query plans

Tom V

@JoeObbish Ah I see

in an existing solution?

Joe Obbish

6:40 PM

yeah

ok, so the foreign key is marked as trusted

it took 0 seconds to do so

Chris Travers

@JackDouglas How have you been?

Joe Obbish

that's, uh, surprising

Lamak

@JoeObbish when did you remove the fks?

Jack Douglas

@ChrisTravers Good thanks — you've moved to Europe, is that right?

Joe Obbish

@Lamak it wasn't me personally

sp_BlitzErik

6:41 PM

were there any modifications between when you disabled and enabled?

Joe Obbish

a year ago?

sp_BlitzErik

wonder if it looks at rowmodctr

Lamak

@JoeObbish it was a "you guys" you

Tom V

informatioin sips through

Joe Obbish

@sp_BlitzErik I created the table, added the constraint, then did the check constraint thing

Chris Travers

6:42 PM

@JackDouglas Yeah. Doing stuff with big databases. First in life sciences (12 TB) and now in ad-tech (herds of 20TB Pg dis)

*dbs

Jack Douglas

That doesn't sound like LedgerSMB, is this a new job too?

sp_BlitzErik

20tb. i've got that much ram in my light up sneakers.

Chris Travers

@JackDouglas yeah. but still doing LedgerSMB stuff.

Tom V

@JoeObbish Can you still ask that guy why he removed them?

Joe Obbish

@TomV no

Chris Travers

6:43 PM

@sp_BlitzErik It is fun :-D

Joe Obbish

no longer works here

also don't trust him

that query plan is atrocious

Chris Travers

total data environment of around 400TB for analytics and transient log analysis of about 1.5PB

Lamak

@JoeObbish well...there seems to be good reason for that

Joe Obbish

there are only 28k distinct values for the foreign key

just build a hash table

Tom V

@Lamak Ya load times

Chris Travers

6:44 PM

(high enough velocity we migrated from ElasticSearch to PostgreSQL)

Joe Obbish

if MS made this better I'd hate to see how it works before

Lamak

@JoeObbish the plan was without the fk, right?

Joe Obbish

@Lamak the insert one is with the FK

@TomV I can't believe how right you are

Lamak

@JoeObbish this plan?

11 mins ago, by Joe Obbish

Joe Obbish

@Lamak yes

Lamak

6:46 PM

yikes

how big is the fact table?

Joe Obbish

@tomV not as in you aren't a smart guy

but the difference is 2.5 minutes versus 1 s

for 120 M rows

1 s to check a constraint on a 120 M row CCI table

Tom V

@JoeObbish That's why I asked what you cared most about

Chris Travers

@Lamak So, sorting is 93% of your work

Tom V

And if you can be reasonably sure it's valid anyway (so you can just add it post-load without bombing out) I would maybe go down that route

Joe Obbish

I just can't believe that it takes a second

well I can, but I can't believe the difference

Tom V

6:48 PM

Could it be, but I'm no CCI expert, maybe it compresses after each second instead of just validating the compressed end result?

sp_BlitzErik

good lord why would it choose a merge join

Chris Travers

@sp_BlitzErik yeah that's what I was wondering

Joe Obbish

there are all kinds of problems

serial, row mode, merge join, sort

Lamak

this seems like an interesting read:

blogs.msdn.microsoft.com/sqlcat/2016/07/21/…

don't know if it helps much though

Joe Obbish

I think I can figure out the one second thing

sp_BlitzErik

6:50 PM

what happens if you hint a hash join? does it error?

Joe Obbish

so this query finishes in under a second:

SELECT *
FROM Fact fk
WHERE NOT EXISTS (
SELECT 1
FROM Dim dd WHERE fk.Key = dd.Key
)

and something like that is all that's needed to validate the constraint

so there you go

Tom V

But that probably gets executed on every insert when the fk is enabled when loading?

Joe Obbish

@TomV well no, I'm saying that you'd disable when loading

you'd be crazy not do, unless you needed the FK as part of your ETL process

hence the "I can't believe how right you are"

@Lamak which part?

Lamak

the "Parallel INSERT and Clustered Columnstore Indexes" part

Joe Obbish

looks like a foreign key doesn't disable parallel insert

so that's something

Tom V

6:53 PM

@JoeObbish Ah yes exactly

unless you expect the validation afterwards to fail and that would cause a full reload

an optimistic/pessimistic strategy thing

swasheck

assessing the impact of the tuple mover?

Tom V

@swasheck Mic drop

TIL

swasheck

i have no idea what's going on. i saw some chatter about inserts but is that the problem we're trying to solve?

> When you import the data into CCI, the data is loaded either into delta rowgroup or compressed rowgroup based on the rule “If batchsize < 102400, the rows are loaded into delta rowgroup otherwise they are directly loaded into compressed rowgroup”. Minimal logging is only supported when data is directly loaded into compressed rowgroup. This means, to get minimal logging with CCI, you must use a batchsize >= 102400.

from: blogs.msdn.microsoft.com/sqlserverstorageengine/2016/01/10/…

Transcript for

The Heap™ – Consultancy ©®