SQL查询优化 [英] SQL Query Optimisation
本文介绍了SQL查询优化的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
以下查询需要8分钟。执行。
有人请帮我优化这个查询
i到目前为止我已经尽力了...
Below query is taking 8 min. to execute.
Somebody please help me for optimising this query
i have tried my best so far..
Declare @Project_Code varchar(50)
Declare @User_Level int
Declare @User_Name varchar(50)
set @Project_Code='PE081507' set @User_Level='9' set @User_Name='mwebber'
Declare @FullQuery as varchar (max)
Declare @MainHeader as varchar (5000)
Declare @SubHeader as varchar (5000)
DECLARE @GridHeader VARCHAR(MAX)
set @GridHeader = 'Queue#'
DECLARE @TOTALCOUNT INT;
SET @MainHeader = '['
SET @SubHeader = '['
;with InvTypeHead
AS
(
SELECT DISTINCT LR.InvType AS InvTypeCode , DT.Description AS InvTypeName,DT.GridOrder,DG.GroupName as DocumentGroup
FROM LevelRights LR
Inner JOIN
invtypemaster DT ON LR.ProjectCode= DT.ProjectCode and LR.InvType = DT.Type
Left Join
Phase2GroupMaster DG
on
DT.projectCode = Dg.Project_Code
and DT.GroupId = DG.GroupID
WHERE LR.ProjectCode=@Project_Code and Ulevel = @User_Level and Display_MyWorkQueue = 1
)
SELECT @MainHeader= COALESCE(@MainHeader,'') + InvTypeCode + '] as [' + InvTypeName + '],[',
@SubHeader = COALESCE(@SubHeader,'') + InvTypeCode + '],[',
@GridHeader = COALESCE(@GridHeader,' ') + DocumentGroup + '|' + InvTypeName + '#' FROM InvTypeHead order by GridOrder
IF (LEN(@MainHeader) > 2)
BEGIN
Print 'Main Header length is greater than 2'
Set @MainHeader = SUBSTRING(@MainHeader,0,len(@MainHeader) - 1)
Set @SubHeader = SUBSTRING(@SubHeader,0,len(@SubHeader) - 1)
print Convert(Varchar(20),getdate(),109)
Set @FullQuery =
'
IF OBJECT_ID(''[TypeHeader_' + @User_Name + ']'') IS NOT NULL
DROP TABLE [TypeHeader_' + @User_Name + ']
IF OBJECT_ID(''[QcodeDetails_' + @User_Name + ']'') IS NOT NULL
DROP TABLE [QcodeDetails_' + @User_Name + ']
IF OBJECT_ID(''[InvoiceData_' + @User_Name + ']'') IS NOT NULL
DROP TABLE [InvoiceData_' + @User_Name + ']
IF OBJECT_ID(''[FinalData_' + @User_Name + ']'') IS NOT NULL
DROP TABLE [FinalData_' + @User_Name + ']
;
Declare @MainQuery as varchar (max)
DECLARE @TOTALCOUNT INT;
set nocount on
print Convert(Varchar(20),getdate(),109)
SELECT Distinct LR.InvType AS InvTypeCode , DT.Description AS InvTypeName,DT.GridOrder into [TypeHeader_' + @User_Name + '] FROM LevelRights LR with(nolock,nowait)
Inner JOIN
invtypemaster DT with(nolock,nowait) ON LR.ProjectCode= DT.ProjectCode and LR.InvType = DT.Type
WHERE LR.ProjectCode=''' + @Project_Code + ''' and Ulevel =''' + CONVERT(varchar(5),@User_Level) + ''' and Display_MyWorkQueue = 1
print Convert(Varchar(20),getdate(),109)
SELECT DISTINCT LR.Ulevel,LR.QCode,Q.QName,Q.Appearing_Sequence into [QcodeDetails_' + @User_Name + '] FROM LevelRights LR with(nolock,nowait)
INNER JOIN
QMaster Q with(nolock,nowait) ON LR.ProjectCode=Q.ProjectCode and LR.QCode=Q.QCode
WHERE LR.ProjectCode=''' + @Project_Code +''' AND Display_MyWorkQueue = 1 and Ulevel =''' + CONVERT(varchar(5),@User_Level) + '''
print Convert(Varchar(20),getdate(),109)
SELECT case when Currentuser =''' + @User_Name + ''' then ''CCU'' else QD.QCode end as QCode, case when Currentuser = ''' + @User_Name + ''' then ''My Queue'' else QD.QName end as QName,
TH.InvTypeCode,TH.InvTypeName, case when Currentuser = ''' + @User_Name + ''' then ''0'' else QD.Appearing_Sequence end as Appearing_Sequence,TD.irn into [InvoiceData_' + @User_Name + '] FROM Invpage TD
right JOIN [QcodeDetails_' + @User_Name + '] QD ON TD.dtl_Status = QD.QCode
full JOIN [TypeHeader_' + @User_Name + '] TH ON TD.InvType = TH.InvTypeCode
WHERE (Currentuser = '''' or Currentuser is null or Currentuser =''' + @User_Name + ''') and Qd.QCode is not null and TD.[C-JamPrjCode]=''' + @Project_Code + '''
print Convert(Varchar(20),getdate(),109)
Select case when I.QCode is null then Q.QCode else I.QCode end as QCode, case when I.QName is null then Q.QName else I.QName end as QName ,
I.InvTypeCode,I.InvTypeName,I.irn, case when I.Appearing_Sequence is null then Q.Appearing_Sequence else I.Appearing_Sequence end as Appearing_Sequence
into [FinalData_' + @User_Name + '] from [InvoiceData_' + @User_Name + '] I full join [QcodeDetails_' + @User_Name + '] Q on I.QCode = Q.QCode
print Convert(Varchar(20),getdate(),109)
set nocount Off
'
--Print @MainHeader
--Print @SubHeader
--Print @GridHeader
Set @FullQuery = @FullQuery + ' SELECT QName as Queue,' + @MainHeader +
--[TPO] as PO,[TNP] as NONPO,[MPO] as PO,[TNP] as NONPO,[DPO] as PO,[DNP] as NONPO
' FROM
(
SELECT QName,InvTypeCode,IRN,Appearing_Sequence from [FinalData_' + @User_Name + '] ) as p
PIVOT
(
count(IRN)
FOR InvTypeCode IN
('
+ @SubHeader +
-- [TPO],[TNP],[DPO],[DNP],[MNP],[MPO]
')
) AS pvt Order by Appearing_Sequence
'
END
print @FullQuery
Execute(@FullQuery)
SET @TOTALCOUNT = (Select @@rowcount)
print Convert(Varchar(20),getdate(),109)
Execute (' IF OBJECT_ID(''[TypeHeader_' + @User_Name + ']'') IS NOT NULL
DROP TABLE [TypeHeader_' + @User_Name + ']
IF OBJECT_ID(''[QcodeDetails_' + @User_Name + ']'') IS NOT NULL
DROP TABLE [QcodeDetails_' + @User_Name + ']
IF OBJECT_ID(''[InvoiceData_' + @User_Name + ']'') IS NOT NULL
DROP TABLE [InvoiceData_' + @User_Name + ']
IF OBJECT_ID(''[FinalData_' + @User_Name + ']'') IS NOT NULL
DROP TABLE [FinalData_' + @User_Name + '] ')
谢谢&此致，
Sunil Mali。
Thanks & Regards,
Sunil Mali.
推荐答案
根据对问题的评论，我建议访问 CodeProject KnowledgeBase 。在那里你会发现很多建议如何提高查询的性能。
As per comments to the question, i'd suggest to visit CodeProject KnowledgeBase. There you'll find tons of advices how to improve performance of query.
这篇关于SQL查询优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
