Jefferson's questions

我有此存储过程，我希望根据 AgentId 或不使用 AgentId 获得最大 startDate。我执行此操作的方法是使用 if else，想看看是否有更好的方法来实现此目的？

  IF (@AgentId = 0)
      BEGIN
          SELECT top (1)max(cgv.StartDate) as AgentLatestPublishedDate,ag.Name
          FROM compendia.Agent ag
          JOIN compendia.DrugCompendium AS dc ON dc.AgentId = ag.OriginalAgentId 
          JOIN compendia.CompendiaGuidelineVersion cgv ON cgv.CompendiaGuidelineVersionId = dc.CompendiaGuidelineVersionId
          JOIN guideline.Disease AS d ON d.DiseaseId = dc.DiseaseId
          WHERE cgv.WorkFlowStatusId = 6 AND ag.EndDate IS NULL AND dc.IsNoLongerRecommended = 0
          group by ag.AgentId,ag.Name
      end

    ELSE
    BEGIN
          SELECT max(cgv.StartDate) as AgentLatestPublishedDate,ag.Name
          FROM compendia.Agent ag
          JOIN compendia.DrugCompendium AS dc ON dc.AgentId = ag.OriginalAgentId 
          JOIN compendia.CompendiaGuidelineVersion cgv ON cgv.CompendiaGuidelineVersionId = dc.CompendiaGuidelineVersionId
          JOIN guideline.Disease AS d ON d.DiseaseId = dc.DiseaseId
          WHERE cgv.WorkFlowStatusId = 6 AND ag.EndDate IS NULL AND dc.IsNoLongerRecommended = 0 and ag.AgentId = @AgentId
          group by ag.AgentId,ag.Name
    end

我正在尝试从 SiteConfiguration 表中的数据获取客户表。

我正在获取数据并将其设置为@AdditionalVoteViewers，这将是一个电子邮件地址字符串。然后我拆分字符串并将结果插入到临时表中。

此时，我将临时表与 Customer 表连接起来以返回CustomerId。

我只是想看看是否有更好的方法来做到这一点。

站点配置表中的数据

[email protected];[email protected]

客户表中的数据

[email protected] 1
[email protected] 2

代码：

declare @AdditionalVoteViewers VARCHAR(100) 
Select @AdditionalVoteViewers = 
(
    SELECT [Value] FROM [SiteConfiguration] 
    where Name = 'AdditionalVoteViewers'
)
print @AdditionalVoteViewers
--CREATE TABLE #TempAdditionalVoteViewers(email  varchar(MAX) )

select * into #TempAdditionalVoteViewers 
from [dbo].[Split] (@AdditionalVoteViewers,';')
--select * from #TempAdditionalVoteViewers

select [CustomerId] into #TempAdditionalVoteViewersId
from [dbo].[Customer] c 
join #TempAdditionalVoteViewers t on c.Email = t.val


select * from #TempAdditionalVoteViewersId

我可以做些什么来提高查询性能吗？我已经包含了执行计划和服务器分析器输出。有索引可以帮忙吗？我发现索引查找需要一些时间，我可以做些什么来帮助解决这个问题吗？在服务器配置文件中，我看到读取次数看起来​​很高，有什么办法可以帮助解决这个问题吗？

SELECT dbo.Templates.VersionKey
FROM            dbo.Templates INNER JOIN
                         dbo.Indications ON dbo.Templates.Id = dbo.Indications.TemplateId INNER JOIN
                         dbo.Sentences ON dbo.Indications.SentenceId = dbo.Sentences.Id INNER JOIN
                         dbo.SentenceParts ON dbo.Sentences.Id = dbo.SentenceParts.SentenceId INNER JOIN
                         dbo.SentencePartValues ON dbo.SentenceParts.Id = dbo.SentencePartValues.SentencePartId
WHERE        (dbo.Templates.Status <> 8) AND 
(dbo.Templates.IsCurrentVersion = 1) AND 
(dbo.Indications.IsActive = 1) AND 
(dbo.Indications.IsCurrentVersion = 1)  
AND (NOT (Sentences.[IsDeleted] = 1)) 
AND (NOT (SentenceParts.[IsDeleted] = 1)) 
AND (NOT (SentencePartValues.[IsDeleted] = 1)) 
and
(SUBSTRING(dbo.SentencePartValues.LiteralValue, 12, 36) = 'a2278339-8add-ff3d-8baa-9654b8b8d00b')

