The Heap™ – Consultancy ©® - 2015-08-28

« first day (1697 days earlier) ← previous day next day → last day (3154 days later) »

Mikael Eriksson

6:24 AM

@Erik Regarding this question.

It is a lot to take in there and I doubt that I get it all but to me it looks like you are looking from something like this.

select top(@NumberOfItems)
  P.PhotoId,
  P.[Key],
  P.Created
from Content.Photo as P
  inner join Content.UserPhotoAssociation as UPA
    on P.PhotoId = UPA.PhotoId
where
  -- Older than CutoffTime
  P.Created < @CutoffTime and
  (
  -- My photos
  UPA.UserId = @UserId or
  -- Photos by someone monitored by me
  UPA.UserID in (
                select CC.Monitored
                from [User].CurrentConnections as CC
                where CC.Monitor = @UserId
                )
  )
order by P.Created desc, P.[Key]

Of course you need to add all the other stuff of counting and filtering but that is what is the gist of it as I understand it. And a bit simpler than counting the values for fetch ... offset before hand.

Posted here first before posting an because there is a good chance I have missed something important.

7:27 AM

morning

anybody care to downvote this

-5

A: Is adding the join columns to a table a good practice to improve performance?

Joins is not recommended if you need good performance by your SQL query. Join will consume Memory and resource. Therefore if you are connecting big tables then it will take much time to finish given search process. Inline query also not recommended since it will save temporary table of given inl...

Sure.

@TomV yep - dreadful and confused (not to mention confusing for a newbie). Of course JOINs will affect performance - and I don't even really understand what he's saying about "inline query"s.

Perhaps they mean correlated subqueries

I was musing on that too.

> Inline query also not recommended since it will save temporary table of given inline query output.

Parsing failure.

My guess was he meant subqueries in the select list using tempdb

but your guess is as good as mine

7:43 AM

@PaulWhite Yes, "Parsing failure" - AKA "problem between user keyboard and user chair"! :-)

1 hour later…

ConcernedOfTunbridgeWells

9:06 AM

i.imgur.com/SHFfnhM.gifv

9:40 AM

I suspect the person who's suggested this change is the OP. No clear evidence, of course, just that the OP's name looks like a shortening of the editor's.

@AndriyM That's what I assumed, also given that both have rep of 1.

Maybe I'm getting lax though as I used to reject edits like that...

@AndriyM Yes same person.

@Colin'tHart I usually reject even when I can see it's the same person, because it's easy to merge accounts. Submitting suggested edits to your own question just makes busy work.

But then we should have a one-click tool for suggesting account merges.

Or does such a thing already exist?

Not that I know of. Easy to find link in the help though.

2 hours later…

11:41 AM

Haha, this user has a .jason file!

0

Q: I want load the Jason File in my local desktop , that i want to load in postgres

I have .jason file in my mac book, that I want to load in postgres server, Kindly navigate me how to do it in simple way, I'm not jason script user, please describe me each and every step very clearly.

12:19 PM

They might be the only person to get the name right. Everyone keeps missing a letter.

12:59 PM

@ConcernedOfTunbridgeWells Unfortunately I can't make it. One of my work colleagues is leaving today so will be out for their leaving drinks.

I can't come either

1:11 PM

Nor me.

I can't either

Who can come?

1:27 PM

COTW

ConcernedOfTunbridgeWells

@MartinSmith Never mind.

@JamesLupolt I think we're down to you, me and possibly @MarkSinkinson

It's getting quite late notice to cancel it. I will make an appearance at Richmond and stay until at least 9 unless both @JamesLupolt and @MarkSinkinson get in touch and say they're not coming.

I'm not sure if I'll be able to come. It depends on what happens at work in the next few hours.

ConcernedOfTunbridgeWells

2:06 PM

@JamesLupolt Would it be better to can it for today and re-schedule in a couple of weeks?

2:27 PM

Not that I'm invited but I don't think I'll be able to fly to Europe today either...

