需要在SQL Server中加快此查询

INNER JOIN attribute_values av
    ON av.attribute_definition_id = #statusCode# 
LEFT JOIN attribute_values av2
    ON pe.prodtree_element_id = av.prodtree_element_id

这就是问题.pe和av之间存在交叉连接,然后是交叉连接上的外连接.你很幸运它只需要30分钟:-)

我想你想要这个:

SELECT pe.prodtree_element_id prodID, pe.prodtree_element_name_s, li.line_name, av2.value
FROM prodtree_element pe
LEFT JOIN prodtree_link pl
    ON pe.prodtree_element_id = pl.to_prodtree_node_id
LEFT JOIN line li
    ON pe.line_code = li.line_code
--replacement
LEFT JOIN
attribute_values av 
         ON pe.prodtree_element_id = av.prodtree_element_id AND
         av.attribute_definition_id = #statusCode# 
--end replacement
WHERE pe.prodtree_element_func_type <> 'WIZARD'
    AND pe.prodtree_element_topo_type = 'NODE'