Link to home
Start Free TrialLog in
Avatar of Bobby X
Bobby XFlag for United States of America

asked on

Need help with T-SQL on SQL Server 2014

Hi,

I need help on optimizing the query below.  If i comment out the last where clause (e.g, (dbo.fnRemoveNonNumericCharacters(ma.phone_1) LIKE '%3367667398%' OR dbo.fnRemoveNonNumericCharacters(ma.phone_2) LIKE '%3367667398%')), the query will return almost instantaneously. The culprit is on this last where clause because it's using a function to first remove all non-numeric characters from phone_1 & phone_2 columns before doing a LIKE against the phone number I am searching for. I've tried creating a non-unique and non-clustered index on both phone_1 & phone_2 columns in the database, but it doesn't help. Is there any way to optimize this query or any other approach to accomplish this in a much optimized way?

SELECT DISTINCT TOP 1000 m.member_id
FROM members m WITH (NOLOCK)
LEFT JOIN members_address ma WITH (NOLOCK) on ma.address_id = m.address_id
WHERE m.isActive = 1
AND (dbo.fnRemoveNonNumericCharacters(ma.phone_1) LIKE '%3367667398%' OR dbo.fnRemoveNonNumericCharacters(ma.phone_2) LIKE '%3367667398%')

Below is the definition of my scalar function fnRemoveNonNumericCharacters():

CREATE FUNCTION [dbo].[fnRemoveNonNumericCharacters]
(
      @inputString VARCHAR(1000)
)
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @inputString) > 0
    BEGIN
        SET @inputString = STUFF(@inputString, PATINDEX('%[^0-9]%', @inputString), 1, '')
    END
    RETURN @inputString
END
GO

Many thanks in advance.
Avatar of chaau
chaau
Flag of Australia image

My suggestion will be to fix your data entry program that populates the Phone fields the first place. Apply the [fnRemoveNonNumericCharacters] at data entry time.

When you fix your data entry code then you can run a global update for your table to fix the phone numbers with the [fnRemoveNonNumericCharacters] for the whole table. That way you will be able to lookup the phone numbers verbatim

If you need the alpha characters in Phone fields for display purpose then you can either fix the display program to use phone mask applicable to your locale or have separate PhoneDisplay fields in the table. However, I wouldn't go the latter path, I would keep one set of data in the table.

If there is no way to fix the program then you will need to add the CleanPhone fields into the table and use a trigger to populate them at inserts/updates.
As an intermediate (and may even be dirty!) solution, you can do the following IF this select is part of a stored procedure.

Before that, you are selecting top 1000, in such case better to have a ORDER BY clause.

You can create a table variable with member_id, address_id and two phone numbers and populate it with member_id, address_id and two phone number values returned by function.

Then use the above table in FROM clause and LEFT OUTER JOIN it with physical tables you already have.

You can even apply index on the table variable on member_id + address_id to improve performance. Basically, divide and conquer!

Hope it helps!
Avatar of Bobby X

ASKER

Nitin Sontakke,

I like your solution. Could you please provide the actual SQL code?

I'd greatly appreciate it.

Many thanks in advance.
Come to think of it, is this the final query? Or part of some bigger logic? That would make a lot of difference.

How many records does members table have? Hope it already has a clustered index on member_id.

Hope member_address also have a clustered index on address_id.

Is there a part of phone numbers which is almost certainly fixed which can be determined WITHOUT calling a function. For example, can we just say '336%' or '%398' as a first pass?

Depending on the answer to these question there is point going ahead otherwise no.

Bottom line, is there ANY way where by we can filter records in two scans. Wherein first scan significantly reduce the result set for second scan.
ASKER CERTIFIED SOLUTION
Avatar of Nitin Sontakke
Nitin Sontakke
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Create a nonclustered index on the ma table and see if that helps:

CREATE UNIQUE NONCLUSTERED INDEX members_address__IX_phone_1_phone_2 ON dbo.members_address ( isActive, address_id, phone_1, phone_2 ) WITH ( /*DATA_COMPRESSION=PAGE,*/ FILLFACTOR = 99, SORT_IN_TEMPDB = ON ) /*ON [filegroupname]*/;
Avatar of Bobby X

ASKER

Scott Pletcher,

I went ahead and tried creating that unique nonclustered index like you suggested, but it didn't help at all.

Thank you though.
Avatar of Bobby X

ASKER

Nitin Sontakke,

Yes, member_id and address_id already have clustered index because they are primary keys.
Avatar of Bobby X

ASKER

Nitin Sontakke,

Great job. The search is much faster now.

Many thanks again to all.