asyncronous sql queries?

Question

Post reply

asyncronous sql queries?

sqlguy-736318

Hall of Fame

Points: 3859
More actions
September 13, 2016 at 4:40 pm

#330100

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

Service Broker would allow for this, however you would not be able to use temp tables.

Shamless self promotion - read my blog http://sirsql.net

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

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?

ScottPletcher SSC Guru Points: 100262 More actions · Answer 3

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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Eric M Russell SSC Guru Points: 125255 More actions · Answer 4

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?

http://stackoverflow.com/questions/5631010/how-to-create-a-temporary-table-in-ssis-control-flow-task-and-then-use-it-in-dat

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 5

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?
http://stackoverflow.com/questions/5631010/how-to-create-a-temporary-table-in-ssis-control-flow-task-and-then-use-it-in-dat

I have done that, worked well in SSIS.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

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?
http://stackoverflow.com/questions/5631010/how-to-create-a-temporary-table-in-ssis-control-flow-task-and-then-use-it-in-dat
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?