查询与视图之间的差异 - 性能明显不同。为什么？ [英] Difference between a query and a view - performance is STARKLY different. Why?

问题描述

我有以下声明，其中包含子视图。  

如果我创建一个查询，并粘贴它并执行，则只需要一个生成结果691行的几秒钟。

如果我将以下内容创建到视图中，并执行"选择前1000行"，则需要两分钟才能生成！

另外，如果我在Powerapps中使用此视图，它就像使用后一种方法一样，因为应用程序会显示几分钟的数据加载进度。  

这是怎么回事？ 我在powerapps中使用其他表格和视图，这些时间并不需要花费4倍的行数。  

 SELECT TOP（100）PERCENT dbo.Documents.DocumentID，dbo.VIEW_SUB_PDM_DWGREVISION_LATEST.DwgRevision，dbo.VIEW_SUB_PDM_DESCRIPTION_LATEST.Description，
 dbo.VIEW_SUB_PDM_PROJECTNAME_LATEST.ProjectName，dbo.VIEW_SUB_PDM_PARTNUMBER_LATEST.PartNumber，dbo.VIEW_SUB_PDM_PARTNUMBER_LATEST。 CurrentStatusID，
 CASE WHEN dbo.Documents.CurrentStatusID<> 10 THEN ELSE 1 0 END AS挂起，dbo.Documents.LatestRevisionNo，dbo.VIEW_SUB_PDM_PARTNUMBER_LATEST.Filename，
 dbo.VIEW_SUB_PDM_RvTbl_DESCRIPTION_LATEST.RvTbl_Description，dbo.VIEW_SUB_PDM_RvTbl_DwgDate_LATEST.RvTbl_DwgDate，dbo.VIEW_SUB_PDM_RvTbl_Approved_LATEST.RvTbl_Approved，
 dbo.VIEW_SUB_PDM_RvTbl_Revision_LATEST .RvTbl_Revision，dbo.DocumentsInProjects.ProjectID 
 FROM dbo.VIEW_SUB_PDM_RvTbl_DESCRIPTION_LATEST RIGHT OUTER JOIN 
 dbo.Documents INNER JOIN 
 dbo.DocumentsInProjects ON dbo.Documents.DocumentID = dbo.DocumentsInProjects.DocumentID INNER JOIN 
 dbo.VIEW_SUB_PDM_PARTNUMBER_LATEST ON dbo.Documents.DocumentID = dbo.VIEW_SUB_PDM_PARTNUMBER_LATEST.DocumentID ON 
 dbo.VIEW_SUB_PDM_RvTbl_DESCRIPTION_LATEST.DocumentID = dbo.Documents.DocumentID LEFT OUTER JOIN 
 dbo。 VIEW_SUB_PDM_DESCRIPTION_LATEST ON dbo.Documents.DocumentID = dbo.VIEW_SUB_PDM_DESCRIPTION_LATEST.DocumentID LEFT OUTER JOIN 
 dbo.VIEW_SUB_PDM_DWGREVISION_LATEST on dbo.Documents.DocumentID = dbo.VIEW_SUB_PDM_DWGREVISION_LATEST.DocumentID LEFT OUTER JOB 
 dbo.VIEW_SUB_PDM_PROJECTNAME_LATEST on dbo.Documents。 DocumentID = dbo.VIEW_SUB_PDM_PROJECTNAME_LATEST.DocumentID LEFT OUTER JOIN 
 dbo.VIEW_SUB_PDM_RvTbl_Approved_LATEST ON dbo.Documents.DocumentID = dbo.VIEW_SUB_PDM_RvTbl_Approved_LATEST.DocumentID LEFT OUTER JOIN 
 dbo.VIEW_SUB_PDM_RvTbl_DwgDate_LATEST ON dbo.Documents.DocumentID = dbo.VIEW_SUB_PDM_RvTbl_DwgDate_LATEST。 DocumentID LEFT OUTER JOIN 
 dbo.VIEW_SUB_PDM_RvTbl_Revision_LATEST ON dbo.Documents.DocumentID = dbo.VIEW_SUB_PDM_RvTbl_Revision_LATEST.DocumentID 
 ORDER BY dbo.Documents.DocumentID DESC 

。

我正在添加下面SQL子表的结构。 它显示了数据的组织方式，希望能够更好地理解我上述陈述的结构。

如上所示，对于每个DocumentID，有几个VariableID表示我需要分成的属性列的"最新"列。值 最大值的RevisionNo代表最新值。 因此，对于零件号（VariableID = 54）
，我需要最新的ValueText。 因此，对于此示例，部件号所需的值为"204069"。 这是每个VariableID值需要完成的逻辑。 这是在那些Sub视图中一次一个地完成，然后与上面的语句一起组合
。

执行刚刚描述的逻辑的语句在此子视图（dbo.VIEW_SUB_PDM_PARTNUMBER_LATEST）：

 SELECT TOP（100）PERCENT DocumentID，VariableID，ExtensionID，PartNumber，RevisionNo，CurrentStatusID， Date，TransitionNr，RevNr，LatestRevisionNo，Filename，Deleted 
 FROM（SELECT dbo.VariableValue.DocumentID，dbo.VariableValue.VariableID，dbo.Documents.ExtensionID，dbo.VariableValue.ValueText AS PartNumber，dbo.VariableValue.RevisionNo， dbo.Documents.CurrentStatusID，
 dbo.TransitionHistory.Date，dbo.TransitionHistory.TransitionNr，dbo.TransitionHistory.RevNr，dbo.Documents.LatestRevisionNo，dbo.Documents.Filename，dbo.Documents.Deleted，ROW_NUMBER（）
 OVER（PARTI TION BY dbo.VariableValue.DocumentID 
 ORDER BY dbo.VariableValue.RevisionNo DESC，dbo.TransitionHistory.TransitionNr DESC）AS Seq 
 FROM dbo.Documents INNER JOIN 
 dbo.VariableValue ON dbo.Documents .DocumentID = dbo.VariableValue.DocumentID INNER JOIN 
 dbo.TransitionHistory ON dbo.Documents.DocumentID = dbo.TransitionHistory.DocumentID 
 WHERE（dbo.Documents.Deleted = 0）AND（dbo.Documents.ExtensionID = 3）AND（dbo.Documents.CurrentStatusID<> 0）AND（dbo.VariableValue.VariableID = 54））t 
 WHERE Seq = 1 
 ORDER BY DocumentID DESC，RevisionNo DESC，TransitionNr DESC 

结果（对于相同的示例）在最终视图中看起来像这样：

以上显示DwgRevision（VariableID = 49），Descirption（VariableID = 47），ProjectName（VariableID = 45），PartNumber（VariableID = 54）

由方式，DwgRevision与其他视图中的RevisionID不同。  

PowerApps需要这种简单的柱状形式的数据。 我没和上面使用的SQL语言结婚。 事实上，比我更聪明的人与这种情况有很大关系。 如果有更好的方法将这个
数据重新组织成显示这些VariableID的最新值的列，我肯定愿意接受它！

解决方案

如果你说SELECT TOP 1000，优化器可能会采用不同的计划，因为重点是找到第一个1000行，而不是全部。 （是的，只有691，但优化器不知道。）

当您运行SELECT TOP 1000 * FROM yourview时尝试添加此提示：

选项（使用提示（'DISABLE_OPTIMIZER_ROWGOAL'））

我认为这个提示是在SQL 2016中添加的，因此它不适用于早期版本。

I have the following statement, which contains sub-views.  

If I create a query, and paste this, and execute, it takes only a couple of seconds to generate the resultant 691 rows.

If I create the contents below into a view, and do a "Select Top 1000 Rows", it takes over TWO MINUTES to generate!

Also, If I use this view in Powerapps, it acts like its using the latter method, because the app shows the data load progress for several minutes.  

What is going on with this?  I use other tables and views in powerapps that dont take that kind of time, and even with 4X as many rows.  

SELECT        TOP (100) PERCENT dbo.Documents.DocumentID, dbo.VIEW_SUB_PDM_DWGREVISION_LATEST.DwgRevision, dbo.VIEW_SUB_PDM_DESCRIPTION_LATEST.Description, 
                         dbo.VIEW_SUB_PDM_PROJECTNAME_LATEST.ProjectName, dbo.VIEW_SUB_PDM_PARTNUMBER_LATEST.PartNumber, dbo.VIEW_SUB_PDM_PARTNUMBER_LATEST.CurrentStatusID, 
                         CASE WHEN dbo.Documents.CurrentStatusID <> 10 THEN 1 ELSE 0 END AS Pending, dbo.Documents.LatestRevisionNo, dbo.VIEW_SUB_PDM_PARTNUMBER_LATEST.Filename, 
                         dbo.VIEW_SUB_PDM_RvTbl_DESCRIPTION_LATEST.RvTbl_Description, dbo.VIEW_SUB_PDM_RvTbl_DwgDate_LATEST.RvTbl_DwgDate, dbo.VIEW_SUB_PDM_RvTbl_Approved_LATEST.RvTbl_Approved, 
                         dbo.VIEW_SUB_PDM_RvTbl_Revision_LATEST.RvTbl_Revision, dbo.DocumentsInProjects.ProjectID
