对于百万条数据进行查询:自己对2万条数据进行的测试,答案是。。。

对于sqlserver处理百万条数据时,我们要注意了,一定要设index,如果不设那么速度会很慢的。

看我的吧:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[testTime] as
declare @d datetime       --define a variable of datetime,i 'll total program 's time
set @d=getdate()

SELECT taskID
FROM Task WITH (INDEX ([PK_Task_1]))   --加了一个index,名称是PK_TASK_1
--FROM Task

WHERE (taskname= '请假单')
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

SQLSERVER可以在建立表时,同时建立索引(index)

CREATE TABLE [dbo].[Task](
 [parentID] [int] NULL,
 [projectID] [int] NULL,
 [taskID] [int] IDENTITY(1,1) NOT NULL,
 [TaskName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
 [remark] [nvarchar](1024) COLLATE Chinese_PRC_CI_AS NULL,
 [CreateTime] [smalldatetime] NOT NULL,
 [CreateBy] [int] NOT NULL,
 [State] [int] NOT NULL,
 [DonePercent] [int] NOT NULL CONSTRAINT [DF_Task_DonePercent]  DEFAULT ((0)),
 [planstart] [datetime] NULL,
 [planFinish] [datetime] NULL,
 [realstart] [datetime] NULL,
 [realFinish] [datetime] NULL,
 [planWork] [float] NULL,
 [realWork] [float] NULL,
 [YCYAID] [int] NULL CONSTRAINT [DF_Task_YCYAID]  DEFAULT ((1)),
 [YCYAValue] [int] NULL CONSTRAINT [DF_Task_YCYAValueID]  DEFAULT ((1)),
 [taskMgrID] [int] NULL,
 [levels] [int] NULL,
 [quality] [int] NULL CONSTRAINT [DF_Task_quality]  DEFAULT ((3)),
 CONSTRAINT [PK_Task_1] PRIMARY KEY CLUSTERED
(
 [taskID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

posted @ 2010-03-19 23:41  张占岭  阅读(901)  评论(2编辑  收藏  举报