添加时间查询优化？

[xzr2004]

4张表结构如下：
CREATE TABLE [dbo].[ERP_SaleOrder](
[ID] [int] IDENTITY(1,1) NOT NULL,
[OrderCode] [nvarchar](50) NULL,
[StorageID] [int] NULL,
[ContactUser] [nvarchar](50) NULL,
[RegionID] [nvarchar](100) NULL,
[Address] [nvarchar](100) NULL,
[PostCode] [nvarchar](50) NULL,
[Tel] [nvarchar](50) NULL,
[Mobile] [nvarchar](50) NULL,
[Email] [nvarchar](50) NULL,
[LogisticsID] [int] NULL,
[ShippingID] [int] NULL,
[OrderStatus] [int] NULL,
[Note] [ntext] NULL,
[Deleted] [int] NULL,
[UserID] [int] NULL,
[UserName] [nvarchar](50) NULL,
[PostTime] [datetime] NULL,
[OrderClass] [int] NULL,
[IsRetail] [int] NULL,
[StorageIDOut] [int] NULL,
[Business] [nvarchar](50) NULL,
[ChargeBusiness] [nvarchar](50) NULL,
[Finance] [nvarchar](50) NULL,
[BusinessTime] [datetime] NULL,
[ChargeBusinessTime] [datetime] NULL,
[FinanceTime] [datetime] NULL,
[OutputTime] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[ERP_SaleOrderDetail](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SaleOrderID] [int] NULL,
[ProductID] [int] NULL,
[WholeSalePrice] [decimal](18, 2) NULL,
[BuyCount] [int] NULL,
[LackCount] [int] NULL,
[ShippingStatus] [int] NULL,
[Deleted] [int] NULL,
[UserID] [int] NULL,
[UserName] [nvarchar](50) NULL,
[PostTime] [datetime] NULL,
[IsGift] [int] NULL,
[Paystatus] [int] NULL,
[ClearingType] [int] NULL,
[Days] [int] NULL,
ID为主键，ERP_SaleOrderDetail通过SaleOrderID与上张表关联，
CREATE TABLE [dbo].[ERP_StorageOutputOrder](
[ID] [int] IDENTITY(1,1) NOT NULL,
[OrderCode] [nvarchar](50) NULL,
[Note] [ntext] NULL,
[LogisticsID] [int] NULL,
[ShippingID] [int] NULL,
[ShippingStatus] [int] NULL,
[StroageIDOut] [int] NULL,
[ShippingTime] [datetime] NULL,
[ClientID] [int] NULL,
[RegionID] [nvarchar](100) NULL,
[Address] [nvarchar](100) NULL,
[ContactUser] [nvarchar](50) NULL,
[Tell] [nvarchar](50) NULL,
[ProductCount] [int] NULL,
[SaleOrderID] [nvarchar](500) NULL,
[Deleted] [int] NULL,
[UserID] [int] NULL,
[Username] [nvarchar](50) NULL,
[PostTime] [datetime] NULL,
[LogisticsCode] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[ERP_StorageOutputOrderDetail](
[ID] [int] IDENTITY(1,1) NOT NULL,
[OutputOrderID] [int] NULL,
[ProductID] [int] NULL,
[SaleOrderID] [int] NULL,
[OrderProductCount] [int] NULL,
[ActualOutputCount] [int] NULL,
[UserID] [int] NULL,
[Username] [nvarchar](50) NULL,
[Deleted] [int] NULL,
[PostTime] [datetime] NULL,
[IsAccount] [int] NULL,
[WholeSalePrice] [decimal](18, 2) NULL,
ID为主键，ERP_StorageOutputOrderDetail通过OutputOrderID与上张表关联，通过SaleOrderID与ERP_SaleOrderDetail关联
我对ID都做了聚集索引，将4张表连接查询，不加时间速度还可以，加了时间很慢，2分多

SELECT ERP_SaleOrderDetail.[ProductID],ERP_SaleOrder.[UserID],ERP_SaleOrder.[UserName],
SUM(ERP_SaleOrderDetail.[LackCount]) AS SumLockCount,SUM(ERP_StorageOutputOrderDetail.[ActualOutputCount]) AS SumActualCount
FROM ERP_SaleOrder INNER JOIN ERP_SaleOrderDetail ON ERP_SaleOrder.[ID] = ERP_SaleOrderDetail.[SaleOrderID] INNER JOIN
ERP_StorageOutputOrderDetail ON ERP_SaleOrderDetail.[SaleOrderID] = ERP_StorageOutputOrderDetail.[SaleOrderID] AND
ERP_SaleOrderDetail.[ProductID] = ERP_StorageOutputOrderDetail.[ProductID] INNER JOIN
ERP_StorageOutputOrder ON ERP_StorageOutputOrderDetail.[OutputOrderID]= ERP_StorageOutputOrder.[ID] WHERE
ERP_SaleOrder.[PostTime] > '2010-5-1 0:00:00' AND ERP_StorageOutputOrder.[PostTime] > '2010-5-1 0:00:00'
AND ERP_SaleOrder.[PostTime] <= '2010-5-24 0:00:00' AND ERP_StorageOutputOrder.[PostTime] <= '2010-5-24 0:00:00' AND
ERP_SaleOrder.[Deleted] = 0 AND ERP_SaleOrderDetail.[Deleted] = 0 AND ERP_StorageOutputOrder.[Deleted] = 0
AND ERP_StorageOutputOrderDetail.[Deleted] = 0
GROUP BY ERP_SaleOrderDetail.[ProductID],ERP_SaleOrder.[UserID],ERP_SaleOrder.[UserName]
如何优化？

[xzr2004]

就这些吗

[like0112]

同意3喽的说法，可以在[posttime]字段加上索引试试看

[xzr2004]

我试了没什么用啊

[htl258_Tony]

有点晕，先帮顶。

[永生天地]

那你把时间加上索引

[htl258_Tony]

看完了代码，照你所说，加上时间条件查询就变慢，在时间字段加上索引可以提高查询效率。

CREATE INDEX IDX_ERP_SaleOrder_PostTime ON ERP_SaleOrder([PostTime])
CREATE INDEX IDX_ERP_StorageOutputOrder_PostTime ON ERP_StorageOutputOrder([PostTime])

先运行这个再查询试试