The Heap™ – Consultancy ©®

Paul White

5:34 AM

@Taryn which hint?

hot2use

Morning

can be deleted:

https://dba.stackexchange.com/q/212725/15356

Paul White

5:51 AM

done thanks

O.Rares

6:12 AM

morning

if I have a one to many table with id from one table , id from another table and an order Id is it better to use the reference tables in inner joins ?

or to set them at FROM and combine the inner joins?

Andriy M

@O.Rares Could you illustrate that with an example, please? I'm not sure I understand the distinction you are trying to make.

Morning

O.Rares

SELECT c1.name,
i1.Name FROM item it
INNER JOIN category c1 ON it.idCategory = c1.Id
INNER JOIN ingredient i1 ON i1.Id = it.idIngredient
WHERE it.orderId = ?
GROUP BY c1.name

the thing is

ingredients are many

this query returns 0 values

for any given orderId

Andriy M

6:28 AM

@O.Rares You mean, there can be many ingredients per item?

O.Rares

yes

for example orderId 1 ,category 1 and 5 values for ingredient

so 5 rows

and I want to get name of the category and 5 ingredient names

again 5 rows

Andriy M

Well, from the above query, an item row is referencing and ingredient row, so there can only be at most one ingredient connected to a given item. If you want many ingredients per item, then an ingredient should be referencing an item

O.Rares

I know that only return one value but when I execute it return nothing

isn't this considered a reference ?

INNER JOIN ingredient i1 ON i1.Id = it.idIngredient

Andriy M

Can you start from a smaller query, like SELECT * FROM item it WHERE it.orderId = ?, i.e. without any joins, just to make sure you get rows at that point. Then add one join, then add another instead, and you should be able to see when the query stops returning rows

My wild guess is references are either null or invalid (the latter implying there are no foreign keys defined to ensure integrity)

In any event, if it's many ingredients – one item, then I would expect the join to ingredient to look something like INNER JOIN ingredient i1 ON i1.idItem = it.Id (if I follow the naming convention right)

O.Rares

do we need fk here ?

your query works

this doesn't SELECT c1.name FROM category c1
INNER JOIN item it ON it.idCategory = c1.Id
WHERE it.orderId = ?

it recognize the Id column but returns 0 rows

Andriy M

6:42 AM

Then at this point the problem is with the it.idCategory reference, and I mean "problem" in a loose sense. The value or values are either null or invalid (i.e. referencing non-existent category IDs)

It's the "invalid" case that foreign keys are meant to prevent. When you have a foreign key defined, you just can't have invalid references. You can have nulls if the referencing column (idCategory in this case) is declared to be nullable.

O.Rares

you're right

spent 3 hours thinking that the query is bad when the Ids are all 0

thanks a lot !

Andriy M

Happy to help and glad it's resolved!

Tom V

7:01 AM

@McNets Haha I feel your pain

McNets

Morning

Tom V

Morning

McNets

@TomV I feel like a hamster in a maze

Tom V

@McNets Maybe you already told me, but which ERP did you guys decide to go with?

McNets

@TomV Sage Enterprise Managment (X3) sage.com/en-gb/sage-business-cloud/enterprise-management

On-premise

Tom V

7:03 AM

And what problems are you facing?

McNets

Basically I need more time

And it must be certified as FDA compliant

Mathematics

7:19 AM

Morning Every Single One Of You, In This Room

O.Rares

Morning Mathematics

Colin 't Hart

8:28 AM

Morning

2

Q: What can be best database schema for multiple project and one database?

I am working on multiple projects but the back-end will remain the same. I have a different server for auth, data, etc. and one database to store all the details. If one projects are creating any data then only that project can access that data and others project shouldn't access that. For exam...

Lame question, lame answers

All IMHO of course

Tom V

8:50 AM

0

A: Good explanation of cascade (ON DELETE/UPDATE) behavior

well, perhaps we can rationalize the syntax. Let's take a Python example: class Parent(self): # define parent's fields class Child(self): # define child's fields parent_pk_is_childs_foreign_key = models.ForeignKey(Parent, on_delete=models.CASCADE) what this line says is on_del...

