Problems with Entity framework queries what a surprise.

@AndriyM Hmm .. more indexes mean more work for INSERTS, of course. For reads, I guess if you had a heap with data written in query sort order the actual work required to sort the data would be small. Then added a clustering index with a different ordering and the run-time work to provide query ordering would go up. Not sufficiently familiar with the heap sorting algorithms to be sure, though.

@MichaelGreen @AndriyM Even without sorting a clustered index makes a select without conditions slightly more work

The clustered index is a little bit larger than the heap so it requires a little bit more IO, but only slightly

@MichaelGreen @TomV I'm sure I had only SELECTs in mind, but as for clustered vs non-clustered, I wasn't considering that distinction at the time. What you both said makes sense to me, thanks.

technology.amis.nl/2015/12/01/…

@Mat'sMug There's plenty of us regulars. I guess we might need to upvote a little more

@Mat'sMug doesn't it make the chat very noisy?

@dezso Their chat is extremely lively without the bots already, so I suppose they hang out in chat more than on main site

@ypercube it appears to be a binary file anyway

I'm pretty sure a chat feed would be the wrong solution. We tried a limited form of it before and it was not universally loved. People just need to vote more on questions regardless (as Phil said).

segment.com/blog/choosing-a-database-for-analytics

> If you’re under 1 TB of data, Postgres will give you a good price to performance ratio. But, it slows down around 6 TB.

and a reaction from one of my teammates:

> so [a database of ours] has only a week left before it "slows down"

also, there are no actual benchmarks shown, no mention of hardware and so on

why do people write such stuff?

@dezso hey, they are Lead Data Analysts ;)

@dezso Must be why my computer is slow. I have more than 6Tb of disk :/

I thought Amazon Aurora was MySQL?

@Phil they say so

but these MySQLs are so many

"Relational databases come with another advantage: you can use SQL to query them" -- OH. My. GAWD. !

"On the other hand, running analytics on semi-structured data generally requires, at a minimum, an object-oriented programming background, or better, a code-heavy data science background.". What a load of shit.

@Phil Hey they need to make it sound difficult to justify there rates, cut them some slack

@dezso In order to be percieved as knowing what they are talking about by folks such as those mentioned here.

@Phil Which sums up the main reason not to use NoSQL technologies for anything that involves complex queries against the data.

it's so quiet in here lately

Sorry been busy

it's all your fault @AaronBertrand :)

Well I do tend to inject lots of "go crap on this" stuff when I'm active :-)

that seems to happen even when you aren't around as well

I blame @Kermit

one of these days i hope to have a job like @AaronBertrand has

"GO FORTH AND CRAP!!!!!!!"

Them "We need an estimate for the work." Me: "I don't have a BRD" Them: "We still need an estimate"

@Zane "I still don't have a BRD"?

Business Requirements Document

Basically: what is engineering building?

Pretty tough to estimate how long it will take to build something (never mind actually build it) without formal requirements describing what you're building.

JUST GIVE ME AN ESTIMATE. YOU KNOW WHATEVER YOU TELL ME WILL BE WRONG ANYWAYS SO LET'S SKIP THAT STEP AND GET ON WITH BLAMING EVERYONE

Thanks, @Aaron. I was just looking for it and thought that "business requirement document" was the most likely meaning. However, what I meant by message to @Zane was to (half-jokingly) suggest a reply to the "We still need an estimate".

@billinkc WHY ARE YOU YELLING?

Ah, sorry I missed that. I thought you were asking what that meant.

Lost my touch, been away too long

@bluefeet Because you know how that email would come in

@bluefeet It's not him, it's Them

@AaronBertrand - quick question - I hope you don't mind. I have a developer asking if it would be better to run an update over a linked server from the destination side or the source side? i.e. data from the source server is used to update data in the destination server. Where should he run the T-SQL update statement? We can create the linked server on either server, it matters not to me.

the source is SQL Server 2012. Destination is 2005

I'm thinking it's best to run it at the source side just because it has the newer version of SQL Server.

