Replace and like - oh great

  • One of the Devs just complained about the speed of this statement:

     

    INNER JOIN staging c
    ON (REPLACE(e.eligibility_ud,'-01','') LIKE SubscriberNumber + '%'
    OR SubscriberNumber LIKE REPLACE(e.eligibility_ud,'-01','') + '%')

    Any ideas on how I could make this go faster?

  • Given your >13,000 points, I need to confirm with you whether this is a joke?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Kinda yeah - feel my pain

     

    I know there is a translate function that can sometimes be used in place of 'replace' but I'm on v120. So no easy way out of his.

     

    • This reply was modified 9 months ago by  krypto69.
    • This reply was modified 9 months ago by  krypto69.
  • Get rid of all the replace stuff, you're non-sargable it has to scan the entire table to make that work, bigger the table longer it will take to complete

  • Well, if REPLACE(e.eligibility_ud,'-01','') is a very common operation in your system, you could create it as a PERSISTED COMPUTED column.

    You could also see what happens if you replace that inner join with two left joins (some tweaking in your SELECT would probably also be required). I have no idea what this would do to performance, however.

    It would help others if you give some examples of the different format combinations of eligibility_ud and SubscriberNumber.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • ahh persisted...hmm its not used that much so maybe overkill for this.

    I like the two left joins - trying that now.

     

    Thanks Phil - appreciate the help!

  • SQL wouldn't necessarily have to scan the entire table, if the relevant columns happened to be in a nonclus index -- not likely here, but theoretically possible.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • I am assuming the eligibility_ud columns is structured as: {SubscriberNumber}-{number}

    So that would mean we could have something like: 123456789-01, 123456789-02, 123456789-03, ...

    If that is the case, and the intended results are to return the Subscriber that matches any/all eligibility - then a pattern match of e.eligibility_ud LIKE SubscriberNumber + '-__' should handle both conditions.  No need for the OR because either statement is attempting to search for a match based on the first part of eligibility_ud = SubscriberNumber.

     

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

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

  • Ahh great catch Jeffery! Much thanks!!

  • If my assumption about the eligibility column is correct - then I would next recommend a persisted computed column on that table as: SUBSTRING(eligibility_ud, 1, CHARINDEX('-', eligibility_ud + '-', 1) - 1) and name that column SubscriberNumber.

    With an index on that column - then joining between the 2 tables would be much more efficient.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

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

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

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