The Heap™ – Consultancy ©® - 2012-01-17

« first day (378 days earlier) ← previous day next day → last day (4504 days later) »

1:16 AM

Should this be moved to SO?

0

Q: time interval period in sql

How to display the time period of the most widely in use in ms sql server I have purchase data, such as: hours the number of purchases 10:00 2 12:00 4 13:00 5 14:00 1 I want to show the period of time with most buyers. So the results would be with the most purcha...

sql-server sql query subquery

Also the question (as written) doesn't make a hell of a lot of sense and his provided query makes absolutely no sense.

1:41 AM

@SimonRigharts probably more appropriate for SO

and yes, question is incomplete and hard to understand

@jcolebrand - see above

What I don't get is, given how he's cross joining two tables - how is he getting back a result that makes any sort of sense?

... To be revealed on the next episode of WTF on DBA.SE!

Oh hey I looked at the query again and it makes even less sense than it did before.

so we have ... a cross join, a group by with no aggregate functions, a having statement with a subquery, this subquery itself having another cross join and grouping by fields that aren't in the select list

just threw in a VtC

as off topic

and left a comment asking OP for more

don't worry kid, we did our best tears up as question is dragged away to morgue

We do what we must. Because we can.

1 hour later…

3:08 AM

@NickChammas do you want to follow that to Stack Overflow or is it just crap?

The question-asker pretty obviously needs help, but the question is borderline as to whether it's salvagable or not

I think that he hasn't got a good business logic

and if we push it to SO as is it's going to be clobbered or lost

so we have to figure out if we fix it and ship it, fix it and answer it and ship it, or fix it and answer it and close it

Well if we answer it it may as well stay here, so I'd say either A: close, B: fix it and ship it, or C: fix it, answer it and close it

if we answer it and it's a code-only Q I'm shipping it to SO

because there's nothing expert there (yet, the sole defining factor of remaining)

I think you understand that, just saying

as far as I understand it, the question itself isn't expert

unless he's done something remarkably stupid with his schema

I was writing SQL to figure out busiest dates back when my job title was still 'factory worker' :v

So, if the question isn't expert, then the options are essentially <close | fix/ship | fix/answer/ship>, right?

3:20 AM

yes

that about sums up my thoughts

if we ship now it'll get buried and lost

so we need his business rules to really be useful

because apparently he hasn't relayed them to us yet

yeah, we need more detail from him

give him a day and if he doesn't respond to Nick's comment then close it?

Aye

10 hours later…

1:31 PM

simple-talk.com/sql/database-administration/…

2

2 hours later…

3:03 PM

dba.stackexchange.com/suggested-edits/511 did we decide the oracle-11g was a superset of oracle?

No idea, but we break SQL Server into versions

I dunno the differences between 'Orables.

I don't either, hence my need to come to the chatroom, but if we're gonna break 'em, that's a valid edit. hey GBN can you approve those?

.@RepMaloney @CarolynBMaloney @DarrellIssa Why can't we have access to NIH research in the first 12 months? H.R.3699 is BULLSHIT.

Tweeted by jcolebrand on January 17, 2012 at 3:14 PM

you mean here?

dba.stackexchange.com/tags/synonyms

3:18 PM

no, sorry. the edit on that Q

Ah I did already

kk

Your tweet confused me too, trying to click it

you can click the text at the bottom

Basically it boils down to: currently NIH research is free to the public in the first 12 months. Those two congresscritters want to make it for-pay forever (as it is after the first 12 months)

another article about the US: economist.com/blogs/democracyinamerica/2012/01/…

3:21 PM

If tax dollars didn't pay for it, that would seem ok. The only thing I'm ok with tax dollars hiding is plans for military bunkers and weapons systems (including battleships, etc)

I'm not saying that the UK has a better system.

or almost anywhere in Europe

France example: telegraph.co.uk/culture/4292402/…

Oh but the point is even Uni's are corporatized nowadays, which is just wrong

1 hour later…

4:37 PM

@gbn do you agree with the author that we (advocates of the relational model) are gonna lose out to "flat file and NoSQL zealots"?

@NickChammas I do. Obviously. I mean, look at the relational databases of the IBM heyday (when the relational won out as a means of space savings, which was more important iirc than actual mathematical models) and then the whole DOS fiasco (directories? really?) and then there's WinFS and ReFS and the whole journaling filesystem which today is effectively a db

</sarcasm> if that wasn't evident

My point was those guys don't understand their history or the reasoning if they think that's accurate

@NickChammas I don't get how that article would lead you to believe NoSQL would win out over Relational

I can argue this either way, because history has shown both models to have benefits. One could argue that a relational db is just a set of flatfiles with a really good search function.

If the reason we're not using SSDs is we're lazy and don't want to change how we work why would we then shift the whole damn paradigm to NoSQL rather than adopting SSDs and proper relational?

@BenBrocka It doesn't. The author, however, is lamenting the fact that SSD vendors aren't doing their part to sell their product as an RDBMS enhancer to tame the tide of anti-RDBMS people.

4:43 PM

I think SSDs are going to see a lot more marketing in multiple regards as they get cheaper and more reliable

He talks about not marketing them 10 years ago, but 10 years ago SSDs were extremely small, risky and crazy expensive.

@jcolebrand Well the key point to the SSD discussion is random IO. Joining multiple tables together to retrieve an object is much more random IO than going one place to get the whole object, for example. So the benefit conferred to relational systems by SSD is greater than for others.

To this date they remain much smaller, have a relatively low life span and are quite expensive. You're fighting more than a paradigm shift

if anecdotal evidence didn't suggest a 10 month shelf life of active use for most commercially viable SSDs, I would say he has a point

@NickChammas but if we remove that need, denormalizing as it were, regardless of the purity of the math, we could prevent that.

Or if we get really wide spindle arrays, with single spindles per block device, we wouldn't have to worry as much about seek times

@jcolebrand As in a SAN. I hope SSDs quickly replace SANs for the small to mid sized enterprise as they become more reliable.

@jcolebrand 10 month? How anecdotal? In my experience most anecdotal "data" on SSDs is "I got one SSD and it failed in X months", which millions could say the same for HDDs. These are things that DO break, the issue is the actual lifespan and failure rate

4:48 PM

@BenBrocka StackOverflow devs. Each has an SSD on their primary box. They've been public about the failure rates before. Maybe we can entice @zypher to tell us if they've got new stats or the like.

That said I've only seen experimental stress tests for consumer grade SSDs for PCs

@NickChammas it's the second half of that I'm waiting on

@jcolebrand as in a whole DB cluster or one SSD in a laptop?

need a few more VtC as dup on this question

@BenBrocka one per desktop. My point was that if the devs machines are failing (because devs do a lot of write-rewrite stuff) then how much faster would a db server fail?

@Gbn tsk tsk for shame answering a shopping list instead of throwing it at me :p

5:02 PM

@jcolebrand But is it due to faulty parts or high I/O? My personal machine has lasted 2 years despite high IO for a personal PC (DB, gaming and video editing). There seems to be a reasonable failure rate but the actual lifespan seems reasonable. I've seen actual stress tests that wear out SSDs by I/O, they lasted the equivalent of 5-10? years of 8 hours of full I/O per day IIRC

The problem is people have an SSD fail and they think "Oh, it's lifespan must be up because it was an SSD" because we know that's a risk. People don't assume their HDD was at end of life because it failed; they usually fail (in personal machines) for mechanical reasons

One would assume high I/O? Jeff Atwood has blogged about it several times. I doubt they buy cheap parts for their devs (they provide awesome work environment)

10 months is not EOL for any part.

"high failure rate" doesn't mean "not cheap", especially what is still pretty early tech for consumer grade stuff

codinghorror.com/blog/2011/05/…

Yeah, that's the one I was thinking of

I know he's talked about them several times.

We need a current-recap from SO tho, not something 9 months old

Note the sporatic times though; 15 days, 72 days. I'm sure a lot if not most of these are failing because of faulty parts or construction, not an inherent, unfixable problem with SSDs as a technology

Current drives aren't that suitable for long-term use but they're continuing to improve the tech and are making some very long-lifespan models as I've heard

5:08 PM

@jcolebrand ugh, you're welcome jcole! ohmigod how rude... prances away like a teenage girl

@NickChammas lol, what?

@BenBrocka notice the overall consistency of 9-10 months tho

@jcolebrand I give you the link you need and you dismiss it because it's 9 months old.

lol

just bustin yer balls

I figured

I rather meant, it's probably time for codinghorror to rebroach the subject

@codinghorror can we get an update re: http://www.codinghorror.com/blog/2011/05/the-hot-crazy-solid-state-drive-scale.html re: current state of SEI disks and failure rates since then?

Tweeted by jcolebrand on January 17, 2012 at 5:05 PM

@jcolebrand yah, that would nice

@jcolebrand Only three of 8 fall particularly close to that mark, the average just happens to fall near it as well. Plus, realtively, those are ancient SSDs (so is mine...)

I note none of them are Samsung...that's what mine us

5:11 PM

@BenBrocka relative to today. at the time they were still reasonably nice

But they're still practically experimental hardware. Hopefully within a couple years consumer PCs will be able to start shipping with them

@BenBrocka apple ships imacs with them if you ask

