The Heap™ – Consultancy ©®

Paul White

03:25

Free life advice: only admit @billinkc is right about something under NDA.

2

-4

I am trying to insert data into Temporary table with the help of Select Statement The query is: insert into tempProductCategory1 select * from ProductCategory ; Problem is that this query running fine but takes so much time. When I check the execution status with the help of show process-list...

So that question has a bit of a tortured history, and 2 reopen votes right now. The OP has improved his question to the point where I don't believe people would vote to close it. Is that enough for a reopen?

Philᵀᴹ

07:28

@PaulWhite He'll still need to edit his post with the code for the other 2 functions that are in the query, plus provide explains for the queries in them. A question that looked incredibly simple is actually very complicated. A view with a select containing functions, does not a simple CTAS make

As it stands, it needs to stay closed IMO

Philᵀᴹ

07:41

0

Q: Need help optimizing the query with large dataset of 400+ Million rows to compare

I am trying to solve this logic problem but my current MySQL schema is giving me a very poor performance with a large dataset. I have a table which contains almost half a billion telephone records. They are called the Do Not Call list or simply the DNC. When I start my campaign to dial numbers, I...

mysql performance optimization

Got to love writing robo-calling software!

dezso

08:04

@ypercubeᵀᴹ I just checked the SQL 92 (in a German version), it allows every separator, including the newline character (or characters, I guess). Rather inconclusively, the only example actually uses newlines.

ypercubeᵀᴹ

08:28

@dezso check in the Syntax Rules.

There is an added rule that says:

> In a <character string literal>, <national character string literal>, <Unicode character string literal>, or
<binary string literal>, a <separator> shall contain a <newline>.

(that's from 2011 version but the 92 has something similar, too)

dezso

@ypercubeᵀᴹ which section is it?

ypercubeᵀᴹ

<literal>

dezso

@ypercubeᵀᴹ that's where I looked at

what I read here is that a separator contains any combination of whitespace, newline (system dependent) and comments, neither one is mandatory

ypercubeᵀᴹ

Yes but Syntax Rule 4 modofies that

I missed it too when I was reading it

dezso

however, this is not the standard itself but the translation of Date, C.; Darwen, H. (1996): A Guide to the SQL Standard

ypercubeᵀᴹ

08:38

> 4) In a <character string literal>, <national character string
literal>, <bit string literal>, or <hex string literal>, a <sep-
arator> shall contain a <newline>.

From (Second Informal Review Draft) ISO/IEC 9075:1992, Database Language SQL- July 30, 1992

Philᵀᴹ

It's too early in the morning to be reading the SQL standard document :P

ypercubeᵀᴹ

@dezso To me, that Rule seems inconsistent with another rule, at 5.2 <token> and <separator>

> 7) SQL text containing one or more instances of <comment> is equiv-
alent to the same SQL text with the <comment> replaced with
<newline>.

dezso

@ypercubeᵀᴹ possibly the 42nd formal one removed this distinction :D

ypercubeᵀᴹ

It's still the same in the 2011 though

with the addition of Unicode literals

dezso

I cannot find a pointer in this book to that, which might be my problem with matching the German text with English or that of the book with missing this point

but all this, in conjunction with ietf.org/rfc/rfc2119.txt, means that PostgreSQL is standard conforming while MySQL not?

ypercubeᵀᴹ

09:16

@dezso if only MySQL was not conforming to this part ;)

@dezso 9.6.1 is released on Thursday probably

dezso

09:36

@ypercubeᵀᴹ heard that, too

we just finished all minor upgrades to the current ones (9.2-9.5), so we can start over (again)

ypercubeᵀᴹ

Yep: postgresql.org/docs/devel/static/release-9-6-1.html

probably to be corrected/updated before the release.

Andriy M

Does "Azure Database(Oracle) Instance" mean essentially "Oracle Database instance on Azure"? The former sounds confusing to me.

Philᵀᴹ

Yes

Andriy M

Thanks

Paul White

10:13

@AndriyM If you don't like the name of a cloud service, just wait 15 minutes.

4

Philᵀᴹ

10:24

1

Q: How to group all linked objects in a undirected cyclic graph

I have a table that has customers and its joint customers. e.g Customer 1 has a joint customer 2. Customer 1 is also a joint customer to Customer 3. I am trying to group all customers that are linked and assign same GroupCustNo to them. In below table 1-2 are linked, 3-1 are linked. So 2-3 are a...

sql-server sql cte recursive

That's an interesting one

Andriy M

10:57

@PaulWhite A two-star joke that I fail to understand.

