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?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
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