@AndriyM My response was essentially this is like asking me how long it will take me to make a kit car. I've never done it I have no frame of reference and you haven't even let me see damn instructions yet.

@Zane How's your Korean? The instructions are very detailed, once you read them you'll have no problem building the car

@MaxVernon Which server has a more go getter attitude.

@Zane hmmm. They are both VMs running on identical hardware, with close to the same configuration. In dev, the source has 6GB max server memory, and the destination has 4GB.

I'm writing up a question on the main site.

@Zane I feel your pain, last week I was asked for an estimate and was supposed to include the business consultants time to design the thing

I would probably lean toward performing the select across the linked server. e.g. UPDATE local SET local.x = remote.x FROM dbo.local INNER JOIN linkedserver.db.dbo.remote ON local.key = remote.key;

But I don't have a real good reason for that, except that it would seem reading across a linked server would be less brittle.

@MaxVernon could you just transfer the data over and stage it then perform the update?

There might be some intimate details about an update plan over a linked server, too, that Paul would probably have more insight about

I suspect version on either side is not very relevant

@billinkc I skip right to the "blame everyone else" bit as soon as they say "hey can we...".

I think you need to consider where the joins/sorts/tempdb will be used, preferably on the same server

@Zane and @AaronBertrand - I appreciate the input!

@EBrown attaboy

I think the waiter in this hotel saw reservoir dogs and recognized my accent "do you want mayonnaise with those fries?"

ah no , pulp fiction

@TomV LOL That would have been priceless to see.

@MaxVernon sqlbits.com/Sessions/Event10/Distributed_Query_Deep_Dive

YWMF

@swasheck YWMF ?

thanks for the video by the way!

@MaxVernon nothing. sorry

NPM!

WHERE im.[ImageDate] BETWEEN DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, -30) AND DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, 0) + 5

WHERE im.[ImageDate] BETWEEN @DateFrom AND @ToFrom

Didn't write it that's how it is.

@EBrown here comes the code-reviewer :p

code review is for the faint of heart ... those faithless inhibitors of progress

@TomV ;)

@Zane Ah, I'd probably change it if it were me.

Anyway. Shouldn't the GETDATE version get a better cardinality estimate due since the value will be calculated prior to execution and therefore will work with an actual value?

@swasheck oi m80 u wanna go m8

PIRATES!

For some reason that version isn't able to go to the histogram it's getting an estimate of 1.

This confuses me I can't seem to put it togather.

@Zane are the values known? if not ... density vector

DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, -30)

Something about the datediff seems to be where it gets thrown off. If I just have dateadd it seems to not have this problem

@Zane BETWEEN @DateFrom AND @ToFrom this what i was talking about

Oh yeah. it's set at the beginning of the query

Hey, uh, how do I write software?

@Zane so it's "sniffable" ... or not? i'm thinking not.

@AaronBertrand RELEASE THE CRAPPEN!

This room is now starred. You guys are awesome.

yes. we are.

i mean ...

TYVM

Does anyone have any suggestions for music you can pull teeth to?

@AaronBertrand Anything "rap" or "trap".

@AaronBertrand stuck in the middle with you

That seems more like an ear/nose/throat specialist than a dentist

@AaronBertrand What style are you looking for, specifically?

I can't get this execution plan the way I want it to go.

@EBrown I was just making a joke because I was playing dentist - I've asked 4 or 5 times for table structure and sample data, and I'm not getting it.

@AaronBertrand Ah, that makes sense.

"Pulling teeth" always comes to mind when I have to ask for something more than three times.

I'm glad I provide it the first time it's asked for. ;)

Also, I've never gotten the chance to personally thank you for providing two (very good) answers to my questions, so thank you greatly for those. :)

No problem, glad to help

Even if I moan about it sometimes

I appreciate it sir, you really are quite good at what you do.

You've saved me several hours of beating my head in.

Thanks

Especially with this one. You and @PaulWhite both helped me greatly, and I have SP's with both those solutions so that when I truly go to production I can performance test both of them.