The Heap™ – Consultancy ©®

@jcolebrand I've got two cases for it and might give it a shot this weekend. HTPC/LabServer at home and now my laptop, as I'm doing more and more TFS/VS/Automation work and need the Hyper-V

DTest

same

jcolebrand

@MarkStoreySmith nice

Mark Storey-Smith

4:07 PM

@jcolebrand would you mind having a look at dba.stackexchange.com/questions/7051/…. I can't get formatting to stick on the 2nd from last block of SQL

actually, both of the last blocks are playing difficult

jcolebrand

I think that tricking the regex parser was the trick

Mark Storey-Smith

top man, thats the ticket

jcolebrand

so all I really did was  \n  \n

gbn

@jcolebrand Opera

DTest

@gbn you would...

@jcolebrand lynx...

jcolebrand

4:16 PM

@DTest masochist

brb, issues with my browser

DTest

hah

gbn

I paid 30USD for Opear back in '99 or '98

Use FF at work

@jcolebrand I recommend Opear

jcolebrand

@gbn I never did care for it

uh huh, just what I thought

things are jiggered here

Mark Storey-Smith

FF started suffering annoying mini freezes for me the last few versions, so I went to chrome last week

Its nifty

DTest

I like chrome, but I still prefer firebug for site devel work.

but FF is a memory hog

Mark Storey-Smith

4:25 PM

So now we've got this chap to detail the horror of his code in full, who's going to give him the bad news? dba.stackexchange.com/questions/7029/…

jcolebrand

PS: I cleaned that up some on the comments, did I go overboard?

Mark Storey-Smith

Nah, ok with me.

My question on BCP could do with the chatter between @gbn and I cleaned up if you're in the mood :)

jcolebrand

link me

Mark Storey-Smith

3

Q: Optimising BCP performance for BLOB data

I'm the process of planning the live migration of a 2TB database to partitioned tables. The system is broadly speaking a document store, the majority of the space being allocated to LOBs of between 50kb and 500kb, with a small percentage in the 500kb to 1MB range. Part of the migration will invol...

jcolebrand

so do you want all of them gone?

seems some of those may have significance for future trawlers

anything you want to move into an answer or the Q?

Mark Storey-Smith

4:46 PM

You might be right, I'll try a quick edit.

Done, comments can probably all go.

jcolebrand

kk

gbn

5:13 PM

@Mark: added an answer to the stats thing

Mark Storey-Smith

@gbn Isn't an online rebuild of 500m rows going to be more chaotic than attempting an update?

My money would be on it being an incrementing range, which the trace flag i mentioned should tackle.

Correction, 330m rows

I get the feeling the chap isn't going to listen to anything other than his original Q anyway, judging by his intitial response to John Sansom and the tone in his comments

gbn

5:28 PM

That's some amount of row changes

sqlskills.com/blogs/kimberly/post/…

Assuming the 20% (we can ignore the 500 in 33om) then 66 million rows are being deleted and inserted daily

There are 86400 seconds in a day

I've managed 40k o^

oops

40k inserts per second but nit 24/7

Mark Storey-Smith

Or its much much less than that so auto isn't being triggered

Which is probably a good thing with that many rows to scan, even with sample

gbn

indeed, but it would have to be heavily skewed values to affect the distribution

1

Q: Auto Update Statistics and FULLSCAN in SQL Server 2008 R2

Is it possible to force FULLSCAN when statistics are updated automatically by SQL Server 2008 R2? If not, is a planned UPDATE STATISTICS WITH FULLSCAN the best way to keep statistics up-to-date? note: The need for the FULLSCAN comes by proved suboptimal plan generation when using non-FULLSCAN ...

sql-server tsql sql-server-2008-r2 table-statistics

Mark Storey-Smith

Can happen with incremental keys quite easily

identity or date

gbn

methinks it isn't stats. Function on a column, or implicit type conversion

@MarkStoreySmith Agree

Mark Storey-Smith

He's suggested that a fullscan gets a good plan, hence my suggestion of getting that plan to stick

One of those we're unlikely to get enough detail about, poster has his mind made up as to what the problem and solution are.

gbn

5:34 PM

exaclty

"answer for my chosen solution" not "answer for my problem"

Mark Storey-Smith

heh, yup

gbn

note "clustered index scan". No seeks. No index scan.

GUID PK anyone?

Mark Storey-Smith

You might be right

His original SO question mentioned that seeks are used when the plan is good though "I don't really see what that has to do with the actual question... anyway, consider that the suboptimal plan used two clustered-index scans whereas in the optimal one they are not present (replaced by seeks on the same indexes)."

Note the tone of his comment!

gbn

anyway. Pub for me. Missus back tomorrow so last night with the boys

