0

I am trying to execute the query as below in SQL Server but it is very slow due to the Replace function, can anyone help in optimizing the query to run very fast

SELECT 
    dbo.Orders.OrigID
FROM
    dbo.Orders
INNER JOIN 
    dbo.OrderedReports ON dbo.Orders.OrigID = dbo.OrderedReports.OrderID
WHERE 
    (REPLACE(Orders.Email, '^^', '''') = 'Khengtjinyap@gmail.com')
Dale K
  • 25,246
  • 15
  • 42
  • 71
kumar shivam
  • 69
  • 1
  • 3
  • 8
  • 2
    It is indeed very slow. There's not a great deal you can do about it with just this query. If your data must contain ^^ and this query is overall important to you, I'd suggest adding a new persisted computed column such as NormalisedEmail that does this replace operation _once_ rather than every time you run the query. – TZHX May 20 '22 at 08:27
  • 5
    You are using `REPLACE` in your `WHERE`, making the query non-SARGable. If you want to improve the performance, you'll need to add a (`PERSISTED`) computed column to your table `Orders` that has the `Email` value with the caret (`^`) characters replaced, and then you would index and query that column. – Thom A May 20 '22 at 08:27
  • 2
    On a separate note, [3+ part naming on Columns will be Deprecated](https://wp.larnu.uk/3-part-naming-on-columns-will-be-deprecated/) and should be avoided. You are far better off aliasing your objects in the `FROM` and using those to qualify your columns. This avoids the use of a feature that will be deprecated and possibly removed in a future version, and makes your code much more succinct and readable. Also, your `ON` clause is missing the equals (`=`) is missing. – Thom A May 20 '22 at 08:27
  • 2
    Why on earth do you have `^^` represent single quote anyway? I'd be suspicious that this is a sign that somewhere isn't using parameterised queries and is using this to hack around the fact that the quote is the literal string delimiter – Martin Smith May 20 '22 at 08:40
  • You can simply remove the entire `REPLACE`, because `'Khengtjinyap@gmail.com'` doesn't contain a single quote, so no value containing `'^^'` will be equal to `'Khengtjinyap@gmail.com'` after the `REPLACE` – HoneyBadger May 20 '22 at 08:58
  • Can you help to define "fast" and "slow" for this problem? And roughly what plan shape you get which is too slow? – Conor Cunningham MSFT May 20 '22 at 12:25
  • If you replace on the other side of the `=` you'll likely get better performance, especially if you always search for a specific address – HoneyBadger May 20 '22 at 15:06

1 Answers1

2

One simple long term fix here would be to simply remove the ^^ symbols from the email before you insert the data. In order to deal with the email data which already exists in the Orders table, you could do the following one-time update:

UPDATE Orders
SET Email = REPLACE(Email, '^^', '')
WHERE Email LIKE '%^^%';

You could also create a new column which contains the cleaned up email addresses. Once you have made this change, just use a direct equality comparison in your query, which can now also use an index on the Email column.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360