what issue on this execution plan ?

ahmed salah 3,216 Reputation points
2021-10-02T21:53:48.567+00:00

I work on sql server 2012
i run statement below
it take too much time may be 5 hours
so can I do enhancement to query to take less time

Query i run as

select  cast(c.partc as int) as partc,cast(x.partx as int) as partx  into extractreports.dbo.diffparamteric  
from extractreports.dbo.partxparamteric x with(nolock)
inner join extractreports.dbo.partcparamteric c with(nolock) on c.FeaturekeycParametric=x.FeaturekeyxParametric and c.FeatureValuecParametric<>x.FeatureValuexParametric
 group by c.partc,x.partx

this is execution plan as below :

https://www.brentozar.com/pastetheplan/?id=Bk2oQ8UEY

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,263 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,181 Reputation points
    2021-10-04T01:16:54.303+00:00

    Hi @ahmed salah ,

    Please refer to below suggestions and check whether any of them is helpful to you.

    1. create necessary clustered index and non-clustered index on both tables to make sure your index is unique if possible since there are two table scan in your execution plan. For example, create non-clustered index with FeaturekeycParametric and FeatureValuecParametric columns.
    2. Removing the group by part since there is no aggregation in your select part.
    3. Update the statistics of both tables to make sure the statistics is up to date.

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 105.9K Reputation points MVP
    2021-10-02T22:06:31.447+00:00

    You should probably think a little further on what you are actually trying to achieve. The query produces over one milliard rows - no wonder that it takes a long time.

    I don't know anything about your tables, but I would guess that

    c.FeaturekeycParametric=x.FeaturekeyxParametric
    

    is an incomplete join condition, so that you for each pair get a cartesian join which results in this row explosion.

    0 comments No comments