项目开发中遇到了sqlserver存储过程的性能问题，需要对一个存储过程进行性能优化，该存储过程未优化前，执行一次需要耗时一分钟以上，对于客户来说完全无法接受，必须要对其进行优化。
存储过程如下
CREATE PROC [dbo].[spGetDrawingList](@companyId INT,
@userId INT,
@projectId INT,
@pageIndex INT,
@pageSize INT,
@keyword NVARCHAR(MAX),
@sortColumnName VARCHAR(MAX),
@sortMode VARCHAR(MAX),
@statusStr VARCHAR(MAX),
@isReviewed INT,
@fullMode INT,
@dcsAdmin BIT,
@catalogStr NVARCHAR(MAX),
@originatorStr NVARCHAR(MAX),
@disciplinesStr NVARCHAR(MAX),
@creatorCompanyIdsStr VARCHAR(MAX),
@drawingTypeStr NVARCHAR(MAX),
@zoneStr NVARCHAR(MAX),
@purposesStr NVARCHAR(MAX),
@masStatusStr NVARCHAR(MAX),
@docTypeNameStr NVARCHAR(MAX))
AS
BEGIN
DECLARE
@resultSql NVARCHAR(max) = N'',
@countSql NVARCHAR(max) = N'',
@filterSql NVARCHAR(max) = N'',
@Parm1 NVARCHAR(MAX) = N'',
@Parm2 NVARCHAR(MAX) = N'';
Declare @ChineseStringColumnSort VARCHAR(100);
SET @ChineseStringColumnSort = N''
create table #resultTable
(
ProjectID INT,
Total INT
)
SET @resultSql = 'select temp.ID,
temp.ProjectID,
temp.DocTypeID,
temp.DrawingTypeId,
temp.contractNo,
temp.subject,
temp.docSetNo,
temp.docSetNumbering,
temp.dsDate,
temp.secured,
temp.refNo,
temp.docSetId,
temp.drawing,
temp.catalog,
temp.CatalogId,
temp.originator,
temp.originatorId,
temp.drawingNo,
temp.drawingTitle,
temp.discipline,
temp.drawingType,
temp.zone,
temp.purpose,
temp.rev,
temp.earlyRev,
temp.latestRev,
temp.count,
temp.fileInfoId,
temp.totalSize,
temp.status,
temp.CompanyAssigned,
temp.UserAssigned,
temp.createdBy,
temp.statusName,
temp.docSetStatus,
temp.isTransferred,
temp.ownerCompanyId,
temp.updated,
temp.Building,
temp.IsSecured,
(select top 1 Total from #resultTable) as Total '
set @countSql = 'insert into #resultTable(ProjectID, Total) SELECT @projectId,count(distinct temp.ID) '
set @filterSql = ' from (select dcsItem.ID,
dcsItem.ProjectID,
dcsDocSet.DocTypeID,
dcsItem.DrawingTypeId,
prjProjectContract.Code as contractNo,
dcsDocSet.subject as subject,
dcsDocSet.docSetNo,
dcsDocSet.Numbering as DocSetNumbering,
dcsDocSet.DocSetDate AS DSDate,
dcsDocSet.isSecured as Secured,
dcsItem.DocRefNo as refNo,
dcsItem.DocSetID as docSetId,
dcsItem.DocName as drawing,
masDrawingCategory.Name as catalog,
dcsItem.CatalogId,
perCompany.Name as Originator,
dcsItem.OriginatorId,
dcsItem.DocNo as drawingNo,
dcsItem.DocTitle as drawingTitle,
viDcsItemDisciplineAndPurpose.Discipline,
masDrawingType.Name as drawingType,
dcsItem.Zone,
viDcsItemDisciplineAndPurpose.Purpose,
dcsItem.Rev as rev,
(select top 1 related.Rev
from dcsItem as related
where related.DocNo = dcsItem.DocNo
and related.ProjectID = dcsItem.ProjectID
and related.IsDrawing = 1 and related.Status = 1
order by related.ID asc) as earlyRev,
(select top 1 related.Rev
from dcsItem as related
where related.DocNo = dcsItem.DocNo
and related.ProjectID = dcsItem.ProjectID
and related.IsDrawing = 1 and related.Status = 1
order by related.ID desc) as latestRev,
(select count(*)
from dcsItem as related
where related.DocNo = dcsItem.DocNo
and related.ProjectID = dcsItem.ProjectID
and related.IsDrawing = 1 and related.Status = 1) as count,
dcsItem.FileInfoId,
sysFileInfo.TotalSize,
dcsItem.DocStatusId as status,
(case isnull(docSetCompany.companyId, ''0'') when ''0'' then ''0'' ELSE ''1'' END) as CompanyAssigned,
(case isnull(docSetUser.userId, ''0'') when ''0'' then ''0'' ELSE ''1'' END) as UserAssigned,
dcsItem.CreatedBy,
masDocumentStatus.Name as statusName,
dcsDocSet.Status as docSetStatus,
dcsDocSet.OwnerCompanyID as OwnerCompanyId,
dcsItem.isTransferred,
dcsItem.Modified as updated,
dcsItem.Building,
dcsDocSet.IsSecured
from dcsItem
left join dcsDocSet
on dcsDocSet.ID = dcsItem.DocSetID
left join sysFileInfo
on sysFileInfo.Id = dcsItem.FileInfoId
left join masDrawingType
on masDrawingType.ID = dcsItem.DrawingTypeId
left join masDrawingCategory
on masDrawingCategory.ID = dcsItem.CatalogId
left join masDocumentStatus
on masDocumentStatus.ID = dcsItem.DocStatusId
left join (select distinct dcsDocSetRelatedPerson.DocSetId, dcsDocSetRelatedPerson.CompanyId from dcsDocSetRelatedPerson left join dcsDocSet on dcsDocSet.ID = dcsDocSetRelatedPerson.DocSetID where dcsDocSet.ProjectID=@projectId and dcsDocSetRelatedPerson.CompanyId=@companyId) docSetCompany
on docSetCompany.DocSetId = dcsDocSet.ID and docSetCompany.companyID = @companyId
left join (select distinct dcsDocSetRelatedPerson.DocSetId, dcsDocSetRelatedPerson.UserId from dcsDocSetRelatedPerson left join dcsDocSet on dcsDocSet.ID = dcsDocSetRelatedPerson.DocSetID where dcsDocSet.ProjectID=@projectId and dcsDocSetRelatedPerson.UserId=@userId) docSetUser
on docSetUser.DocSetId = dcsDocSet.ID and docSetUser.UserId = @userId
left join viDcsItemDisciplineAndPurpose on dcsItem.ID = viDcsItemDisciplineAndPurpose.ItemID
left join perCompany
on perCompany.ID = dcsItem.OriginatorId
left join prjProjectContract
on prjProjectContract.ID = dcsDocSet.ProjectContractID '
IF (@disciplinesStr IS NOT NULL and @disciplinesStr <> '')
BEGIN
set @filterSql = CONCAT(@filterSql,' inner join (select distinct dcsItemDiscipline.ItemID from dcsItemDiscipline where DisciplineID in (SELECT col FROM SplitIn(@disciplinesStr,'',''))) dcsDiscipline on dcsDiscipline.ItemID = dcsItem.ID ')
END
IF (@purposesStr IS NOT NULL and @purposesStr <> '')
BEGIN
set @filterSql = CONCAT(@filterSql,' inner join (select distinct dcsItemPurpose.ItemID from dcsItemPurpose where PurposeID in (SELECT col FROM SplitIn(@purposesStr,'',''))) dcsPurpose on dcsPurpose.ItemID = dcsItem.ID ')
END
set @filterSql = CONCAT(@filterSql,'where dcsItem.ProjectID = @projectId
and dcsItem.Status = 1
and dcsItem.IsDrawing = 1
and dcsItem.ID = (select top 1 lastest.ID
from dcsItem as lastest
left join dcsDocSet
on dcsDocSet.ID = lastest.DocSetID
left join (select distinct dcsDocSetRelatedPerson.DocSetId, dcsDocSetRelatedPerson.CompanyId from dcsDocSetRelatedPerson left join dcsDocSet on dcsDocSet.ID = dcsDocSetRelatedPerson.DocSetID where dcsDocSet.ProjectID=@projectId and dcsDocSetRelatedPerson.CompanyId=@companyId) docSetCompany1
on docSetCompany1.DocSetId = lastest.DocSetID and docSetCompany1.CompanyId = @companyId
left join viDcsItemDisciplineAndPurpose on lastest.ID = viDcsItemDisciplineAndPurpose.ItemID
where lastest.DocNo = dcsItem.DocNo
and lastest.ProjectID = dcsItem.ProjectID and lastest.IsDrawing = 1 and lastest.Status = 1 ')
IF (@fullMode = 0 and @companyId <> 0)
BEGIN
set @filterSql = CONCAT(@filterSql, ' and (dcsDocSet.Status = 1 and (docSetCompany1.companyId is not null ')
IF (@dcsAdmin = 1)
BEGIN
set @filterSql = CONCAT(@filterSql, ' or dcsDocSet.OwnerCompanyID = @companyId ')
END
set @filterSql = CONCAT(@filterSql, ' )) ')
END
set @filterSql = CONCAT(@filterSql, ' order by viDcsItemDisciplineAndPurpose.purpose asc,lastest.rev desc, lastest.ID desc) ')
IF (@fullMode = 0 and @companyId <> 0)
BEGIN
set @filterSql = CONCAT(@filterSql, ' and (dcsDocSet.Status = 1 and (docSetCompany.companyId is not null ')
IF (@dcsAdmin = 1)
BEGIN
set @filterSql = CONCAT(@filterSql, ' or dcsDocSet.OwnerCompanyID = @companyId ')
END
set @filterSql = CONCAT(@filterSql, ' )) ')
IF (@isReviewed = 1)
BEGIN
set @filterSql = CONCAT(@filterSql, ' AND dcsItem.IsReviewed = 1')
END
END
set @filterSql = CONCAT(@filterSql, ' ) AS temp where 1=1 ')
IF (@statusStr IS NOT NULL and len(ltrim(rtrim(@statusStr))) > 0)
BEGIN
set @filterSql = CONCAT(@filterSql, ' AND temp.status IN (SELECT col FROM SplitIn(@statusStr,'',''))')
END
IF (@originatorStr IS NOT NULL and len(ltrim(rtrim(@originatorStr))) > 0)
BEGIN
set @filterSql = CONCAT(@filterSql, ' AND temp.OriginatorId IN (SELECT col FROM SplitIn(@originatorStr,'',''))')
END
IF (@masStatusStr IS NOT NULL and len(ltrim(rtrim(@masStatusStr))) > 0)
BEGIN
set @filterSql = CONCAT(@filterSql, ' AND temp.DocStatusId IN (SELECT col FROM SplitIn(@masStatusStr,'',''))')
END
IF (@drawingTypeStr IS NOT NULL and len(ltrim(rtrim(@drawingTypeStr))) > 0)
BEGIN
set @filterSql = CONCAT(@filterSql, ' AND temp.DrawingTypeId IN (SELECT col FROM SplitIn(@drawingTypeStr,'',''))')
END
IF (@creatorCompanyIdsStr IS NOT NULL and @creatorCompanyIdsStr <> '')
BEGIN
set @filterSql = CONCAT(@filterSql, 'and temp.ID IN (SELECT DISTINCT dcsItem.ID
FROM dcsItem
LEFT JOIN usrUser ON usrUser.ID = dcsItem.CreatedBy
AND usrUser.Status <> -1
LEFT JOIN perCompany ON usrUser.CompanyID = perCompany.ID
AND perCompany.Status <> -1
WHERE ProjectID = @projectId
AND dcsItem.Status <> -1
AND perCompany.ID in (SELECT col FROM SplitIn(@creatorCompanyIdsStr,'',''))
)')
END
IF (@zoneStr IS NOT NULL and len(ltrim(rtrim(@zoneStr))) > 0)
BEGIN
set @filterSql = CONCAT(@filterSql, @zoneStr)
END
IF (@catalogStr IS NOT NULL and len(ltrim(rtrim(@catalogStr))) > 0)
BEGIN
set @filterSql = CONCAT(@filterSql, ' AND temp.catalog IN (SELECT col FROM SplitIn(@catalogStr,'',''))')
END
IF (@docTypeNameStr IS NOT NULL and len(ltrim(rtrim(@docTypeNameStr))) > 0)
BEGIN
set @filterSql = CONCAT(@filterSql, ' AND temp.DrawingTypeId IN (SELECT col FROM SplitIn(@docTypeNameStr,'',''))')
END
IF (@keyword IS NOT NULL AND @keyword <> '')
BEGIN
SET @filterSql = CONCAT(@filterSql, ' AND ( temp.catalog LIKE CONCAT(''%'',@keyword,''%'') OR temp.originator LIKE CONCAT(''%'',@keyword,''%'')
OR temp.drawingNo LIKE CONCAT(''%'',@keyword,''%'') OR temp.drawingTitle LIKE CONCAT(''%'',@keyword,''%'')
OR temp.discipline LIKE CONCAT(''%'',@keyword,''%'') OR temp.drawingType LIKE CONCAT(''%'',@keyword,''%'')
OR temp.zone LIKE CONCAT(''%'',@keyword,''%'') OR temp.purpose LIKE CONCAT(''%'',@keyword,''%'')
OR temp.earlyRev LIKE CONCAT(''%'',@keyword,''%'') OR temp.latestRev LIKE CONCAT(''%'',@keyword,''%'')
OR temp.count LIKE CONCAT(''%'',@keyword,''%'') OR temp.drawing LIKE CONCAT(''%'',@keyword,''%'')
OR temp.refNo LIKE CONCAT(''%'',@keyword,''%'') OR temp.docSetNo LIKE CONCAT(''%'',@keyword,''%''))')
END
set @countSql = CONCAT(@countSql, @filterSql)
set @resultSql = CONCAT(@resultSql, @filterSql)
IF (@sortColumnName = 'drawing')
SET @ChineseStringColumnSort = 'collate Chinese_PRC_CI_AS '
set @resultSql = CONCAT(@resultSql, ' order by '+@sortColumnName+' '+ @ChineseStringColumnSort + @sortMode)
IF(@pageSize <> -1)
set @resultSql = CONCAT(@resultSql, ' offset ((@pageIndex - 1) * @pageSize) rows fetch next @pageSize rows only')
set @Parm1 = '
@keyword NVARCHAR (MAX),
@statusStr NVARCHAR (MAX),
@catalogStr NVARCHAR (MAX),
@originatorStr NVARCHAR(MAX),
@disciplinesStr NVARCHAR(MAX),
@creatorCompanyIdsStr VARCHAR(MAX),
@drawingTypeStr NVARCHAR(MAX),
@zoneStr NVARCHAR(MAX),
@purposesStr NVARCHAR(MAX),
@masStatusStr NVARCHAR(MAX),
@projectId int,
@companyId int,
@userId int,
@fullMode int,
@dcsAdmin int,
@docTypeNameStr VARCHAR (MAX)'
set @parm2 = CONCAT(@parm1, ',@sortColumnName VARCHAR(MAX),@sortMode VARCHAR(MAX),@pageIndex INT, @pageSize INT')
EXEC sp_executesql @countSql, @Parm1,
@keyword = @keyword,
@statusStr=@statusStr,
@catalogStr=@catalogStr,
@originatorStr=@originatorStr,
@disciplinesStr=@disciplinesStr,
@creatorCompanyIdsStr=@creatorCompanyIdsStr,
@drawingTypeStr=@drawingTypeStr,
@zoneStr=@zoneStr,
@purposesStr=@purposesStr,
@masStatusStr=@masStatusStr,
@projectId=@projectId,
@companyId=@companyId,
@userId=@userId,
@fullMode=@fullMode,
@dcsAdmin=@dcsAdmin,
@docTypeNameStr=@docTypeNameStr
EXEC sp_executesql @resultSql, @Parm2,
@keyword = @keyword,
@statusStr=@statusStr,
@catalogStr=@catalogStr,
@originatorStr=@originatorStr,
@disciplinesStr=@disciplinesStr,
@creatorCompanyIdsStr=@creatorCompanyIdsStr,
@drawingTypeStr=@drawingTypeStr,
@zoneStr=@zoneStr,
@purposesStr=@purposesStr,
@masStatusStr=@masStatusStr,
@projectId=@projectId,
@companyId=@companyId,
@userId=@userId,
@fullMode=@fullMode,
@dcsAdmin=@dcsAdmin,
@docTypeNameStr=@docTypeNameStr,
@sortColumnName=@sortColumnName,
@sortMode=@sortMode,
@pageIndex = @pageIndex,
@pageSize = @pageSize
drop table #resultTable
END
优化后的代码：
CREATE PROC [dbo].[spGetDrawingList](@companyId INT,
@userId INT,
@projectId INT,
@pageIndex INT,
@pageSize INT,
@keyword NVARCHAR(MAX),
@sortColumnName VARCHAR(MAX),
@sortMode VARCHAR(MAX),
@statusStr VARCHAR(MAX),
@isReviewed INT,
@fullMode INT,
@dcsAdmin BIT,
@catalogStr NVARCHAR(MAX),
@originatorStr NVARCHAR(MAX),
@disciplinesStr NVARCHAR(MAX),
@creatorCompanyIdsStr VARCHAR(MAX),
@drawingTypeStr NVARCHAR(MAX),
@zoneStr NVARCHAR(MAX),
@purposesStr NVARCHAR(MAX),
@masStatusStr NVARCHAR(MAX),
@docTypeNameStr NVARCHAR(MAX))
AS
BEGIN
DECLARE
@resultSql NVARCHAR(max) = N'',
@countSql NVARCHAR(max) = N'',
@filterSql NVARCHAR(max) = N'',
@Parm1 NVARCHAR(MAX) = N'',
@Parm2 NVARCHAR(MAX) = N'';
Declare @ChineseStringColumnSort VARCHAR(100);
SET @ChineseStringColumnSort = N''
--create memo table
create table #docSetCompany(docSetId int,CompanyId int)
insert #docSetCompany( docSetId,CompanyId )
select distinct dcsDocSetRelatedPerson.DocSetId,
dcsDocSetRelatedPerson.CompanyId
from dcsDocSetRelatedPerson
left join dcsDocSet
on dcsDocSet.ID = dcsDocSetRelatedPerson.DocSetID
where dcsDocSet.ProjectID=@projectId
and dcsDocSetRelatedPerson.CompanyId=@companyId
OPTION (MAXDOP 1)
create table #docSetUser(docSetId int,UserId int)
insert #docSetUser( docSetId,UserId )
select distinct dcsDocSetRelatedPerson.DocSetId,
dcsDocSetRelatedPerson.UserId
from dcsDocSetRelatedPerson
left join dcsDocSet
on dcsDocSet.ID = dcsDocSetRelatedPerson.DocSetID
where dcsDocSet.ProjectID=@projectId
and dcsDocSetRelatedPerson.UserId=@userId
OPTION (MAXDOP 1)
create table #resultTable
(
ProjectID INT,
Total INT
)
SET @resultSql = 'select temp.ID,
temp.ProjectID,
temp.DocTypeID,
temp.DrawingTypeId,
temp.contractNo,
temp.subject,
temp.docSetNo,
temp.docSetNumbering,
temp.dsDate,
temp.secured,
temp.refNo,
temp.docSetId,
temp.drawing,
temp.catalog,
temp.CatalogId,
temp.originator,
temp.originatorId,
temp.drawingNo,
temp.drawingTitle,
temp.discipline,
temp.drawingType,
temp.zone,
temp.purpose,
temp.rev,
temp.earlyRev,
temp.latestRev,
temp.count,
temp.fileInfoId,
temp.totalSize,
temp.status,
temp.CompanyAssigned,
temp.UserAssigned,
temp.createdBy,
temp.statusName,
temp.docSetStatus,
temp.isTransferred,
temp.ownerCompanyId,
temp.updated,
temp.Building,
temp.IsSecured,
(select top 1 Total from #resultTable) as Total '
set @countSql = 'insert into #resultTable(ProjectID, Total) SELECT @projectId,count(distinct temp.ID) '
set @filterSql = ' from (select dcsItem.ID,
dcsItem.ProjectID,
dcsDocSet.DocTypeID,
dcsItem.DrawingTypeId,
prjProjectContract.Code as contractNo,
dcsDocSet.subject as subject,
dcsDocSet.docSetNo,
dcsDocSet.Numbering as DocSetNumbering,
dcsDocSet.DocSetDate AS DSDate,
dcsDocSet.isSecured as Secured,
dcsItem.DocRefNo as refNo,
dcsItem.DocSetID as docSetId,
dcsItem.DocName as drawing,
masDrawingCategory.Name as catalog,
dcsItem.CatalogId,
perCompany.Name as Originator,
dcsItem.OriginatorId,
dcsItem.DocNo as drawingNo,
dcsItem.DocTitle as drawingTitle,
viDcsItemDisciplineAndPurpose.Discipline,
masDrawingType.Name as drawingType,
dcsItem.Zone,
viDcsItemDisciplineAndPurpose.Purpose,
dcsItem.Rev as rev,
(select top 1 related.Rev
from dcsItem as related
where related.DocNo = dcsItem.DocNo
and related.ProjectID = dcsItem.ProjectID
and related.IsDrawing = 1 and related.Status = 1
order by related.ID asc) as earlyRev,
(select top 1 related.Rev
from dcsItem as related
where related.DocNo = dcsItem.DocNo
and related.ProjectID = dcsItem.ProjectID
and related.IsDrawing = 1 and related.Status = 1
order by related.ID desc) as latestRev,
(select count(*)
from dcsItem as related
where related.DocNo = dcsItem.DocNo
and related.ProjectID = dcsItem.ProjectID
and related.IsDrawing = 1 and related.Status = 1) as count,
dcsItem.FileInfoId,
sysFileInfo.TotalSize,
dcsItem.DocStatusId as status,
(case isnull(#docSetCompany.companyId, ''0'') when ''0'' then ''0'' ELSE ''1'' END) as CompanyAssigned,
(case isnull(#docSetUser.userId, ''0'') when ''0'' then ''0'' ELSE ''1'' END) as UserAssigned,
dcsItem.CreatedBy,
masDocumentStatus.Name as statusName,
dcsDocSet.Status as docSetStatus,
dcsDocSet.OwnerCompanyID as OwnerCompanyId,
dcsItem.isTransferred,
dcsItem.Modified as updated,
dcsItem.Building,
dcsDocSet.IsSecured
from dcsItem
left join dcsDocSet
on dcsDocSet.ID = dcsItem.DocSetID
left join sysFileInfo
on sysFileInfo.Id = dcsItem.FileInfoId
left join masDrawingType
on masDrawingType.ID = dcsItem.DrawingTypeId
left join masDrawingCategory
on masDrawingCategory.ID = dcsItem.CatalogId
left join masDocumentStatus
on masDocumentStatus.ID = dcsItem.DocStatusId
left join #docSetCompany
on #docSetCompany.DocSetId = dcsDocSet.ID and #docSetCompany.companyID = @companyId
left join #docSetUser
on #docSetUser.DocSetId = dcsDocSet.ID and #docSetUser.UserId = @userId
left join viDcsItemDisciplineAndPurpose on dcsItem.ID = viDcsItemDisciplineAndPurpose.ItemID
left join perCompany
on perCompany.ID = dcsItem.OriginatorId
left join prjProjectContract
on prjProjectContract.ID = dcsDocSet.ProjectContractID '
IF (@disciplinesStr IS NOT NULL and @disciplinesStr <> '')
BEGIN
set @filterSql = CONCAT(@filterSql,' inner join (select distinct dcsItemDiscipline.ItemID from dcsItemDiscipline where DisciplineID in (SELECT col FROM SplitIn(@disciplinesStr,'',''))) dcsDiscipline on dcsDiscipline.ItemID = dcsItem.ID ')
END
IF (@purposesStr IS NOT NULL and @purposesStr <> '')
BEGIN
set @filterSql = CONCAT(@filterSql,' inner join (select distinct dcsItemPurpose.ItemID from dcsItemPurpose where PurposeID in (SELECT col FROM SplitIn(@purposesStr,'',''))) dcsPurpose on dcsPurpose.ItemID = dcsItem.ID ')
END
set @filterSql = CONCAT(@filterSql,'where dcsItem.ProjectID = @projectId
and dcsItem.Status = 1
and dcsItem.IsDrawing = 1
and dcsItem.ID = (select top 1 lastest.ID
from dcsItem as lastest
left join dcsDocSet
on dcsDocSet.ID = lastest.DocSetID
left join (select distinct dcsDocSetRelatedPerson.DocSetId, dcsDocSetRelatedPerson.CompanyId from dcsDocSetRelatedPerson left join dcsDocSet on dcsDocSet.ID = dcsDocSetRelatedPerson.DocSetID where dcsDocSet.ProjectID=@projectId and dcsDocSetRelatedPerson.CompanyId=@companyId) docSetCompany1
on docSetCompany1.DocSetId = lastest.DocSetID and docSetCompany1.CompanyId = @companyId
left join viDcsItemDisciplineAndPurpose on lastest.ID = viDcsItemDisciplineAndPurpose.ItemID
where lastest.DocNo = dcsItem.DocNo
and lastest.ProjectID = dcsItem.ProjectID and lastest.IsDrawing = 1 and lastest.Status = 1 ')
IF (@fullMode = 0 and @companyId <> 0)
BEGIN
set @filterSql = CONCAT(@filterSql, ' and (dcsDocSet.Status = 1 and (docSetCompany1.companyId is not null ')
IF (@dcsAdmin = 1)
BEGIN
set @filterSql = CONCAT(@filterSql, ' or dcsDocSet.OwnerCompanyID = @companyId ')
END
set @filterSql = CONCAT(@filterSql, ' )) ')
END
set @filterSql = CONCAT(@filterSql, ' order by viDcsItemDisciplineAndPurpose.purpose asc,lastest.rev desc, lastest.ID desc) ')
IF (@fullMode = 0 and @companyId <> 0)
BEGIN
set @filterSql = CONCAT(@filterSql, ' and (dcsDocSet.Status = 1 and (#docSetCompany.companyId is not null ')
IF (@dcsAdmin = 1)
BEGIN
set @filterSql = CONCAT(@filterSql, ' or dcsDocSet.OwnerCompanyID = @companyId ')
END
set @filterSql = CONCAT(@filterSql, ' )) ')
IF (@isReviewed = 1)
BEGIN
set @filterSql = CONCAT(@filterSql, ' AND dcsItem.IsReviewed = 1')
END
END
set @filterSql = CONCAT(@filterSql, ' ) AS temp where 1=1 ')
IF (@statusStr IS NOT NULL and len(ltrim(rtrim(@statusStr))) > 0)
BEGIN
set @filterSql = CONCAT(@filterSql, ' AND temp.status IN (SELECT col FROM SplitIn(@statusStr,'',''))')
END
IF (@originatorStr IS NOT NULL and len(ltrim(rtrim(@originatorStr))) > 0)
BEGIN
set @filterSql = CONCAT(@filterSql, ' AND temp.OriginatorId IN (SELECT col FROM SplitIn(@originatorStr,'',''))')
END
IF (@masStatusStr IS NOT NULL and len(ltrim(rtrim(@masStatusStr))) > 0)
BEGIN
set @filterSql = CONCAT(@filterSql, ' AND temp.DocStatusId IN (SELECT col FROM SplitIn(@masStatusStr,'',''))')
END
IF (@drawingTypeStr IS NOT NULL and len(ltrim(rtrim(@drawingTypeStr))) > 0)
BEGIN
set @filterSql = CONCAT(@filterSql, ' AND temp.DrawingTypeId IN (SELECT col FROM SplitIn(@drawingTypeStr,'',''))')
END
IF (@creatorCompanyIdsStr IS NOT NULL and @creatorCompanyIdsStr <> '')
BEGIN
set @filterSql = CONCAT(@filterSql, 'and temp.ID IN (SELECT DISTINCT dcsItem.ID
FROM dcsItem
LEFT JOIN usrUser ON usrUser.ID = dcsItem.CreatedBy
AND usrUser.Status <> -1
LEFT JOIN perCompany ON usrUser.CompanyID = perCompany.ID
AND perCompany.Status <> -1
WHERE ProjectID = @projectId
AND dcsItem.Status <> -1
AND perCompany.ID in (SELECT col FROM SplitIn(@creatorCompanyIdsStr,'',''))
)')
END
IF (@zoneStr IS NOT NULL and len(ltrim(rtrim(@zoneStr))) > 0)
BEGIN
set @filterSql = CONCAT(@filterSql, @zoneStr)
END
IF (@catalogStr IS NOT NULL and len(ltrim(rtrim(@catalogStr))) > 0)
BEGIN
set @filterSql = CONCAT(@filterSql, ' AND temp.catalog IN (SELECT col FROM SplitIn(@catalogStr,'',''))')
END
IF (@docTypeNameStr IS NOT NULL and len(ltrim(rtrim(@docTypeNameStr))) > 0)
BEGIN
set @filterSql = CONCAT(@filterSql, ' AND temp.DrawingTypeId IN (SELECT col FROM SplitIn(@docTypeNameStr,'',''))')
END
IF (@keyword IS NOT NULL AND @keyword <> '')
BEGIN
SET @filterSql = CONCAT(@filterSql, ' AND ( temp.catalog LIKE CONCAT(''%'',@keyword,''%'') OR temp.originator LIKE CONCAT(''%'',@keyword,''%'')
OR temp.drawingNo LIKE CONCAT(''%'',@keyword,''%'') OR temp.drawingTitle LIKE CONCAT(''%'',@keyword,''%'')
OR temp.discipline LIKE CONCAT(''%'',@keyword,''%'') OR temp.drawingType LIKE CONCAT(''%'',@keyword,''%'')
OR temp.zone LIKE CONCAT(''%'',@keyword,''%'') OR temp.purpose LIKE CONCAT(''%'',@keyword,''%'')
OR temp.earlyRev LIKE CONCAT(''%'',@keyword,''%'') OR temp.latestRev LIKE CONCAT(''%'',@keyword,''%'')
OR temp.count LIKE CONCAT(''%'',@keyword,''%'') OR temp.drawing LIKE CONCAT(''%'',@keyword,''%'')
OR temp.refNo LIKE CONCAT(''%'',@keyword,''%'') OR temp.docSetNo LIKE CONCAT(''%'',@keyword,''%''))')
END
set @countSql = CONCAT(@countSql, @filterSql)
set @resultSql = CONCAT(@resultSql, @filterSql)
print '@countSql='+@countSql
print '@resultSql='+@resultSql
IF (@sortColumnName = 'drawing')
SET @ChineseStringColumnSort = 'collate Chinese_PRC_CI_AS '
set @resultSql = CONCAT(@resultSql, ' order by '+@sortColumnName+' '+ @ChineseStringColumnSort + @sortMode)
IF(@pageSize <> -1)
set @resultSql = CONCAT(@resultSql, ' offset ((@pageIndex - 1) * @pageSize) rows fetch next @pageSize rows only')
set @resultSql = CONCAT(@resultSql, ' OPTION(querytraceon 8649) ')
set @Parm1 = '
@keyword NVARCHAR (MAX),
@statusStr NVARCHAR (MAX),
@catalogStr NVARCHAR (MAX),
@originatorStr NVARCHAR(MAX),
@disciplinesStr NVARCHAR(MAX),
@creatorCompanyIdsStr VARCHAR(MAX),
@drawingTypeStr NVARCHAR(MAX),
@zoneStr NVARCHAR(MAX),
@purposesStr NVARCHAR(MAX),
@masStatusStr NVARCHAR(MAX),
@projectId int,
@companyId int,
@userId int,
@fullMode int,
@dcsAdmin int,
@docTypeNameStr VARCHAR (MAX)'
set @parm2 = CONCAT(@parm1, ',@sortColumnName VARCHAR(MAX),@sortMode VARCHAR(MAX),@pageIndex INT, @pageSize INT')
EXEC sp_executesql @countSql, @Parm1,
@keyword = @keyword,
@statusStr=@statusStr,
@catalogStr=@catalogStr,
@originatorStr=@originatorStr,
@disciplinesStr=@disciplinesStr,
@creatorCompanyIdsStr=@creatorCompanyIdsStr,
@drawingTypeStr=@drawingTypeStr,
@zoneStr=@zoneStr,
@purposesStr=@purposesStr,
@masStatusStr=@masStatusStr,
@projectId=@projectId,
@companyId=@companyId,
@userId=@userId,
@fullMode=@fullMode,
@dcsAdmin=@dcsAdmin,
@docTypeNameStr=@docTypeNameStr
EXEC sp_executesql @resultSql, @Parm2,
@keyword = @keyword,
@statusStr=@statusStr,
@catalogStr=@catalogStr,
@originatorStr=@originatorStr,
@disciplinesStr=@disciplinesStr,
@creatorCompanyIdsStr=@creatorCompanyIdsStr,
@drawingTypeStr=@drawingTypeStr,
@zoneStr=@zoneStr,
@purposesStr=@purposesStr,
@masStatusStr=@masStatusStr,
@projectId=@projectId,
@companyId=@companyId,
@userId=@userId,
@fullMode=@fullMode,
@dcsAdmin=@dcsAdmin,
@docTypeNameStr=@docTypeNameStr,
@sortColumnName=@sortColumnName,
@sortMode=@sortMode,
@pageIndex = @pageIndex,
@pageSize = @pageSize
drop table #docSetCompany
drop table #docSetUser
drop table #resultTable
END
问题分析：存储过程性能问题出在标红的两个子查询中，docSet表中数据量为100w+,dcsDocSetRelatedPerson表中数据量80W+，子查询时条件限定后的结果集一般为近千条。每次执行时，docSet表关联子查询结果集时，对于外部查询返回的每一行数据，子查询都要执行一次。在关联子查询中是信息流是双向的。外部查询的每行数据传递一个值给子查询，然后子查询为每一行数据执行一次并返回它的记录。然后，外部查询根据返回的记录做出决策，docSet表中数据量过多造成了性能问题，直接导致查询过慢。
解决方法：因为每次执行该存储过程时，子查询中的参数和结果集都是固定的，因此，将两个子查询抽出来做成内存表，可以避免外部查询返回的每一行数据，子查询都要执行一次的问题，子查询的结果可以从内存表中提供。从而提高性能。
PS:1.在家想远程链接公司电脑复制SQL脚本，结果公司又停电导致办公电脑关机了，改良后的脚本暂时就没放上来了，真是受够公司的这帮物业了，一周两次停电，上次还报废了一台服务器。
2.跑路的同事写这个存储过程关联了11张以上的表，应该是不规范的，个人觉得除非万不得已应该避免这种情况的。
576
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
