查询执行时间 [英] query execution times

问题描述

查询1和2相同，除了查询1包含以下额外约束.

Query 1 and 2 are same except query1 contains following extra constraints.

s.Date <= p.EDate
AND s.Date >=  p.BDate
AND s.Date <= st.EDate
AND s.Date >=  st.SDate

为什么query1的执行时间小于查询2的执行时间.

查询1:

why the execution time of query1 is less than execution time of query 2.

query1:

<pre lang="msil">|--Sort(DISTINCT ORDER BY:([st].[S_Address] ASC))
     |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[P_Id], [s].[Date]))
          |--Merge Join(Inner Join, MERGE:([st].[S_Id])=([s].[S_Id]), RESIDUAL:([MyTDW].[dbo].[sales].[S_Id] as [s].[S_Id]=[MyTDW].[dbo].[Stores].[S_Id] as [st].[S_Id] AND [MyTDW].[dbo].[sales].[Date] as [s].[Date]<=[MyTDW].[dbo].[Stores].[EDate] as [st].[EDate] AND [MyTDW].[dbo].[sales].[Date] as [s].[Date]>=[MyTDW].[dbo].[Stores].[SDate] as [st].[SDate]))
          |    |--Clustered Id Scan(OBJECT:([MyTDW].[dbo].[Stores].[PK__Store__014935CB] AS [st]), ORDERED FORWARD)
          |    |--Clustered Id Scan(OBJECT:([MyTDW].[dbo].[sales].[PK_m] AS [s]),  WHERE:([MyTDW].[dbo].[sales].[Date] as [s].[Date]=''2007-12-31 00:00:00.000'' AND [MyTDW].[dbo].[sales].[QuantitySold] as [s].[QuantitySold]>(10000)) ORDERED FORWARD)
          |--Clustered Id Seek(OBJECT:([MyTDW].[dbo].[Prods].[PK__Prods__7F60ED59] AS [p]), SEEK:([p].[PId]=[MyTDW].[dbo].[sales].[P_Id] as [s].[P_Id]),  WHERE:([MyTDW].[dbo].[sales].[Date] as [s].[Date]<=[MyTDW].[dbo].[Prods].[EDate] as [p].[EDate] AND [MyTDW].[dbo].[sales].[Date] as [s].[Date]>=[MyTDW].[dbo].[Prods].[BDate] as [p].[BDate]) ORDERED FORWARD)

查询2:

query 2:

|--Sort(DISTINCT ORDER BY:([st].[S_Address] ASC))
     |--Merge Join(Inner Join, MERGE:([p].[PId])=([s].[P_Id]), RESIDUAL:([MyTDW].[dbo].[sales].[P_Id] as [s].[P_Id]=[MyTDW].[dbo].[Prods].[PId] as [p].[PId]))
          |--Clustered Id Scan(OBJECT:([MyTDW].[dbo].[Prods].[PK__Prods__7F60ED59] AS [p]), ORDERED FORWARD)
          |--Sort(ORDER BY:([s].[P_Id] ASC))
               |--Nested Loops(Inner Join, OUTER REFERENCES:([st].[S_Id], [Expr1006]) WITH UNORDERED PREFETCH)
                    |--Clustered Id Scan(OBJECT:([MyTDW].[dbo].[Stores].[PK__Store__014935CB] AS [st]))
                    |--Clustered Id Seek(OBJECT:([MyTDW].[dbo].[sales].[PK_m] AS [s]), SEEK:([s].[S_Id]=[MyTDW].[dbo].[Stores].[S_Id] as [st].[S_Id]),  WHERE:([MyTDW].[dbo].[sales].[Date] as [s].[Date]=''2007-12-31 00:00:00.000'' AND [MyTDW].[dbo].[sales].[QuantitySold] as [s].[QuantitySold]>(10000)) ORDERED FORWARD)

推荐答案

Query1 的执行时间应该更少，因为它具有额外的约束，因此正在处理和从数据库中检索较少的数据量.

Execution time of Query1 should be less, because it has extra constraints and hence it is processing and retrieving less amount of data from the database.

这篇关于查询执行时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！