Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parallel Processing


Parallel Processing

Author
Message
Greg Edwards-268690
Greg Edwards-268690
SSC Eights!
SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)

Group: General Forum Members
Points: 819 Visits: 8266
Are you processing all dimensions first, then fact table?
And what is the performance processing 1 store at a time?
Generally, we would partition by year.
Clustered index on Business Unit, which would be similar to store.
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10712 Visits: 12016
mr_effe (4/17/2014)
I think this solution doesn't help me. Because a switch partition leads to a Schema-M lock. And if all of my parallel processes wants a schema-m lock nothing works anymore.

I already had this situation with my "truncate partition" function which uses switch partition to delete the table partition. It works fine if only one process is active. If i started the jobs in parallel all of them waits for each other. So I have already switched the logic from "truncate partition" to a delete logic.

So I think this solution will not help me :-(

If you have N partitions, you can use N+2 jobs:- one which switches the partitions out into the new table and then kicks off the insert job for each partition, one per partition for the inserts, and one which polls to find finished jobs from the set handling the inserts and when it finds one switches the corresponding partition back and notes that it's dealt with that job so it won't poll for it again, and when there is nothing left to poll for terminates.
That avoids the clashes on the schema mod lock.
Obviously you need to deal with one of the insert jobs failing, but it's probably easier to deal with than it is in the case where the insert jobs are clashing on locks.

Tom

mr_effe
mr_effe
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 120
Hi,

thanks a lot for your tips. I've decided to change the sequence of the jobs of each parallel process, so I could also minimize the locks.

But I have still a performance isssue due to wrong execution plans.

I load the data from the staging into the core dwh via merge statements in stored procedures. As input-parameter the StoreId will be used which will be mapped to variable v_StoreId. The following statement is an example of a merge statement

MERGE CORE_TABLE_AB as t
USING
(SELECT * FROM STAGE_TABLE_A a WHERE StoreID = @v_StoreId
INNER JOIN
SELECT * FROM STAGE_TABLE_B b WHERE StoreID = @v_StoreId
ON a.StoreId = b.StoreId
) s
ON t.StoreId = s.StoreId
AND t.UniqueId = s.UniqueId
WHEN NOT MATCHED BY TARGET THEN
(...)
WHEN MATCHED THEN
(...)

This procedure will be executed for each of the 6 StoreId's. But for some of the StoreId's there are no data in the staging tables. And this lead to my performance problems i think. Because the execution plan would use a nested loop for the inner join of STAGE_TABLE_A and STAGE_TABLE_B for the StoreId's which has no data in it. And falsely the execution plan for the StoreId's with data in the stage tables uses then also a nested loop which leads to a long runtime.

I've already analyzed the statistics of the tables. They are correct and if I display the execution plan in SSMS (it shows me the correct plans for the StoreIds without input data (nested loops) as well as for the StoreIds with data (hash match). But here I've replaced the variable @v_StoreId by the real StoreId.

So do somebody of you know what I have to do to have different execution plans for the different StoreId's? Is the variable @v_StoreId the problem?

Many thanks
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