query execution times

See more:

SQL-Server-2005

SQL-Server

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

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)

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)