September 13, 2016 at 4:40 pm
I'm working on a sproc that needs to run several independent select statements. So let's say I have 3 select statements like this:
select * from x into #x
select * from y into #y
select * from z into #z
Let's say each select statement takes 2s to complete. If I run the sproc as shown above then the total query time would be 6s. However, if I was able to run the queries asyncronously/simultaneously then I could exec the sproc in 2s total. Is there a way to do something like this in T-SQL?:
select * from x into #x async
select * from y into #y async
select * from z into #z async
September 14, 2016 at 7:43 am
Service Broker would allow for this, however you would not be able to use temp tables.
September 14, 2016 at 9:57 am
Same comment for SSIS: it's straightforward to do, but you wouldn't be able to use temp tables for that.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 14, 2016 at 10:57 am
Keep in mind, though, that you could use non-temp table names in tempdb. Tables will have slightly less overhead in tempdb.
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".
September 14, 2016 at 11:08 am
By setting the property RetainSameConnection = True on the Connection Manager, temporary tables created in one Control Flow task can be retained in another task. You would have X number of Control Flow tasks execute in parallel, each populating a temp table for use in a subsequent task.
How to create a temporary table in SSIS control flow task and then use it in data flow task?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 14, 2016 at 11:48 am
Eric M Russell (9/14/2016)
By setting the property RetainSameConnection = True on the Connection Manager, temporary tables created in one Control Flow task can be retained in another task. You would have X number of Control Flow tasks execute in parallel, each populating a temp table for use in a subsequent task.How to create a temporary table in SSIS control flow task and then use it in data flow task?
I have done that, worked well in SSIS.
September 14, 2016 at 8:00 pm
Lynn Pettis (9/14/2016)
Eric M Russell (9/14/2016)
By setting the property RetainSameConnection = True on the Connection Manager, temporary tables created in one Control Flow task can be retained in another task. You would have X number of Control Flow tasks execute in parallel, each populating a temp table for use in a subsequent task.How to create a temporary table in SSIS control flow task and then use it in data flow task?
I have done that, worked well in SSIS.
Absolutely - using it within the same SSIS package and performing not just the prep but also whatever you were planning to do to the temp files. For some reason I was fixated on simply prepping the data using SSIS. Prep + whatever DML required the prep is a much better solution.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply