How to avoid using operator when there are different values on two tables ?

  • I work on sql server 2017 I need to get different of feature value based on partid and feature name

    I search for alternative way to compare different values based on partid and feature name without using <> not equal operator

    because i face slow when using <> not equal so what i do

    so i try below

     

    create table #parts
    (
    PartId int,
    FeatureName varchar(50),
    FeatureValue varchar(50)
    )
    insert into #parts(PartId,FeatureName,FeatureValue)
    values
    (1211,'Height',50),
    (3211,'Air',90),
    (6121,'Size',300),
    (7921,'Area',790),
    (9871,'Factors',210)


    create table #partsDetails
    (
    PartId int,
    FeatureName varchar(50),
    FeatureValue varchar(50)
    )
    insert into #partsDetails(PartId,FeatureName,FeatureValue)
    values
    (1211,'Height',120),
    (3211,'Air',90),
    (6121,'Size',200),
    (7921,'Area',790),
    (9871,'Factors',410)

    select p.* from #parts p
    inner join #partsDetails d on p.partid=d.partid and p.featurename=d.featurename and p.featurevalue<>d.featurevalue

     

    expected result

     i have 10 milion row on table #parts

    and 15 milion rows on table #partsDetails

    so if there are another way then i will check it

     

  • You have no indexes, so you are forcing the query engine to scan the entire tables.

    Yes, not equals/not in/not exists can be less efficient than equals/in/exists, but with proper indexing they can be efficient.

    How many rows do you have where FeatureValue differs?

    Your scenario seems kind of arbitrary. Why would #partsDetails table have exactly the same columns as #parts. It seems odd that given you are querying for differences, you are not actually returning the differing #partsDetails.FeatureValue column in your select.

    It's also odd, though not inconceivable, that FeatureValue would differ, unless #parts.FeatureValue actually represents a default or standard value, while #partsDetails.FeatureValue represents an implemented value. But in that case, I would suggest the column in #parts should be named more specifically (e.g., FeatureValueDefault) to identify what it actually represents.

     

  • It looks like you want the results from the first table when they don't match.  For this specific case I would use EXCEPT instead of joining and comparing:

      Select * From #parts p
    Except
    Select * From #partsDetails pd;

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    It looks like you want the results from the first table when they don't match.  For this specific case I would use EXCEPT instead of joining and comparing:

      Select * From #parts p
    Except
    Select * From #partsDetails pd;

    That's the ticket, IMHO.  It'll even do NULL comparisons that way.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • But the except version gives a different result than join,  if the table Parts have rows - PartID and FeatureName - that does not exist in PartsDetails. And from the definition of the tables, one of the solutions must de wrong.

  • In this context, the <> isn't really a problem. The issue with <> in joins is typically that you have to compare every record in the second table to the current record in the primary table to see what doesn't match.

    With the right indexing applied, that won't be an issue here. For every record in #Parts, you will only have to read one row in #PartsDetail - the one that matches on PartID and FeatureID. That will happen with a SEEK PREDICATE. From there, a PREDICATE will compare FeatureValue from the two tables and decide what rows make it into the final result set.

    You'll need to make sure #Parts and #PartsDetail have a unique index on PartID and FeatureID, with FeatureValue as an included column. With the exact table specifications you give above, I'd actually use a clustered primary key on (partid, featureid).

  • To compare data by using the New Data Comparison Wizard

    On the SQL menu, point to Data Compare, and then click New Data Comparison

    Identify the source and target databases

    Select the check boxes for the tables and views that you want to compare.

  • This was removed by the editor as SPAM

  • jemmielennister wrote:

    Using joins to compare columns by priority among the table. For example, left join returns all values from the first table and null value for the not-matched records from the second table. Similarly, we can use right join, inner join, full join and self join as per our requirements.

    Smells like a precursor to SPAM.  You copied a paragraph from Example 2 of the article at the following link.  Or should I say your spam-testing-bot did?

    https://www.geeksforgeeks.org/how-to-compare-columns-in-two-different-tables-in-sql/

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • jimmyblack21 wrote:

    To compare data by using the New Data Comparison Wizard On the SQL menu, point to Data Compare, and then click New Data Comparison Identify the source and target databases Select the check boxes for the tables and views that you want to compare.

    Yeah... you're another one.  Here's the link that you plagiarized...

    https://docs.microsoft.com/en-us/sql/ssdt/how-to-compare-and-synchronize-the-data-of-two-databases?view=sql-server-ver16

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply