22,210
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE A(
[id] [int] IDENTITY(1,1) NOT NULL,
[username] [nvarchar](50) NOT NULL,
[typeid] [nchar](1) NOT NULL,
[flag] [int] NOT NULL DEFAULT (0),
[addtime] [datetime] NOT NULL--还很多字段,这儿只列出主要的
CONSTRAINT [PK_iesoo_qyml] PRIMARY KEY CLUSTERED
(
[id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
--视图A
CREATE VIEW View_A
AS
begin
SELECT username,typeid,flag,addtime
FROM A
WHERE (typeid= 'A') AND (flag = 1)
end
--测试查询
SELECT COUNT(1) FROM A --有30几万条数据
SELECT COUNT(1) FROM View_A --有20几万条数据
------问题:
SELECT username,count(1) N FROM View_A
GROUP BY username
--这个分组查询需要5秒。能不能更快些啊?
CREATE VIEW View_A
WITH SCHEMABINDING
AS
SELECT username,typeid,flag,addtime
FROM A
WHERE (typeid= 'A') AND (flag = 1)
CREATE CLUSTERED INDEX idx_username
ON View_A (username);
加个索引试下
create index IX_type on A (typeid,flag )