FROM            dbo.VIEW_SUB_PDM_RvTbl_DESCRIPTION_LATEST RIGHT OUTER JOIN
                         dbo.Documents INNER JOIN
                         dbo.DocumentsInProjects ON dbo.Documents.DocumentID = dbo.DocumentsInProjects.DocumentID INNER JOIN
                         dbo.VIEW_SUB_PDM_PARTNUMBER_LATEST ON dbo.Documents.DocumentID = dbo.VIEW_SUB_PDM_PARTNUMBER_LATEST.DocumentID ON 
                         dbo.VIEW_SUB_PDM_RvTbl_DESCRIPTION_LATEST.DocumentID = dbo.Documents.DocumentID LEFT OUTER JOIN
                         dbo.VIEW_SUB_PDM_DESCRIPTION_LATEST ON dbo.Documents.DocumentID = dbo.VIEW_SUB_PDM_DESCRIPTION_LATEST.DocumentID LEFT OUTER JOIN
                         dbo.VIEW_SUB_PDM_DWGREVISION_LATEST ON dbo.Documents.DocumentID = dbo.VIEW_SUB_PDM_DWGREVISION_LATEST.DocumentID LEFT OUTER JOIN
                         dbo.VIEW_SUB_PDM_PROJECTNAME_LATEST ON dbo.Documents.DocumentID = dbo.VIEW_SUB_PDM_PROJECTNAME_LATEST.DocumentID LEFT OUTER JOIN
                         dbo.VIEW_SUB_PDM_RvTbl_Approved_LATEST ON dbo.Documents.DocumentID = dbo.VIEW_SUB_PDM_RvTbl_Approved_LATEST.DocumentID LEFT OUTER JOIN
                         dbo.VIEW_SUB_PDM_RvTbl_DwgDate_LATEST ON dbo.Documents.DocumentID = dbo.VIEW_SUB_PDM_RvTbl_DwgDate_LATEST.DocumentID LEFT OUTER JOIN
                         dbo.VIEW_SUB_PDM_RvTbl_Revision_LATEST ON dbo.Documents.DocumentID = dbo.VIEW_SUB_PDM_RvTbl_Revision_LATEST.DocumentID
ORDER BY dbo.Documents.DocumentID DESC

.

I am adding the structure of the SQL Sub-tables below.  It shows how the data is organized, to hopefully bring some more understanding on the structure of my statement above.

As shown above, for every DocumentID, there are several VariableIDs that represent properties that I need separated into columns of "Latest" value.  RevisionNo of the largest value represents the latest value.  Thus, for Part Number (VariableID=54) I need the latest ValueText.  So, for this example, the value I need for Part Number is "204069".  That is the logic that needs done for every VariableID value.  That is being done one at a time in those Sub views, and then assembled all-together with the statement above.

The statement that does the logic just described is in this sub-view (dbo.VIEW_SUB_PDM_PARTNUMBER_LATEST):

SELECT        TOP (100) PERCENT DocumentID, VariableID, ExtensionID, PartNumber, RevisionNo, CurrentStatusID, Date, TransitionNr, RevNr, LatestRevisionNo, Filename, Deleted
FROM            (SELECT        dbo.VariableValue.DocumentID, dbo.VariableValue.VariableID, dbo.Documents.ExtensionID, dbo.VariableValue.ValueText AS PartNumber, dbo.VariableValue.RevisionNo, dbo.Documents.CurrentStatusID, 
                                                    dbo.TransitionHistory.Date, dbo.TransitionHistory.TransitionNr, dbo.TransitionHistory.RevNr, dbo.Documents.LatestRevisionNo, dbo.Documents.Filename, dbo.Documents.Deleted, ROW_NUMBER() 
                                                    OVER (PARTITION BY dbo.VariableValue.DocumentID
                          ORDER BY dbo.VariableValue.RevisionNo DESC, dbo.TransitionHistory.TransitionNr DESC) AS Seq
FROM            dbo.Documents INNER JOIN
                         dbo.VariableValue ON dbo.Documents.DocumentID = dbo.VariableValue.DocumentID INNER JOIN
                         dbo.TransitionHistory ON dbo.Documents.DocumentID = dbo.TransitionHistory.DocumentID
WHERE        (dbo.Documents.Deleted = 0) AND (dbo.Documents.ExtensionID = 3) AND (dbo.Documents.CurrentStatusID <> 0) AND (dbo.VariableValue.VariableID = 54)) t
WHERE        Seq = 1
ORDER BY DocumentID DESC, RevisionNo DESC, TransitionNr DESC

The result (for the same example) looks like this in the final view:

The above shows DwgRevision (VariableID=49), Descirption (VariableID=47), ProjectName (VariableID=45), PartNumber (VariableID=54)

By the way, DwgRevision is not the same as RevisionID in the other view.  

PowerApps needs this data in this simple columnar form.  I'm not married to the SQL language I used above.  In fact, people MUCH smarter than me had a great deal to do with how this came about.  If there is a better way to reorganize this data into columns showing the latest values for these VariableIDs, i'm certainly open to it!

解决方案

If you say SELECT TOP 1000, the optimizer may go for a different plan, since the focus is now on finding the first 1000 rows, not all of them. (Yeah, there are only 691, but the optimizer does not know that.)

When you run SELECT TOP 1000 * FROM yourview try adding this hint:

OPTION (USE HINT('DISABLE_OPTIMIZER_ROWGOAL'))

That hint was added in SQL 2016, I think, so it will not work on earlier versions.

这篇关于查询与视图之间的差异 - 性能明显不同。为什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！