0

enter image description here

As you can see in the above screenshot, this query provides a parallel insert:

INSERT INTO #StateAllocationData WITH (TABLOCK)
  (ProjectID,StateId,StateLineDescriptionId,PartnerID,Value)  
SELECT @ProjectID as ProjectID,sld.StateId,sld.ID,TaxReturnPartnerNumber,0 as Value  
FROM Meta.States S(NOLOCK)  
   LEFT JOIN Meta.StateAllocationLineDescriptions SLD(NOLOCK) ON S.StateId = SLD.StateId  
   join Ottp.PartnerData PD on ProjectID=@Projectid  
WHERE  SLD.isDeleted = 0 AND SLD.ID IS NOT NULL  

enter image description here

However, as you can see in the above screenshot, this query doesn't do the parallel insert, and I'm wondering why:

INSERT INTO  #SAmt WITH (TABLOCK) (ProjectID,StateId,K1SummaryID,StateLineDescriptionId)  
SELECT @ProjectID AS 'ProjectID',  
     S.StateId,  
     SLD.StateLineDescriptionId AS 'K1SummaryID',  
     SLD.ID AS 'StateLineDescriptionId'  
FROM Meta.States S(NOLOCK)  
    LEFT JOIN Meta.StateAllocationLineDescriptions SLD(NOLOCK) ON S.StateId = SLD.StateId  
WHERE --SLD.isK1Summary <> 0 and  
      SLD.isDeleted = 0  
     AND SLD.ID IS NOT NULL  
2
  • 1
    i'm guessing since you did not provide any execution plan or table structure or working data set.. based on the images you have provided, the 2nd pic only retrieving 2K rows compared to your 1st pic which has 6k rows. there's a lot of factors to consider before the optimizer decides if it will use parallelism.. like no. of rows, server config, etc. It's tough to give concrete answer without providing details of the problem.
    – Dan Co
    Jul 28 at 6:08
  • Take a look at minimal reproducible example and help me write this query in SQL for details about how to format your question to get the best responses.
    – Hannah Vernon
    Aug 5 at 15:36

1 Answer 1

2

The queries obviously are not the same. In the first query you are trying to filter by inner join with... @ProjectID, in the second query you don't.

Try using

SET STATISTICS TIME ON
GO 
--query 1
GO
--query 2
GO
SET STATISTICS TIME OFF 
GO

Compare the CPU time and the elapsed time of the first and second queries.

Sometimes SQL Server decides against using a parallel insert to use less resources (less cpu time, for example) and the query ends up be slower (longer elapsed time).

If you have SQL Server 2016 or later you can try using the following undocument query hint to see if parallel execution is faster via OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')) at the end of each query. Be aware that undocumented features like this should only be used in non-production environments and only for testing purposes, unless you're willing to assume the risk that the feature changes in future without notice, potentially breaking your code.

For example:

INSERT INTO #SAmt WITH (TABLOCK)
(
    ProjectID,
    StateId,
    K1SummaryID,
    StateLineDescriptionId
)
SELECT @ProjectID AS 'ProjectID',
       S.StateId,
       SLD.StateLineDescriptionId AS 'K1SummaryID',
       SLD.ID AS 'StateLineDescriptionId'
FROM Meta.States S (NOLOCK)
    LEFT JOIN Meta.StateAllocationLineDescriptions SLD (NOLOCK)
        ON S.StateId = SLD.StateId
WHERE --SLD.isK1Summary <> 0 and  
    SLD.isDeleted = 0
    AND SLD.ID IS NOT NULL
OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.