SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parallel Processing


Parallel Processing

Author
Message
scott mcnitt
scott mcnitt
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 437
Simon's post about using the OUTPUT clause on the update becomes available for us to use in SQL 2005 databases. It is an awesome feature (Oracle has had it for a while).
Kevin.McEvoy
Kevin.McEvoy
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 16
Really interesting article.

Could the clean up process use something like sp_who to find out if a process was currently running or orphaned. I'm always a bit dubious about things that depend on timings which would need to be tuned for each installation depending upon resources, data sizes etc.
Simon Facer
Simon Facer
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1897 Visits: 724

Scott was exactly right. The OUTPUT clause of the UPDATE allows us to get data back from the inserted and deleted tables (the same as we reference in DML triggers). In the case of my code snippet, I always want to process the first record in the table with Status = 1 (Ready to Process) and a StartTime < Now. I contemplated using Transactions and Setting Lock hints, but it was a lot of work compared to using the UPDATE ... OUTPUT statement. This way the record is updated to Status = 2 (In Process) and the row ID value returned to the code in a single statement - no extended or manually coded locking required.


BOL has a good article on the OUTPUT clause; but a couple of points - (1) you can get back any data from the inserted or deleted tables, not just a single column and (2) the INTO table has to pre-exist, hence the CREATE TABLE statement at the top of the code.


The Code snippet is written as part of a loop (using the forbidden GOTO statement - lets see who jumps on that, but that's a different discussion), hence the DELETE statement.


Hope that helps.



Sunny-138471
Sunny-138471
SSC Veteran
SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)SSC Veteran (248 reputation)

Group: General Forum Members
Points: 248 Visits: 266
Kevin.McEvoy (10/12/2009)
Really interesting article.

Could the clean up process use something like sp_who to find out if a process was currently running or orphaned. I'm always a bit dubious about things that depend on timings which would need to be tuned for each installation depending upon resources, data sizes etc.


Thanks Kevin. Yes I too tried digging into any such readily available command which could tell whether the process is currently active. Hope to get more leads in this forum. The UPDATE..OUTPUT statement suggested by Simon appears to be very helpful. Thanks Simon :-)

Thanks
Satish
wiltc-836148
wiltc-836148
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 103
Interesting article....I'm surprised at the hoops you have to jump through.

On DB2 for i, all I need to do is set QQRYDEGREE for the system or for individual jobs to something besides *NONE (or *NBRTASKS 1).

http://www-03.ibm.com/servers/enable/site/education/abstracts/4aea_abs.html

From the above course:
How SMP works

The DB2 SMP feature provides the optimizer and database engine with additional methods and strategies for retrieving data and processing data in a parallel manner. SMP enables database parallelism on a single system or LPAR - where multiple (CPU and I/O) processors that share memory and disk resources - to work simultaneously toward achieving a single end result. This parallel processing means that the database engine can have more than one (or all) the processors working on a single query at the same time. You can significantly improve the performance of a processor-bound query with this feature on multiple-processor systems by distributing the processor load across more than one processor.

Although using SMP does not require the presence of more than one processor, database parallelism is most effective when more than one physical processor is available to run the tasks or threads.

Given that SMP is achieved through the use of the System i server and its i5/OS advanced architecture, table partitioning is not required for database parallelism.

Charles Wilt
Simon Facer
Simon Facer
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1897 Visits: 724

Charles, I think you missed the point ...


SQL will SMP parallel-process out of the box, automatically generating query plans to take advantage of multiple available processors as appropriate. The article was about manually setting up parallel processing tasks. In the example I gave, the parallel tasks are querying multiple independant unrelated servers simultaneously; this is not an SMP task as you were referring to.



wiltc-836148
wiltc-836148
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 103
Simon,

I see that your discussion post mentions multiple servers...

But in the article in question, http://www.sqlservercentral.com/articles/Locking/67952/ only seems to be talking about one server.

So what does the article's technique have that MS SQL Server doesn't out of the box?

Thanks!
Charles Wilt

Simon Facer (10/12/2009)

Charles, I think you missed the point ...


SQL will SMP parallel-process out of the box, automatically generating query plans to take advantage of multiple available processors as appropriate. The article was about manually setting up parallel processing tasks. In the example I gave, the parallel tasks are querying multiple independant unrelated servers simultaneously; this is not an SMP task as you were referring to.

Simon Facer
Simon Facer
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1897 Visits: 724
The article was about how to process a single data set using multiple processes. The concept is how to start multiple independant non-overlapping processes against a single table. My example shows a use of the given technique (or my alternative) that cannot be accomplished with SMP.



RBarryYoung
RBarryYoung
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19214 Visits: 9518
Very nice article. One suggestion would be to look into using Service Broker, as I believe that you will find that it can simplify or even enhance several aspects of your approach.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
ChiragNS
ChiragNS
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4133 Visits: 1865
Very good article.

Couple of things i would like to know about the update statement with output clause. What type of locks would be involved with update statement and how to get more than one record with the update statement since you have used MIN function.

"Keep Trying"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search