Probably shows my complete disconnect from clouds.

dezso

@AndriyM I think you could ask for a lengthy explanation

Andriy M

@dezso like varchar(8000)?

dezso

@AndriyM you might risk truncation

proper systems have text

Marian

@dezso ntext :)

dezso

@Marian no, that's not

:D

Marian

11:08

@AndriyM Microsoft changed their Azure SQL db name a few times ..

Andriy M

@Marian Oh I see, thanks. My disconnect from the subject was even more complete than I thought.

Tom V

@AndriyM Microsoft Azure SQL Database (formerly SQL Azure, SQL Server Data Services, SQL Services, Windows Azure SQL Database) (from wikipedia)

Andriy M

@TomV I may have read that article once. If I have, I guess it somehow didn't register with me at the time that there were so many names for it.

Paul White

11:26

@AndriyM Well you see, it's funny because Microsoft are quite famous for changing the names (and service guids) of their cloud offerings on a startlingly frequent basis. Not just Azure SQL DB - or whatever it is called today - but a huge number of the ever-expanding list of Azure services and offerings.

So the joke is, basically, that Microsoft change their cloud services names very frequently, so if you don't like one, you won't have to wait very long until it changes to something else you might like more, less, or about the same.

Obviously the 'joke' works better if they change it to better names on average, and if you see the humour in my exaggerating the frequency with which those name changes happen.

The names don't literally change every 15 minutes of course, so it's quite possible to pick holes in the joke's literal accuracy, but therein lies some of the intended humourous effect.

Andriy M

@PaulWhite I did indeed think that 15 minutes were probably an exaggeration, it's very nice of you to confirm that.

Paul White

@AndriyM The Be Nice policy in action. Along with the Jokes Explained At Great Length Policy.

2

Andriy M

Both are group policies, I understand.

Paul White

Ha ha ha very good (Trump would approve).

Andriy M

Ah, my joke works on two levels then. It's one level more than I expected.

ypercubeᵀᴹ

11:53

@AndriyM you'll have to explain it for me. What joke?

3

Paul White

That's the spirit.

Andriy M

Bummer, looks like for some people the joke worked on one level fewer than I expected.

4

dezso

12:15

@AndriyM the explanation(s) are still only 1110 character long, so the type choice is excellent

dezso

12:44

@PaulWhite nytimes.com/interactive/2016/01/28/upshot/…

James Lupolt

Just noticed something potentially interesting. SELECT ROUND(CAST(9.83570191 AS float),8) returns a slightly different value under compatibility level 120 versus 130. It can display identical results depending on the client's truncation/rounding rules of course.

Paul White

13:09

@JamesLupolt Yeah.

0x4023ABE11EE94F3A
0x4023ABE11EE94F39

But I can't believe you find that interesting.

You probably read things like How It Works: SQL Parsing of Number(s), Numeric and Float Conversions for fun :-/

2

Lamak

@PaulWhite you mean you don't?

Paul White

@Lamak Nope.

Many many technical things interest me. But not that.

Lamak

in @JamesLupolt defense, he said potentially interesting

James Lupolt

@PaulWhite I used a packet sniffer to get those hex values. Did you use a different method?

Paul White

@JamesLupolt Um yeah I cast it to varbinary :)

James Lupolt

13:14

D'oh

Paul White

Using e.g. WireShark is way cooler though ;)

James Lupolt

Haha

The TDS parser in Network Monitor is a bit better

I tried using Microsoft Message Analyzer but it appears to have been built by a different civilization and I couldn't get it to work

Paul White

Ah yes I guess it would be. Probably a bit less cool.

Philᵀᴹ

I do like wireshark. tcpdump is fun too

Paul White

We're all 1337 hax0rs here.

Well, except me, I cast to varbinary.

That's really amused me btw.

dezso

13:17

@PaulWhite and the rest of us just wait for you two to get the job done

Philᵀᴹ

I want one of these: theintercept.com/document/2016/10/23/kraken-overview

Andriy M

All this talking about funny and amusing things... I hope you guys are not trying to sneak in an unexplained joke here.

Philᵀᴹ

(See: theintercept.com/2016/10/23/… )

Paul White

@AndriyM Perish the thought.

@JamesLupolt Interestingly (or not) the opening error in Bob's post only reproduces under 120.

DECLARE
    @f1 float = 576460752305.00000,
    @f2 float = 576460752305.000000

SELECT @f1 - @f2;

Under 120 that gives me 0.0001220703125

Under 130 it is 0

But you'll probably need to hook SSMS to an oscilloscope and fire kaons at it to show that yourself.

snigger

James Lupolt

heh

Paul White

13:23

Yeah I can be a dick with the best of them.

James Lupolt

Hopefully the company I work for isn't making any decisions based on whether the volatility of a stock is 0x4023ABE11EE94F3A instead of 0x4023ABE11EE94F39.

If they are, I guess they shouldn't be using floats.

Paul White

I added a comment to the article btw.

Hm. I seem to be much more interested in this than I claimed earlier.

I hereby claim inconsistency to be a fundamental human right. We're all eventually inconsistent.

Philᵀᴹ

I prefer a bit of 0xDEADBEEF personally

Lamak

@PaulWhite I accept that claim

you are the boss anyway

Paul White

@Lamak You'll unaccept it later. As is your right.

Philᵀᴹ

13:28

MySQL crashes if you try and do anything with the hex 0x5CA1AB1E

4

Paul White

Noice.

Shock news earlier about MySQL implementing something in the SQL Standard though.

Andriy M

But... what about Facebook, Uber et al?

Lamak

@PaulWhite maybe it's not the topic that's interesting, but discussing it with interesting people (I'm gonna exclude myself from those people though)

Paul White

@Lamak That's very profound.

Everyone, @Lamak's been kidnapped and replaced by an impostor!

2

:-O

Andriy M

By @billinkc

Philᵀᴹ

13:31

What happened to @kermit ?

Paul White

Yeah right cos KFC is sooooo profound.

2

@Philᵀᴹ He went BOOM!

Philᵀᴹ

Last seen Sep 20 at 17:35

Slacker

Lamak

@PaulWhite a profound impostor

Andriy M

@PaulWhite I meant that to be ambiguous (kidnapped by Bill or replaced by Bill?).

There, my first explained joke (I think).

Paul White

@AndriyM Oh excellent work. Loving that.

Andriy M

13:34

Not at length, though, I'm still learning.

Tom V

@AndriyM That's a bit of a short explanation though

Andriy M

@TomV It is, yeah, my bad.

Paul White

@JamesLupolt I always assumed floats were safe with integer values. Perhaps the 130 change is to make that true?

For integers less than 2^n where n is 24 or 53 for SQL Server.

9,007,199,254,740,992 for float.

James Lupolt

I guess that would explain Bob's example

Paul White

It's possible.

James Lupolt

13:47

I only noticed a difference because someone asked me why a number was 9.83570191 instead of 9.835701910000001

The extra quadrillionth wasn't a problem, he just wanted to know why

Paul White

Collect all those quadrillionths and put them in a secret account :)

Lamak

@PaulWhite do you remember how to see the "activity history" of a post?, I mean, where it shows when it was edited, voted, etc

Paul White

@Lamak The timeline you mean?

Lamak

@PaulWhite that was it

thanks

Andriy M

A quadrillionth of a penny saved is... a penny saved?

Paul White

13:52

69

A: List of unlinked pages on Stack Exchange sites

Global pages Site list /topbar/site-switcher/site-list, e.g. http://stackoverflow.com/topbar/site-switcher/site-list can be accessed from any site, with identical contents HTML format Hot questions for mobile http://stackexchange.com/hot-questions-for-mobile JSON format (need to parse it m...

3

Lamak

@PaulWhite thanks

Paul White

@AndriyM Worked for Richard Pryor.

ypercubeᵀᴹ

Hmm, sorry I retracted the close vote, I had a mysql answer also but that didn't have an accepted answer. Not sure where I went wrong but I obviously did — Tom V 45 mins ago

@TomV what was the question you had linked to? I found one anyway for MySQL and closed it.

Paul White

3

Q: Unique constraint across two columns

I need to add a constraint with two columns that says if any given value is present in one of the columns, then: 1) It cannot be duplicated in the same column. 2) It cannot be duplicated in the other column either. The constraint we are looking to make is with PrimaryEmail & SecondaryEma...

sql-server constraint

Tom V

@ypercubeᵀᴹ My vote action added a comment which I deleted

Paul White

14:02

@TomV Yeah the link was to the Q & A shown above. Slightly different title.

ypercubeᵀᴹ

@PaulWhite thnx.

Tom V

14:24

@PaulWhite Thanks

@PaulWhite I'd have to VtC that joke as unclear

ypercubeᵀᴹ

2

Q: How to represent class table inheritance (current DBMS-specific way please)?

I want to implement class either-or table inheritance (Account, CatAccountDetails, DogAccountDetails) in SQL Server. I found a recommendation for MySQL here (How do I map an IS-A relationship into a database?). Basically, it is: use identical primary keys between the parent table (Account) and...

