Dynamics Community Forum Thread Details

how to optimize the performance for sending email

Hi guys,

I got a sql script from database, as shown below, it seems like resend the email,but this script takes 15s every time, it affects the performance of database, I want to know how to optimize this functionality,

appreciate for your help!

(@StateCode0 int,@StatusCode0 int,@DirectionCode0 bit,@DeliveryAttempts0 int,@ParticipationTypeMask0 int,@PartyId0 uniqueidentifier,@PartyId1 uniqueidentifier,@PartyId2 uniqueidentifier,@PartyId3 uniqueidentifier)select
top 5 "email0".Subject as "subject"
, "email0".Description as "description"
, "email0".PriorityCode as "prioritycode"
, "email0".ActivityId as "activityid"
, "email0".ModifiedOn as "modifiedon"
, "email0".StateCode as "statecode"
, "email0".StatusCode as "statuscode"
, "email0".DeliveryAttempts as "deliveryattempts"
, "email0".AttachmentCount as "attachmentcount"
, convert(bigint, "email0".VersionNumber) as "versionnumber"
, N'' as "safedescription"
from
Email as "email0" WITH (NOLOCK) join ActivityParty as "activityparty1" WITH (NOLOCK) on ("email0".ActivityId = "activityparty1".ActivityId and (((("activityparty1".ParticipationTypeMask = @ParticipationTypeMask0 and ("activityparty1".PartyId in (@PartyId0
, @PartyId1
, @PartyId2
, @PartyId3)))))))
where
(("email0".StateCode = @StateCode0 and ("email0".StatusCode != @StatusCode0 or "email0".StatusCode is null) and "email0".DirectionCode = @DirectionCode0 and (((((("email0".DeliveryAttempts = @DeliveryAttempts0)))))))) order by
"email0".ActualEnd asc

Thanks,

Sean

-- Ensure a USE statement has been executed first. USE AdventureWorks GO -- Look at the Index Information SELECT DB_NAME(idxst.database_id) AS [database_name], OBJECT_NAME(idxst.object_id, idxst.database_id) AS [object_name], QUOTENAME(idxif.name) [index_name], CASE WHEN avg_fragmentation_in_percent < 10 THEN 'LOW' WHEN avg_fragmentation_in_percent < 30 THEN 'MEDIUM' WHEN avg_fragmentation_in_percent < 50 THEN 'HIGH' ELSE 'EXTREME' END as fragmentation_indicator, idxst.* FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL , 'LIMITED') AS idxst -- not using DETAILED or SAMPLED INNER JOIN sys.indexes idxif ON idxst.object_id = idxif.object_id AND idxst.index_id = idxif.index_id ORDER BY [object_name], [index_name] GO

SET NOCOUNT ON; DECLARE @objectid int; DECLARE @indexid int; DECLARE @partitioncount bigint; DECLARE @schemaname nvarchar(130); DECLARE @objectname nvarchar(130); DECLARE @indexname nvarchar(130); DECLARE @partitionnum bigint; DECLARE @partitions bigint; DECLARE @frag float; DECLARE @command nvarchar(4000); SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag INTO #work_to_do FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do; OPEN partitions; WHILE (1=1) BEGIN; FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; IF @@FETCH_STATUS < 0 BREAK; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid; IF @frag < 30.0 SET @command = N'ALTER INDEX ' @indexname N' ON ' @schemaname N'.' @objectname N' REORGANIZE'; IF @frag >= 30.0 SET @command = N'ALTER INDEX ' @indexname N' ON ' @schemaname N'.' @objectname N' REBUILD'; IF @partitioncount > 1 SET @command = @command N' PARTITION=' CAST(@partitionnum AS nvarchar(10)); EXEC (@command); PRINT N'Executed: ' @command; END; CLOSE partitions; DEALLOCATE partitions; DROP TABLE #work_to_do; GO

Quick Links

Subscribe to this forum!