Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Parallel Processing Expand / Collapse
Author
Message
Posted Monday, October 12, 2009 5:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 5:39 AM
Points: 41, Visits: 378
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).
Post #801479
Posted Monday, October 12, 2009 5:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 5:23 AM
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.
Post #801487
Posted Monday, October 12, 2009 6:41 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:48 PM
Points: 1,570, Visits: 676

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.



Post #801528
Posted Monday, October 12, 2009 7:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 11:09 PM
Points: 162, Visits: 263
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
Post #801545
Posted Monday, October 12, 2009 8:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 31, 2014 1:25 PM
Points: 10, Visits: 101
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
Post #801601
Posted Monday, October 12, 2009 8:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:48 PM
Points: 1,570, Visits: 676

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.



Post #801638
Posted Monday, October 12, 2009 9:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 31, 2014 1:25 PM
Points: 10, Visits: 101
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.
Post #801645
Posted Monday, October 12, 2009 9:23 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:48 PM
Points: 1,570, Visits: 676
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.


Post #801654
Posted Monday, October 12, 2009 7:41 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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."
Post #801904
Posted Monday, October 12, 2009 11:41 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 1, 2014 6:55 AM
Points: 2,366, Visits: 1,845
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"
Post #801946
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse