February 9, 2016 at 9:01 am
Hi,
I have 10 CTAS statements creating temp tables. Once they are created, they are LEFT OUTER JOINED.
Is there a way, a parallel execution of these 10 CTAS statements be enforced programmatically or does the Query optimiser engine take care of this automatically?
thank you
February 9, 2016 at 9:05 am
etl2016 (2/9/2016)
Hi,I have 10 CTAS statements creating temp tables. Once they are created, they are LEFT OUTER JOINED.
Is there a way, a parallel execution of these 10 CTAS statements be enforced programmatically or does the Query optimiser engine take care of this automatically?
thank you
What are CTAS?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 9, 2016 at 9:08 am
hi, CTAS is create table as SELECT
thank you
February 9, 2016 at 9:21 am
You could try a combo of this function:
http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx
Or using powershell
https://technet.microsoft.com/en-us/library/jj713711.aspx
gsc_dba
February 9, 2016 at 9:56 am
etl2016 (2/9/2016)
hi, CTAS is create table as SELECTthank you
The simplest way I can think of doing this is using an SSIS package to submit the queries in parallel via multiple ExecuteSQL tasks.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 9, 2016 at 12:32 pm
That sounds like Oracle not SQL Server. SS doesn't have CTAS syntax.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 9, 2016 at 12:41 pm
ScottPletcher (2/9/2016)
That sounds like Oracle not SQL Server. SS doesn't have CTAS syntax.
Aha, I just assumed it was an unorthodox way of referring to SELECT INTO.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 9, 2016 at 1:51 pm
ScottPletcher (2/9/2016)
That sounds like Oracle not SQL Server. SS doesn't have CTAS syntax.
It's SQL Server Parallel Data Warehouse aka APS, or the Azure version of it, Azure DWH. CTAS replaces SELECT ... INTO in the PDW.
PDW is not a standard SQL Server, SSIS is not a good option for working on data within the PDW, because it doesn't take advantage of the distributed nature of the data, and it requires pulling the data out and putting it back in.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2016 at 1:53 pm
etl2016 (2/9/2016)
Is there a way, a parallel execution of these 10 CTAS statements be enforced programmatically or does the Query optimiser engine take care of this automatically?
The CTAS statements will be executed one after each other. T-SQL is synchronous in terms of statement execution. Each one may run in parallel depending on the distribution of the data.
The only way to make the CTAS statements run at the same time as each other would be multiple sessions each with one or more of the CTAS statements, but then you risk running into the concurrent query limit.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2016 at 2:36 pm
GilaMonster (2/9/2016)
The CTAS statements will be executed one after each other. T-SQL is synchronous in terms of statement execution. Each one may run in parallel depending on the distribution of the data.
CTAS needs to apply exclusive locks on system objects.
While the first one is not finished no other ones would be able to start.
_____________
Code for TallyGenerator
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply