sql server—基于表中的值运行动态sql,然后将它们附加到同一结果集

kxeu7u2r  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(291)

我需要查询一个名为“customdata”的表。
该表包含三列(相关列)。一个包含字段,另一个包含表名,第三个包含外部表中记录的id。目标是获取这些值,在该外部表中查找该字段的值,并将其作为列附加到结果集中。这有可能吗?如果表还没有被读取,我就不能用表中的数据来构建动态sql。
我正试图通过将其转换为存储过程来修复notmapped字段中当前发生的异常缓慢的查找。

ID  Value  foreignTable ftIndex ftRow   customField 
1  "yes"  "tblDriver"    2001    "Name"  "Licensed?"  
2  "no"   "tblDriver"    2002    "Name"  "Licensed?"
3  "7"    "tblOrigin"    1131    "Name"  "tank count"

预期产量:

1 "licensed" "yes" 'Darryl Coffman'
2 "licensed" "no"  'Cash Rainer'
3 "tank count" "7"   'texas field'
8ftvxx2r

8ftvxx2r1#

虽然这并没有明确回答这个问题,但我想将我的上述评论总结成一个答案,说明为什么你的设计有如此多的缺陷,以及为什么它不起作用;所以你需要修复它。请注意,我们没有足够的信息来说明该修复是什么,除了说明您需要花费大量时间将设计修复为标准化方法之外。
首先,实现最终目标的唯一方法是使用动态sql。如果您只在一个columns值之后,那么您实际上是“ok”,因为您可以编写如下语句:

SELECT DT.ID,
       DT.CustomField,
       DT.[Value],
       D.[Name]
FROM dbo.DenormalisedTable DT
     JOIN dbo.tblDriver D ON DT.ForeignTable = N'tblDriver'
                         AND DT.ftIndex = D.ID
WHERE DT.ftRow = N'Name';

不幸的是,您继续声明情况并非如此,这意味着您将需要一个如下所示的查询(对于我们的2个示例表):

SELECT DT.ID,
       DT.CustomField,
       DT.[Value],
       CASE DT.ForeignTable WHEN N'tblDriver' THEN CASE DT.CustomField WHEN N'Name' THEN D.[Name] END
                            WHEN N'tblOrigin' THEN CASE DT.CustomField WHEN N'Name' THEN O.[Name] END
       END
FROM dbo.DenormalisedTable DT
     LEFT JOIN dbo.tblDriver D ON DT.ForeignTable = N'tblDriver'
                              AND DT.ftIndex = D.ID
     LEFT JOIN dbo.tblOrigin O ON DT.ForeignTable = N'tblOrigin'
                              AND DT.ftIndex = O.ID;

显然,它还有很多其他的表,很可能还有其他的列(不仅仅是一个表) name 列)动态获取的值。所以你最终会有这样可怕的结局:

SELECT DT.ID,
       DT.CustomField,
       DT.[Value],
       CASE DT.ForeignTable WHEN N'tblDriver' THEN CASE DT.CustomField WHEN N'Name' THEN D.[Name]
                                                                       WHEN N'Age' THEN D.Age
                                                                       WHEN N'Dob' THEN D.Dob
                                                   END
                            WHEN N'tblOrigin' THEN CASE DT.CustomField WHEN N'Name' THEN D.[Name]
                                                                       WHEN N'Age' THEN D.Age
                                                                       WHEN N'Dob' THEN D.Dob
                                                   END
                            WHEN ... --20 more WHENs, 50? Plus all the inner CASE expressions
                            WHEN N'tblOwner' THEN CASE DT.CustomField WHEN N'FirstTraded' THEN Onr.FirstTraded
                                                                      ...
                                                  END
       END AS ColumnValue
FROM dbo.DenormalisedTable DT
     LEFT JOIN dbo.tblDriver D ON DT.ForeignTable = N'tblDriver'
                              AND DT.ftIndex = D.ID
     LEFT JOIN dbo.tblOrigin O ON DT.ForeignTable = N'tblOrigin'
                              AND DT.ftIndex = O.ID
     LEFT JOIN ...
     ---20 more JOINs, 50?
     LEFT JOIN dbo.tblOwner Onr ON DT.ForeignTable = N'tblOwner'
                               AND DT.ftIndex = Onr.ID;

然而,这有一些主要问题。第一种是数据类型的隐式转换
例如,注意我有列 Name , Age ,和 Dob 在这里。这些选择是有意的,因为有完全不同类型的数据;字符串、数字以及日期和时间。如果 CASE 表达式返回不同的数据类型,然后将使用数据类型优先级来确定返回的数据类型。这很可能以日期和时间数据类型结束,这意味着您的数字和/或 (n)varchar 由于转换错误,列将使语句失败。这意味着上述方法失败了。尽管你可以 CONVERT 每个表达式在 THEN s、 这会严重影响数据,并很容易导致数据易用或无法按您的需要显示。
这个问题也适用于 ON 假设 ID 列的数据类型相同( int ?),因为拥有不同的数据类型将彻底打破这一局面。如果没有,那么你就需要使用 TRY_CONVERT 到适当的数据类型,这就引出了下一点:性能。
表演会很糟糕。我们不要拐弯抹角。由于不同表所需的读取次数太多,这样的查询不会很好地执行。幸运的是,根据数据库的大小,它可以在几分钟甚至几小时内返回数据。添加类似 TRY_CONVERTON 破坏了rdbms使用索引进行搜索的可能性(很小)。
最后,我们还有可伸缩性。编写上述内容本身就是一项任务,这意味着您必须使用动态sql。但是你要解决的问题是性能问题,我也告诉过你,这个解决方案会很慢,我是说慢。动态语句并不能改善这一点,甚至在一开始就需要考虑让语句工作的因素也不小;所以我们不要走那条路,因为它已经被扔出窗外了。
因此,要得到一个性能查询,这里唯一的解决方案就是修复您的设计。规范化数据,不要存储查找表在表中的位置之类的信息。像这样的设计,虽然可能“看起来”直观的用户,不可扩展,并没有很好的表现。这种设计通常来自于将rdbms视为编程语言,并应用相同的逻辑。sql不是一种编程语言,它的工作方式也不一样;它擅长什么和奋斗什么是完全不同的。
热释光;dr:你的设计就是你的查询速度慢的原因。你不能回避这个问题,因为你的疑问(不管是什么)不是根本原因。只有修复设计才能修复性能问题,而这正是您需要集中大量时间和资源来修复的地方。

相关问题