That's strictly not even a python example but a Django example

Colin 't Hart

What a weird answer.

dezso

10:22 AM

'In fact it is often a desired behavior to leave children of a deleted parent, due to auditing and reporting reasons, as well as recovering accidental deletions' - that would be disastrous in a (sane) database. — dezso 10 secs ago

Taryn

10:55 AM

@PaulWhite HASH on a LEFT JOIN

Paul White

Ooo that's pretty heavy

Thanks

Taryn

No matter what we could not get a plan that we wanted to all users

Paul White

Well if it works, it works eh

Adaptive joins for row mode processing will help in the future

Taryn

took a few weeks of tweaking various things to just use that

Paul White

Was it a big complicated query?

I know, 100 questions, but these things interest me

Taryn

10:59 AM

no worries, I'm happy to share and maybe you'll have different suggestions

It's not like I really know what I'm doing half the time :)

Paul White

Well unless it runs against the downloadable SE database or SEDE, I probably couldn't say much about the specifics, but it is interesting to hear about the issues people face anyway

Taryn

unfortunately the rephistory table isn't public

So we has two that were giving us issues

SELECT CAST(CreationDate AS Date) Date,
	SUM(CASE WHEN rhd.PostId IS NULL THEN RepChange ELSE 0 END) RepChange
FROM RepHistory rh
LEFT JOIN (SELECT PostId FROM RepHistory WHERE RepHistoryTypeId = 17  AND UserId = @UserId) rhd
	ON rh.PostId = rhd.PostId
WHERE rh.UserId = @UserId
GROUP BY CAST(CreationDate AS Date)
ORDER BY CAST(CreationDate AS Date)
OPTION (OPTIMIZE FOR (@UserId UNKNOWN));

The tiny one

 Select Cast(CreationDate as Date) CreationDate,
        Sum(RepChange) RepChangeTotal,
        Sum(Case When RepHistoryTypeId In(17)
                    Or pd.Deleted Is Not Null
                Then 0
                Else RepChange
            End) RepChangeWithoutDeletes,
        Sum(Case When RepHistoryTypeId In(5, 6)
                Then 0
                Else RepChange
            End) RepChangeWithoutDownvotes,
        Sum(Case When RepHistoryTypeId In(5, 6, 17)
                    Or pd.Deleted Is Not Null

and the not so tiny one

the second one is used on the /users/<id> page and the first is the network rep graph on stackexchange.com for a user

Paul White

I see

I would prefer adding HASH JOIN to the OPTION clause over using LEFT HASH JOIN in general.

The second one adds an implicit FORCE ORDER, which is not obvious and can be problematic in future

Taryn

I'm sure this will be something we'll have to revisit again in the future. These have been troublesome for years

working for some high rep users, but not others, working for mid-rep users but not others

Paul White

Yes they are good examples of a common problem

Taryn

11:10 AM

Here is the plan for the second one - brentozar.com/pastetheplan/?id=SkQxQHyNm

Paul White

Yep I see

Taryn

When I first started tuning it I got a much different, better plan that worked for everyone and we even forced it using query store for a bit.

But since we have so many sites we didn't get the same plan on other problematic sites and no matter what we did we couldn't get the different plan

So it was working on SO but not on MSE or SoftwareEngineering

Paul White

It is a problem for plans that need not to recompile every time

And where temporary table use is not suitable

Taryn

Yup, I got the plan I wanted if I forced recompile but we can't use that in prod

Paul White

So that's the reason for errors on some user tabs a while back

Taryn

11:18 AM

exactly and when we did the failover last week it brought them back

Paul White

AGs not fully integrated with query store then

Thanks for explaining the problem

Taryn

I just started turning it on. SO had it on already, which let me force the plan after the failover. We haven't turned it on most of the SE sites yet because it takes diskspace on servers we had been short of available space

@PaulWhite of course, anytime

Paul White

TRUNCATE TABLE dbo.Comments;

Solve all your space issues :)

