sql 执行效率
我有如下两个存储过程
ALTER PROCEDURE [dbo].[P_EmailLog_Clear]
AS
DECLARE @Emails VARCHAR(8000)
SET @Emails = ''
SELECT @Emails = @Emails + email + ','
FROM PUB_MailSendLog ,PUB_MailTypeConfig
WHERE PUB_MailSendLog.mailConfigID = PUB_MailTypeConfig.mailConfigID
AND DATEADD("M",PUB_MailTypeConfig.validateMonth,PUB_MailSendLog.sendTime) < getdate()
/* 声明循环次数 */
DECLARE @Count INT
SET @Count = 0
SELECT @Count = LEN(@Emails) - LEN(REPLACE(@Emails,',',''))
/* 声明循环变量 */
DECLARE @i INT
SET @i = 0
/* 声明零时变量 */
DECLARE @tempEmail NVARCHAR(100)
SET @tempEmail = ''
--PRINT @Emails
--PRINT @Count
IF (LEN(@Emails) > 0)
BEGIN
WHILE(@i < @Count)
BEGIN
-- PRINT('执行')
SELECT @tempEmail = SUBSTRING(@Emails ,0 ,PATINDEX ('%,%' , @Emails))
DELETE FROM PUB_MailSendLog WHERE email = @tempEmail
SELECT @Emails = SUBSTRING(@Emails ,PATINDEX('%,%' , @Emails) + 1 ,LEN(@Emails))
SET @i = @i + 1
END
END
ALTER PROCEDURE [dbo].[P_EmailLog_Clear]
AS
DELETE FROM PUB_MailSendLog WHERE logGUID IN
(
SELECT t1.logGUID FROM PUB_MailSendLog t1,PUB_MailTypeConfig t2
WHERE t1.mailConfigID = t2.mailConfigID
AND DATEADD(MONTH ,t2.validateMonth ,t1.sendTime) < GETDATE()
)
也就是循环删除一个是逐行删除.另外一个是用 IN 删除
请问那个效率高一些