Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Approach for multi procs insert to one table

Author  Topic 

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2012-11-19 : 11:54:14
Hi All -

I've got a question regarding handling multiple jobs/procs inserting data into a single table at the same time.

Background:
- We've got a monthly job that needs to query, summarize and insert data into table.
- There are about 20 jobs that run now (one per region) and that will increase as our company adds/defines new regions
- The amount of data per region varies. So, the time to query/summarize varies....some regions have a query that runs in a few seconds and the larger volume regions can take 20 mins and up to an hour.
- The data that we query is from a set of views into various tables (separated by region)
- The desire is to reduce the total time that this monthly process consumes.
- The table ONLY has a single Clustered Index
- SQL 2008 R2

Approaches and Questions:
(1) Have a job that executes a stored proc that runs the query/inserts serially.
- The simplest solution to maintain
- Takes the longest to complete the process


(2) Schedule 20 jobs (for now), one for each process so that the query/inserts can happen in Parallel.
- Maintenance issue for 20 jobs and later as regions get added.
- Not sure if there is any blocking of the parallel proceses that could cause more performance issues than running the jobs serially. The PK is a single column (uniqueidentifier) that is unique across all regions (comes from the regional tables). Will there be more contention because of the single Clustered Index?
- However, this appears to provide a way for the faster/smaller queries to complete and not have to wait

Anyway, just wondering if anyone has a thought regarding this and if my approach (#2) seems like it is going to cause problems.

thanks for any thoughts/suggestions
- will

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-19 : 13:23:55
Well the suggestion depends on
1) What are the indexes you have on Table? What are the queries? Is it cursor?
2) How many processors you have in server? Is it Enterprise Edition?
Go to Top of Page

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2012-11-19 : 13:46:06
quote:
Originally posted by sodeep

Well the suggestion depends on
1) What are the indexes you have on Table? What are the queries? Is it cursor?
2) How many processors you have in server? Is it Enterprise Edition?



Thanks for the review and questions.
The Index is just a single column clustered index on a uniqueidentifier data type. The GUIDs are unique across the all regions/enterprise.

We are running Enterprise Edition, two CPUs and 128GB of RAM.

Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-19 : 14:07:49
quote:
Originally posted by dhw

Hi All -

I've got a question regarding handling multiple jobs/procs inserting data into a single table at the same time.

Background:
- We've got a monthly job that needs to query, summarize and insert data into table.
- There are about 20 jobs that run now (one per region) and that will increase as our company adds/defines new regions
- The amount of data per region varies. So, the time to query/summarize varies....some regions have a query that runs in a few seconds and the larger volume regions can take 20 mins and up to an hour. - The data that we query is from a set of views into various tables (separated by region)
- The desire is to reduce the total time that this monthly process consumes.
- The table ONLY has a single Clustered Index
- SQL 2008 R2

Approaches and Questions:
(1) Have a job that executes a stored proc that runs the query/inserts serially.
- The simplest solution to maintain
- Takes the longest to complete the process


(2) Schedule 20 jobs (for now), one for each process so that the query/inserts can happen in Parallel.
- Maintenance issue for 20 jobs and later as regions get added.
- Not sure if there is any blocking of the parallel proceses that could cause more performance issues than running the jobs serially. The PK is a single column (uniqueidentifier) that is unique across all regions (comes from the regional tables). Will there be more contention because of the single Clustered Index?
- However, this appears to provide a way for the faster/smaller queries to complete and not have to wait

Anyway, just wondering if anyone has a thought regarding this and if my approach (#2) seems like it is going to cause problems.

thanks for any thoughts/suggestions
- will




The above red is what concerns. You have to find out why it is taking 1 hour for some query to run. You can post the query here so smart people over can give you best approach considering that you have provided all the indexes?

If you are joining based on clustered index then it should run faster? Only thing i see with Step 2 is it can cause blocking issues. If it is possible for you to run steps in Weekend or very low activity time,then it should be good.
Go to Top of Page

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2012-11-19 : 16:22:39
Thanks for the continuing review and help.

I share your concern and one of my main tasks is to analyze the queries/performance to see if there is anything I can do to improve the performance.

Based on our current job schedule, I do believe we are targeting a weekend or early AM time slot.

thanks
- will
Go to Top of Page
   

- Advertisement -