CODEWITHSUNDEEP
sqlsql-servert-sql
Coding Flow
Coding Flow

Reputation: 21881

SQL Server Query optimiser problem

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?

Upvotes: 0

Views: 180

Answers (3)

Coding Flow
Coding Flow

Reputation: 21881

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.

Upvotes: 0

Brian Rudolph
Brian Rudolph

Reputation: 6318

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.

Upvotes: 3

DForck42
DForck42

Reputation: 20357

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?

Upvotes: 0

Related Questions