The Heap™ – Consultancy ©®

Joe Obbish

12:00 AM

true

I have less than 6 years of experience

I probably don't know anything

ypercubeᵀᴹ

12:11 AM

@AaronBertrand Some even claim that GROUP BY is extremely inefficient compared to PIVOT ;)

I'm talking about our our suspended and revived recently friend

This is not the right methodology. There is no reason to use SUM(CASE) logic on versions of SQL Server that support PIVOT. SUM(CASE) is what you used to have to do before PIVOT existed. It is extremely inefficient. — Matthew Sontum 2 days ago

Aaron Bertrand

1:48 AM

I don't have enough experience to dispute this either. Maybe he can prove it to us.

Paul White

4:01 AM

@JoeObbish @ypercubeᵀᴹ If there is an index, I would use a recursive CTE skip scan simulation. Otherwise, a CLR implementation. You could also try:

SET ROWCOUNT 5; -- TOP (n)

SELECT DISTINCT ID
FROM X_LARGE_TABLE_2
OPTION (FAST 1, HASH GROUP); -- For the flow distinct

SET ROWCOUNT 0;

Assuming multiple statements and hints are OK.

Joe Obbish

4:23 AM

@PaulWhite I thought that SET ROWCOUNT was deprecated

but apparently that's only true when interacting with DELETE, INSERT, and UPDATE

interesting

you're using it instead of TOP

I assume because ROWCOUNT has no affect on the query plan?

Paul White

@JoeObbish It avoids setting a row goal, which TOP does.

It's interesting, because we want a row goal to get the Flow Distinct, but we don't want a row goal to affect the expected number of duplicates. Separating the ROWCOUNT and the FAST achieves both.

@JoeObbish Yes. It is widely thought that SET ROWCOUNT is deprecated for SELECT as well. It's not, as you say.

Aaron Bertrand

SET ROWCOUNT is only deprecated for DML

Sorry, maybe we can get a 4th person to confirm that too

Paul White

Otherwise it'll just be anecdotal :)

Joe Obbish

@AaronBertrand I know just the guy. has 17 years of experience

@PaulWhite that's a good trick. I think that ypercube implemented something equivalent to the recursive CTE skip scan approach

Paul White

I shouldn't really have starred that but ha ha ha

Joe Obbish

4:27 AM

what do you think of the LAG() and SUBSTRING approaches? I can quote them again (not sure if you read the entire exchange, it was pretty long)

Paul White

@JoeObbish I did read the messages but didn't open/try all the examples.

They're all valid - only you can choose which you prefer of course.

LAG might do better as a Window Aggregate (requires batch mode).

Joe Obbish

I'll give that a shot

Paul White

But for simplicity, I do find the ROWCOUNT/FAST 1 quite appealing for the rowstore heap case.

Partly because it looks like it should be dumb/deprecated, but is in fact rather subtle.

Ultimately, SQL Server ought to consider skip scan itself. On the backlog, perhaps.

Joe Obbish

@PaulWhite neither one of the tricks I know for getting batch mode worked, not sure why

both the empty CCI and the NCCI filtered index on the table

perhaps SQL server thought there weren't enough rows to make batch mode worth it

Paul White

Hm.

Joe Obbish

4:33 AM

wild guess on my part

I do plan to try out the dreaded, infamous plan guide as part of my analysis

you mentioned you were a fan once

Paul White

Yes I like plan guides. I get a Window Agg for LAG(1) but not LAG(0). Interesting.

Joe Obbish

@PaulWhite You're right. Batch mode helps quite a bit here

both a variable and optimize for don't seem to work here

could just filter out NULLs

by "work", I mean run a LAG(0) with window agg

Paul White

Yes. It seems Window Agg LAG might be hard coded for literals >= 1. I'll try to confirm that.

Filtering out NULLs from LAG(1) seems a reasonable approach.

Joe Obbish

@PaulWhite Thanks for the help. I'll dig around a little more and post a question on Monday. You can show everyone up with your SET ROWCOUNT trick :)

Paul White

I couldn't force a plan for LAG(1) Window Agg on LAG(0). The details are hidden inside ImplementSequenceProject.

Someone with more experience will likely be able to offer a better solution.

Joe Obbish

4:51 AM

@PaulWhite better solution for the general problem? or getting batch mode?

Paul White

@JoeObbish Both/either. Just a dig at Mr. 17 years really.

Joe Obbish

@PaulWhite ah, missed that. and here I was thinking that there weren't any showplan operators left to try...

Paul White

:)

Joe Obbish

6:21 AM

I will admit that plan guides are awesome for troubleshooting

Andriy M

8:41 AM

@JoeObbish I was reading your discussion with ypercubeᵀᴹ last night until I went to bed, and this morning I've read the rest of it as well as your discussion with Paul. So now I'd like to ask you to forget what I told you yesterday about my specialising in T-SQL. It's quite obvious to me now that I know next to nothing about it. Thank you :)

ypercubeᵀᴹ

1:47 PM

HE IS BACK

0

Q: Which Non-Functional Requirements Are The Most Important?

The majority of the questions I see on this site are functional in nature "How Do I Do X?" "Why Did Y Happen?" And the answers are functional in nature as well. "SELECT SUM(CASE)" "The connection did not close when the user logged out of their computer" This is fine for hobbyists, but in the sof...

Aaron Bertrand

2:42 PM

He finds execution plans useless. That's cool. I find breast pumps useless for me but...

5

ypercubeᵀᴹ

@AaronBertrand What can we say, "PIVOT is always better"

Fine. I recently converted a SELECT UNION SELECT GROUP to use CTE PIVOT at my current work. That conversion made it run 160x faster. But I never tested the intermediate SUM(CASE). I have a laptop/VPN so I just tested it. In my test case the CTE PIVOT took 10 seconds to return, the CASE(SUM) took 14. I retested a couple of times and the results are consistent. PIVOT is always better. Worst PIVOT trial was 11 seconds, best SUM(CASE) was 13 seconds. — Matthew Sontum 1 min ago

Considering his previous comments about CI and NCI indexes, I really doubt he knows what a covering index is.

ypercubeᵀᴹ

3:02 PM

I'll post this one here for posterity.

But, on a more personal note, have you considered a different profession? You say that you have 2 years of experience on me, but you are roughly where I was 10 years ago (7 years experience) I too thought a lot of cases were 'it depends' back then. Now I've worked long enough to see with a singular clarity, and everything is simple/easy. You might still get there, but is you indeed have 19 years and aren't there yet, you may never be, — Matthew Sontum 3 mins ago

Oh boy. this is better than the Batman LEGO I'm going to later.

ypercubeᵀᴹ

3:15 PM

It gets better and better.

From the hacking threats we've gone to publishing threats. Awesome

McNets

4:40 PM

0

Q: Is SUM(CASE) Ever Better Than CTE PIVOT?

To refresh everyone's memory, there are two types of ways to perform a PIVOT, before SQL 2005, when PIVOT was introduced, most people did this: SELECT RateID SUM(CASE WHEN RateItemTypeID = 1 THEN UnitPrice ELSE 0 END), SUM(CASE WHEN RateItemTypeID = 2 THEN UnitPrice E...

sql-server performance query-performance optimization

Joe Obbish

@AndriyM Sometimes I feel like I don't know anything either. You have plenty of good answers all over the site!

@McNets I suppose that's technically a valid question

Paul White

A better question would have been: why does this demo show PIVOT running 10x faster than SUM(CASE....

Joe Obbish

well sure, it's not a question that I would personally ask

McNets

@JoeObbish I know, but is it necessary "To refresh everyone's memory"? If he wants to argue can open a chat not a question.

Joe Obbish

@McNets If you let people get under your skin then they win

Aaron Bertrand

4:54 PM

Right, I still doubt that those examples perform any differently, so the assertion that PIVOT is faster even in that case is dubious. Can't answer now though, on the way to a kid's birthday party

Joe Obbish

and besides, maybe someone new to the discussion reads the question and answer and learns something

Aaron Bertrand

My only issue is that he states one is faster than the other, almost as if it's understood as fact. That's not a good way to frame a question. iMHO

The only evidence he offered supporting that are his own observations and he is refusing to acknowledge that execution plans don't lie

Andy K

hey guys

Andriy M

@JoeObbish When it comes to query tuning, I still rely more on what makes sense outwardly, which is not always enough. I believe it would be fair to say that my understanding of physical operators is lacking.

Andy K

does anyone heard about PowerBI?

Andriy M

5:07 PM

@JoeObbish Thank you nonetheless!

Andy K

do you think the product will last or will it die as some kind of hype product?

it is not related to database per se

but anyway, I'm asking, just in case

I cannot ask that on any SE's site as it is opinion based

The company I'm working with, wants to make investments on Qlik but we would like to know how worthy is the other solution we heard about, Power BI

Aaron Bertrand

And you want to make business decisions based on opinions in chat?

Andy K

not only, based on if you have some insights

I'm doing my own due diligence

what we are saying here, I'll have a thorough look

a chat like a interweb link or any kind of documents need to be verified

but a feedback especially from here can be helpful

I'm not asking in a chat for jewellery's lover ;)

Paul White

5:27 PM

1

A: Is SUM(CASE) Ever Better Than CTE PIVOT?

Repeating the tests from Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - By Jeff Moden, 2010/08/06 (first published: 2008/08/19) on rextester Normal rextester: http://rextester.com/UVZE87903 create table #timer (what varchar(64), ended datetime); insert into #timer values ('Start'...

@MartinSmith In case (ha!) you're interested, the difference in expression positioning is due to project normalization. This happens for CASE and not for PIVOT because the latter is only expanded to the CASE form during cost-based optimization. Project normalization happens before CBO. Disable with TF 9259.

Aaron Bertrand

I feel like if he's only seeing perf difference on one machine and unwilling to share more details it might perhaps be too localized.

Paul White

It's impossible to know what he's seeing since there's no repro.

BTW Martin the pivot expansion rule is ExpandPivot.

Martin Smith

@PaulWhite Thanks - yes always interested in stuff like that!

Paul White

It's a shame he wasn't able to provide DDL or plans. The devil is in the detail. Often it's an implicit conversion or a subtlety of execution. Ah well.

The boring answer is the one we already know: use the syntax you prefer, and only explore alternatives if performance is not acceptable. In most cases PIVOT and a proper implementation of Agg(CASE... will perform practically identically.

Confirmation bias is a thing though.

Philᵀᴹ

5:50 PM

@McNets the question needs nuking

Paul White

This question is valid: Can anyone construct an example where PIVOT is slower?

The rest is fluff really.

William Mariager

Is there a common reason to have multiple databases for the same application? I'm guessing I can't have any relationship between databases, so as far as I can think, you'll almost always have a single database?

I don't have anything particular in mind, I'm just curious.

Joe Obbish

@WilliamMariager Which RDBMS platform?

William Mariager

SQL Server is what I was looking at. Currently changing a few things in Azure, and I noticed I could add more databases to my SQL Server, but couldn't come up with a reason to do so. :)

Joe Obbish

@WilliamMariager I can give you a few reasons for having multiple databases for non-Azure if that would be helpful

William Mariager

5:58 PM

That'd be cool. Like I said, I'm mostly just curious. :)

Joe Obbish

@WilliamMariager There are some settings that are scoped at the database level. So you might want to split your data between databases

For example, suppose that you have your production data in an Always On availability group

that requires a recovery model of full

recovery model is set at the database level

now let's say you do an ETL and have a lot of staging data that you write once per day

you could put your staging data in the same database, but it would be an unnecessary performance and storage hit

if you create a separate database just for the staging data you can give it a recovery model of simple

you would write less to the log files and wouldn't replicate that data to other databases

it's easy to write queries that look at data from multiple databases on the same server

I don't know how different things are for Azure

McNets

@WilliamMariager In my case, our ERP uses a SQL-Server database, and we have admin rights, but we prefer to add a second one just to avoid issues with proper releases and upgrades.

Paul White

Multi-tenant is another reason. Customers often require more separation than a schema can provide. Common server and application, multiple databases.

Jack Douglas

@ypercubeᵀᴹ DBFiddle now has pg 9.4 as well, hint hint ;)

William Mariager

6:14 PM

@JoeObbish, I see. I don't know about Azure either. I have run into a few differences, but nothing too bad yet.

@PaulWhite, That makes sense.

Jack Douglas

