The 2nd Monitor

Malachi

15:00

@Marc-Andre Experts....?

rolfl

Nah, If I wanted to get your attention I would say "hog-riders are fools" ;-)

2

with bugs in their teeth.

Malachi

@Phrancis lol

2

@rolfl lol

2

I think I am close to running out of stars just catching up there....

Christoph

flushed out my own starred comment by starring @Malachi's lols

2

Marc-Andre

@Malachi Well I consider answerer expert. And it's "publicity", we do have "real" experts!

Malachi

@Marc-Andre Agreed

I need to figure out why I can't get Ajax Control Toolkit to install into this Crappy VB.net site.... ugh!

be back when I figure it out

Mat's Mug

15:06

vb.net suggested synonym: why

3

rolfl

only because wtf is already a synonym for lolcode ....

Vogel612

and wtf-is-this-shit a synonym to phpoop-security

Malachi

15:22

it's Legacy, and it's hooked into things just deep enough for me to not want to try and rewrite it. or should I? maybe I suggest that for my next project.....

maybe they don't want me doing it?????

rolfl

Don't touch things you don't want to own.

skiwi

It sucks to always work with 3-letter acronyms at work, now I keep typing every acronym I know instead of the one I want

rolfl

All you need is one .... TLA

Malachi

@rolfl I already own it....it's my project now...

@rolfl Texas Library Association?

rolfl

Well, that was fast: or should I? maybe I suggest that for my next project.....

Mat's Mug

15:26

lol #stuckinvb

rolfl

@Malachi Three Letter Acronym....

Malachi

@rolfl I had to scroll on my Google Search for that one, but did eventually see it...lol

rolfl

@Malachi Your Google-fu sucks ... was item 2 for me:

TLA

TLA may refer to: Three-letter acronym or abbreviation == Organisations == Three-letter agency (e.g. CIA, FBI, FSB or NSA) Textile Labour Association, an Indian trade union, precursor of Self-Employed Women's Association of India Tour de las Américas, a professional golf tour in Latin America Territorial local authority, the territorial authorities of New Zealand == Entertainment == The TLA, or Theater of the Living Arts, a music venue in Philadelphia, Pennsylvania TLA Entertainment Group, a movie retailer and distributor TLA Releasing, a film distribution division The Lawrence Arms, punk band...

Malachi

I think the reason it wasn't rewritten before I got here is because they didn't think it could be done.... but I think I can just rewrite it in C# and keep the extra libraries as well. but it would take some time

@rolfl I just searched "TLA"

google.com/…

rolfl

Malachi

15:29

is that what showed when you clicked the link?

Phrancis

Malachi's Google must be broken

2

rolfl

@Malachi Yup.

google.ca/search?q=TLA

Malachi

Phrancis

Malachi

I got less results than you guys got..... weird......

Bad Chrome, Bad Chrome...

Phrancis

15:31

Google is very subjective

2

skiwi

Malachi

and it took longer..... I highlighted and right clicked and hit search google.....

Mat's Mug

@rolfl quick question: I have a CTE that selects ~780K rows from a source; then I [inner] join with 4 tables and cross apply a table-valued-function - result is ~1.5M rows, ...and it runs in about 10 minutes. Does SQL Server perform the joins in the order I'm expecting (i.e. as written in the query), or it could be stupid enough to do the cross apply before doing the inner joins?

skiwi

I love the picture of all the partying people.

rolfl

@Malachi It's because 'murica' .... where .... nevermind.

@Mat'sMug It does it in the order that the explain-plan says it does it.

74

Q: How do I obtain a Query Execution Plan?

In Microsoft SQL Server how can I get a query execution plan for a query / stored procedure?

Malachi

15:34

VS2010 doesn't give me an option to change the framework for this site.....

Phrancis

Afbeeldingen = Images?

skiwi

@Phrancis Yup

Mat's Mug

execution plan looks good... I guess I'm going to have to live with these 10 minutes.

@Malachi what version is it targeting?

rolfl

@Mat'sMug 10 minutes to build and process 1.5M records, which is 150,000 a minute, which is 2500 a second. Now, that's not fast, and not slow either......

Mat's Mug

I think I'm going to put it up for review

rolfl

15:40

There may be room for improvement.....

Captain Obvious

2

Q: Learning python classes exercise. Dict of names and birth dates which prints with age at birthday of current year. Edit and save.

As a beginner (Learn Python the Hard Way) I've set myself this exercise with classes and it works but looks very inelegant to me. I'm trying to write classes which could be re-used in other code. I've left long variable names and comments so you can see what I thought I was doing. What should I f...

python-2.7

Phrancis

I was about to suggest that @Mat'sMug

rolfl

Huh, if you put a row of dashes in a chat window, it creates a break between messages.... like:

rolfl

This is after the break

Mat's Mug

uh, no

rolfl

15:43

and again.

Mat's Mug

oh wait

yeah

Phrancis

> (Hope this is the right kind of question - my first post.)

Mat's Mug

interesting

Phrancis

Wish more people had that attitude

Malachi

@Mat'sMug some of the projects in the solution target 3.5 and others target 2.0, but when I go to the application tab of the properties there is no target framework field to change

Phrancis

15:45

skiwi

ok

Malachi

lol

3

when I try to install Ajax Control Toolkit throught NuGet I keep getting an error like this

> Install-Package : Could not install package 'System.Spatial 5.2.0-rc1'. You are trying to install this package into a project that targets '.NETFramework,Version=v3.5', but the pack
age does not contain any assembly references or content files that are compatible with that framework. For more information, contact the package author.

rolfl

Seems like a comprehensive message, and useful.... wow.

skiwi

For some reason when someone uses ellipses, I always have the tendency to count them to see if he/she put exactly 3, or perhaps 4

Malachi

@rolfl I think I need to change the framework to something like 4.5 or even 4.0, but then I hit the issue above

18 mins ago, by Malachi

VS2010 doesn't give me an option to change the framework for this site.....

or project

Heslacher

15:54

@Malachi Targets .NET 4.0 or Silverlight 4.0

Malachi

@Heslacher it can't create a packages folder because it doesn't have permissions for some reason, and so it can't install to a folder that doesn't exist, at least that is what I am getting from the output when I try to install that one package that is getting hung up

Captain Obvious

-1

Q: Algorithm to find the largest number from a list of +tive integers using while loop only

I need some help on how to write a Algorithm to read a list of positive integers from the user until the user enters the value -1. Once the user enters -1, the program displays the largest value in the input list. I do not want to use arrays. While loop only. please help. I am using visual logic ...

algorithm

Heslacher

@Malachi never had that problem

@CaptainObvious Off topic

Malachi

I created it, and it deleted the folder when I tried to install again....

Phrancis

@Capt FCITW

Heslacher

15:57

How about (for testing purpose) to add the package to a new 4.0 project ?

Vogel612

@Phrancis naaah hammer is always faster ;)

rolfl

Wow, you guys are getting good......

Mat's Mug

@Malachi you can't retarget the solution, you need to retarget each project

rolfl

closed in minutes.

Malachi

@Mat'sMug regarget?

Phrancis

15:58

... regarget?

Mat's Mug

ugh.. can't even blame the phone this time ;)

skiwi

If I were drinking coffee now, then I would've splitted it!

2

Malachi

@Mat'sMug it won't let me retarget any project

@Mat'sMug star slap

rolfl

@skiwi splitted?

skiwi

@rolfl ~~splitted~~ spilled

Phrancis

16:03

FizzBuzz zombie o_O

What does the light blue highlight mean? ^^

Malachi

@Phrancis it is one of your favorite tags

Simon André Forsberg

Monking

Phrancis

Ah ok cool

Simon André Forsberg

Spent the working day with JavaScript, HTML and CSS... I had to think "How would I do this in Java?" when I was fixing a bug in a JS script

rolfl

Monking..... and how was work today ... ? ;-)

Heslacher

16:10

Monking @SimonAndréForsberg

Simon André Forsberg

@rolfl HTML, CSS, JS.... enough said?

Well, at least I didn't do any PHP...

rolfl

Nope, it should be: $$$ nuff said

Simon André Forsberg

And I actually worked on something quite fun in JS...

I did some refactoring!! :D

Vogel612

hmm My latest SEDE query shows, that in my best Answer I earned an upvote every 40 characters...

data.stackexchange.com/stackoverflow/query/228880/…

well every 42 on SO..

Mat's Mug

posted

0

Q: Normalizing Invoices

I need to move data from one database to another, and since I don't have SSIS I'm going this ETL with T-SQL scripts. One of the source tables contains invoice details, and features a column that contains the number of units invoiced per size (it's clothing); as I transfer the data over to my dat...

optimization sql sql-server join

Captain Obvious

16:24

2

Q: Normalizing Invoices

I need to move data from one database to another, and since I don't have SSIS I'm going this ETL with T-SQL scripts. One of the source tables contains invoice details, and features a column that contains the number of units invoiced per size (it's clothing); as I transfer the data over to my dat...

optimization sql sql-server join

Mat's Mug

@Captain well that was fast! like, a new record!

Simon André Forsberg

@Mat'sMug don't get used to it...

Mat's Mug

IKR

Phrancis

@Mat'sMug I like how you write your SQL, not a big fan of SHOUTCASE personally ;-)

Vogel612

@Phrancis well we had that discussion, right?

In ~~code~~ querystrings it IMO has its reasoning.

rolfl

16:27

@Mat'sMug Is the 10 minutes when run as an Insert, or when run as a query?

Mat's Mug

query

when I uncommented the insert it took just below 12 minutes

and when I ran it again it inserted 0 rows in just above 10 minutes

every table read is a clustered index scan, perhaps I should specify

except the source table, a table scan, but it doesn't cost me much. The join with the BucketString results is costing 32% with nested loops.

Malachi

I am going to rewrite a portion of the code, right now it utilizes an entire ASPX page when there should be a dropdown instead.... going to be fun to rewrite though....

@Mat'sMug how big is the text you are expecting, isn't varchar(max) only like 6000 characters?

Mat's Mug

lol it's 21 buckets of 5 characters, padded with a whitespace, plus 1 offset on the left, so 127 characters.

but the function is intended to be generic, and it will be reused.

but yeah varchar(max) is probably way too much for every use case.

Malachi

I was worried you had a lot of text going in there....

better to be on the high side anyways

if you know for a fact it won't ever be over 500 characters you could varchar(500) or something like that, not sure what the performance increase would be though...

Phrancis

@Vogel612 I was looking for our discussion, and found this gem:

Mar 2 at 18:33, by Vogel612

given the case Bob stores the cookies in the men's toilet, what would happen if alice tried to redeem her cookies??

Mat's Mug

16:39

if I do select * from Staging.dbo.[SourceTable] the table scan runs for 49 seconds and selects 780,173 rows.

I can create indexes on that table if needed, but the execution plan isn't suggesting any, so I haven't.

rolfl

@Mat's ... can you experiment on the database now?

I would like to see the left-join removed, and replaced with a not-exists......

......

Mat's Mug

actually there are already indexes on f1 and f2

@rolfl interesting.. I do the left join check on every table I'm transferring over..

I'm a bit in the middle of something right now, but yeah

rolfl

and not exists (select 1
                      from [DestinationDatabase].dbo.InvoiceDetailSizes dst
                      where detail.Id = dst.InvoiceDetailId
                         and sz.Id = dst.SizeId)

Mat's Mug

^^ running (with execution plan)

Vogel612

Toodles!

Mat's Mug

16:47

toodles!

rolfl

doodles

Phrancis

Poodles!

Malachi

muggles

Mat's Mug

@Malachi that will be a very minor improvement, but given the function is called almost a million times in that single script, I'll take it.

noodles ...time for lunch

rolfl

@Mat'sMug I suspect the performance will be about the same..... honestly, I imagine it was smart enough to translate the == null as a no-exists.

But, the logic is clearer.

Mat's Mug

16:48

it's 4 minutes into it

rolfl

The cross apply is not functionality I am familiar with.... and, it woul dbe my next suggestion, especially if it can be moved in to the CTE..... somehow.

Malachi

@Mat'sMug there are a couple of equations that you can declare outside, but the variable declaration might also take time, but it might outweigh the cost of having them inside the query happening every cycle....

Mat's Mug

@rolfl I thought of that, but then I thought if the joins occur before the function call...

..then I'm only calling the function on the rows that matter.

rolfl

meh, you may be right...... I see an amplification of data though, the joins will be reproducing the same cross apply for the same source records multiple times (since you start with .75M records and end with 1.5M).

hate to say it, but experimentation is the name of the game, maybe.....

I think the 2500perSecond is sounding quite reasonable....

Damn, still running.

9 mins ago, by Mat's Mug

^^ running (with execution plan)

9 minutes so far.

Mat's Mug

yep.

11:40 and still going

looks like the left join is faster :P

12:06

well you only added a clustered index scan at the end, that hash match is costing 19%

and its a right anti semi join

the function call was 8%, now 7% - something else has taken a hit

rolfl

17:00

the right anti-semi-join should be fast....

the 7% is probably because of the increased runtime overall.

Mat's Mug

yeah

rolfl

You are probably not pulling actual IO data though, so the data may be 'cold'.

there may be different contention patterns too with other queries running.... too many variable.s

Mat's Mug

hmm I'm pretty much alone on that server :)

well until people start using the report server I set up

rolfl

Was the last thing run on that server the same query?

(i.e. the data was all cached?).

Mat's Mug

was probably gone from cache

but the first-first I did with mine run was below 12 minutes

rolfl

17:04

OK, I am not really arguing here ... ;-) Just musing....

Mat's Mug

Phrancis

You should edit that into your question @mug I think it adds value overall to have the exec plan

RubberDuck

@Mat'sMug Look at the table scan and right anti-semi join.

Mat's Mug

@Phrancis I'm running my query again, that's the query plan with @rolfl's amendments

skiwi

@Mat'sMug I was thinking that was a git network graph for a second...

Captain Obvious

17:09

0

Q: Refactoring really large nested function

OK we have currently rewritten an Excel calculation engine in F# and are looking to refactor the code to make it a lot more idiomatic and standardised. One of the big problems with the code is that we have a massive function named companyModel that builds a CompanyModel type (a record type that ...

f#

Phrancis

Oh

Mat's Mug

@RubberDuck there's no getting away from the table scan - I'm actually traversing the entire source table... or can it be turned into an index scan somehow?

Jamal

@CaptainObvious If anyone is currently editing this, speak now. :P

rolfl

@Mat'sMug Won't be much difference.... you're going through all the records anyway.

Mat's Mug

that's what I thought

Phrancis

17:18

About how large is the result set of function [dbo].[BucketString]?

Mat's Mug

up to 21 rows, often only 1, most often 3-4

actually given the result size, average would be closer to 2

@rolfl finished in 12:01

rolfl

Ok, essentially the same.

is that returning 0 records?

Mat's Mug

yup

Christoph

poll of the ---week--- monday:
"right_aligned"
or
"aligned_right"
?

rolfl

So, essentially, the entire cost is in the not-exists.

what happens after that, is not computed......

What is the clustered index on [DestinationDatabase].dbo.InvoiceDetailSizes

Mat's Mug

17:26

I put the query plan into the question

Christoph

I still suck at chat formatting even if I do exactly as described in the faq. interesting.

Mat's Mug

it's absolutely unreadable though

skiwi

After reading up more, Rust looks awesome! doc.rust-lang.org/nightly/intro.html Only really a pity that it uses the let syntax, instead of letting the programmer provide a fully parametrized type (like Java), as now it leads to strange constructs

Phrancis

@Mat'sMug if you use Chat here to make a link, and post the link to the picture instead of the picture itself it may be better

Mat's Mug

I just did that - you can now click the picture, it's linking to itself ;)

still, hardly readable

but I don't have funky snag-it tools here, unlike some...

note it creates a right outer join instead of that funky semi-not-sure-it's-a-join thingy

rolfl

17:29

@Mat'sMug - do me a favour, and create the index: create unique index AK_CRTest on [DestinationDatabase].dbo.InvoiceDetailSizes (InvoiceDetailId, SizeId, Id)

You can do? or is that too much change?

Mat's Mug

done. and running

that DB is all mine, so is the SQL instance itself - even the server it's running on is my own little playground.

dream-job

rolfl

OK, the problem I am anticipating with your query is the clustered index use of the table.

Clustered means that the data is the index... (stored in index order), with some hashes outside that make it fast to look up.

the problem is that the index I presume is on the ID.....

Mat's Mug

clustered means the index is stored in the same order as it's physically stored on disk

rolfl

and we want to access the data via the InvoiceDetailID.

@Mat'sMug That is true, but it also means that all of the non-index data is stored with the index data.

Mat's Mug

you're right about that unique index though, I didn't have one (but there are FK's to all related tables)

rolfl

17:34

I am hoping that the index just created is a 'covering' index for the query, and that it will be significantly smaller, and have a better access path for the query.

If the query is still running, I suspect that it did not choose to use the index.....

Mat's Mug

hmm

4:40 (still running)

rolfl

nice... ;-)

Oh....

I should have asked you to set no execute .... and just show the plan.

You should possibly also update statistics ;-)

Mat's Mug

that's where my DBA skills end

rolfl

Actually, I bet the statistics were taken with an empty table .....

You have done an update stats... right?

Mat's Mug

nope

how do I do that?

rolfl

17:38

msdn.microsoft.com/en-us/library/ms173804.aspx

Simon André Forsberg

@Mat'sMug Snag It FTW!

skiwi

lol: twitter.com/a_quilt_bot/status/514477040615948288

Mat's Mug

@rolfl should I run this overnight every night along with the rest of the ETL process?

rolfl

I am reading up on it here: msdn.microsoft.com/en-us/library/ms190397.aspx

times have changed a bit. It seems that there are settings that MS recommends that keep the stats auto-updated when changes exceed thresholds.

It is possible your stats were already up to date.

Mat's Mug

it's SQL 2014 Express

rolfl

17:44

But, there is no damage in running it now, and learning about it later... ;-)

Mat's Mug

12:03 completed

..and didn't use the unique index

went with the clustered one

rolfl

And that... sucks (three . for ellipsis for @skiwi).

Mat's Mug

looks like this is going to be about micro-optimizations. @Malachi might be spot on

rolfl

No, that clustered index lookup is a problem..... the clustered index is on Id for the dst table?

Mat's Mug

yup

RubberDuck

17:48

@Mat'sMug Did it pick up a new query plan? It's best to make sure. If it didn't, clear the plan cache and run it a few times.

rolfl

So it is joining from the src table to the Dst table using an index it does not have the value for ;-)

skiwi

What on earth...

> This message is too long.

Oh...

reddit.com/r/programming/comments/2hkvu7/…

No copy pasting then

Mat's Mug

@rolfl then... instead if where dst.Id is null I could say where dst.SizeId is null and dst.OrderDetailId is null and it should pick it up.

rolfl

or use the not-exists variant.

which does not use the where clause values, or the left-outer-join ... right?

Heslacher

@skiwi The first reply ..

Mat's Mug

17:55

@rolfl maybe putting the id in the where clause is what's telling SQL Server to use the clustered index

oh gosh.. I had included the Units column as part of the UQ index' key

janos

evening

rolfl

hey @janos.

Mat's Mug

hi!

janos

hey guys

rolfl

@skiwi Here's an old-timer's version: macs.hw.ac.uk/~pjbk/humour/polynomial.html

Cute Poly Nomial and Curly Pi

Transcript for