Post reply

Are there are any way to get partc without make join by Verification Hash?

  • November 25, 2021 at 12:24 pm

    Go to Answer

    #3956825

    I work on sql server 2014

    I need to get partc as expected result

    Actualy i need partc from table #partsc that have Verification Hash on table #VervicationCode

    are there are any way to get part c without join with Verification Hash

    create table #partsc
     (
     partc int,
     [VerificationHash] VARBINARY(512) NULL 
     )
     insert into #partsc(partc,VerificationHash) 
     values
     (11234,0x566B391EDEB07A47B17B89265ABE19850655885D83247AE7E7C28849D8873A31F56D139E4987BFE85B476035E39B7308C5D203955A238F92D5A2096B50AD674C),
     (15431,0x5677965D16CEEDC754EAED74780662518E132F08C1B6CFF67BE62D21100C3B4C63E09937BB7153CDE67550F957AFBB23658B2193EB842FC173A8F8460DED246D),
     (67501,0x569132E995038072EA4AE52DC760FCB2C21AE1A6F047C66FF86B483BF575DE2EF412B3B10CDC8B8DC8B8D31D28BF90442C31811F13EC5B49DE1EBC008B6EEF16),
     (87190,0x56C5668A7FCB015D39EE602FAC80124050D0ADCA684D77FC99D7C293DCC09DA1A4FA2514F89E86D7619AE20A72C6CAE2A7BFE776F89C5494C36994C84241D705),
     (987610,0x56E0315397B4A932570101661B449FC41DF9683F5EDCC7387A8C9FA8E357A06FCF1989587CCF3E0548F79F36BB2F5C91BF6CD9E988EA9922C5C865A043B51B1F),
     (89871,0x57B1B8FF59BFE3F788AA1C6D5CFFC40D54F0D494D60B43CED1B52C1E290CC8AE62C3E1220365F1DCB0A00688AF92222DC32CC5454DCF9572812735D9FAF7E3C7),
     (12909,0x57DAFCAA14E6561FD8C2109627732DFDBEC5E169682080EF1107FA96ED505CC18BD22924BFA19A1A346BB15440FF48F53202A88672AD6FC66F4DAF0D518BC69C)
        
     create table #VervicationCode
     (
     [VerificationHash] VARBINARY(512) NULL 
     )
     insert into #VervicationCode(VerificationHash) 
     values
     (0x5677965D16CEEDC754EAED74780662518E132F08C1B6CFF67BE62D21100C3B4C63E09937BB7153CDE67550F957AFBB23658B2193EB842FC173A8F8460DED246D),
     (0x569132E995038072EA4AE52DC760FCB2C21AE1A6F047C66FF86B483BF575DE2EF412B3B10CDC8B8DC8B8D31D28BF90442C31811F13EC5B49DE1EBC008B6EEF16),
     (0x56C5668A7FCB015D39EE602FAC80124050D0ADCA684D77FC99D7C293DCC09DA1A4FA2514F89E86D7619AE20A72C6CAE2A7BFE776F89C5494C36994C84241D705),
     (0x56E0315397B4A932570101661B449FC41DF9683F5EDCC7387A8C9FA8E357A06FCF1989587CCF3E0548F79F36BB2F5C91BF6CD9E988EA9922C5C865A043B51B1F)

    expected result will be :

    partc

    15431

    67501

    87190

    987610

  • November 25, 2021 at 2:06 pm

    #3956859

    Why would you want to avoid JOIN?  Maybe use IN but there's no benefit afaik and it might actually be slower

    select partc
    from #partsc
    where VerificationHash in(select VerificationHash
                              from #VervicationCode);

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • November 25, 2021 at 6:04 pm

    #3956924

    thank you for reply

    i need to generate id for two tables then join by id

    so How to do that please ?

    if i join hash to hash then for big data will be slow

    so i need to generate id for every hash then join by it

    so how to do that

  • November 25, 2021 at 6:08 pm

    Answer

    #3956925

    I agree with Steve here - why would you want to avoid the JOIN?  Put a good index on it and you should be good to go.

    As an alternate approach to the "IN" you could use "EXISTS" with something like:

    SELECT partc
    FROM #partsc A
    WHERE EXISTS (SELECT 1
                          FROM #VervicationCode B
    WHERE A.VerificationHash = B.VerificationHash);

    Not sure if it is any faster or better than the "IN" approach, but gets you the same result.  My quick check on my test system, the execution plan is identical with EXISTS vs IN, and personally, I find the IN approach easier to understand what is going on.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • November 25, 2021 at 6:17 pm

    #3956926

    thank you very much for support

  • November 25, 2021 at 6:56 pm

    #3956929

    Personally, I don't think my answer was any better than Steve's.  I think his answer was probably just as "correct" as mine.

    But to address your concern about needing to add an ID, there is no fast way to add that after the fact.  It should have been put in place at design time if it was needed.  But joining on VARBINARY, with a good index on the column, should not be that slow, even with large data sets.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • November 29, 2021 at 1:05 am

    #3957650

    Avoiding a join here is a mistake, IMHO, especially if the hash-code is going to be used for "verification".

    Ah... there's that word... "verification".  By itself it can mean so many different things and imply so very many different uses especially when it comes to "big data" (as you said).

    So, before you go any further, let's ask the question... what do YOU mean by "verification" and what is this "verification" going to be used for?  In the process of telling us that, please provide the DDL for the two tables involved including what the Primary Key and the Clustered Index (they could be different) are for both.  Who knows?  We might even be able to setup a million row test table to help you make sure of the best method.  😀

    Also, why are you using VARBINARY(512) when it appears that all of your data (seen so far) is only BINARY(64)?  The SHA2-512 hashing algorithm uses 512 BITs, not BYTEs.  512 bits is only 64 bytes.

     

    • This reply was modified 3 years ago by  Jeff Moden. Reason: Corrected a mistake. I original posted an incorrect length and also made an assumption that the data was a simple hash... it's way to big for that
    • This reply was modified 3 years ago by  Jeff Moden. Reason: One more correction... at 64 Bytes, these can be valid hashes using SHA2_512 hashing algorithm

    --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)

Viewing 7 posts - 1 through 6 (of 6 total)

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