ciao

Mark Storey-Smith

5:51 PM

cheerio

jcolebrand

6:02 PM

woot, tim fixed the chatscripts

if you're using the SE Chatscripts, you'll want to update them.

You can try /update or you can just revisit the rchern github link and install them again

github.com/rchern/StackExchangeScripts/wiki/Install

Nick

@MarkStoreySmith I noticed you updated your answer to my question about one vs. many databases. It still says "Scaling out would be the correct term but yes" which, as I noted in the comments, is incorrect.

@JackDouglas re: this answer do you have a large data set to test against?

Jack Douglas

@Nick Hi Nick, thanks for your answer on that one

My real data set is large

I could knock up a large dummy set if that is what you mean?

Nick

nope, I was just curious to see how the query performed against your real data set

and if any of the variations/other suggestions performed better

Jack Douglas

the perils of pretend data - well spotted

I'll try them all out - I'm still trying to work out if I can use collect somehow

Nick

yeah :)

@JackDouglas ah, I only have 9i here. COLLECT looks neat

Jack Douglas

6:22 PM

woohoo

`with w as (select x, cast(collect(y) as table_integer) as ys from t group by x)
select w1.x as subset_x, w2.x as superset_x from w w1 join w w2 on (w1.x<>w2.x and w1.ys submultiset of w2.ys);`

time to start testing :)

Nick

oh neat. submultiset looks like it was created for this purpose

Mark Storey-Smith

@Nick was just fixing a typo, let me refresh my memory on the comments

@Nick ok, think we're talking at cross purposes on that bit. I think my point was that when the term "scaling up" is used it refers to adding more cpu/memory to a single server, "scaling out" being multiple machines. So your "Assuming each database is on separate hardware, scaling up yields more performance benefits." is sort of mixing the terms if you see what I mean

Could probably tidy the answer and comments up a bit if you like?

I'll move some of the comments in then we can ask a mod to delete the comments

Nick

yeah, agreed

you've already scaled out by having the databases on separate machines, so then scaling each of those up yields more benefits than scaling one machine with one database up

which is a mix of the two

@JackDouglas What constraints/indexes do you have on the table, btw?

Mark Storey-Smith

6:41 PM

See how that looks @Nick

Done a couple of minor edits, brought one of the comments up into the answer

Jack Douglas

@Nick (x, y) is the primary key (well not really but it's close enough)

Nick

@JackDouglas no index on (y, x)? you'll need that

in addition to an index on (x, y)

@MarkStoreySmith I clarified my scaling up statement in my question. We can delete all the comments we have, minus the one about SAN caching (which I think is still relevant)

Mark Storey-Smith

Groovy

@JackDouglas would you mind doing a chit chat cleanup on dba.stackexchange.com/questions/4547/… for @Nick and I. Just leave Nicks comment regarding SAN cache in place please.

Jack Douglas

@Nick It doesn't use the indexes even if I create them - full scans and a hash join

@Mark sure :)

you mean the one starting "Understood. With caching at the SAN or database level, however, this is not an issue."?

Mark Storey-Smith

Muchos gracias

Nick

6:52 PM

@JackDouglas yah

Jack Douglas

@MarkStoreySmith that comment doesn't make sense if I remove the others

Nick

I might even delete that and re-comment so it makes sense

Jack Douglas

@Nick ok I'll blow the lot away

Mark Storey-Smith

@Nick sounds like a plan

Jack Douglas

all yours...

@Nick thought you might like to know your answer wins by a factor of 200 or so against the collect/multiset :) (and a factor of 2ish over my other answer)

Nick

6:56 PM

nice! and my answer works on SQL Server too :)

you're still gonna post the COLLECT approach, though, right? It's pretty cool and may serve as a good usage example to others

Jack Douglas

@Nick yes, I'm trying to think of a witty caption

would help if I had any wit :(

Nick

I thought wit was invented by a Brit.

Mark Storey-Smith

@Nick I'd certainly like to think so

Jack Douglas

gbn is funny and he's a Brit :)

"don't look at me. I draw like a 3 year old"

made me laugh

@Nick why do you think indexes would help? all I can think of is they might save a sort for the cross join if it did a merge rather than hash

Nick

@JackDouglas yeah, that's what I was thinking

but I guess if you're querying the whole table at once they may not matter really

Mark Storey-Smith

7:05 PM

@JackDouglas If you're in a tidying mood, comments could also be removed from gbns answer in dba.stackexchange.com/questions/3943/…

Original point I commented on got edited out and gbn added a clarification instead

Jack Douglas

@MarkStoreySmith I'm not sure - I found all the comments helpful given the way the answer has been amended

The original point seems to be still there - gbn has just added an explanation

Mark Storey-Smith

Fair point

Jack Douglas

7:21 PM

"specifically people from the Oracle world think this way"??????

this site has waaay to much microsoft bias :)

