Post reply

Parallel Processing in SQL ?

  • October 2, 2011 at 11:00 pm

    #249237

    Hi,

    I wanted to know if there is any way in T -SQL where we can execute >2 queries (Insert/Delete/Update) in parallel to one another.

    Thanks,

    Ankit

    ______________________________________________________________________

    Ankit
    MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
    "Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
    You are already naked. There is no reason not to follow your heart.”

  • October 2, 2011 at 11:07 pm

    #1389277

    May be you can elaborate more on your requirements?

    the simplest way is to execute queries in multiple query windows.

  • October 2, 2011 at 11:17 pm

    #1389279

    There is no requirement as such. I just wanted to know how can I execute the 2 queries together

    ( Insert into xyz values (1,2,3)

    GO

    Insert into uvw values (1,2,3)

    GO

    )

    and not one after another.

    The way we have Task in SSIS where we can execute multiple task in parallel.

    Thanks for your help 🙂

    ______________________________________________________________________

    Ankit
    MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
    "Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
    You are already naked. There is no reason not to follow your heart.”

  • October 2, 2011 at 11:31 pm

    #1389281

    There has to be a mechanism to initiate both transactions at the same time. May be via a scheduled job. Even then no one can guarantee that both transactions will be executed exactly at the same time. What if the server has just one cpu? Even if there are multiple cpus/cores, you cannot define that 2 threads execute 2 queries submitted by you at the same time. It's for SQL Server db engine to decide.

  • October 2, 2011 at 11:50 pm

    #1389285

    This would mean that you need to use Degree's of Parallelism (MAXDOP). But it will complicate your code and has hardware dependency (multiple cores).

    Check this to get better idea

    http://msdn.microsoft.com/en-us/library/ms178065.aspx

  • October 2, 2011 at 11:56 pm

    #1389288

    prithvi.m (10/2/2011)

    This would mean that you need to use Degree's of Parallelism (MAXDOP).

    Err, no.

    MAXDOP sets the maximum number of cores that operators in a single query can run. What the OP is asking for is to run two separate queries in parallel and the answer to that is simple - run them on two separate connections

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 3, 2011 at 12:02 am

    #1389289

    Thanks Gila Monster for the clarification.

    ______________________________________________________________________

    Ankit
    MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
    "Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
    You are already naked. There is no reason not to follow your heart.”

  • October 3, 2011 at 1:31 am

    #1389318

    SQL_By_Chance (10/2/2011)

    There is no requirement as such. I just wanted to know how can I execute the 2 queries together

    ( Insert into xyz values (1,2,3)

    GO

    Insert into uvw values (1,2,3)

    GO

    )

    and not one after another.

    The way we have Task in SSIS where we can execute multiple task in parallel.

    Thanks for your help 🙂

    You could:

    1) Create two separate jobs and start them in parallel

    2) Code a CLR procedure that runs the queries in parallel. Please note that the use of multithreading makes the assembly marked as UNSAFE.

    3) (seriously) Tune the code so that it runs so fast that running serially is not an issue.

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • October 3, 2011 at 1:47 am

    #1389326

    Thanks Mr. Gianluca,

    It does give me some insight. What I was looking for through this question was as to "How the SSIS tasks work at the backend during parallel execution?"

    I hope your question is in line with the internal architecture of SSIS parallel task execution.

    Regards,

    Ankit

    ______________________________________________________________________

    Ankit
    MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
    "Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
    You are already naked. There is no reason not to follow your heart.”

  • October 3, 2011 at 2:01 am

    #1389330

    I see. Here you will find an interesting (beta) project by Adam Machanic that I think will help you with your assignment: http://sqlblog.com/files/folders/beta/entry29021.aspx.

    -- Gianluca Sartori

  • October 3, 2011 at 2:29 am

    #1389339

    Thanks Sir 🙂

    ______________________________________________________________________

    Ankit
    MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
    "Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
    You are already naked. There is no reason not to follow your heart.”

  • October 16, 2011 at 5:36 am

    #1395054

    Hello Ankit,

    Try this post, I believe it can help.

    http://mrbool.com/p/Parallel-processing-of-SQL-instructions-Part-1/22596

    Pedro da Cunha

Viewing 12 posts - 1 through 11 (of 11 total)

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