2

Taryn

abuse my powers for good

Paul White

@Taryn I'll pass the outline of your problem onto the people that are currently looking into this area.

Taryn

11:22 AM

@PaulWhite cool, thanks!

Paul White

In the meantime, do try OPTION (HASH JOIN) instead of LEFT HASH JOIN if you get a chance. It gives the optimizer a bit more flexibility.

hot2use

11:37 AM

Related?

8

Q: Trying to view Jon Skeet reputation causes 500 error

There's this link that shows your reputation across all networks (just change the id with the appropriate one. me doesn't work). Trying to view Jon Skeet's... well, unsurprisingly, the server doesn't face the request.

bug status-completed reputation

Related?

Taryn

11:51 AM

@PaulWhite ok, I'll try that this morning

@hot2use yeah that's one of the issues

Taryn

12:51 PM

@PaulWhite as I expected, you were right. Making that change gives us the actual execution plan I wanted with a hash match - right outer join. Poked a dev asking them to make the query change

Paul White

Cool. There are probably more complete solutions, but it would require a more detailed investigation. And often it is right to stop with a good enough solution found :)

Taryn

Works for us, so we're good. If it breaks again, then I'll fix it again. Thanks again for the help

Philᵀᴹ

2:29 PM

I just wrote a table DDL generator in a single SQL statement. Must have been bored :-)

ConcernedOfTunbridgeWells

2:40 PM

I did that once, for much the same reason.

17

A: Tool for Scripting Table Data

Here are some scripts I wrote for reverse engineering SQL server schemas. They may be of some use. Also, as a general interest they give some examples of how to get various bits of information out of the data dictionary. I've added an MIT license below to make permission-to-use explicit and fo...

Evan Carroll

3:48 PM

@ConcernedOfTunbridgeWells there is no point in putting a copyright notice on code on this site, because you're releasing it under CC-BY-SA

you can add the MIT license, but it is only ever dual licensed -- you can't release code in MIT-exclusively and people are free to remove your notice under the terms of the CC-BY-SA

Jack Douglas

4:11 PM

@EvanCarroll CC-BY-SA/MIT etc (licenses) are orthogonal to copyright ownership and assertion

generally the CC licenses require you to keep the Copyright notice intact: en.wikipedia.org/wiki/Creative_Commons_license#Attribution

Evan Carroll

But that notice is provided by SO.

Which specifies how you are to attribute it.

Attribution Required

Jeff Atwood on June 25, 2009

All the content contributed to Stack Overflow or other Stack Exchange sites is cc-wiki (aka cc-by-sa) licensed, intended to be shared and remixed. We even provide all our data as a convenient data dump, seeded by us.

But our cc-wiki licensing, while intentionally permissive, does require attribution.

Attribution — You must attribute the work in the manner specified by the author or licensor (but not in any way that suggests that they endorse you or your use of the work).

I thought it was pretty clear what “attribution” meant, but given the semi-scammy way the content is popping up in some seedier areas of the internet, maybe not: …

It's useful for the purposes of dual-licensing, but you always have to accept the CC-BY-SA license which permits me to rip off the notice, and your name and link back to your profile and question, and stackoverflow alias.

Jack Douglas

> Since 2004, all current licenses (beside the CC0 waiver) require attribution of the original author, the BY component.[24] The attribution must be given to "the best of [one's] ability using the information available".[36] Generally this implies the following: Include any copyright notices (if applicable). If the work itself contains any copyright notices placed there by the copyright holder, those notices must be left intact, or reproduced in a way that is reasonable to the medium in which…

that's what the license says, I don't think it matters what Jeff says

Evan Carroll

4:30 PM

Heh, not sure how that would play out.

But it's a good link

I can try it next time ;)

Next time I want to delete a question that SO/SE doesn't want deleted, I'll just add a 500 line copyright notice to it in the middle of a code.

My assumption is that it would be "cleaned up" regardless of what the CC wiki says.

ypercubeᵀᴹ

5:00 PM

;A rather passive-aggressive edit;

dba.stackexchange.com/posts/212748/revisions

Andriy M

5:14 PM

Statement initiators, yes, sometimes they are just necessary.

Evan Carroll

6:42 PM

TIL about IFF

sp_BlitzErik

@EvanCarroll welcome to 2012

Evan Carroll

Wait until you get an actual bool type, and boolean context and I can welcome you to 1985

sp_BlitzErik

yeah that's super important

can't just use a bit

Evan Carroll

Seriously, a=b breaks the spec because you want = to be an implicit assignment operator for T-SQL. Who in the crap thought that was a good idea.

you can't just use a bit. because there is no bit-context, and there is no logical-bit operator.

sp_BlitzErik

james p. microsoft

Evan Carroll

6:50 PM

Balmer McGates

sp_BlitzErik

@EvanCarroll i have no idea what this means so i assume it's wrong

Evan Carroll

SELECT a=b ... WHERE a=b, has two-contexts for an operator over the same type. There is no operator in the projection that will return bit, so bit isn't a solution. And, there is no way force the bit-context (if that's what we're calling it) at the top, because that's TSQL-assignment context (or whatever they call it in the lexer)

afaik, there are no operators in the spec that are context sensitive.

Actually the only language that does that, that I can think of is perl.

sp_BlitzErik

so how does postgres handle it?

Max Vernon

magic

Evan Carroll

a=b returns the same thing in both contexts: true

jadarnel27

6:59 PM

VB.NET has context-sensitive operators.

sp_BlitzErik

yeah that sounds like something i'd complain about if i couldn't get people to use my database for free

Evan Carroll

because = isn't used for both assignment and equality-check. It's just always 100% of the time equality check in postgres SQL

@jadarnel27 They're fun, but only really fun if you have the ability to switch between contexts.

jadarnel27

I don't totally understand what you mean, but I disagree on the principle that nothing about VB.NET is fun.

Evan Carroll

hahahah

In Perl for instance, there is a boolean, list, and scalar context.

Max Vernon

VB.Net is compiled to the same object-code as C#. They are essentially exactly the same.

jadarnel27

7:03 PM

yesterday, by jadarnel27

You're on my list now, Vernon.

Different list now, buddy.

Evan Carroll

And you can control how your functions are used in those contexts with wantarray and what context the functions are evaluated in with scalar, or with by forcing list context.

Max Vernon

@jadarnel27 sweeeet

Evan Carroll

perl -E'sub foo { wantarray? 42: 7 }; print foo; print scalar foo;`

jadarnel27

@MaxVernon ;)

Max Vernon

C uses = for assignment, and == for comparisons. That eliminates confusion when looking at the code, unless you're a code-reviewer who doesn't understand that.

@EvanCarroll perl kills me. It's like regex for insane people.

jadarnel27

7:04 PM

@MaxVernon C# does that too, I'm a fan.

The plus sign in C# is used for a few different things. Arithmetic addition, string concatenation, delegate assignment.

Max Vernon

since SQL is declarative, I think it makes sense to use = for both assignment and equality.

Evan Carroll

usually you can use unary + to force something into a numeric context, lots of languages support that.

Including Python, iirc.

Max Vernon

context is important for declaratives.

Evan Carroll

7:17 PM

@sp_BlitzErik how good are you with math?

@sp_BlitzErik here is a question, given the two slopes, db-engines.com/en/ranking_trend/system/PostgreSQL and db-engines.com/en/system/Microsoft+SQL+Server Will Microsoft lose enough market share to be less popular than PostgreSQL before I get back from lunch. If not, how much longer could it possibly take.....

sp_BlitzErik

@EvanCarroll as long as the numbers are weight plate increments, pretty good

Evan Carroll

And it's the most biased metric available divorced from github and the entire open source community entirely

sp_BlitzErik

those are exactly the kinds of graphs i'd make if i were reaching for something

anyway, off to the dumb dumb hut!

have a great weekend

Transcript for

The Heap™ – Consultancy ©®