sql-server database-design schema foreign-key relational-division

There is a Necromancer badge. There should be a Self-Resurrection one.

2

Paul White

Richard Pryor - Computer Hacker - Superman 3

►

@ypercubeᵀᴹ Crikey.

Kermit

16:20

@Philᵀᴹ

@norbkrupa You should stop by The Heap more often :P

Tweeted by trollied on October 25, 2016 at 1:37 PM

Stopped by

Lamak

you are too literal

Philᵀᴹ

16:38

@kermit Hello! How's your ever-hectic executive life?

Kermit

17:03

it's pretty ever-hectic

2nd kid is due next week

you?

Philᵀᴹ

All good, ta! All the best for the 2nd sprog...

Kermit

17:18

thank you

Tom V

19:29

I cant help but think how ambitious this guy is for attempting to write his own dta.exe for another RDBMS

I guess he could decompile the .NET code but I won't suggest that because he would probably violate multiple EULA's

that would be fairly useless too since it's likely to use undocumented or propriatary features not available with other vendors

dezso

20:35

@TomV the answer you gave should be enough to at least start thinking about it

one could possibly hook into the query planner to fetch some actual data about queries

Erik

@jcolebrand I don't know if "brass tax" was a pun for "brass tacks" or a typo in your blogging meta post. I was going to edit the post but stopped because I realized you might be going for a pun.

jcolebrand

@Erik It's a pun

Erik

ok cool. I'm glad I didn't go through with the edit and asked first

jcolebrand

heh

it would've notified me and I would've glanced at it

@TomV have you seen this site? referencesource.microsoft.com/#mscorlib/system/…

dezso

@Erik what are you two talking about?

Tom V

20:48

@dezso But I'm still not sure it would help him write anything for OgreSQL though :)

dezso

@TomV well, creating (but not executing) plans is cheap there, too

Tom V

@jcolebrand I haven't checked in detail related to this question but I can't seem to find anything dta.exe in there even though I know about the repository, good suggestion anyway

dezso

at least usually, when I don't have one of those bright ideas

Erik

@dezso in this meta post meta.dba.stackexchange.com/q/2624/72091 it mentions "brass tax" but the proper idiom is "brass tacks"

dezso

with 40k tables it's easy to go wrong

jcolebrand

20:49

@TomV I think I knew about the repository but forgot about it again and then came back across it today

Erik

The pun being tax vs tacks

dezso

@Erik I was after the link, thank you

Tom V

Oh hi @Erik , long time no see

Erik

sure thing

jcolebrand

Just saying that if it's going to be in .NET code, and from MS, it's probably on the repository

So no decompiling necessary

Erik

20:50

@TomV Thanks.

jcolebrand

@Erik I felt like I had made the point clear in the following paragraph ...

Tom V

@jcolebrand Most of it isn't, I remember a discussion with Solomon, sec

@jcolebrand dba.stackexchange.com/questions/136268/… see the comments on the first answer

Erik

@jcolebrand Once I knew you were going for the pun it made sense. Originally I thought you were just trying to call back to the idiom and you honestly didn't know it was really tacks.

JzInqXc9Dg

i have a query i didnt write... and i see something like

from [tableA], [tableB]

jcolebrand

@TomV Thanks! Good read

JzInqXc9Dg

20:55

is this a join?

jcolebrand

36

Q: ANSI vs. non-ANSI SQL JOIN syntax

I have my business-logic in ~7000 lines of T-SQL stored procedures, and most of them has next JOIN syntax: SELECT A.A, B.B, C.C FROM aaa AS A, bbb AS B, ccc AS C WHERE A.B = B.ID AND B.C = C.ID AND C.ID = @param Will I get performance growth if I will replace such query with this: SELECT ...

sql-server tsql join sql

Join (SQL)

A SQL join clause combines columns from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining columns from one (self-table) or more tables by using values common to each. ANSI-standard SQL specifies five types of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS. As a special case, a table (base table, view, or joined table) can JOIN to itself in a self-join. A programmer declares a JOIN statement to identify rows for joining. If the evaluated predicate is true, the combined row is then produced in the...

@JzInqXc9Dg so, basically, yes

JzInqXc9Dg

ok

and also just as a refresh.... "Join" == "left outer join" ?

Lamak

@JzInqXc9Dg no

jcolebrand

Without seeing more of your code I'm gonna go with this is a cross join

Tom V

@JzInqXc9Dg depends on the where, could be a cross join

oh, what @jcolebrand says

Andriy M

20:57

If you mean whether the JOIN keyword on its own means a specific kind of a join, then in probably most implementations it stands for INNER JOIN.

JzInqXc9Dg

^thats what i meant

sorry... not good questin asking

i meant JOIN keyword on its own

but not they've got me thinking... as im reading this comma join stuff

Tom V

and the RDBMS if the where is *= or =* it could be an outer join in one of the directions

JzInqXc9Dg

yes, i just noticed how there is no ON when using this comma thing (what's this called?)

so, that's why you guys are saying it depends on the WHERE clause i assume

Tom V

@JzInqXc9Dg google old style joins

JzInqXc9Dg

ok i see

Andriy M

21:00

@JzInqXc9Dg You can call the comma thing a comma join. Not sure if that's an official term but it's quite an established one.

JzInqXc9Dg

so in using a comma join, you can make it all kinds of joins. inner join would have a.id = b.id in the WHERE clause for example

or outer comma join would have to be something like.... a.id != b.id

(something like that...)

Andriy M

No, a.id != b.id wouldn't give you an outer join.

Tom V

@JzInqXc9Dg yes, AFAIK it's referred to as old style, I seem to recall it had an asterisk or + or something to define an outer join in most of the rdbms, not !=

JzInqXc9Dg

weird

Tom V

but we're talking about something that was used 20 years ago

I'm glad I forgot the details

JzInqXc9Dg

21:02

ok, well i think i only see it a couple times here so far. and there isnt anything in the where clause except like [field] = 'A'

whoever wrote this code - either is old code, or old person? lol

Andriy M

Old habits die hard

Tom V

or an old RDBMS but very old if that's the case :)

jcolebrand

@TomV sadly I see it in modern development because of this

Tom V

@jcolebrand Modern != contemporary :)

dezso

What's your use case which does not require working foreign keys? — dezso 12 secs ago

James Lupolt

21:20

Thinking about using a packet sniffer to retrieve all my query results now tbh

Tom V

@dezso Low quality review sucks, I had to open the question in a new tab to downvote the answer and then click "looks ok" or "skip" since there is no downvote button in that review queue

dezso

@TomV I feel your pain

Tom V

I believe there was a meta question at some point (by Aaron?) but I can't seem to find it readily

dezso

Just to clarify, but looks important for future readers: That means that you don't want to implement (and don't want to allow others to implement) relational databases onto the relevant MySQL installation, right? — MDCCL 2 mins ago

James Lupolt

Isn't the OP saying he wants to prevent people from disabling FK checks?

I guess I'm probably misreading

dezso

21:26

Or you want to actually prevent disabling them? I am confused. — dezso 14 secs ago

@JamesLupolt seems you are right

Andriy M

@JamesLupolt You're probably not. Or MDCCL seems to misread likewise. (And so do I.)

dezso

@AndriyM MDCCL misread as I did and whoever upvoted my comment and the answerer, too

unless I am misreading something

Tom V

I misread at first, but I was prejudiced by someone else misreading so I think another person may be reading it right

VtC unclear it is then

Andriy M

Perhaps foreign keys seem too foreign to the OP.

@TomV Closing it will reject the migration.

Just to keep in mind

dezso

@AndriyM or he has interesting users

Tom V

21:34

@AndriyM If he clarifies by responding to the comments stating confusion and it's reopened would that reapprove the migration or is the decision final? I didn't think of that

the edit by @dezso makes it clear but I'm not sure it's the OP's intended question

Andriy M

@TomV When a migrated question is closed, it becomes locked on the target site. You can't do anything with it.

The OP seems to confirm @dezso's reading.

Tom V

retracted

Andriy M

upvoted

dezso

distracted

Andriy M

@dezso That was a somewhat radical edit. But it probably makes sense in this case.

dezso

21:42

@AndriyM I was tempted to keep the wording, just add something like 'disabling the disablement'

Andriy M

I was about to suggest the answerer edit it themselves, but I guess it's fine, they've provided one of the key points (removing the super privilege) anyway.

dezso

@AndriyM ah, that

Andriy M

Ah, yeah, I meant the answer.

dezso

well, I guess the answerer would do it in any case

MDCCL

@dezso I was confused by what I interpreted as inconsistencies betweeen the title and the body content. Fortunately, the situation is clear now, the OP wants to enforce FK definitions and the situation was funny :D

dezso

21:45

@MDCCL yes, apparently reading the title helps. Sometimes.

I missed that completely

Philᵀᴹ

Busy here tonight

Transcript for

The Heap™ – Consultancy ©®