@MikaelEriksson I think your solution is sound and much nicer than the direction I was heading. Ironically this query started off as a TOP and when the new requirements came I had it in my mind that I needed to page instead. Yesterday when I was updating my progress I realized that I couldn't filter just on time because the time stamp isn't required to be unique across users. As such I think I need to have the KEY be the input to the stored procedure and filter off the key's row number

An explanation of that edge case is at the very bottom of my very long question. I'm happy to accept your solution and then post another question if I have trouble converting the query to use row number/key if you think that is the most appropriate. When I posted the question I didn't realize/think about the edge case that will cause filtering by time buggy.

@ConcernedOfTunbridgeWells is your username a play on "Disgusted/Delighted of Tunbridge Wells"? en.wikipedia.org/wiki/Disgusted_of_Tunbridge_Wells

2:48 PM

Hey who wants a brain teaser

It's deadlock related!

@JNK Shoot

I have stored proc that runs a lot

every time a user finishes some work, it basically says "what's the next thing to work on?"

Basic flow is, it queries a queue table, gets first record, then locks that record

in a transaction

recently I have been having a number of deadlocks when 2 ppl run this thing within 10ms of each other

Sound problematic already

you should be getting and locking the record atomatically

it is atomic

eg select for update in Oracle

2:51 PM

oh you mean update top 1

Which platform? Can you show some code?

DUH

why the hell did that not occur to me

man I'm glad I mentioned it

Can you show some code?

The third most popular developer question about sql: what defined online coding standard for TSQL do you guys use?

sure colin one sec

2:53 PM

@JNK just because you are doing sequential operations in a transaction doesn't mean you're not susceptible to deadlock

oh I know

OK, just making sure :-)

OH so part of the reason I do a select

Is I have to return a bunch of attributes of the record

but you could update one record first to mark it and then select the other attributes

SELECT TOP 1
	@a = a, @b = b
FROM
       MyTable
WHERE
<conditions>

this is obviously greatly wsimplified

@Colin'tHart problem with that is each worker can have like 5 things open

these are in tabs in a browser

they can be working in 5 tabs

or more

2:55 PM

where's your locking code?

so I can't jsut assume that whatever record is marked is the one I just did

its a sep subprocedure

Why? Doesn't sound atomic to me

but basically it is
UPDATE MyTable SET UserID = @UserID, StartTime = SystDateTime()

the subprocedure has other logging stuff in it as well

and I also need to handle the case where the process doesn't do anything

i.e. there is no next thing to work on, which happens

@ConcernedOfTunbridgeWells I'm unfortunately not going to be there. In class with Paul and Kimberly this week and my brain is rather frazzled!

3:11 PM

Anyone have experience with redgate deployment tools?

@JNK UPDATE has an OUTPUT clause.

Remus Rusanu has a very good post about sound queue patterns in SQL Server. Using Tables as Queues

2

Critical sections can also be handled with sp_getapplock.

@PaulWhite of course...I use it elsewhere in related processes

Never occurred to me to do it here

I'll have to refactor eventually (soon)

our transaction level held steady until a month ago when we added a new client

which bumped it up 50%

That sort of sudden increase will test most designs :)

Did they also bump your salary 50%?

@PaulWhite Wouldn't that be nice! :)

@PaulWhite they bumped it SOME

3:24 PM

@JNK SOME = Sub-Optimal Montary Effusion?

@Erik LOL

user image

looks like my homeland is about to get crapped upon

@JNK Well that's better than what usually happens. Good to hear.

@swasheck Ha ha that reminds me of the story recently where a shop put an unfortunate extra 'c' in 'discounts' in a display/advertisement.

@PaulWhite it was coupled with a promotion though so could be coincidence!

'avagoodweegend!

3:44 PM

@ConcernedOfTunbridgeWells I think so.

