sql - 高效查询MSSQL数据库

Question

我的任务是从 MSSQL 数据库中获取一些数据。我不是数据库所有者，我无法进行任何更改或添加任何索引或任何内容。我必须利用我所拥有的。 (我认为数据库设计者吸毒了。)

数据库是通过 python 脚本访问的，但我将在此处显示伪代码，因为它是重要的 SQL。

为此有 5 项数据，我们称它们为 A、B、C、D 和 RecipeInstance。在数据库中，A、B、C 和 D 连接在一起并存储在单个列中，如 A@B@C@D。 'A@B@C@D' 和 RecipeInstance 之间存在一对多关系。

我的 2 个任务是:

1) 给定A、B、C、D得到所有的食谱

这在概念上很简单，但我的查询速度很慢。这是我对此的查询:

SELECT PDEName as recipe
FROM RecipeInstance
WHERE PdeInstanceId
IN (SELECT DISTINCT PdeInstanceId FROM RecipeTableValue WHERE CellValue
IN (SELECT DISTINCT PDEName FROM RunInstance WHERE PdeInstanceId
IN (SELECT PdeInstanceId FROM RunTableValue WHERE CellValue = 'A@B@C@D')))

此查询需要 16 秒。我真的需要让它更快。我尝试将其分解为 4 个单独的查询，但它们加起来仍然需要 16 秒。这些表上没有有用的索引，我无法创建任何索引。无论如何，任何人都可以想到让它更快吗？

2) 给定 A、B、C 和配方得到 D

这更复杂，因为从 RecipeInstance 到 D 所在的 TargetInstance 没有关系。这是我想出的:

select PdeName as TargetPdeName
FROM TargetInstance
WHERE PdeName like 'A@B@C@%'

# this query returns between 20,000 and 40,000 rows

foreach TargetPdeName returned from the above query
    SELECT PDEName as RecipePdeName
    FROM RecipeInstance
    WHERE PdeInstanceId
    IN (SELECT DISTINCT PdeInstanceId FROM RecipeTableValue WHERE CellValue
    IN (SELECT DISTINCT PDEName FROM RunInstance WHERE PdeInstanceId
    IN (SELECT PdeInstanceId FROM RunTableValue WHERE CellValue = TargetPdeName)))

    if RecipePdeName == Recipe:
        # this is the one we want
        (a, b, c, d) = TargetPdeName.split('@')
        return d

所以这里的问题显然是我必须运行数万个查询，每个查询需要 16 秒。任何人都可以看到我如何以有效的方式向后遍历这种关系吗？

Answer 1

下面是JOIN 和EXISTS 查询。尝试两者，让我们知道它们是如何运行的。

1)

加入版本

SELECT DISTINCT reci.PDEName as recipe
FROM RecipeInstance reci
JOIN RecipeTableValue rectv ON reci.PdeInstanceId = rectv.PdeInstanceId
JOIN RunInstance runi ON rectv.CellValue = runi.PDEName
JOIN RunTableValue runtv ON runi.PdeInstanceId = runtv.PdeInstanceId 
WHERE runtv.CellValue = 'A@B@C@D'

存在版本

SELECT PDEName as recipe
FROM RecipeInstance reci
WHERE EXISTS (
    SELECT * FROM RecipeTableValue rectv 
    WHERE rectv.PdeInstanceId = reci.PdeInstanceId
    AND EXISTS (
        SELECT * FROM RunInstance runi 
        WHERE runi.PDEName = rectv.CellValue
        AND EXISTS (
            SELECT * FROM RunTableValue runtv 
            WHERE runi.PdeInstanceId = runtv.PdeInstanceId
            AND CellValue = 'A@B@C@D'            
        )
    )
)

2) 编辑要将ti.PdeName 拆分为@ 并提取最后一个值，您需要定义自己的函数。参见 How do I split a string so I can access item x

加入版本

SELECT DISTINCT ti.PdeName
FROM RecipeInstance reci
JOIN RecipeTableValue rectv ON reci.PdeInstanceId = rectv.PdeInstanceId
JOIN RunInstance runi ON rectv.CellValue = runi.PDEName
JOIN RunTableValue runtv ON runi.PdeInstanceId = runtv.PdeInstanceId 
JOIN TargetInstance ti ON runtv.CellValue = ti.PdeName
WHERE reci.PDEName = "MyRecipe"

存在版本

SELECT ti.PdeName
FROM TargetInstance ti
WHERE EXISTS (
    SELECT * FROM RunTableValue runtv
    WHERE runtv.CellValue = ti.PdeName
    AND EXISTS (
        SELECT * FROM RunInstance runi
        WHERE runi.PdeInstanceId = runtv.PdeInstanceId 
        AND EXISTS (
            SELECT * FROM RecipeTableValue rectv
            WHERE rectv.CellValue = runi.PDEName
            AND EXISTS (
                SELECT * FROM RecipeInstance reci
                WHERE reci.PdeInstanceId = rectv.PdeInstanceId
                AND reci.PDEName = "MyRecipe"
            )
        )
    )
)