执行计划 https://www.brentozar.com/pastetheplan/?id=By68aKtfp

SQL Server 探查器

我有两个表 CustomerProfile 和 NewData。CustomerProfile 有 CustomerId、ProfileId、startDate 和 endDate。NewData 表是传入的新数据，只有 ProfileId。我创建了一个临时表，其中包含两个名为 #groupedProfileIds 的表的 id。然后我循环临时表检查 id 是否在任何主表中。然后根据我执行 insert 或 update 的计数。我曾尝试使用 while 并检查两个表的计数，但想看看是否有更好的方法。

这样做的目的是在 newData 表的基础上更新 CustomerProfile 表。如果 id 不在 newData 表中，则 CustomerProfile 表将更新结束日期。如果它们是新的 ID，它们将被插入一个开始日期。我使用'@OldProfileId = 1 和@NewProfileId = 0'进行更新，因为数据不在新表中。对于插入，我执行“@OldProfileId = 0 和 @NewProfileId = 1”，因为数据就在 newData 中。我不需要检查 1 ，因为它们都在 table 中。

http://sqlfiddle.com/#!18/73845

            select * INTO #groupedProfileIds from(
            SELECT ProfileId FROM CustomerProfile
                UNION 
                SELECT ProfileId FROM NewData)  as #tmp

            declare @Rowcount int 
            select @Rowcount=count(*) from #groupedProfileIds
            while( @Rowcount>0)
            begin 
             select @Rowcount=@Rowcount-1;
             DECLARE @ProfileIds int
             set @ProfileIds = (SELECT Id FROM #groupedProfileIds order by Id desc OFFSET @Rowcount ROWS FETCH NEXT 1 ROWS ONLY);
             print @ProfileIds

             DECLARE @OldProfileId INT 
             SET @OldProfileId = (SELECT Count(ProfileId) FROM CustomerProfile where ProfileId = @ProfileIds)

             DECLARE @NewProfileId INT
             SET @NewProfileId = (SELECT Count(ProfileId) FROM #NewData where ProfileId = @ProfileIds)

             IF @OldProfileId = 1 and @NewProfileId = 0
             BEGIN
                update DBO.CustomerProfile set endDate = GETDATE() where ProfileId = @ProfileIds 

             end

             IF @OldProfileId = 0 and @NewProfileId = 1
             BEGIN
                INSERT INTO DBO. CustomerProfile (CustomerId, ProfileId,startDate)
                VALUES (CustomerId,@ProfileIds,GETDATE())
             end

我通过在我的存储过程中连接 @whereClause 变量来构建这个 where 条件。在这一点上，我正在使用案例来添加附加值。如果有更好的方法来做到这一点？

SET @whereClause  = 
    ( CASE  
        -- APPType Clinical
         WHEN (@ApplicationType = 'Clinical') THEN @whereClause + ' AND vpgl.AppType = Clinical'
         -- APPType Patients
         WHEN (@ApplicationType = 'Patients') THEN @whereClause + ' AND vpgl.AppType = Patients'
          -- APPType Web
         WHEN (@ApplicationType = 'Web') THEN @whereClause + ' AND vpgl.AppType = null'
         ELSE @whereClause
       END  
    ) 

使用介于或小于之间的速度和减少数据库压力的首选方法是什么？

SELECT LogId
FROM [Master].[dbo].log mlog
WHERE cast(LogDate as Date) BETWEEN '2019-01-01' AND '2019-12-31'

或者，这样做会更好吗？

SET @StartLogId = (SELECT TOP 1 LogID
FROM [Master].[dbo].log mlog
WHERE LogDate >= '2019-01-01T00:00:00.000')

SET @EndLogId = (SELECT TOP 1 LogID
FROM [Master].[dbo].log mlog
WHERE LogDate < '2019-12-31T00:00:00.000'
ORDER BY LogDate DESC);

SELECT LogId
FROM [Master].[dbo].log mlog
WHERE mlog.LogId >= @StartLogId
    AND mlog.LogId <= @EndLogId;
```