之前我也有发过一个相同的贴子, 以为解决问题了,但其实并没有解决, 请各位再次赐教.我们有三张表
Doc : 用于存放文件记录, 包括文件创建时间,最后修改时间,文件名称,文件类型等
D759246 : 用于存放文件类型为759246的文件的各索引的值.
Alias : 用于存放文件所在的目录ID, 以及存放文件的存取权限其中Doc表已经存放文件约75万条记录, alias存放75万条记录, D759246存放41万条记录Doc已建索引:I_Index_ClassID:
Nonclustered DocClassID
IX_Doc_01
Nonclustered DocLastModifyDate, DocCreateDate
IX_Doc_02
Nonclustered DocRevID, DocClassID, DocCheckOutBy
IX_Doc_03
Nonclusterd DocACL
PK_Doc
Clustered Unique DocIDAlias已建索引:
IX_Alias Nonclusted DirID
IX_Alias_01 Nonclustered DirID,DocID
IX_Alias_CLUS Clusted DocID
PK_Alias Nonclustered Unique AliasIDD759246已建索引:
IX_D759246 Nonclustered A759247,A759248,A759249
IX_D759246_1 Nonclustered A759247,A759248
PK_D759246_589C25F3 Clustered Unique DocrevID
--用于寻找符合条件的前十条记录
SELECT
TOP 10
Doc.DocID,
Doc.DocrevID,
DocClassID,
DocCreateDate,
DocCreateBy,
DocLastModifyDate,
DocLastModifyBy,
DocCheckOutDate,
DocCheckOutBy,
DocCheckOutRevID,
DocACL,
DocPublishGroup,
DocVer,
DocTitle,
DocLockStatus,
DocLockBy,
DocLockDate,
DocLockKey,
DocMediaInfo,
DocArchived,
Alias.AliasID,
Alias.DocID,
Alias.DirID,
Alias.IsMaster,
Alias.PrimarySortNo,
Alias.SecondarySortNo ,
A759247,
A759248,
A759249,
A759250,
A1573437
FROM
Doc inner join D759246 on Doc.DocRevID = D759246.DocRevID
inner join Alias on Alias.DocID = Doc.DocID
WHERE
(DocClassID =759246) AND
Alias.DirID = (1581543)
AND ( ( ( (Alias.DocAccessGroup LIKE '% G1 %') OR (Alias.DocAccessGroup LIKE '% U1 %') ) AND ( (Alias.DocDeniedGroup NOT LIKE '% G1 %') AND (Alias.DocDeniedGroup NOT LIKE '% U1 %') ) OR ( (Alias.DocAccessGroup LIKE '% U1 %') AND (Alias.DocDeniedGroup NOT LIKE '% U1 %') ) ) )ORDER BY A759247, A759248 DESC
--用于获取符合条件的总数据量
SELECT
COUNT(*)
FROM
Alias
INNER JOIN
Doc
ON Alias.DocID=Doc.DocID
WHERE
(Alias.DirID = (1581543)) AND (Doc.DocClassID=759246)当一个人执行的时候, 用了11秒, 当两个人同时执行的时候,用了19秒, 当三个人同时执行的时候, 用了27秒, 当四个人同时执行时, 已经要50秒了, 如何能提高他的速度呢
Doc : 用于存放文件记录, 包括文件创建时间,最后修改时间,文件名称,文件类型等
D759246 : 用于存放文件类型为759246的文件的各索引的值.
Alias : 用于存放文件所在的目录ID, 以及存放文件的存取权限其中Doc表已经存放文件约75万条记录, alias存放75万条记录, D759246存放41万条记录Doc已建索引:I_Index_ClassID:
Nonclustered DocClassID
IX_Doc_01
Nonclustered DocLastModifyDate, DocCreateDate
IX_Doc_02
Nonclustered DocRevID, DocClassID, DocCheckOutBy
IX_Doc_03
Nonclusterd DocACL
PK_Doc
Clustered Unique DocIDAlias已建索引:
IX_Alias Nonclusted DirID
IX_Alias_01 Nonclustered DirID,DocID
IX_Alias_CLUS Clusted DocID
PK_Alias Nonclustered Unique AliasIDD759246已建索引:
IX_D759246 Nonclustered A759247,A759248,A759249
IX_D759246_1 Nonclustered A759247,A759248
PK_D759246_589C25F3 Clustered Unique DocrevID
--用于寻找符合条件的前十条记录
SELECT
TOP 10
Doc.DocID,
Doc.DocrevID,
DocClassID,
DocCreateDate,
DocCreateBy,
DocLastModifyDate,
DocLastModifyBy,
DocCheckOutDate,
DocCheckOutBy,
DocCheckOutRevID,
DocACL,
DocPublishGroup,
DocVer,
DocTitle,
DocLockStatus,
DocLockBy,
DocLockDate,
DocLockKey,
DocMediaInfo,
DocArchived,
Alias.AliasID,
Alias.DocID,
Alias.DirID,
Alias.IsMaster,
Alias.PrimarySortNo,
Alias.SecondarySortNo ,
A759247,
A759248,
A759249,
A759250,
A1573437
FROM
Doc inner join D759246 on Doc.DocRevID = D759246.DocRevID
inner join Alias on Alias.DocID = Doc.DocID
WHERE
(DocClassID =759246) AND
Alias.DirID = (1581543)
AND ( ( ( (Alias.DocAccessGroup LIKE '% G1 %') OR (Alias.DocAccessGroup LIKE '% U1 %') ) AND ( (Alias.DocDeniedGroup NOT LIKE '% G1 %') AND (Alias.DocDeniedGroup NOT LIKE '% U1 %') ) OR ( (Alias.DocAccessGroup LIKE '% U1 %') AND (Alias.DocDeniedGroup NOT LIKE '% U1 %') ) ) )ORDER BY A759247, A759248 DESC
--用于获取符合条件的总数据量
SELECT
COUNT(*)
FROM
Alias
INNER JOIN
Doc
ON Alias.DocID=Doc.DocID
WHERE
(Alias.DirID = (1581543)) AND (Doc.DocClassID=759246)当一个人执行的时候, 用了11秒, 当两个人同时执行的时候,用了19秒, 当三个人同时执行的时候, 用了27秒, 当四个人同时执行时, 已经要50秒了, 如何能提高他的速度呢
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货