SQL Server查询优化器问题

Question

SQL server优化查询的方式似乎会导致查询中断。下面两个例子说明了这一点：

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 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

上面的查询抛出了一个异常，声明它不能将nvarchar值转换为浮点数。tElementData.Value是一个nvarchar(500)字段，有些记录没有数字值，但是tElementTemplate = 'RPODCQRated‘的所有值都可以转换为浮点型，正如top查询所证明的那样。SQL server似乎在尝试连接之前应用了CONVERT(float，ED.Value)。我需要第二个查询才能以某种方式工作，我可以重写它，但在不重写现有应用程序的整个数据层的情况下，我可以做的事情有一些限制。

我尝试过的一些方法都没有用:将最后一个条件移动到where子句中而不是连接中，将第一个查询转换为CTE并将where子句应用于CTE，创建一个标量函数，该函数在尝试执行转换之前对数据调用IsNumeric。

我唯一能做的就是在临时表中插入所有数据，然后对临时表应用where子句。不幸的是，要将其实现为解决方案，需要对应用程序的数据层进行广泛的重构，以便在搜索某些记录时解决一个模糊的bug。

有什么想法吗？

Answer 1

在SQL中，确保线性计算的唯一方法是使用Case语句

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 

这可能会导致对ElementName的重复检查，但据我所知，这是确保评估顺序的唯一方法。

当然，除非您将整个eval移出查询，并将结果嵌套在CTP中，然后对结果进行强制转换。

Answer 2

我会试着把它分解成这样的东西：

;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

或者，您是否尝试过"where ED.Value='0.006388‘或任何varchar等价物？“

Answer 3

我已经通过使用表函数解决了这个问题。最后一个join子句的元素名称、运算符和右手边的值都是动态生成的。我创建了下面的tvf，并用对tvf的调用替换了select语句的相关部分。

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

我还想提一下Brian Rudolph的回答也是有效的，但在我看到他的帖子之前，我已经实现了这个解决方案。