第一次慢,是指:SQL Server 第一次運行後,執行第一次查詢到該表時,慢得很。之後是正常的,每次重啟服務器,都有這麼一次慢得很的查詢。本來在服務器上問題並不嚴重,可現改做成單機版的時候,問題就嚴重了。
CREATE INDEX [IX_PSKQ_ReportDaily_Index2] ON [dbo].[PSKQ_ReportDaily_Index2]([PSID]) ON [PRIMARY] GO 应该再建立上面的索引另外 如果第二次运行 DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE 后1、2次查询的速度应该是一样的
[PSID] [int] NOT NULL ,
[Workday] [smalldatetime] NOT NULL ,
[ID_Organization] [int] NULL ,
[ID_Shift] [int] NULL ,
[ID_KQWay] [int] NULL ,
[SalaryType] [int] NULL ,
[DayType] [int] NULL ,
[DaySort] [varchar] (16) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[ID_Job] [int] NULL ,
[ID_WorkStatus] [int] NULL ,
[Shift] [varchar] (16) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL ,
[CardCount] [int] NULL ,
[OtherCardCount] [int] NULL ,
[cAllHour] [numeric](9, 4) NULL ,
[cDay] [numeric](9, 4) NULL ,
[cWorkHours] [numeric](9, 4) NULL ,
[cManHour] [numeric](9, 4) NULL ,
[cOverTime] [numeric](9, 4) NULL ,
[LeaveEarly] [int] NULL ,
[LeaveEarlyTime] [numeric](9, 4) NULL ,
[BeLate] [int] NULL ,
[BeLateTime] [numeric](9, 4) NULL ,
[Absent] [int] NULL ,
[AbsentHour] [numeric](9, 4) NULL ,
[QianCard] [int] NULL ,
[QianCardUnLegal] [int] NULL ,
[JBunCount] [int] NULL ,
[DinnerOver] [int] NULL ,
[CardNULLCount] [int] NULL ,
[DayStart] [smalldatetime] NULL ,
[DayEnd] [smalldatetime] NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[PSKQ_ReportDaily_Index] WITH NOCHECK ADD
CONSTRAINT [PK_PSKQ_ReportDaily_Index] PRIMARY KEY CLUSTERED
(
[PSID],
[Workday]
) ON [PRIMARY]
GOCREATE INDEX [IX_PSKQ_ReportDaily_Index] ON [dbo].[PSKQ_ReportDaily_Index]([Workday]) ON [PRIMARY]
GO CREATE INDEX [IX_PSKQ_ReportDaily_Index_1] ON [dbo].[PSKQ_ReportDaily_Index]([ID_Organization]) ON [PRIMARY]
GO查詢語句1:
SELECT * FROM PSKQ_ReportDaily_Index
WHERE workday > '2007/08/01' AND workday < '2007/08/30' AND PSID = 2238
查詢語句2:
SELECT PSID , SUM(cWorkHours),SUM(cAllHour) FROM PSKQ_ReportDaily_Index
GROUP BY PSID
WHERE workday > '2007/08/01' AND workday < '2007/08/30'
GO
应该再建立上面的索引另外 如果第二次运行
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
后1、2次查询的速度应该是一样的