Ugh, the devs on this project are bad at databases. This is a table for warehouse inventory:
CREATE TABLE [dbo].[BDIVTRAN](
[VVTDLT] [char](1) NOT NULL DEFAULT (''),
[VVTCO#] [numeric](2, 0) NOT NULL DEFAULT ((0)),
[VVTWH#] [numeric](2, 0) NOT NULL DEFAULT ((0)),
[VVTYMD] [numeric](8, 0) NOT NULL DEFAULT ((0)),
[VVTRF1] [char](25) NOT NULL DEFAULT (''),
[VVTRF2] [char](25) NOT NULL DEFAULT (''),
[VVTTXN] [char](20) NOT NULL DEFAULT (''),
[VVTPC] [numeric](6, 0) NOT NULL DEFAULT ((0)),
[VVTQTY] [numeric](9, 0) NOT NULL DEFAULT ((0)),

The char(25) are names of varying lengths. They have # in their column names.

And then when I looked at the data, it contains inventory snapshot data as well as transactions, denoted by the VVTTXN column.

What is the policy on dupes (and de facto dupes)? I think pointing towards a definitive answer for FAQs is a good idea - how do we discourage repeat answers? Downvote the dupe? Upvote the original? Both? Neither? Check here and here.

@Vérace I looked at that example long and hard earlier and failed to come to a firm conclusion.

Upvote good questions and answers. Downvote poor ones. Dupe-ness isn't really a factor, unless you're thinking about a basic failure to search for a very common question. Once a question is closed as a duplicate, no more answers can be added. In the case of the target, a 15K user or mod can protect it. Ultimately, it can even be locked. A me-too copy answer added long after the original can be flagged.

In some cases the newer question has the better answer. Personally, I don't have a big problem with closing the earlier question as a dupe of the latter, but I can see why people might think that's weird.

For exact dupes, I will merge if it makes sense to do so, and no one loses out too badly.

39

A: Should I vote to close a duplicate question, even though it's much newer, and has more up to date answers?

If the new question is a better question or has better answers, then vote to close the old one as a duplicate of the new one. You can flag and ask a moderator to merge after closure if they're exactly the same. If they differ based on the versions of the relevant systems, then they're not real...

4:13 PM

113

Q: How should duplicate questions be handled?

What should I do when I see a question that is a duplicate of another one? Should I answer it? Should I downvote it? Should I comment? Should I edit the question to indicate it's a duplicate? Should I tag the question with the duplicate tag? If I can, should I (vote to) close the question? What...

support faq exact-duplicates

There could be hundreds of different, related, perfectly valid questions on the same topic. There is no One True Question.

--- end

zzzzzzzzzzzzzzzzzzzzzz

Sorry @swasheck

not your fault

i'm just sleepy

Oh I thought that was commentary ;)

nope

4:20 PM

Then I'm not sorry :)

@Vérace If you follow the trail back, the dupe target in this case is probably a dupe of this, which has epic votes. However, the answer is arguably inferior to the later ones.

@PaulWhite you dont need to be ... so great

Now I'm sorry again.

stop it. you're kiwi, not canadian

4:35 PM

@PaulWhite And the answer deserves "epic" votes - but it does beg the question as to why the original answer wasn't improved (point harvesting?). I'm trying to go over my old answers and edit them rather than re-posting answers of yore!

@swasheck lol

@Vérace Not sure. I agree that improving old answers is generally good. I think I'm going to dupe that chain. Middle answer and current dupe target seems best, agreed?

@PaulWhite on this question did you/someone remove my comments because I was acting like an American jerk or because they weren't helpful in general?

@Erik I considered them obsolete, given later comments.

ok cool

4:51 PM

@Erik Hopeless title-only edit by the OP. Leaving it to rot. Will auto-delete soon.

Trickle-loading a columnstore today and it only takes twice as long compared to a b+tree. I was worried it would be worse...

@MarkSinkinson Oh, my coworker Jess is in that class.

@ConcernedOfTunbridgeWells So I won't be able to make it either. I'm away from the 6th-20th of Sept, but will do my best to be at the next one if it's not in that window...

@JamesLupolt 2014 or 2016?

@PaulWhite 2014. I haven't read much about the columnstore changes in 2016 yet, aside from the wacky clustered columnstore with nonclustered btree stuff. It sounds like I should!

5:09 PM

@PaulWhite yeah that question was doomed from the start. I suggested listening to the error message they translated to the question and they thanked me for answering the question.....

We gave it a fair go, that's for sure.

are you sure?

I stopped myself from leaving a comment asking what the point was of testing something that's going to run on Oracle on a different database with different syntax rules and different performance characteristics (and possibly different semantics).

@swasheck well I'm not positive because as the wise people of Fern Gully said only fools are positve

@JamesLupolt Goodness only knows what the real scenario is.

5:13 PM

@JamesLupolt That was one of many clues that the situation was hopeless....

@JamesLupolt Good summary chart here

@PaulWhite Thanks, RCSI support will probably help me. Any idea why multi-threaded batch mode support came before single-threaded?

@JamesLupolt My understanding is that the initial implementation targeted parallel only because: columnstore -> large data -> parallelism. Adding serial batch mode was based on user feedback on real-world usage.

5:33 PM

@Erik You get a star for the Fern Gully reference.

was considering flagging it

2

A: In Sql Server, is there a way to check if a selected group of rows are locked or not?

If I understand the request correctly, the goal is to delete batches of rows, while at the same time, DML operations are occurring on rows throughout the table. The goal is to delete a batch; however, if any underlying rows contained within the range defined by said batch are locked, then we must...

Winner.

...

@swasheck Flagging what?

@PaulWhite the fern gully reference. it was a joke.

clearly not a good one

Oh. I was away and didn't even see that.

5:52 PM

@Erik Thanks a lot. =)

@swasheck If you think it was offensive then please flag and remove it. I meant it as a silly reference to a silly old movie. Which is the way I think @mmarie took it. I wasn't trying to be derogatory/judgmental/<insert negative intent>

Look, @swasheck. Another person who doesn't get your humor.

@MDCCL Anytime, but why?

@mmarie genius is never fully recognized in its own season

13 mins ago, by swasheck

@PaulWhite the fern gully reference. it was a joke.

@Erik Hahaha, Hi, I've just logged in and received a notification from this message: chat.stackexchange.com/transcript/message/23710675#23710675

@Erik And, of course, congratulations on your spectacular reputation rise, more than 1000 pts. in less than a month!

5:57 PM

yesterday, by Erik

@PaulWhite I think @MDCCL consistently pumps out really detailed quality answers to modeling questions I noticed the one you highlighted too.

@MDCCL Sure thing. I also called you out in this answer related to your answers before I knew how to reach out to people here. Let me know if you want to be edited out.

@PaulWhite Thanks to you, Paul, as well. It still needs some small details/"polishing" added, so I will include them ASAP.

My rise has more to do with semi-productive slacking when I should be writing load testing (no desire to be a professional tester) than personal brilliance

@Erik Do not worry about that post. One thing that comes to my attention from your kind comments in there is when you mention that you can't follow the notation and / or concepts fully. In this regard, I think that I could enhance my writing style or provide some more sources in order to make my answers more approachable. I would really apreciate your feedback on that aspect. =)

@PaulWhite wowsers. that must've been bad. "(no history available)"

6:09 PM

@Erik It's just great that you come here and take the time to share your knowledge and talent with the community. Testing is not my favourite ambit, either, ha ha ha; but if it has to be done, it has to be done in the best possible way, 'tough I don't have much experience in this respect.

@swasheck Nuked from orbit. It's the only way to be sure.

@MDCCL I'll think about that and get back to you with a more refined answer. Right now the best I can do is a crummy math analogy. I think math wonks can express themselves using notation that is perfect for expressing their ideas, but it is incomprehensible to laymen.

Invariably I'm able to understand the essence of your answer (I think), but there are times when I think I lose some of the details/nuances. I'm sure I could pick them up if I really needed the information in the answer though. Does that make sense and/or help?

@PaulWhite lol

:)

