Bobby X
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.fnRemoveNonNumericCha racters(ma .phone_1) LIKE '%3367667398%' OR dbo.fnRemoveNonNumericChar acters(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.fnRemoveNonNumericCha racters(ma .phone_1) LIKE '%3367667398%' OR dbo.fnRemoveNonNumericChar acters(ma. phone_2) LIKE '%3367667398%')
Below is the definition of my scalar function fnRemoveNonNumericCharacte rs():
CREATE FUNCTION [dbo].[fnRemoveNonNumericC haracters]
(
@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.
I need help on optimizing the query below. If i comment out the last where clause (e.g, (dbo.fnRemoveNonNumericCha
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.fnRemoveNonNumericCha
Below is the definition of my scalar function fnRemoveNonNumericCharacte
CREATE FUNCTION [dbo].[fnRemoveNonNumericC
(
@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.
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!
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!
ASKER
Nitin Sontakke,
I like your solution. Could you please provide the actual SQL code?
I'd greatly appreciate it.
Many thanks in advance.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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]*/;
CREATE UNIQUE NONCLUSTERED INDEX members_address__IX_phone_
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.
I went ahead and tried creating that unique nonclustered index like you suggested, but it didn't help at all.
Thank you though.
ASKER
Nitin Sontakke,
Yes, member_id and address_id already have clustered index because they are primary keys.
Yes, member_id and address_id already have clustered index because they are primary keys.
ASKER
Nitin Sontakke,
Great job. The search is much faster now.
Many thanks again to all.
Great job. The search is much faster now.
Many thanks again to all.
When you fix your data entry code then you can run a global update for your table to fix the phone numbers with the [fnRemoveNonNumericCharact
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.