The Heap™ – Consultancy ©®

I sometimes forget my umbrella at work on the day it rained, and have to bring it back on one of the following days, when, of course it's already sunny and there's not even a hint of an impending rain.

But I'm sure I never look like a dork in such cases. If anything, I probably look eccentric, haha

Colin 't Hart

We have umbrellas at work that we can borrow :-)

dezso

I don't understand people with umbrellas ;)

there are so good rain coats around

(the difference is the wind, I mean)

anyhow, good morning

it's sunny here

JohnSnow

8:28 AM

Well, umbrella was 25kr at IKEA, Raincoat was 1000kr at Gekås :(

And I already have several coats :P

Tom V

11:57 AM

neato

a request came in last week for a failing sql agent job, by now it has escalated to 'no ldf for tempdb', 'The log for database 'msdb' is not available', 'fcb::close-flush: Operating system error (null) encountered.' and the likes

and not a single db available

Tom V

12:17 PM

somebody's been screwing around with the cluster setup/storage

JohnSnow

12:30 PM

O.O

Tom V

preprod

this should only be going live next week :)

JohnSnow

Meanwhile at my place: MongoDB, Y U So Unsafe?

Tom V

1:17 PM

MongoDB: 'but im so much faster'

JohnSnow

Faster at getting hacked :(

ypercube

0

A: Best way to write SQL Query that checks a column for non-NULL value or NULL

Using coalesce to handle NULL should not have any performance impact. Try this once. SELECT ... FROM ... WHERE a.Blah = @Blah AND coalesce(a.VersionId,-1) = coalesce(@VersionId,-1)

I think the claim is wrong. Any comments?

Kermit

1:33 PM

boom

0

A: Insert Unique Records in Vertica with COPY query

You can add NO COMMIT to your COPY and run ANALYZE_CONSTRAINTS before a COMMIT: dbadmin=> CREATE TABLE tbl (a int PRIMARY KEY); CREATE TABLE dbadmin=> COPY tbl FROM STDIN NO COMMIT; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 1 >> 2 >> 2

Please VTC as dup

0

Q: Copy restricting duplicate entries

I need to load files in vertica using copy but i do not want duplicate entries. As unique constraint will not be helpful as it will load data and then i may have to delete duplicate entries from ananlyse_constraints().Other is using insert with bit of logic to check wether entry exist in table. I...

Colin 't Hart

@ypercube Claim is wrong. Comment is correct. Requires a function-based index to work.

ypercube

@Colin'tHart Funny thing is that the commenter made another answer - where he claims that using isnull instead of coalesce will be ok and the query will use an index.

Colin 't Hart

@ypercube yeah, just reading them

ypercube

I thought this was wrong but he insists. Am I wrong?

Colin 't Hart

@ypercube Hmm, not sure. Maybe SQL Server has some special logic to process isnull?

I can't find anything to indicate that. It's just an ordinary function. So using it will prevent the optimizer from seeing an index, unless the index is on isnull(<column>, <value>)

I'm not going to get involved.

ypercube

1:49 PM

I'll wait for @MikaelEriksson or @PaulWhite to comment ;)

(Aaron seems to be busy these days)

ConcernedOfTunbridgeWells

For those of you with arts degrees ...

4

dezso

2:30 PM

@ypercube it looks like he doesn't insist anymore

7

I'm not sure about the solution as an output

Colin 't Hart

@dezso Sarcasm is the output, solution the side effect? :-)

Tom V

"possible" side effect :)

ypercube

@dezso thnx to @AndriyM ;)

Andriy M

2:50 PM

@ypercube That was a useful discussion for me as well.

I've certainly heard it before that ISNULL behaved differently from COALESCE in certain situations. I may even have heard about this particular property as well (that it still allows you to have an index seek if you use it on a non-nullable column), but I wasn't sure and had to verify.

And verifying things yourself is a sure way of cementing your knowledge.

6

ypercube

@AndriyM the isnull(x, 0) is converted by the optimizer to x when the x column is defined with NOT NULL, probably.

Andriy M

3:11 PM

@ypercube Something like that. In the XML plan I can only see that with a nullable column I have a <Predicate> branch and with a non-nullable one a <SeekPredicate> instead.

So it kind of ignores ISNULL (because it knows that that column is not nullable)

Aaron Bertrand

3:24 PM

@AndriyM very nice

Kermit

just need 4 more close votes plz.. stackoverflow.com/questions/31586540/…

Zane

Got to love stored procedures that call stored procedures that call stored procedures.

Always fun to go down that effing rabbit hole.

@Kermit don.e

Kermit

ty

Aaron Bertrand

@Zane If only there were some tool that provided the nested stack trace for you

mmarie

@Kermit done

Kermit

3:39 PM

ty

Zane

Holy Smowzow I think I may have found the single worst query in our environment.

@AaronBertrand Wait. Sentry does that?

I'm still getting used to it.

Aaron Bertrand

@Zane if it was caught by top sql then yes you should see the whole trace. Same if you generate a plan from within PE Pro.

Kermit

@AaronBertrand @Zane @mmarie @ypercube thank you

Zane

God I wish I could show you guys this.

Kermit

@Zane if there was only a tool that anonymized it

Zane

3:47 PM

Without seeing the code it wouldn't be the same.

There are 112 columns populated like this.

Kermit

bollocks

Zane

Select
	(select count(column) From dbo.HugeTable where predicate = 'A'),
	(select count(column) From dbo.HugeTable where predicate = 'B'),
	(select count(column) From dbo.HugeTable where predicate = 'C')
from dbo.ThisHappens112Times

^^^ that's a alot of table scans.

ConcernedOfTunbridgeWells

Ubuntu is an ancient African word, and it means "I can't configure Debian"

6

Andriy M

@AaronBertrand Thanks! It does read almost as if I know what I'm talking about :) (All right, exaggerating there, but not much.)

ConcernedOfTunbridgeWells