The Heap™ – Consultancy ©®

Michael Green

00:24

The other day I found myself musing about inter-query parallelisation. Not intra-query that we know and love where the optimizer chooses parallel operators depending on MAXDOP (for the SQL Server world). Instead, I'm talking about inter-statement parallelisation such as "INSERT these rows in this table and while that's happening INSERT those rows in that other table; when they're both finished do this."

Sometimes an overly large query produces a poor plan. Splitting the query then combining the temporary results set is one strategy for getting a better plan. If the part-queries could be executed in parallel run time would be reduced.

Has anyone come across papers, blogs or interwebz chatter on this topic? Or is this just crazy-talk?

Joe Obbish

01:00

@MichaelGreen I've seen a custom built framework using jobs in sql server which I think did what you're describing

different queries get sent to different sessions and when all of them are done something else happens

McNets

06:04

morning

Tom V

06:40

@MichaelGreen SSIS would do that easily

ypercubeᵀᴹ

07:31

161

Q: Brief outage planned for Wed, May 3, 2017 at 8pm US/Eastern (00:00 UTC) (like a fire drill for computers)

MicroVersion: Planned service degradation: All Stack Overflow/Stack Exchange sites read-only for 20 minutes on Wed, May 3, 2017 shortly after 8PM US/Eastern (midnight UTC). If you blink, you'll miss it. Short version: There will be a service degradation for up to 20 minutes shortly after 8PM US...

Andy K

morning

Tom V

Good to know. I'll schedule my spam bots to do some extra posts at 23:59 UTC. — Suragch Apr 28 at 2:02

McNets

09:00

@TomV do you know maillefer.com?

Tom V

09:11

@McNets No, why?

McNets

@TomV They are providers too. And they have FDA certification or are in process.

Mathematics

10:52

Afternoon

what's the best way of telling an external system about changes made to sql table

Andriy M

Sounds not entirely unlike a search engine request

Good afternoon

Mathematics

@AndriyM it's just one table, audit.. so whenever it's changed we need to tell our other users who are working on same item

ConcernedOfTunbridgeWells

@swasheck Depends what you define by 'works well.' If you ask the CFO (as I did once) he was happy with it because they'd been able to free up a whole load of money tied up in inventory and implement some process efficiencies. If you ask all the engineers with homebrew access databases they'd say it never works as well as what they'd done themeselves.

Andriy M

@Mathematics Even then you can still try looking for an answer using Internet search :)

ConcernedOfTunbridgeWells

If by 'works well' you mean 'easy to integrate with', that really depends on whether you're an end user or a SAP consulting form. It's all a matter of perspective.

Mathematics

11:08

@AndriyM I doubt if internet will give me an easy on this :)

I can create a web service, keep calling sql... but won't it be a sql killer ?

Andriy M

11:28

@Mathematics Perhaps. But searching the Internet is, in a way, like asking a question in an enormous chat room. If you think that the global "chat room" can hardly give you an easy solution, then how do you rate your chances asking around here? :)

Tom V

Just needs some XML

ConcernedOfTunbridgeWells

@TomV Now you have two problems.

Tom V

I got 99 problems but XML ain't one

Mathematics

@AndriyM I keep making mistake of thinking this room is for SQL Dbas.. , please accept my apologies

Tom V

@Mathematics that question is just way to broad to be quickly answered in this chat room

Mathematics

11:53

@TomV Pointing me in right direction would be enough, e.g. names of tech I could look at, but no worries... thanks :)

ypercubeᵀᴹ

mjg59.dreamwidth.org/48429.html

Nice one, Intel!

Andriy M

@Mathematics I don't have much knowledge about change tracking but I guess I could give you some pointers by googling. I was just curious why you yourself wouldn't take that course of action before coming here.

Mathematics

@AndriyM Already answered your question, I am not asking anymore or I can leave room if that's not enough ?

Andriy M

@Mathematics I have no issues with your being here, and I can promise you I will have no issues with your asking questions here from now on. Looking back, I guess my reaction may have come across as passive aggressiveness but there was genuine curiosity in my question. Sorry about any trouble caused.

Mathematics

Just answering to help you with curiosity, I did came up with a prototype which works, but I am not happy with architecture, so I thought because this is such a common scenario I might get some insights.

In field of architecture it's difficult to get help from google, there are strategies but they take more time.

