提高T-SQL查询性能的解决方案(我使用UDF) [英] Solutions to increase T-SQL Query Performance (I Used UDF)
本文介绍了提高T-SQL查询性能的解决方案(我使用UDF)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
亲爱的所有人，
我的程序性能有问题，首先我要解释一下过程
我有一个视图，可以在此处获取我的邮件列表:
Dear All ,
I Have A Problem with the Procedure Performance , First I want To Explain the process
I have View That Get Me a list of Mails Here Its :
SELECT m.Mail_ID ,
mt.MailTo_ID ,
mt.MailTo_ToType ,
( CASE WHEN mp.MailPass_ID IS NULL
THEN ( CASE WHEN mt.Mail_FromInternal = 0
THEN ( SELECT Name
FROM dbo.Lookups
WHERE ID = m.MailFromID
) + ' -External Mail'
ELSE emFrom.Employee_Name + ' / '
+ hFrom.Hierarchy_Name
END )
ELSE mp.MailsPasses_Sender
END ) AS Sender ,
( CASE WHEN mp.MailPass_ID IS NULL THEN toExternal.Name
ELSE mp.MailsPasses_Reciver
END ) AS Reciver ,
m.Mail_Title ,
ISNULL(SUBSTRING(CONVERT(NVARCHAR, Mail_ContentSize / 1024.0 / 1024.0),
0,
CHARINDEX('.',
CONVERT(NVARCHAR, Mail_ContentSize / 1024.0
/ 1024.0) + 2.0, 0) + 3), '0.00') + ' M.B' AS MailContentSize ,
( CASE WHEN ( SELECT Configuration_Value
FROM [Configurations]
WHERE Configuration_ID = 14
) = 'True' THEN CONVERT(NVARCHAR ,m.Mail_SenderDate,131)
ELSE CONVERT(NVARCHAR,m.Mail_SenderDate)
END ) AS Mail_SenderDate ,
emFrom.Employee_ID AS FromEmployeeID ,
emFrom.Hierarchy_ID AS FromHierarchyID ,
toExternal.ID AS ToHierarchyID ,
NULL AS ToEmployeeID ,
( SELECT dbo.[fn_GetSenderFirstReciever](emFrom.Hierarchy_ID)
) AS SenderFirstReciever ,
NULL AS RecieverFirstReciever ,
ISNULL(priority.Name, 'No Data') AS PriorityName ,
ISNULL(mailStatus.Name, 'No Data') AS MailStatusName ,
Mail_Owner_Name ,
Mail_Owner_Mobile_Number ,
Mail_ReadDate ,
Mail_Body ,
Mail_BodyPlanText ,
Mail_StatusID ,
Mail_Priorty_ID ,
Mail_Owner_Send_SMS ,
m.Mail_Status ,
mt.Employee_Folder_ID ,
m.Mail_Catigory_ID ,
m.Mail_General_ID ,
m.Mail_PublicID ,
m.Mail_ParentMailID ,
m.Process_ID ,
mp.MailPass_ID ,
mp.MailPass_ByEmployeeID ,
mp.MailPass_Hierarchy_ID ,
mp.MailPass_IsPublished ,
mp.MailPass_Note ,
mp.MailPass_PublishedByEmployeeID ,
mp.MailPass_PublishingDate ,
mp.MailPass_ToEmployeeID ,
mp.MailPass_Type ,
mp.MailPass_ReadDate ,
mp.MailPass_Rank ,
mp.MailPass_IsInernal ,
mp.MailTo_ID AS MailPass_MailTo_ID ,
mpp.MailsPassesProccess_Name AS LastActionProcess ,
m.IdentificationNo
FROM dbo.Mails m
INNER JOIN dbo.MailsTo mt ON m.Mail_ID = mt.Mail_ID
INNER JOIN dbo.Employees emFrom ON emFrom.Employee_ID = m.Employee_ID
INNER JOIN dbo.Hierarchy hFrom ON hFrom.Hierarchy_ID = emFrom.Hierarchy_ID
INNER JOIN dbo.Lookups toExternal ON toExternal.ID = mt.MailTo_ToID
LEFT JOIN dbo.Lookups priority ON priority.ID = m.Mail_Priorty_ID
LEFT JOIN dbo.Lookups mailStatus ON mailStatus.ID = m.Mail_StatusID
LEFT JOIN vwMailsPasses mp ON mp.Mail_ID = mt.Mail_ID
AND ( mp.MailPass_Rank = ( SELECT
MAX(MailPass_Rank)
FROM
dbo.MailsPasses
WHERE
MailTo_ID = mp.MailTo_ID
)
OR mp.MailPass_Rank IS NULL
)
LEFT JOIN dbo.MailsPassesProccess mpp ON mpp.MailsPassesProccess_ID = m.MailsPassesProccess_ID
WHERE mt.MailTo_ToType = 2
否，在某些情况下，我想从此视图"中选择数据:
No I want to select Data From This VIEW in some Cases LIKE :
CREATE TABLE #temp ( hierarcyID BIGINT ) ;
INSERT INTO #temp
SELECT i
FROM dbo.fn_GetCTSHierarchiesUnderHierarchyIDWithoutTheSenderNode(@HierarchyID)
SELECT vm.* ,
me.MailExporter_ID ,
me.Mail_ExportNo ,
me.Mail_Exported ,
me.Hierarchy_ID AS ExportedByHierarchy_ID ,
me.MailExporter_Rank ,
me.Employee_ID AS ExportedByEmployee_ID ,
me.Hidden AS ExportedIsHidden ,
mi.MailImporter_ID ,
mi.Mail_ImportNo ,
mi.Mail_Imported ,
mi.Hierarchy_ID AS ImportedByHierarchy_ID ,
mi.MailImporter_Rank ,
mi.Employee_ID AS ImportedByEmployee_ID ,
mi.Hidden AS ImportedIsHidden
FROM dbo.vwMails vm
LEFT JOIN dbo.MailsExporter me ON vm.MailTo_ID = me.MailTo_ID
AND ( EXISTS ( SELECT
MailExporter_ID
FROM
dbo.MailsExporter
WHERE
Hierarchy_ID = ( SELECT
dbo.[fn_GetCTSHierarchyIDUnderHierarchyIDWithoutTheSenderNode](@HierarchyID,
FromHierarchyID)
)
AND MailTo_ID = vm.MailTo_ID )
OR ( SELECT
dbo.[fn_GetCTSHierarchyIDUnderHierarchyIDWithoutTheSenderNode](@HierarchyID,
FromHierarchyID)
) IS NULL
)
AND ( EXISTS ( SELECT
MailExporter_ID
FROM
dbo.MailsExporter
WHERE
Hierarchy_ID = ( SELECT
dbo.[fn_GetFarestHierarchyIDByHierarchyID](FromHierarchyID)
)
AND MailTo_ID = vm.MailTo_ID )
OR ( SELECT
dbo.[fn_GetFarestHierarchyIDByHierarchyID](FromHierarchyID)
) IS NULL
)
LEFT JOIN dbo.MailsImporter mi ON vm.MailTo_ID = mi.MailTo_ID
AND mi.Hierarchy_ID = @HierarchyID
问题是我需要运行此函数以确保先从发送者层次结构下的层次结构中导出行，然后功能才包含递归:
The Problem is I need To run this function to ensure that the row is exported before from the Hierarchy Under the sender Hierarchy THE FUNCTION IS Contains Recursive:
CREATE FUNCTION [dbo].[fn_GetCTSHierarchyIDUnderHierarchyIDWithoutTheSenderNode]
(
@Hierarchy_ID BIGINT ,
@senderHierarchyID BIGINT
)
RETURNS BIGINT
AS
BEGIN
DECLARE Hierarchy_ID CURSOR
FOR SELECT Hierarchy_ID
FROM dbo.Hierarchy
WHERE HierarchyParent_ID = @Hierarchy_ID AND Hierarchy_IsDeleted=0
OPEN Hierarchy_ID
FETCH NEXT FROM Hierarchy_ID
INTO @Hierarchy_ID
WHILE @@FETCH_STATUS = 0
BEGIN
IF ( @Hierarchy_ID IS NOT NULL )
BEGIN
IF EXISTS ( SELECT Hierarchy_ID
FROM dbo.Hierarchy
WHERE Hierarchy_ID = @Hierarchy_ID
AND Hierarchy_IsCTS = 1
AND Hierarchy_ID IN (
SELECT i
FROM dbo.fn_GetHierarchiesUpperHierarchyID(@senderHierarchyID) ) )
RETURN @Hierarchy_ID
ELSE
SET @Hierarchy_ID = ( SELECT dbo.[fn_GetCTSHierarchyIDUnderHierarchyIDWithoutTheSenderNode](@Hierarchy_ID,
@senderHierarchyID)
)
END
FETCH NEXT FROM Hierarchy_ID INTO @Hierarchy_ID
END
IF EXISTS ( SELECT Hierarchy_ID
FROM dbo.Hierarchy
WHERE Hierarchy_ID = @Hierarchy_ID
AND Hierarchy_IsCTS = 1 )
RETURN @Hierarchy_ID
ELSE
BEGIN
SET @Hierarchy_ID = NULL
END
RETURN @Hierarchy_ID
END
如何增加此Procedure函数?！?
如果还不够理解，请给我评论，我将更新
最好的问候
Ab Abulubbad
How to increase this Procedure function ?!?
if this not enough to understand please send me a comment and i''ll Update
Best Regards
Ab Abulubbad
推荐答案
我注意到的第一个问题是您在函数中使用了游标，游标只是效率低下而又缓慢. 您将需要使用带有CTE(公用表表达式)的递归查询来转换函数.
还在您的层次结构(邻接)中使用哪种类型的数据模型?
The first issue I notice is that you are using a cursor in your function, cursor are simply inefficient and slow.
You will need to convert the function using a recursive query with a CTE (common table expression).
Also what type of data model are using for your hierarchy (adjacency)?
性能调优并不总是有确切的答案.但是这里有一些链接可以帮助您调整T-SQL查询的性能.
http://msdn.microsoft.com/en-us/library/ms172984％28v = sql.100％29.aspx
http://msdn.microsoft.com/en-us/sqlserver/bb671432
Performance tuning not always has exact answer. But here are some links which may help you in Performance tuning of your T-SQL Queries.
http://msdn.microsoft.com/en-us/library/ms172984%28v=sql.100%29.aspx
http://msdn.microsoft.com/en-us/sqlserver/bb671432
这篇关于提高T-SQL查询性能的解决方案(我使用UDF)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