user image

@swasheck move along, move along, nothing to see here...... :p

6:23 PM

@Erik Sure, It makes sense and is helpful too, thank you. I get what you mean, and in this regard, one of the intentions I have when droping that kind of concepts (that could seem unpurposely pedantic) is that, when used in the apropriate context, it can motivate seekers who are not familiar with such concepts to search for papers/books/resources of value, hopefully it has worked some times :)

@Erik Reading and re-reading, and... relational theory has been extremely helpful for me, so I consider it can be useful for other data/programming professionals, as well.

@swasheck It's the trash can.

by invitation only :)

6:42 PM

An invitation to a trash can – that sounds... special.

@MDCCL I agree.

@PaulWhite We have a lost soul in need of a Shepard dba.stackexchange.com/a/112567/72091

@Erik Completed. Up to the user to complete the merge now. Thanks for the alert.

@Erik On a side note, I see that you work with C# so, have you played with ASP.NET Identity?

@MDCCL A bit. I've done mostly forms auth though. How can I help?

@Erik I'm planning to engage on some kind of experimental/sample personal system. I have worked mostly with ASP.NET since some time ago, but I am considering some alternatives. I have never completely liked Membership nor Simple Membership.

@Erik I will document myself on ASP.NET Identity and perharps will ask for your opinions on the future, if you are OK with it, of course. Maybe posting some questions on StackOverflow could be nice, so that I can offer extra motivation.