Mark Storey-Smith

Strength in numbers, I like it :)

Jack Douglas

7:40 PM

@Nick is gist better than pastebin? (never used either before today)

Nick

@JackDouglas I think it's cleaner and more professional-looking.

Jack Douglas

ta :)

Nick

plus, if you use git, your gists are automagically turned into git repos you can update from the command line

Jack Douglas

can you highlight portions of the text on either? (I think the answer is no)

Nick

like, link to lines? not that I know

that would be clean though

Mark Storey-Smith

7:48 PM

Is it preferable to put code in pastebin/gist than a Q or A? Always or when it gets to a particular size?

Nick

@MarkStoreySmith when it gets to a particular size

minimize having the reader go to external sources as much as possible

Jack Douglas

@MarkStoreySmith have a look at my first comment on Nick's answer here - can't think what else to do in this situation

Nick

which is why we like links to be accompanied by summaries of the info behind them

but when your snippet is huge, just put it somewhere else

Mark Storey-Smith

gotcha

Nick

for example, this question was already large enough, so I posted the sample schema on gist

Mark Storey-Smith

7:54 PM

@Nick I always meant to come back to that bank schema question!

Nick

this answer on the other hand, fit without requiring a scroll bar (I actually edited the code so it would fit the 600px limit) and delivers powerfully right there

Mark Storey-Smith

Some of the answers set me off on one of my "GOOD LORD NO!!!" paths so I kept stepping back :)

Nick

@MarkStoreySmith heh, I still haven;t gotten around to that. my company put that off for a future sprint, which is why I haven't accepted an answer yet

Mark Storey-Smith

For the record (in brief) I am vehemently opposed to 2), the materialized view approach

I'll make it one of my commute jobs next week and see if I can phrase something socially acceptable :)

Jack Douglas

@Nick hey I get scroll bars

Mark Storey-Smith

7:58 PM

The archival/rollup is definitely a no-no also

Looks like the answer you awarded bounty too is on the right lines

Jack Douglas

@MarkStoreySmith materialized view = indexed view?

Mark Storey-Smith

@JackDouglas yes

Nick

@JackDouglas what browser/zoom size are you using?

Jack Douglas

what makes you hate it?

@Nick chrome 100%

Nick

@MarkStoreySmith I'd love to see it.

@JackDouglas ahh, i'm chrome 120%

i like my stuff big and easy to see

@MarkStoreySmith Im leaning towards that at the moment, plus an INSTEAD OF trigger to capture the old balance in the same row as txn amount and new balance

Mark Storey-Smith

8:03 PM

@JackDouglas I can't see how it would be viable to afford the overhead of the additional write in a system of that nature.

Jack Douglas

@MarkStoreySmith ie high transaction volume

Mark Storey-Smith

@JackDouglas exactly

The additional write to an indexed view is very different to an additional write to a balances table for instance.

Holding the current balance on the account record, combined with recording transactions with an opening balance (as per Andrews answer) sounds like the best route to me

But its one of those I'm inclined to hack some code together for :)

Jack Douglas

@MarkStoreySmith I think you are right - and you can't beat a single write can you

Nick

@MarkStoreySmith I'm looking forward to seeing that when you get around to it. :)

Mark Storey-Smith

Setting myself up for an EPIC fail here no? :)

Nick

8:16 PM

hahahah

well, epic scrutiny

Jack Douglas

@MarkStoreySmith We'll all be watching very closely :)

Nick

which then leads to either epic fail or epic win

Mark Storey-Smith

And on that note, dinner and a movie time

Cheers chaps

Nick

cheers

Jack Douglas

enjoy

jcolebrand

8:53 PM

Yay I've done my part to make this room active

However, given the number of rooms I'm in that are routinely active ... I can see I can't manage to stay on top of what goes on in here anymore :p

DTest

you leaving us?

jcolebrand

no no, just saying that if I don't respond to the convos, it's probably cos I didn't see them :p

DTest

good enough

jcolebrand

@Nick made an excellent point earlier about the use of gist to post long blocks of SQL

Nick

gist is da bees knees

too bad careers.so doesn't pull in gists, even though gist claims gists are also git repos (and repos should be pulled in)

jcolebrand

9:02 PM

0

Q: What content needs to go in the main site faq, the first block?

What information do you think needs to be in the header block of our FAQ? Check out some of the FAQs of our sister sites: http://english.stackexchange.com/faq http://gaming.stackexchange.com/faq http://photo.stackexchange.com/faq and especially http://programmers.stackexchange.com/faq I'm s...