July 21, 2010 at 10:55 am
After applying CU5 the following line causes my query to fail when issued against a sql 2000 linked server. I shouldn't say fail, it just pegs out the processor and never finishes. If this one part of the where clause is removed, it runs fine. It also worked before I applied CU5. Any ideas why?
where right(a.gltd_glpd_no,2) != '13'
July 21, 2010 at 11:04 am
I can't say why it's different before and after the cumulative update, but I can tell you that this is generally a problematic way to query data.
Doing any kind of manipulation on the field in the WHERE clause will prevent the use of any index and will generally result in significantly degraded performance. You would be much better off creating a calculated column that is indexed and then query off of that.
July 21, 2010 at 11:19 am
bteraberry (7/21/2010)
I can't say why it's different before and after the cumulative update, but I can tell you that this is generally a problematic way to query data.Doing any kind of manipulation on the field in the WHERE clause will prevent the use of any index and will generally result in significantly degraded performance. You would be much better off creating a calculated column that is indexed and then query off of that.
I absolutely agree. I didn't write it.
July 21, 2010 at 12:04 pm
No need for a computer column here. You should use the like operator.
DECLARE @t TABLE(
col VARCHAR(10)
);
INSERT INTO @t VALUES ('13abc');
INSERT INTO @t VALUES ('1abcde');
SELECT *
FROM @t
where col NOT LIKE '13%'
July 21, 2010 at 12:09 pm
Adam Haines (7/21/2010)
No need for a computer column here. You should use the like operator.
DECLARE @t TABLE(
col VARCHAR(10)
);
INSERT INTO @t VALUES ('13abc');
INSERT INTO @t VALUES ('1abcde');
SELECT *
FROM @t
where col NOT LIKE '13%'
The posted query uses the RIGHT function, so the WHERE clause would be:
where col NOT LIKE '%13'
This will probably result in a table scan or at least an index scan either way.
July 21, 2010 at 12:18 pm
Adam Haines (7/21/2010)
No need for a computer column here. You should use the like operator.
DECLARE @t TABLE(
col VARCHAR(10)
);
INSERT INTO @t VALUES ('13abc');
INSERT INTO @t VALUES ('1abcde');
SELECT *
FROM @t
where col NOT LIKE '13%'
You would be correct if he was looking for the left 2 characters. That would be easy. Since he's looking for the right 2 characters a computed column would be one way to get a seek and avoid the scan that would necessarily result from like '%13'.
July 21, 2010 at 12:21 pm
Michael Valentine Jones (7/21/2010)
Adam Haines (7/21/2010)
No need for a computer column here. You should use the like operator.
DECLARE @t TABLE(
col VARCHAR(10)
);
INSERT INTO @t VALUES ('13abc');
INSERT INTO @t VALUES ('1abcde');
SELECT *
FROM @t
where col NOT LIKE '13%'
The posted query uses the RIGHT function, so the WHERE clause would be:
where col NOT LIKE '%13'This will probably result in a table scan or at least an index scan either way.
Your right I missed the Ends with.. Thanks for the correction :^). You are also right in that a table scan will result with the like operator using an ends with search. In this scenario, it is probably best to use the original suggest method, of a persisted computed column or an indexed view that filters the data.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply