February 5, 2024 at 4:00 pm
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?
February 5, 2024 at 4:03 pm
Given your >13,000 points, I need to confirm with you whether this is a joke?
February 5, 2024 at 4:19 pm
February 5, 2024 at 4:32 pm
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
February 5, 2024 at 4:34 pm
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.
February 5, 2024 at 5:12 pm
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!
February 5, 2024 at 6:33 pm
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".
February 5, 2024 at 7:46 pm
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
February 5, 2024 at 9:51 pm
Ahh great catch Jeffery! Much thanks!!
February 5, 2024 at 10:55 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy