February 3, 2014 at 10:58 am
Hello,
I would like to know if there is a better way to do the following:
DECLARE @TempTab TABLE (PhoneNum VARCHAR(10));
INSERT INTO @TempTab (PhoneNum) VALUES
(1234567890), (1231111111), (1231111112), (1233333333), (1230000000),
(1234999991), (1235555555), (1237777777), (1239999999);
-- Can this criteria be replaced with something better?
SELECT * FROM @TempTab
WHERE RIGHT(PhoneNum, 7) NOT IN ('0000000', '1111111', '2222222', '3333333', '44444444',
'5555555', '6666666', '7777777', '8888888', '9999999');
/*
Disired output:
1234567890
1231111112
1234999991
*/
Thank you,
djj
February 3, 2014 at 11:14 am
Hi.
Your code will inevitably result in a table scan.
So would my only candidate replacement, which would replace the RIGHT function with a NOT LIKE '%1111111'
Thanks,
John.
February 3, 2014 at 11:19 am
Thank you.
So it would be like?
WHERE PhoneNum not like '%1111111'
AND PhoneNum not like '%2222222'
AND PhoneNum not like '%3333333'
AND PhoneNum not like '%4444444'
AND PhoneNum not like '%5555555'
AND PhoneNum not like '%6666666'
AND PhoneNum not like '%7777777'
AND PhoneNum not like '%8888888'
AND PhoneNum not like '%9999999'
No slick way of making it just one criteria?
February 3, 2014 at 11:30 am
djj (2/3/2014)
Thank you.So it would be like?
WHERE PhoneNum not like '%1111111'AND PhoneNum not like '%2222222'
AND PhoneNum not like '%3333333'
AND PhoneNum not like '%4444444'
AND PhoneNum not like '%5555555'
AND PhoneNum not like '%6666666'
AND PhoneNum not like '%7777777'
AND PhoneNum not like '%8888888'
AND PhoneNum not like '%9999999'
No slick way of making it just one criteria?
Correct. No slick way.
February 3, 2014 at 11:32 am
That's also going to result in a scan because a leading edge wild card can't use an index key.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 3, 2014 at 11:34 am
The only way to make it perform better is to make the search terms Sarg-able and index-able, as John referred to, otherwise you are stuck with a table scan.
you could create a persisted calculated column on the right
(phone,7), index it, and compare it, but i would bet that a search like this is more of a one-time validation process, so I'd think you just surrender to a table scan, and fix the data for offending matches.
also, aren't you looking for garbage data, so you want to find entries that ARE IN (111... etc, instead of not in?
Lowell
February 3, 2014 at 11:36 am
Grant, now that you point it out I remember about the scan.
I am not that worried about the scan for the desired use, I was just hoping to learn a better way to do the criteria rather than the ten lines.
Thanks again to you both.
February 3, 2014 at 11:39 am
Lowell, thanks for the reply. I am actually looking to delete the "bad" numbers so yes it is an IN.
February 3, 2014 at 12:02 pm
djj (2/3/2014)
Lowell, thanks for the reply. I am actually looking to delete the "bad" numbers so yes it is an IN.
well going forward, you could add a check constraint to prevent bad data.
i was fiddling around with a filtered idnex to find the offenders, as well, just for fun.
Lowell
February 4, 2014 at 6:21 pm
With a PRIMARY KEY on telephone number using SQL 2012, both of these queries get me a Clustered Index Scan.
DECLARE @TempTab TABLE (PhoneNum VARCHAR(10) PRIMARY KEY);
INSERT INTO @TempTab (PhoneNum) VALUES
(1234567890), (1231111111), (1231111112), (1233333333), (1230000000),
(1234999991), (1235555555), (1237777777), (1239999999);
-- Can this criteria be replaced with something better?
SELECT *
FROM @TempTab
WHERE RIGHT(PhoneNum, 7) NOT IN ('0000000', '1111111', '2222222', '3333333', '4444444',
'5555555', '6666666', '7777777', '8888888', '9999999');
SELECT * FROM @TempTab
WHERE PhoneNum not like '%0000000'
AND PhoneNum not like '%1111111'
AND PhoneNum not like '%2222222'
AND PhoneNum not like '%3333333'
AND PhoneNum not like '%4444444'
AND PhoneNum not like '%5555555'
AND PhoneNum not like '%6666666'
AND PhoneNum not like '%7777777'
AND PhoneNum not like '%8888888'
AND PhoneNum not like '%9999999';
Edit: Same in SQL 2008 R2.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 4, 2014 at 6:27 pm
This code gets me a non-clustered INDEX Scan on 2008 R2 and 2012.
CREATE TABLE #TempTab
(
ID INT IDENTITY PRIMARY KEY
,PhoneNum VARCHAR(10)
,CONSTRAINT IX1 UNIQUE NONCLUSTERED (PhoneNum)
);
INSERT INTO #TempTab (PhoneNum) VALUES
(1234567890), (1231111111), (1231111112), (1233333333), (1230000000),
(1234999991), (1235555555), (1237777777), (1239999999);
-- Can this criteria be replaced with something better?
SELECT *
FROM #TempTab
WHERE RIGHT(PhoneNum, 7) NOT IN ('0000000', '1111111', '2222222', '3333333', '4444444',
'5555555', '6666666', '7777777', '8888888', '9999999');
SELECT *
FROM #TempTab
WHERE PhoneNum not like '%0000000'
AND PhoneNum not like '%1111111'
AND PhoneNum not like '%2222222'
AND PhoneNum not like '%3333333'
AND PhoneNum not like '%4444444'
AND PhoneNum not like '%5555555'
AND PhoneNum not like '%6666666'
AND PhoneNum not like '%7777777'
AND PhoneNum not like '%8888888'
AND PhoneNum not like '%9999999';
GO
DROP TABLE #TempTab;
The only reason a "table scan was inevitable" was because the original table had no indexes.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 4, 2014 at 6:35 pm
dwain.c (2/4/2014)
This code gets me a non-clustered INDEX Scan on 2008 R2 and 2012.
CREATE TABLE #TempTab
(
ID INT IDENTITY PRIMARY KEY
,PhoneNum VARCHAR(10)
,CONSTRAINT IX1 UNIQUE NONCLUSTERED (PhoneNum)
);
INSERT INTO #TempTab (PhoneNum) VALUES
(1234567890), (1231111111), (1231111112), (1233333333), (1230000000),
(1234999991), (1235555555), (1237777777), (1239999999);
-- Can this criteria be replaced with something better?
SELECT *
FROM #TempTab
WHERE RIGHT(PhoneNum, 7) NOT IN ('0000000', '1111111', '2222222', '3333333', '4444444',
'5555555', '6666666', '7777777', '8888888', '9999999');
SELECT *
FROM #TempTab
WHERE PhoneNum not like '%0000000'
AND PhoneNum not like '%1111111'
AND PhoneNum not like '%2222222'
AND PhoneNum not like '%3333333'
AND PhoneNum not like '%4444444'
AND PhoneNum not like '%5555555'
AND PhoneNum not like '%6666666'
AND PhoneNum not like '%7777777'
AND PhoneNum not like '%8888888'
AND PhoneNum not like '%9999999';
GO
DROP TABLE #TempTab;
The only reason a "table scan was inevitable" was because the original table had no indexes.
A nonclustered index scan on the column is no major advantage. You still have to traverse every value in the index. Depending on the width of the table, the advantage may be close to zero.
So, I stand corrected in part. A SCAN is inevitable. For your example of a one column table you would have no advantage at all.
Thanks
John.
February 4, 2014 at 6:43 pm
JohnFTamburo (2/4/2014)
A nonclustered index scan on the column is no major advantage. You still have to traverse every value in the index. Depending on the width of the table, the advantage may be close to zero.So, I stand corrected in part. A SCAN is inevitable. For your example of a one column table you would have no advantage at all.
Thanks
John.
Yep. Now that I agree with.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply