The Heap™ – Consultancy ©®

2:18 AM

Hey @PaulWhite what's with the 250 bounty on that Access question? I thought Access was not a real DB (joking... kinda)

in The 2nd Monitor, 8 mins ago, by 200_success

There's a code review question on DBA with a +250 bounty, if anyone is interested.

Just found myself a bit curious about it :)

2 hours later…

3:50 AM

@Phrancis Lots of factors. There's no good reason we can't have 'expert' Access questions here; we shouldn't discriminate on DBMS alone. Also, this meta question. Some of it is about potentially attracting good contributors here.

Think of it as experimental. It might work; it might not.

OK. I'm taking a stab at it as I've had the misfortune of using Access more than I'd like to admit. But I must say, what the OP is trying to do is really pushing the limit

Poor little Jet DB optimizer must be crying in the corner.

It strikes me the question is as much about algorithm and approach as it is about Access per se.

Yeah, I get that impression as well.

12 hours ago, by Paul White

@MaxVernon And for the love of fluffy bunnies don't use a UDF in a constraint.

^^wat

@Phrancis It has the potential to perform terribly, and may even result in incorrect behaviour.

12 hours ago, by Paul White

Example of a UDF being unreliable in a CHECK constraint.

I meant more like... who does that?

Wow that last query of theirs, with the first set in the FROM nested 3 levels deep... wow

4:09 AM

@Phrancis Ah I see. I didn't quite get the multi-layered nuances of the "wat"

lol

1 hour later…

A: Access (Jet) SQL: DateTime stamps in TableB flanking each DateTime stamp in TableA

5:22 AM

I must first compliment you on your courage to do something like this with an Access DB, which from my experience is very difficult to do anything SQL-like. Anyways, on to the review. First join Your IIF field selections might benefit from using a Switch statement instead. It seems to be some...

5:43 AM

@Phrancis Where you say "old ANSI-92 join syntax", I think you mean "old ANSI-89 join syntax".

5:55 AM

Oh. Whoops, let me fix that, nice catch

srutzky

6:27 AM

@PaulWhite I found a few good candidate answers. I need to clean one of them up now that I am seeing it again ;-). I will post some options tomorrow. But, I did manage to find something interesting here ( sqlservercentral.com/Forums/FindPost1725137.aspx ). It seems to be an optimizer issue with SQLCLR TVFs and NVARCHAR(MAX) and in a rather specific situation. Not sure if you have any insight on that one ;-)

@srutzky Thanks. It's not super-vital you find the One True Answer here, but clean-up and review of old answers is always a good thing, so I won't discourage you from doing that :)

5 hours later…

11:52 AM

@Phrancis Nice. But these triple parentheses before the SELECT stops my poor brain from parsing the query. Any chance of a better formatting?

Oh, never mind. It makes some sense.

@ypercube The core of the problem reminds me of your response to my top-n-rows-per-group question, for systems that don't support APPLY or ROW_NUMBER.

@PaulWhite Yeah, I remember that. But that approach (which I've used in MySQL not Access) works fairly well when the base table (A) or the number of distinct values needed is small.

Here A has 1M rows and B has 50K.

Yes, it's a question of efficiency. Just noting the correlation.

It's a wonder we managed to write queries at all in SQL Server 2000 and earlier sometimes.

And I haven't grasped the 2nd part of the problem. That "flanking" thing is a barrier ;)

@ypercube I think it's a simple as finding the one or two location readings (one for an exact match, one either side for no exact match) for each X then interpolating (if necessary) to calculate a location. The interpolation is obviously on the basis of the first table timestamp vs the second table timestamps.

Flanking = closest either side of the known timestamp.

The question makes it all sound a lot more complicated than it is.

Does that make sense to you?

4 hours later…

3:44 PM

@PaulWhite yes, that helps (sorry, I was on wifi from mobile and lost signal)

No worries.

3 hours later…

7:00 PM

Eng - Aus live on swedish tv. Time to learn the game i think. @PaulWhite you're a rugby fan right?

@MikaelEriksson Yep.

Are you watching, sleeping or don't care because it is Aussies?

I suppose he is roooting for England, to piss them off ;)

7:23 PM

@MikaelEriksson Watching yes. Happy for either side to win; means the other side loses.

Tough to support Aussie, though it might be better for us if they do win

Ha! Try!

@PaulWhite Swedish commentary makes a good job explaining what is going on. Probably unbearable for someone knowing the game.

My daughter liked rugby after I explained that the main thing is that someone catches the ball and all the others are trying to fall over and throw him down. She thinks it's fun.

7:38 PM

That's essentially it, yes.

1 hour later…

8:42 PM

@PaulWhite you think farrel deserved the yellow card or not? Commentator here is not sure.

8:53 PM

@MikaelEriksson Yes. Tackle without the ball and lead with the shoulder.

Australia 33 - England 13 Full Time

The hosts of the tournament are out with a game left to play. Crikey.

@PaulWhite ok thanks. It was in my inexperienced opinion a really good game.

A: What is fragmenting my index on a table with stable traffic?

@MikaelEriksson Yes, quite exciting in places.

Some good rugby as well.

Australia might well be favourites now.

1 hour later…

srutzky

10:03 PM

After review and cleanup, it seems that the vast majority of SQLCLR questions are over at S.O. But there is one question I like that is not specific to SQLCLR but my answer is:

http://dba.stackexchange.com/questions/44764/central-stored-procedure-to-execute-in-calling-database-context/112353#112353

Of course, it was asked and answered before I joined DBA.SE, and I only chanced upon it a month ago. I think my preference would be for this one mainly to raise visibility for a scenario where SQLCLR provides for a rather elegant solution that is simpler to use, and provides better functionalit…

srutzky

10:23 PM

@PaulWhite Or, if for any reason that question / answer doesn't fit within the spirit of what you are wanting to do, then any of the following 3 would be fine (and I think I figured out how to properly post those now, we shall see ;-):

In General Just to be clear at the start: fragmentation refers to when the next logical data page (i.e. the values in the fields for 1 or more rows), is not the next physical data page (the location of page in the data file). Non-fragmented pages (in Physical Page order): Logical Page 1: A1, ...

A: How To Strip Hebrew Accent Marks

The trick here is to realize that these characters that you see in the question with the "accents" aren't really the characters (i.e. "These aren't the droidscharacters you are looking for" ;-) ). The "accents" are various types of notations indicating things like: vowels (lines and dots that a...

A: UPDATE performance where no data changes

If I have an UPDATE statement that does not actually change any data (because the data is already in the updated state), is there any performance benefit in putting a check in the where clause to prevent the update? There certainly could be as there is a slight performance difference due to ...

woo hoo!

1 hour later…