and pay extra of course

They just have to counter the "ever increasing GB" factor of HDD marketing (which most people don't get anyway) with the much easier "HOLY F*** IT'S FAST". Which would be quite easy if they could promise a reasonable lifespan

I meant "PC" as in computer, not Linux/Windows box

I'm glad to hear Apple offers them though, didn't know that

What will really matter is when a Dell/HP/Sony laptop starts coming with them out of the box

@NickChammas nah. They'll fuck it up at some point

Normalisation isn't about performance

I'd like to see the aftermath of some twat implementing your banks back end system in NoSQL. No bank probably

NoBANK

5:17 PM

if you look at PCI and MySQL: you have to use InnoDB for ACID

@BenBrocka hahahahah

Even MVCC can bollix you: en.wikipedia.org/wiki/Snapshot_isolation#Definition

MVVC forces some transaction integrity onto app monkeys

interesting read

Curiously (and my experience is limited) most 3rd party trading apps I've seen run on Sybase or SQL Server. Oracle comes a poor 3rd

@gbn yah, check out this: tpc.org/tpce/default.asp

5:20 PM

A large bank I'm familiar with had issues getting Oracle to run a trading app. The Sybase DBAs laughed their arses off

and the results: tpc.org/tpce/results/tpce_price_perf_results.asp

all SQL Server

"The TPC-E benchmark simulates the OLTP workload of a brokerage firm."

@NickChammas christ that's new

MVCC slows you down. Just look at Postgres docs

@NickChammas Why do so many computer science related orgs have sites like that? Looks exactly like U of Iowa's CS dept website. Very late-ninties.

But hey, they have valid CSS

@BenBrocka probably because they were all trained in similar environments and thus learned the same tools/methodologies for data display

and they aren't selling anything or requires a nice UX

5:25 PM

It just gives a look that's very...comp-sci

Everything requires a nice UX :)

But comp sci depts usually have better sites than the psychology departments. Like... wadsworth.com/psychology_d/special_features/ext/workshops/…

It must be the result of some sort of disease. Every psych professor has a site that looks just like that.

sorry, i hadn't read that whole article till now, is he really basing his premise on FusionIO RamDisks?

that being the final image in his article

what happens when those PCI go dark?

I assuredly don't agree with the form proposed by:

Fifth normal form

Fifth normal form (5NF), also known as Project-join normal form (PJ/NF) is a level of database normalization designed to reduce redundancy in relational databases recording multi-valued facts by isolating semantically related multiple relationships. A table is said to be in the 5NF if and only if every join dependency in it is implied by the candidate keys. A join dependency *{A, B, … Z} on R is implied by the candidate key(s) of R if and only if each of A, B, …, Z is a superkey for R. Example Consider the following example: {| class="wikitable" |+ Travelling Salesman Product Availabili...

if only because it's too simplistic

I assumed it was just a stock image, I guess he does mention them specifically

@jcolebrand How so?

@BenBrocka go read that example

I suppose because I'm too used to int joins

@BenBrocka heh, I've noticed similar styles among PhD candidates for example

Simplistic because it results in super tiny simple tables in some cases?

5:38 PM

short, text only with a new links to published work

@NickChammas these things are the first results for Google searches, though, damn it!

It's horrifying

If you google psychological terminology you either get Wikipedia or you get one of those

:D

6

Q: UX Stack Exchange Top User Swag

As a thank you for being awesome, if you have at least 1,125 reputation and are on page 1 or page 2 of … http://ux.stackexchange.com/users?tab=reputation&filter=all … we'll be sending you a little care package shortly: UX Stack Exchange t-shirt in your size UX Stack Exchange...

discussion swag

Guess what I get

nice

the shirts aren't bad. a little thin but they hold up

Was wondering about shirts. Most print-your-own places have fairly inferior quality

I assume SE is using somewhere in the mid-range of shirt-makey businesses

well plus they're in NYC so it's easier to find agents to help in that, I imagine

Mark Storey-Smith

6:09 PM

@BenBrocka Where's my grumpy dba swag?

we have to be around for a bit longer :-\

Mark Storey-Smith

@jcolebrand Seen the visits/day lately? At 5.8k now!

are we really? Hmmm

unfortunately atm we're on a mild downward trend in visitors

but this will be the third downturn since November, and we're still on a higher daily average of visits than we've been in a while

total page views is clocking in at 8400

we are most assuredly, however, up over the long term since then with visits per day

Also, I'm goofy, I wasn't accounting for the 1st that just happened a few days ago, we actually had strong traffic over the last week of the year, compared to what it could've been

more traffic then than before thanksgiving in the US.

what might be more surprising is new visits per day accounts (according to the logs) for half of our traffic daily

Mark Storey-Smith

@jcolebrand all good

6:31 PM

@jcolebrand Related to we've flagged dozens of SO ones for migration, and the banner that appears at the top of a page after migration?

@gbn that might be it, but half?

7:18 PM

@jcolebrand Actually UX.SE just graduated. Dunno about stats/how long DBA has been around period though. UX was in super long beta

idk, are you swag hounds that interested in getting TShirts?

@jcolebrand that may have been why too. DBAs may not be the best swag hounds

bah, most of the DBAs I know love getting swag. I'm kinda past that now, unless it's a really nice pen.

The UX sketchbook is my fav. The bad part is it'll get used up :(

ConcernedOfTunbridgeWells

7:54 PM

@BenBrocka Beats my stickers and letter from Jeff for being in first two pages of serverfault :)