Or in other words... google don't help with such queries (I know we can search anything on google but we have to be smart right ? won't going to spend months searching for something...)

ypercubeᵀᴹ

12:06

@Mathematics How often is the table changed? Who changes it? Some other application?

Mathematics

@ypercubeᵀᴹ it's random sometime it's only once or twice a day and other days like 100s and it's modified using custom code which is using stored procedures

ypercubeᵀᴹ

@Mathematics 100s of times? Are we talking about DDL changes or inserts/updates?

Andriy M

@Mathematics That's all very fair, but when you are asking for help (as opposed to searching the Internet), it really helps those whom you are asking to know what you have already tried. You asked your question broadly without even mentioning the fact that you'd already done some research. It really looked like a lazy question to me.

Mathematics

@ypercubeᵀᴹ insert/updates

dezso

12:50

afternoon all

sitting in a Cassandra training the whole week

the presenter is a nice French guy, having some slides in French, too - the 'pardon my French' joke was already made, thanks @MaxVernon and others

2

Max Vernon

nods

Andy K

have fun @dezso

Paul White

@JackDouglas db<>fiddle still has an issue displaying uniqueidentifier types. Are you already aware of this?

(2017 only)

McNets

13:11

@PaulWhite use 2016 dbfiddle.uk/…

Joe Obbish

13:33

good morning

Forrest

@JoeObbish Morning Joe

Andy K

if you want a nice curse (not too violent) , you can say crotte de bique@dezso

hi Joe

McNets

13:47

morning Joe

dezso

14:23

@AndyK sounds useful

Matt

I have a silly rookie problem. I am trying to transpose a table of "settings" which is one row with about 100 columns e.g `Locator, WOMEN_LB, MEN_LB`. The only row would contain those setting values. `UNPIVOT` seems like what I want but I don't want to do any aggregation on the data (like most of the existing questions cover), just transpose. I could easily use Dynamic SQL for this but I figured there was a better way. I can only get it to work for the one column..

SELECT Setting_Name, Setting_value

Jack Douglas

@PaulWhite yes, I think I need to use a different driver on Linux — I will give that a try

Matt

and now I forget the chat markup apparently.

Lamak

@JackDouglas linux and drivers....sounds about right (I have no idea what I'm talkin about, as always)

Jack Douglas

linux and php and drivers :S

Andriy M

14:39

@Matt Are all columns of compatible types?

Paul White

DECLARE @S AS table (v1 integer, v2 integer, v3 integer);
INSERT @S VALUES (1, 2, 3);
SELECT U.[name], U.[value]
FROM @S AS S
UNPIVOT ([value] FOR [name] IN (v1, v2, v3)) AS U;

Matt

No. When I combine more columns I get an error

Paul White

You have to make sure the types match.

Andriy M

After all, you are putting them in one column.

Paul White

Conversion to strings or sql_variant is a common workaround.

Matt

14:40

Because now it will all be in one column so type is important... yes. Ok

Its a mix of int a strings.... its only for my visual display so a simple case should do it.

Thanks for pointing that out.

Paul White

23

Q: Why does SQL Server require the datatype length to be the same when using UNPIVOT?

When applying the UNPIVOT function to data that is not normalized, SQL Server requires that the datatype and length be the same. I understand why the datatype must be the same but why does UNPIVOT require the length to be the same? Let's say that I have the following sample data that I need to ...

sql-server unpivot

bluefeet

14:55

i remember that question :)

Matt

Thanks @AndriyM and @PaulWhite . A sub query with a casts made it work for me. I should have looked up the error first.

I'm smrter now

@bluefeet You are apparently the pivoting genius since the 7 or so SO questions I have been reading have all be answered by you.

bluefeet

I <3 PIVOTs

Lamak

you <3 '%PIVOT's

Matt

indeed

Andriy M

@Matt That must be some kind of typo, you probably meant "700 or so".

Matt

15:03

I have only time to read so many before I got confused.

Andriy M

bluefeet has answered a lot of PIVOT & UNPIVOT questions, here and on SO

bluefeet

@AndriyM yes, I have answered a lot of them

Lamak

@AndriyM keywords would be :"questions I have been reading"

Matt

I didnt want to get to the point where I see the ghost of two blue triangles as I walked around

Andriy M

@Lamak Ah yes, that's a fair point.

Lamak

15:05

I'm known for my fairness

ConcernedOfTunbridgeWells