6:58 PM

Sure thing. I'll be glad to let you know what I think. Beyond the motivation I think StackOverflow is a good idea in general because like I said I'm no expert.

Truth be told I feel more comfortable in C# than SQL not that my rep counts reflect that.

@Erik Thank you. I am thinking about trying other stuff, like RoR or Django, but it's still a very immature idea.

@Erik I really like C#, it's a great language, but maybe it's time to play with different toys.

@MDCCL Anytime. Virtually all my experience is with the Microsoft stack so I'll be of very limited use if you need help in those arenas. New toys are always fun. :)

@PaulWhite Of course.

7:31 PM

fun with data

7:52 PM

@MDCCL I'm trying to handle your flag, but not quite sure what moderator action you think is necessary?

So I'm going to decline it. We can still discuss the issue here (or privately if you wish).

@PaulWhite OK. Sure, how can we discuss it in private?

@MDCCL Give me two minutes. I'll be right with you.

...

@Calvinthesneak It seems you have two accounts. If this is unintentional, please visit the help centre, where you can merge your accounts.

@PaulWhite OK. BTW, you could flag my flag as NOT CLEAR WHAT YOU ARE FLAGGING. =)

@MDCCL ha ha :)

flag that

8:05 PM

I have waaaay too many tabs open right now.

I'll be around here for some time, we can deal with that issue when you have more space, if you wish.

Done.

8:40 PM

@PaulWhite Done and done.

is this an ok suggestion, @PaulWhite ?

i'd go with "... shouldn't be so concerned" because you still need to exercise a good deal of judgment when sifting through suggested/missing indexes. that missing index suggestion doesn't take your whole workload into consideration - even if it is part of your regular workload. — swasheck 1 min ago

@swasheck Yep.

yayme

@swasheck You can also recommend to use optimize for ad hoc workloads

8:57 PM

@Kin yeah. thought about that.

There are sorts of weird and wonderful things he could do, but none of the really interesting ones are supported, and I'm not sure why he wants to do this.

Anyway, that's me done for the time being. Catch you all later.

sure. use your frickin' brain

2 hours later…

11:24 PM

Yes, sounds like OP is trying to run a query that someone doesn't want him to, and leave no trace

Or maybe not

« first day (1697 days earlier) ← previous day next day → last day (3154 days later) »

Transcript for

Aug '1528

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