@ConcernedOfTunbridgeWells I was surprised it's so much stuff

ConcernedOfTunbridgeWells

@BenBrocka Probably because it was early days - I spent a bit of time answering (mostly) database questions when SF first started - I was on the second page of the users at the time, so I got a nice letter from Jeff. By the time they came out I had dropped onto the third page of SO so I didn't get anything for that.

I haven't been so active these days, so I'm really just somewhere in the pack on both sites now.

One for the Oracle folks here - does anybody know if OWB on unix or linux can natively read a MS Jet datbase?

Also, has anybody worked with Autosys in living memory?

8:14 PM

Not I

ConcernedOfTunbridgeWells

Just starting on my first Oracle gig since 2005.

I've used Control-M and the I-Series Robot scheduler (whatever that's called). From what I can tell Autosys has a fairly similar method of running jobs of remote machines.

@ConcernedOfTunbridgeWells - Just read through some of your stuff on SF. Nice, lengthy posts explaining history of X or structure of technology Y.

ConcernedOfTunbridgeWells

8:33 PM

@NickChammas Thanks.

2 hours later…

10:14 PM

@ConcernedOfTunbridgeWells Speaking of web developers barely knowing SQL, I had one yesterday that was absolutely adamant that it was "Simple Query Language"

@SimonRigharts I always counter that with Structured, and vice versa, to keep people on their toes

At this point I'm pretty sure I've forgotten

Are you aware, however, that it is a 4GL?

it was originally Structured

but by now it's .. is there a term for when an acronym becomes the predominant name for something?

and no for some reason I thought it was 3GL

10:51 PM

@jcolebrand [citation needed]

1 hour later…

ConcernedOfTunbridgeWells

11:52 PM

@SimonRigharts 3GLs: COBOL, C, Pascal, Fortran etc.

4GLs - although 4GLs are supposed to be all sorts of things, in practice the most (only) really meaningful application of the term is a category of languages developed for making business applications.

language generation is a bit of a fuzzy concept once you go beyond 3GL though

ConcernedOfTunbridgeWells

Oracle Forms, Synon, Informix 4GL, DBase/Clipper and that family, Progress, Powerhouse etc.

Yes, but if you actually looked at products marketed and described as 4GLs, those are the sort of langauges that people mean.

Oh god. Why is it the same people that accidentally cross join tables that think the new-style join syntax is pointless bloviation :(

(I have actually heard that exact phrase)

ConcernedOfTunbridgeWells

Stop calling it 'new style' join syntax and use its correct name 'ANSI join syntax'

Tell them it's what the standard says.

Yet another case for the need to be able to punch people in the face through the internet, I guess

ConcernedOfTunbridgeWells

11:56 PM

Can't you do it in person?

Or was this someone on SO?

no, someone in my org

different building though

ConcernedOfTunbridgeWells

There you go. Do it in person. :D

so going over there to punch them in the face falls foul of my effort/reward ratio

I guess I could stack them up and do a whole bunch at once

ConcernedOfTunbridgeWells

Who needs the internet - face to face contact is so much more satisfying.

Sometimes face to fist contact is even more satisfying.

I'm sure the police would understand "But officer he was using obsolete join syntax!" as a gross provocation, right?

ConcernedOfTunbridgeWells

11:58 PM

Well, I wouldn't convict you if I was on the jury.

That's ... reassuring

ConcernedOfTunbridgeWells

You know I did the data model for the juror history search used by the courts department, don't you.

Probably you didn't know that.

« first day (378 days earlier) ← previous day next day → last day (4504 days later) »

Transcript for

Jan '1217

The Heap™ – Consultancy ©®

General on- and off-site discussion for dba.stackexchange.com....

frogs mysql nosql oracle postgresql sqlite sql-server

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC

site design / logo © 2024 Stack Exchange Inc; legal

mobile