SQL Server Query optimiser problem

Question

The way that SQL server seems to be optimising a query is causing it to break. This is illustrated with the two examples below:

SELECT distinct ET.ElementName, ET.Shared, CONVERT(float,ED.Value), ED.SheetSetVersionID, ED.SheetDataID
FROM tElementData ED 
INNER JOIN tElementTemplate ET 
ON ED.ElementTemplateID = ET.ElementTemplateID 
AND ET.ElementName like 'RPODCQRated'

The above query works fine but is not the query I need to run.

SELECT distinct ET.ElementName, ET.Shared, CONVERT(float,ED.Value),    ED.SheetSetVersionID, ED.SheetDataID
FROM tElementData ED 
INNER JOIN tElementTemplate ET 
ON ED.ElementTemplateID = ET.ElementTemplateID 
AND ET.ElementName like 'RPODCQRated'
AND CONVERT(float,ED.Value) = 0.006388

The above query throws an exception saying that it cannot convert an nvarchar value to a float. tElementData.Value is an nvarchar(500) field and some records do have none numeric values but all values where tElementTemplate = 'RPODCQRated' can be converted to a float, as the top query proves. It seems that SQL server in its wisdom is applying the CONVERT(float,ED.Value) before it tries the join. I need the second query to work somehow, I can rewrite it but there are limitations on what I can do without rewriting the entire data layer of an existing application.

Things i have tried that don't help: moving the last criteria into a where clause rather that the join, making the first query into a CTE and applying the where clause to the CTE, creating a scalar function that calls IsNumeric on the data before trying to do a convert.

The only thin i could get to work was to insert all the data in a temporary table then apply a where clause to the temporary table. Unfortunately to implement this as a solution would involve extensive refactoring of the data layer of an application in order to solve an obscure bug when searching for certain records.

Any ideas?

This is explained well in this answer (which also explains why trying to avoid this by using a CTE won't work) http://stackoverflow.com/questions/5191701/tsql-divide-by-zero-encountered-despite-no-columns-containing-0/5203211#5203211 — Martin Smith, Mar 09 '11 at 17:27
Thanks Martin, I do realise why it is doing this as outlined in the answer in your link, what i was getting at was how might i get round it. — Ben Robinson, Mar 09 '11 at 17:37
@Ben - The order of case statement evaluation is guaranteed as per Brian's answer. — Martin Smith, Mar 09 '11 at 17:43
What I would do is fix the bad design. You should not store float and string data in the same column. You appear to be using EAV tables which is a poor design choice in itself. This willbe difficult to query and will usually perform badly (especially if you are always having to convert data to the correct type) and be impossible to maintain and will cause no end of data integrity problems. Fix the poor design first. EAV should only be used rarely and only for things that cannot be designed for in advance. For fleixbility you are giving up maintainibilty and performance. — HLGEM, Mar 09 '11 at 17:59
I agree that it is a poor design, it is difficult to maintain and support and does perform badly. Unfortunately changing this would mean a fundamental rewrite of the application, I am developer who has been asked to fix a small bug in an app I have never worked on before. There is no way that management would accept a solution that involved a fundamental rewrite of the app and the hundreds of non chargeable man hours of work that would involve. — Ben Robinson, Mar 10 '11 at 09:53
@Martin Brian's answer does work but i think it may just be pure luck, see my comment on his post. If the order of evaluation of a CASE WHEN is guaranteed then the one i tried should also have worked but it resulted in the same error. — Ben Robinson, Mar 10 '11 at 10:11

score 3 · Accepted Answer · answered Mar 09 '11 at 17:04

3

The only way in SQL to ensure linear evaluation is to use a Case Statement

SELECT distinct ET.ElementName, ET.Shared, CONVERT(float,ED.Value), ED.SheetSetVersionID, ED.SheetDataID 
FROM tElementData ED  
INNER JOIN tElementTemplate ET  
ON ED.ElementTemplateID = ET.ElementTemplateID  
AND ET.ElementName like 'RPODCQRated' 
AND CASE(WHEN ET.ElementName like 'RPODCQRated' then CONVERT(float,ED.Value) else 0 end) = 0.006388

This will likely cause a duplicate check on the ElementName, but as far as i know, it's the only way to ensure the order of evaluation.

Unless, of course, you move the entire eval out of the query and nest the results in a CTP and do the cast on the results.

answered Mar 09 '11 at 17:04

Brian Rudolph

6,142
2
23
19

I am going to mark this as the asnwer. I did not use this solution although it does work, providing you remove the brackets between the CASE and WHEN and after end. Curiously i had already tried something similar as follows and it didn't work. `AND CASE WHEN IsNumeric(ED.Value) = 1 then CONVERT(float,ED.Value) else null end = 0.006388` – Ben Robinson Mar 10 '11 at 10:06
@Ben - `IsNumeric` just checks whether it is convertible to at least one of the number datatypes not necessarily that it can be converted to a float. e.g. `select isnumeric('+'),isnumeric('.'),isnumeric('$')` all return 1 but can't be converted to float. – Martin Smith Mar 10 '11 at 11:31

score 0 · Answer 2 · answered Mar 09 '11 at 16:58

0

i would try breaking it out into something like this:

;with a as
(

SELECT distinct
    ET.ElementName,
    ET.Shared,
    CONVERT(float, ED.Value),
    ED.SheetSetVersionID,
    ED.SheetDataID
FROM
    tElementData ED
    INNER JOIN tElementTemplate ET
        ON ED.ElementTemplateID = ET.ElementTemplateID
           AND ET.ElementName like 'RPODCQRated'
)
select *
from a
where CONVERT(float, ED.Value) = 0.006388

or, have you tried "where ED.Value='0.006388' or whatever the varchar equivilent is?

answered Mar 09 '11 at 16:58

DForck42

19,789
13
59
84

Yeah using a CTE like your example and it didn't help. I can't do a string comparison because the operator is dynamically generated, it would be fine for = but for > it would do an alphabetic comparison. – Ben Robinson Mar 09 '11 at 17:33

score 0 · Answer 3 · answered Mar 10 '11 at 10:03

I have solved this problem by using a table function. The element name, the operator and the right hand value of the last join clause are all dynamically generated. I created the below tvf and replaced the relevant part of the select statement with a call to the tvf.

CREATE FUNCTION tvfAdvancedSearch
(
    @TemplateType nvarchar(500)
)
RETURNS 
@Results TABLE 
(   
    ElementName nvarchar(50),
    Shared tinyint, 
    Value NVARCHAR(500), 
    SheetSetVersionID int, 
    SheetDataID int
)
AS
BEGIN
    INSERT INTO @Results
    SELECT distinct ET.ElementName, ET.Shared, ED.Value, ED.SheetSetVersionID, ED.SheetDataID
    FROM tElementData ED 
    INNER JOIN tElementTemplate ET 
        ON ED.ElementTemplateID = ET.ElementTemplateID 
        AND ET.ElementName like @TemplateType   
    RETURN 
END
GO

I would also like to mention that Brian Rudolph's answer also worked but i had already implemented this solution before I saw his post.

SQL Server Query optimiser problem

3 Answers3