Can anyone tell me if this post, about installing SQL Server on Debian, means "SQL Server 2016" by "Sql Server vNext"?

Paul White

@JackDouglas No it means the next version of SQL Server, currently in preview.

Jack Douglas

ah

thanks!

Paul White

@JackDouglas And it requires:

Red Hat Enterprise Linux 7.2
Ubuntu Linux 16.04
SUSE Linux Enterprise Server v12 SP2

In case that's why you were asking.

Jack Douglas

Yes the packages are for Ubuntu but I think they will install on Debian

So is the preview version of SQL Server the first released on Linux?

Or was 2016 released too?

Paul White

6:29 PM

@JackDouglas Yes it's brand new.

2016 is only for Windows.

Jack Douglas

That's useful, thanks

Paul White

0

Q: Is it possible to install Microsoft SQL Server v.Next on Debian?

Microsoft has released the relational DB engine part of the SQL Server for Linux, including Ubuntu 16.01. Is it possible to use this to install it on Debian (Jessie, for example)? If so, What different requirements would I have to consider to get it working? I know this is pretty new, but I'd a...

sql-server linux database debian

McNets

Does anyone use any tool to import EDI files from SQL-Server?

Paul White

Also this: informaticapressapochista.com/linux/… for Debian 8.

Jack Douglas

@PaulWhite I think that is the post I linked :p

Paul White

6:34 PM

@JackDouglas Oh! I assumed it was a q on main LOL

Ha ha ha ooops

Jack Douglas

:)

Philᵀᴹ

7:02 PM

@McNets EDI is an acronym. There's many different formats of files for different industries etc. So, yes & no

McNets

@Philᵀᴹ By now my customer was sending me DELFOR messages, formatted as XML, now he has changed to SAP and he has sent to me a sample of the new messages. I think it's EDIFACT.

UNB+UNOA:3+0941F200254740+RECEPTOR+170216:0858+20170216085837'UNH+1+DELFOR:

Paul White

7:20 PM

@ypercubeᵀᴹ Why OUTER APPLY? I would use CROSS APPLY there. Scalar aggregates always return a value, even on an empty set. Small point.

The way I first learned to write a (dynamic) pivot was with a cursor.

Philᵀᴹ

@McNets get the spec off them. There's no single format, it's an acronym

ypercubeᵀᴹ

@PaulWhite Just in case there are rows for one RateType but not for others.

Paul White

@ypercubeᵀᴹ But the SUM will still return a NULL even on an empty set.

ypercubeᵀᴹ

@PaulWhite Say a RateID has only RowType=1 values and not for 2 or 3. Then the 1st cross apply would be joined with the main table but the 2nd and 3rd join would remove that RateID. Right?

McNets

@Philᵀᴹ could be D91?

Paul White

7:28 PM

@ypercubeᵀᴹ No, you'll get (correctly) a NULL.

Because the SUM of no values is NULL.

ypercubeᵀᴹ

Really? So what's the difference with OUTER APPLY?

Paul White

@ypercubeᵀᴹ None in that specific case

ypercubeᵀᴹ

Let me check. I may be not explaining right what I'm thinking

Paul White

If the APPLY could return an empty set, there would be a difference. It can't. Scalar aggregates never return an empty set.

@ypercubeᵀᴹ Sure. Try an example.

Trivially:

DECLARE @T table (c1 integer);
INSERT @T VALUES (1);
SELECT * FROM @T AS T CROSS APPLY (SELECT s = SUM(T2.c1) FROM @T AS T2 WHERE 0 = 1) AS CA;

Output: 1, NULL

Compare with the vector aggregate:

DECLARE @T table (c1 integer);
INSERT @T VALUES (1);
SELECT * FROM @T AS T CROSS APPLY (SELECT s = SUM(T2.c1) FROM @T AS T2 WHERE 0 = 1 GROUP BY ()) AS CA;

Output: <nothing>

Evan Carroll

3

Q: ERROR: requested character too large for encoding

I'm using PostgreSQL 9.6 on Linux. I got an error when I do a test on chr() function. postgres=# select chr(1199111); ERROR: requested character too large for encoding: 1199111 postgres=# select chr(55296); ERROR: requested character not valid for encoding: 55296 postgres=# select chr(100000)...

postgresql encoding

HALLO EVERYONE

ypercubeᵀᴹ

7:54 PM

@PaulWhite You are right of course.

It's only needed if there is no GROUP BY in the apply subqueries

Paul White

@ypercubeᵀᴹ That extra 'f' makes all the difference.

OUTER apply is needed if there is a GROUP BY inside the APPLY.

Andriy M

extra 'if'?

Paul White

@AndriyM The original said "You are right off course".

Andriy M

@PaulWhite Aha, I see

Paul White

Comment and edit explained at medium length.

ypercubeᵀᴹ

7:58 PM

@PaulWhite did you mean "if there is no GROUP BY..."?

Paul White

@ypercubeᵀᴹ Nope.

This is fun.

Andriy M

If there's no GROUP BY, you can use either OUTER APPLY or CROSS APPLY.

Paul White

Yes.

If there is a GROUP BY you have to use OUTER APPLY.

Andriy M

...to guarantee that the row for which you are calculating the aggregate value is preserved (since the GROUP BY query may return an empty set).

Paul White

Exactly so.

T-SQL Master.

Andriy M

8:07 PM

Hopefully I'll master it someday.

ypercubeᵀᴹ

@PaulWhite And also if there is no GROUP BY and no aggregate. Right?

Paul White

@ypercubeᵀᴹ Right.

Or just a GROUP BY and no aggregate :)

@ypercubeᵀᴹ There's no need to put any of that in your answer by the way. Just thought it was interesting to chat about.

ypercubeᵀᴹ

@PaulWhite yeah, thnx.

I guess the behaviour may be related to the difference in results between aggregate without GROUP BY and GROUP BY ()

.. which we had discussed some years ago due ot a relevant question.

Jack Douglas

1> select @@version
2> go

Microsoft SQL Server vNext (CTP1.3) - 14.0.304.138 (X64)
        Feb 13 2017 16:49:12
        Copyright (C) 2016 Microsoft Corporation. All rights reserved.
        on Linux (Debian GNU/Linux 8 (jessie))
(1 row affected)

@PaulWhite yippee

Paul White

@JackDouglas Congrats!

ypercubeᵀᴹ

8:20 PM

nice!

Jack Douglas

thanks, though there is no real reason why I can't just install it on Windows, and I'm going to have to do that for 2016 and before.

Paul White

8:30 PM

@JackDouglas I thought the reason would be Windows licencing?

I assumed that was the reason you were using Debian for DBfiddle.

Jack Douglas

No it just fits together neater with what I'm used to doing — with Windows VMs, I'll either need to have a separate webserver VM or figure out how to run apache+PHP on Windows, or (least likely) figure out how to use IIS+whatever on Windows

I've got Windows Datacentre on the host already so the cost of adding Windows VMs isn't easy to quantify — it's not zero but it is more related to how much resources they use

Paul White

Ok cool.

ypercubeᵀᴹ

8:52 PM

@JackDouglas can you run apache+php in one (Linux) VM and connect to either Windows (SQL Server) or Linux (the rest dbms)?

Or is that what you meant?

Jack Douglas

The model is to keep the DB VMs as simple as possible so each has it's own webserver and a small amount of code — the queries are passed in, and results returned with json. Every DB VM uses an identical API, so the central webserver doesn't need to know anything about the DB it is talking to. With Linux it is 1 VM (apache+php+rdbms) per DB VM, and with Windows it'll be 2 with that model.

Which is not that big of a deal

Aaron Bertrand

10:39 PM

Kudos. I, for one, would love to see any real, reproducible example where PIVOT is faster. I know my skills are clearly somewhat lacking, but I still believe this claim is either fabricated or due to other, undisclosed variables that lead to the two queries not really being equivalent after all. — Aaron Bertrand ♦ 5 mins ago

ypercubeᵀᴹ

11:15 PM

An accepted answer, with -53 score, wow!

-53

A: Is there an idiom available, that is exactly opposite to "Cake walk" or "Child's play"?

Adulting Adulting (v): to do grown up things and hold responsibilities such as, a 9-5 job, a mortgage/rent, a car payment, or anything else that makes one think of grown ups. Used in a sentence: Jane is adulting quite well today as she is on time for work promptly at 8am and appears well...