【sql】sql一些摘要
--1、临时表(DECLARE @details Table())
DECLARE @details Table(DTaskDetailsId [uniqueidentifier] NOT NULL,--任务id DAdvertCompanyFloorId [uniqueidentifier] NOT NULL,--楼盘id DFloorDiscName [nvarchar](100) NOT NULL,--楼盘名 DAdvertCompanyPointId [uniqueidentifier] NOT NULL,--点位id DMediaName [nvarchar](30) NOT NULL,--媒体位 DElevator [nvarchar](30) NOT NULL,--电梯 DUnitName [nvarchar](100) NOT NULL,--门洞 DFloorUnit [nvarchar](30) NOT NULL,--楼栋 DRegionName [nvarchar](100) NOT NULL --区域名称 )
--2、插入by查询(insert select)(和case when then)--条件中尽量不要(字符+、转换类型、isnotnull)操作字段本身
insert into @details select top (@top) d.[TaskDetailsId] ,CASE when acf.AdvertCompanyFloorId is null then @defaultGuid else acf.AdvertCompanyFloorId end ,d.[FloorDiscName] ,CASE when acpp.AdvertCompanyPointId is null then @defaultGuid else acpp.AdvertCompanyPointId end ,d.[MediaName] ,d.[Elevator] ,d.[UnitName] ,d.[FloorUnit] ,d.RegionName from Task_StageDetails d left join Advert_Company_FloorInfo acf on acf.AdvertCompanyFloorName =d.FloorDiscName and acf.AdvertCompanyId=@advertCompanyId left join [Advert_Company_PointPositionInfo] acpp on d.FloorUnit = acpp.TungName and d.UnitName = acpp.UnitName and (d.Elevator = acpp.ElevatorName or d.Elevator = acpp.StoreyName) and d.MediaName = acpp.MediaName and acf.AdvertCompanyFloorId=acpp.AdvertCompanyFloorId where PlanId=@planId and d.AdvertCompanyPointId=@defaultGuid
--3、更新by子查询(update selec)
--test select * from test1 --id number sort s --1 1 1 2021-01-16 18:02:40.193 --2 2 9 2021-01-16 18:02:40.193 --3 3 3 2021-01-16 18:02:40.193 select * from test2 --id number sort s -- 1 1 1 2020-09-19 15:25:07.007 -- 2 2 2 2020-09-19 15:25:07.007 update test1 set sort = (select top 1 sort from test2 where test1.number=test2.number ) where number in ( select t1.number from test1 t1 inner join test2 t2 on t1.number=t2.number ) go select * from test1 --id number sort s --1 1 1 2021-01-16 18:02:40.193 --2 2 2 2021-01-16 18:02:40.193 --3 3 3 2021-01-16 18:02:40.193 select * from test2 --id number sort s -- 1 1 1 2020-09-19 15:25:07.007 -- 2 2 2 2020-09-19 15:25:07.007
--4、linq的select实体内赋值时尽量少使用linq方法，会派生表后再查询一次，数据量大或者有多个linq方法时很慢。
--例如：下面linq中select的//detailsEmp.FirstOrDefault、// detailsEmp.Count,会执行多次查询Task_StageDetails。
--//join联表再Take(1)，和在select结果集中赋值查询FirstOrDefault，扫描计数逻辑读取次数是一样的。都是子查询TOP 1，再OUTER APPLY。
items = (from img in db.Set<Task_StagePlanImageName>() join plan in db.Set<Task_StagePlanInfo>() on img.PlanId equals plan.PlanId join details in db.Set<Task_StageDetails>().Where(where_detail) on new { img.PlanId, img.ImageName } equals new { details.PlanId, ImageName = details.NewAdvertName } join collect in db.Set<Task_JobNumberCollectImage>() on new { img.Id } equals new { Id = collect.ImageId } into collectEmp from collect in collectEmp.DefaultIfEmpty() select new View_Task_JobNumberCollectImage() { Plan = plan, ImageModel = img, ImageUrl = "",//detailsEmp.FirstOrDefault().NewAdvertImage, ImageTaskCount =0,// detailsEmp.Count(x => x.TaskDetailsId != Guid.Empty), JobNumberCollectImageCount = collect.Count, MediaSpecs = "",//detailsEmp.FirstOrDefault().MediaSpecs, CompanyFloorId = floorIdsIsNull ? Guid.Empty : details.CompanyFloorId, } ).Where(where_base);
--不好的linq生成的慢sql。
SELECT [Project8].[Sum] AS [Sum], [Project8].[PlanId1] AS [PlanId], [Project8].[ReleaseState] AS [ReleaseState], [Project8].[PlanNumber] AS [PlanNumber], [Project8].[PlanName] AS [PlanName], [Project8].[InstallTime] AS [InstallTime], [Project8].[ReleaseCount] AS [ReleaseCount], [Project8].[NotTakeCount] AS [NotTakeCount], [Project8].[NotSubmitCount] AS [NotSubmitCount], [Project8].[InstallSuccessCount] AS [InstallSuccessCount], [Project8].[FailEndCount] AS [FailEndCount], [Project8].[NotConfirmCount] AS [NotConfirmCount], [Project8].[CreateTime] AS [CreateTime], [Project8].[CreateUserName] AS [CreateUserName], [Project8].[TrialTime] AS [TrialTime], [Project8].[AdvertCompanyId] AS [AdvertCompanyId], [Project8].[CompanyId] AS [CompanyId], [Project8].[IssueState] AS [IssueState], [Project8].[InstallJobIds] AS [InstallJobIds], [Project8].[StartTime] AS [StartTime], [Project8].[EndTime] AS [EndTime], [Project8].[PlanBatchId] AS [PlanBatchId], [Project8].[Id] AS [Id], [Project8].[PlanId] AS [PlanId1], [Project8].[ImageName] AS [ImageName], [Project8].[CollectCount] AS [CollectCount], [Project8].[Number] AS [Number], [Project8].[C1] AS [C1], [Project8].[C3] AS [C2], [Project8].[Count] AS [Count], [Project8].[C2] AS [C3], CASE WHEN (@p__linq__2 = 1) THEN @p__linq__3 ELSE [Project8].[CompanyFloorId] END AS [C4] FROM ( SELECT [Project7].[Id] AS [Id], [Project7].[PlanId] AS [PlanId], [Project7].[ImageName] AS [ImageName], [Project7].[Sum] AS [Sum], [Project7].[CollectCount] AS [CollectCount], [Project7].[Number] AS [Number], [Project7].[PlanId1] AS [PlanId1], [Project7].[ReleaseState] AS [ReleaseState], [Project7].[PlanNumber] AS [PlanNumber], [Project7].[PlanName] AS [PlanName], [Project7].[InstallTime] AS [InstallTime], [Project7].[ReleaseCount] AS [ReleaseCount], [Project7].[NotTakeCount] AS [NotTakeCount], [Project7].[NotSubmitCount] AS [NotSubmitCount], [Project7].[InstallSuccessCount] AS [InstallSuccessCount], [Project7].[FailEndCount] AS [FailEndCount], [Project7].[NotConfirmCount] AS [NotConfirmCount], [Project7].[CreateTime] AS [CreateTime], [Project7].[CreateUserName] AS [CreateUserName], [Project7].[TrialTime] AS [TrialTime], [Project7].[AdvertCompanyId] AS [AdvertCompanyId], [Project7].[CompanyId] AS [CompanyId], [Project7].[IssueState] AS [IssueState], [Project7].[InstallJobIds] AS [InstallJobIds], [Project7].[StartTime] AS [StartTime], [Project7].[EndTime] AS [EndTime], [Project7].[PlanBatchId] AS [PlanBatchId], [Project7].[CompanyFloorId] AS [CompanyFloorId], [Project7].[Count] AS [Count], [Project7].[C1] AS [C1], [Project7].[C2] AS [C2], (SELECT COUNT(1) AS [A1] FROM [dbo].[Task_StageDetails] AS [Extent7] WHERE ([Extent7].[InstallJobNumberId] = @p__linq__0) AND ([Project7].[PlanId] = [Extent7].[PlanId]) AND ([Project7].[ImageName] = [Extent7].[NewAdvertName]) AND ([Extent7].[TaskDetailsId] <> @p__linq__1)) AS [C3] FROM ( SELECT [Project6].[Id] AS [Id], [Project6].[PlanId] AS [PlanId], [Project6].[ImageName] AS [ImageName], [Project6].[Sum] AS [Sum], [Project6].[CollectCount] AS [CollectCount], [Project6].[Number] AS [Number], [Project6].[PlanId1] AS [PlanId1], [Project6].[ReleaseState] AS [ReleaseState], [Project6].[PlanNumber] AS [PlanNumber], [Project6].[PlanName] AS [PlanName], [Project6].[InstallTime] AS [InstallTime], [Project6].[ReleaseCount] AS [ReleaseCount], [Project6].[NotTakeCount] AS [NotTakeCount], [Project6].[NotSubmitCount] AS [NotSubmitCount], [Project6].[InstallSuccessCount] AS [InstallSuccessCount], [Project6].[FailEndCount] AS [FailEndCount], [Project6].[NotConfirmCount] AS [NotConfirmCount], [Project6].[CreateTime] AS [CreateTime], [Project6].[CreateUserName] AS [CreateUserName], [Project6].[TrialTime] AS [TrialTime], [Project6].[AdvertCompanyId] AS [AdvertCompanyId], [Project6].[CompanyId] AS [CompanyId], [Project6].[IssueState] AS [IssueState], [Project6].[InstallJobIds] AS [InstallJobIds], [Project6].[StartTime] AS [StartTime], [Project6].[EndTime] AS [EndTime], [Project6].[PlanBatchId] AS [PlanBatchId], [Project6].[CompanyFloorId] AS [CompanyFloorId], [Project6].[Count] AS [Count], [Project6].[C1] AS [C1], [Project6].[C2] AS [C2] FROM ( SELECT [Project4].[Id] AS [Id], [Project4].[PlanId] AS [PlanId], [Project4].[ImageName] AS [ImageName], [Project4].[Sum] AS [Sum], [Project4].[CollectCount] AS [CollectCount], [Project4].[Number] AS [Number], [Project4].[PlanId1] AS [PlanId1], [Project4].[ReleaseState] AS [ReleaseState], [Project4].[PlanNumber] AS [PlanNumber], [Project4].[PlanName] AS [PlanName], [Project4].[InstallTime] AS [InstallTime], [Project4].[ReleaseCount] AS [ReleaseCount], [Project4].[NotTakeCount] AS [NotTakeCount], [Project4].[NotSubmitCount] AS [NotSubmitCount], [Project4].[InstallSuccessCount] AS [InstallSuccessCount], [Project4].[FailEndCount] AS [FailEndCount], [Project4].[NotConfirmCount] AS [NotConfirmCount], [Project4].[CreateTime] AS [CreateTime], [Project4].[CreateUserName] AS [CreateUserName], [Project4].[TrialTime] AS [TrialTime], [Project4].[AdvertCompanyId] AS [AdvertCompanyId], [Project4].[CompanyId] AS [CompanyId], [Project4].[IssueState] AS [IssueState], [Project4].[InstallJobIds] AS [InstallJobIds], [Project4].[StartTime] AS [StartTime], [Project4].[EndTime] AS [EndTime], [Project4].[PlanBatchId] AS [PlanBatchId], [Project4].[CompanyFloorId] AS [CompanyFloorId], [Project4].[Count] AS [Count], [Project4].[C1] AS [C1], (SELECT TOP (1) [Extent6].[MediaSpecs] AS [MediaSpecs] FROM [dbo].[Task_StageDetails] AS [Extent6] WHERE ([Extent6].[InstallJobNumberId] = @p__linq__0) AND ([Project4].[PlanId] = [Extent6].[PlanId]) AND ([Project4].[ImageName] = [Extent6].[NewAdvertName])) AS [C2] FROM ( SELECT [Project3].[Id] AS [Id], [Project3].[PlanId] AS [PlanId], [Project3].[ImageName] AS [ImageName], [Project3].[Sum] AS [Sum], [Project3].[CollectCount] AS [CollectCount], [Project3].[Number] AS [Number], [Project3].[PlanId1] AS [PlanId1], [Project3].[ReleaseState] AS [ReleaseState], [Project3].[PlanNumber] AS [PlanNumber], [Project3].[PlanName] AS [PlanName], [Project3].[InstallTime] AS [InstallTime], [Project3].[ReleaseCount] AS [ReleaseCount], [Project3].[NotTakeCount] AS [NotTakeCount], [Project3].[NotSubmitCount] AS [NotSubmitCount], [Project3].[InstallSuccessCount] AS [InstallSuccessCount], [Project3].[FailEndCount] AS [FailEndCount], [Project3].[NotConfirmCount] AS [NotConfirmCount], [Project3].[CreateTime] AS [CreateTime], [Project3].[CreateUserName] AS [CreateUserName], [Project3].[TrialTime] AS [TrialTime], [Project3].[AdvertCompanyId] AS [AdvertCompanyId], [Project3].[CompanyId] AS [CompanyId], [Project3].[IssueState] AS [IssueState], [Project3].[InstallJobIds] AS [InstallJobIds], [Project3].[StartTime] AS [StartTime], [Project3].[EndTime] AS [EndTime], [Project3].[PlanBatchId] AS [PlanBatchId], [Project3].[CompanyFloorId] AS [CompanyFloorId], [Project3].[Count] AS [Count], [Project3].[C1] AS [C1] FROM ( SELECT [Project1].[Id] AS [Id], [Project1].[PlanId] AS [PlanId], [Project1].[ImageName] AS [ImageName], [Project1].[Sum] AS [Sum], [Project1].[CollectCount] AS [CollectCount], [Project1].[Number] AS [Number], [Project1].[PlanId1] AS [PlanId1], [Project1].[ReleaseState] AS [ReleaseState], [Project1].[PlanNumber] AS [PlanNumber], [Project1].[PlanName] AS [PlanName], [Project1].[InstallTime] AS [InstallTime], [Project1].[ReleaseCount] AS [ReleaseCount], [Project1].[NotTakeCount] AS [NotTakeCount], [Project1].[NotSubmitCount] AS [NotSubmitCount], [Project1].[InstallSuccessCount] AS [InstallSuccessCount], [Project1].[FailEndCount] AS [FailEndCount], [Project1].[NotConfirmCount] AS [NotConfirmCount], [Project1].[CreateTime] AS [CreateTime], [Project1].[CreateUserName] AS [CreateUserName], [Project1].[TrialTime] AS [TrialTime], [Project1].[AdvertCompanyId] AS [AdvertCompanyId], [Project1].[CompanyId] AS [CompanyId], [Project1].[IssueState] AS [IssueState], [Project1].[InstallJobIds] AS [InstallJobIds], [Project1].[StartTime] AS [StartTime], [Project1].[EndTime] AS [EndTime], [Project1].[PlanBatchId] AS [PlanBatchId], [Project1].[CompanyFloorId] AS [CompanyFloorId], [Project1].[Count] AS [Count], (SELECT TOP (1) [Extent5].[NewAdvertImage] AS [NewAdvertImage] FROM [dbo].[Task_StageDetails] AS [Extent5] WHERE ([Extent5].[InstallJobNumberId] = @p__linq__0) AND ([Project1].[PlanId] = [Extent5].[PlanId]) AND ([Project1].[ImageName] = [Extent5].[NewAdvertName])) AS [C1] FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[PlanId] AS [PlanId], [Extent1].[ImageName] AS [ImageName], [Extent1].[Sum] AS [Sum], [Extent1].[CollectCount] AS [CollectCount], [Extent1].[Number] AS [Number], [Extent2].[PlanId] AS [PlanId1], [Extent2].[ReleaseState] AS [ReleaseState], [Extent2].[PlanNumber] AS [PlanNumber], [Extent2].[PlanName] AS [PlanName], [Extent2].[InstallTime] AS [InstallTime], [Extent2].[ReleaseCount] AS [ReleaseCount], [Extent2].[NotTakeCount] AS [NotTakeCount], [Extent2].[NotSubmitCount] AS [NotSubmitCount], [Extent2].[InstallSuccessCount] AS [InstallSuccessCount], [Extent2].[FailEndCount] AS [FailEndCount], [Extent2].[NotConfirmCount] AS [NotConfirmCount], [Extent2].[CreateTime] AS [CreateTime], [Extent2].[CreateUserName] AS [CreateUserName], [Extent2].[TrialTime] AS [TrialTime], [Extent2].[AdvertCompanyId] AS [AdvertCompanyId], [Extent2].[CompanyId] AS [CompanyId], [Extent2].[IssueState] AS [IssueState], [Extent2].[InstallJobIds] AS [InstallJobIds], [Extent2].[StartTime] AS [StartTime], [Extent2].[EndTime] AS [EndTime], [Extent2].[PlanBatchId] AS [PlanBatchId], [Extent3].[CompanyFloorId] AS [CompanyFloorId], [Extent4].[Count] AS [Count] FROM [dbo].[Task_StagePlanImageName] AS [Extent1] INNER JOIN [dbo].[Task_StagePlanInfo] AS [Extent2] ON [Extent1].[PlanId] = [Extent2].[PlanId] LEFT OUTER JOIN [dbo].[Task_StageDetails] AS [Extent3] ON ([Extent3].[InstallJobNumberId] = @p__linq__0) AND ([Extent1].[PlanId] = [Extent3].[PlanId]) AND ([Extent1].[ImageName] = [Extent3].[NewAdvertName]) LEFT OUTER JOIN [dbo].[Task_JobNumberCollectImage] AS [Extent4] ON [Extent1].[Id] = [Extent4].[ImageId] ) AS [Project1] ) AS [Project3] ) AS [Project4] ) AS [Project6] ) AS [Project7] ) AS [Project8] WHERE [Project8].[C3] > 0
--5、sqlserver执行大量数据update select时，用中间表代替select的大量数据，避免执行慢(推测原因是阻塞和锁，以及每一次子查询的where执行消耗)。
--任务视图中间表更新 truncate table Table_TaskNoAdvertPoint insert into Table_TaskNoAdvertPoint select distinct d.[TaskDetailsId] as DTaskDetailsId ,d.PlanId as DPlanId ,CASE when acf.AdvertCompanyFloorId is null then '00000000-0000-0000-0000-000000000000' else acf.AdvertCompanyFloorId end as DAdvertCompanyFloorId --,acf.AdvertCompanyFloorId as DAdvertCompanyFloorId ,d.[FloorDiscName] as DFloorDiscName ,CASE when acpp.AdvertCompanyPointId is null then '00000000-0000-0000-0000-000000000000' else acpp.AdvertCompanyPointId end as DAdvertCompanyPointId --,acpp.AdvertCompanyPointId as DAdvertCompanyPointId ,d.[MediaName] as DMediaName ,d.[Elevator] as DElevator ,d.[UnitName] as DUnitName ,d.[FloorUnit] as DFloorUnit ,d.RegionName as DRegionName from Task_StageDetails d left join Advert_Company_FloorInfo acf on acf.AdvertCompanyFloorName =d.FloorDiscName and acf.AdvertCompanyId=d.AdvertCompanyId left join [Advert_Company_PointPositionInfo] acpp on d.FloorUnit = acpp.TungName and d.UnitName = acpp.UnitName and (d.Elevator = acpp.ElevatorName or d.Elevator = acpp.StoreyName) and d.MediaName = acpp.MediaName and acf.AdvertCompanyFloorId=acpp.AdvertCompanyFloorId where d.AdvertCompanyPointId= '00000000-0000-0000-0000-000000000000' and d.planid=@planId --匹配楼盘 update Task_StageDetails set AdvertCompanyFloorId = (select top 1 DAdvertCompanyFloorId from Table_TaskNoAdvertPoint d where Task_StageDetails.TaskDetailsId=d.DTaskDetailsId ) where Task_StageDetails.TaskDetailsId in (select DTaskDetailsId from Table_TaskNoAdvertPoint where DPlanId=@planId ) --匹配点位 update Task_StageDetails set AdvertCompanyPointId = (select top 1 DAdvertCompanyPointId from Table_TaskNoAdvertPoint d where Task_StageDetails.TaskDetailsId=d.DTaskDetailsId ) where Task_StageDetails.TaskDetailsId in (select DTaskDetailsId from Table_TaskNoAdvertPoint where DPlanId=@planId )
--6、查看sql执行的计划
DBCC DROPCLEANBUFFERS --清空缓存
-- set statistics profile on
-- SET STATISTICS TIME ON
SET STATISTICS IO ON --开启IO统计
逻辑读取和LOB逻辑读取，是在性能调优时的重点。通常创建合适的索引或重写查询可以彻底降低。
https://www.cnblogs.com/woodytu/p/4535658.html 扫描计数（Scan count）： 根据微软在线帮助，扫描计数是在任何方向都达到叶级别后启动的查询/扫描数，目的在于检索用于构造输出的最终数据集的所有值。 如果使用的索引是主键的唯一索引或聚集索引并且您仅查找一个值，则扫描计数为 0。 例如 WHERE Primary_Key_Column = <value>。 当您使用对非主键列定义的非唯一的聚集索引搜索一个值时，扫描计数为 1。 这是为了针对您正在搜索的键值检查重复值。 例如 WHERE Clustered_Index_Key_Column = <value>。 当 N 为通过使用索引键定位键值后，在叶级别的左侧或右侧启动的不同查找/扫描数时，则扫描计数为 N。 这个数字告诉我们优化器所选择的计划，对这个对象的重复读取次数。很多人误以为这个是对整张表的读取次数，这是完全错误的。 我们通过一个例子来理解扫描计数。 复制代码 1 CREATE TABLE ScanCount (Id INT IDENTITY(1,1),Value CHAR(1)) 2 INSERT INTO ScanCount (Value ) VALUES ('A') ,('B'),('C'),('D'), ('E') , ('F') 3 CREATE UNIQUE CLUSTERED INDEX ix_ScanCount ON ScanCount(Id) 4 5 SET STATISTICS IO ON 6 --Unique clustered Index used to search single value 7 SELECT * FROM ScanCount WHERE Id =1 8 --Unique clustered Index used to search multiple value 9 SELECT * FROM ScanCount WHERE Id IN(1,2,3,4,5,6) 10 --Unique clustered Index used to search multiple value 11 SELECT * FROM ScanCount WHERE Id BETWEEN 1 AND 6 复制代码 我们来看下上面3个查询语句的输出。 在第1个SELECT语句的输出里，扫描计数为0。这和MSDN里在线帮助“如果使用的索引是主键的唯一索引或聚集索引并且您仅查找一个值，则扫描计数为 0。”描述一致。因为它是唯一索引（聚集/非聚集索引），不需要在叶子层，进行进一步的向左或向右扫描，因为这里只有一个值来匹配。那也是在唯一索引上查找单一值，扫描计数为0的原因。扫描计数是1的话，会在非唯一索引（聚集或非聚集索引）上发生。 对于第2个SELECT语句，扫描计数是6.这是因为我们在找多个不同值。MSDN在线帮助对此有详细说明： “如果使用的索引是主键的唯一索引或非聚集索引，你在查找N个值，则扫描计数为N。”。 我们来看看执行计划里的SEEK谓语，将更清晰: 即使只有一个where条件，还是会分裂成多个谓语。对于每个SEEK谓语，它会生成1个扫描数。 对于最后一个SELECT语句，扫描计数为1，因为MSDN在线帮助说了： “当 N 为通过使用索引键定位键值后，在叶级别的左侧或右侧启动的不同查找/扫描数时，则扫描计数为 N。” 在叶子节点聚集索引结构用来找到1值后，叶子层的向左扫描开始，直到找到值6。我们看下执行计划里的SEEK 谓语，将更清晰： 逻辑读取（logical Read）： 从数据缓存读取的页数。数字越小，性能越好。在性能调优中这个数字非常重要。因为它不会随着执行又执行而改变，除非数据或查询语句有变动。在进行性能调优时，这个可以作为性能提升的重要参考。 物理读取（physical reads）： 从磁盘读取的页数。这个会随着执行又执行而改变。大多数情况下，连续第2次的执行时，它的物理读取值为0（可以参考上面连续查询的物理读取数变化）。 如果连续执行后，物理读取次数下降了，我们可以假定是服务器上内存使用配置的错误，或者服务器工作量饱和，有内存压力。你需要在服务器级别思考问题的原因。在查询调优时，这个数字不太重要，因为它一直在变，对于下降这个值，你不能对它做出太多控制。 预读 （read-ahead reads）： 为进行查询而放入缓存的页数。这个值告诉我们物理页读取数，即SQL Server执行的，作为预读机制的一部分。在查询执行请求那些可能用到页之前，SQL Server把物理数据页读入缓存，用于完成接下来查询的页需要。 可以看到，物理读取是2次，预读是946次。这就是说，查询执行请求了2个页，并预读了946个页到数据缓存，SQL Server估计下次查询可能要用到这些页。和物理读取一样，这个值对在查询调优里并不重要。 lob 逻辑读取（lob logical reads）： 从数据缓存读取的 text、ntext、image 或大值类型 (varchar(max)、nvarchar(max)、varbinary(max)) 页的数目。这个和逻辑读一样重要，我们要非常重视。 lob 物理读取（lob physical reads）： 从磁盘读取的 text、ntext、image 或大值类型页的数目。 lob 预读（lob read-ahead reads）： 为进行查询而放入缓存的 text、ntext、image 或大值类型页的数目。 总结下，逻辑读取和LOB逻辑读取是2个重要数值，在性能调优时，我们要重点围观。如果把这2个值调低，不在本文的讨论范围。通常创建合适的索引或重写查询可以帮助我们彻底降低这2个值。
--7、查询是否区分大小写
设置某表某列: alter table TableName ALTER Column ColumnName nvarchar(200) COLLATE Chinese_PRC_CS_AS --(Chinese_PRC_CS_AS 区分，Chinese_PRC_CI_AS 不区分)
当次查询:
select * from TableName where ColumnName collate Chinese_PRC_CS_AS=‘value‘ --区分
select * from TableName where ColumnName collate Chinese_PRC_CS_AS like ‘value%‘ --不区分
或者转二进制查询。
--8、left join和where in相遇
//where存在in时，select列或group列包含left join的表字段，会慢。使用inner或where的in前面增加使用到leftjoin的表的字段增加条件 is not null。
例如
//--视图内有leftjoin，使用in条件需要再in前加left的非空条件
List<View_OrderGetNucleateView> nucleates = DBSession.IView_OrderGetNucleateViewDAL.GetListViewBy(x =>x.SampleNumber!=null && batchNumbers.Contains(x.BatchNumberNucleicAcid));
例如
SELECT dbo.Order_GetNucleate.BatchNumberNucleicAcid
,dbo.Order_GetNucleate.StateNucleicAcid
,dbo.Member_PetSampleBox.SampleNumber
,count(dbo.Member_PetSampleBox.SampleNumber) as [Count]
FROM dbo.Order_GetNucleate
left JOIN dbo.Member_PetSampleBox ON dbo.Member_PetSampleBox.PetIds = dbo.Order_GetNucleate.PetIds
AND dbo.Member_PetSampleBox.IsEffective = 1
AND dbo.Member_PetSampleBox.SampleType = dbo.Order_GetNucleate.SampleType
AND dbo.Member_PetSampleBox.BoxType != 1
--这里需要加is not null，或改成inner join
where dbo.Member_PetSampleBox.SampleNumber is not null and BatchNumberNucleicAcid in ('20230626-3C','20230626-2C','20230626-1Y','20230621-1Y','20230620-2C','20230620-1Y','20230619-1Y','20230618-2C','20230618-1Y','20230616-2C','20230616-1Y','20230615-1Y','20230614-2C','20230614-1Y','20230613-1Y','20230612-3C','20230612-2Y','20230612-1Y','20230609-1Y','20230608-1Y')
group by dbo.Order_GetNucleate.BatchNumberNucleicAcid
,dbo.Order_GetNucleate.StateNucleicAcid
,dbo.Member_PetSampleBox.SampleNumber
--9、修改表名列名
--例：
use SmartLockAccountAndOrderDB
go
--修改表名
exec sp_rename 'Lock_OpenConfig','Lock_OpenConfigOrder'
go
--修改表Lock_OpenConfigOrder的列名updated，改为Updated。
exec sp_rename 'Lock_OpenConfigOrder.updated','Updated','COLUMN'
--10、记录事务和锁
--事务隔离级别，读未提交 READ UNCOMMITTED，读已提交 Read Committed，可重复读 Repeatable Read，序列化 Serializable。
--隔离级别当前事务去操作其他事务锁住资源的规则，和其他事务操作当前事务锁住资源的规则。
--读未提交：READ UNCOMMITTED
--会读取其他事务未提交的可变数据，不阻塞当前事务查询。
--读操作不申请共享锁，不阻塞其他事务更新。
--读已提交：Read Committed
--不会读取到其他事务未提交的可变数据，不阻塞当前事务查询。
--读完释放共享锁，不阻塞由其他事务更新。
--可重复读：Repeatable Read
--在一个事务中对相同KEY多次读取是重复的，阻塞首次查询可变数据。
--如果先查询过KEY，其他事物再操作，二次查询相同的KEY不阻塞。当前事务没查过的，其他事务更新或新增的未提交KEY，则是首次查询会阻塞。
--读取申请共享锁保持到当前事务完成，阻塞其他事务更新当前事务查询过的数据。
--读取不申请域锁，不阻塞其他事务新增。
--序列化：Serializable
--每次都从数据库读取，阻塞首次查询可变数据。
--如果先查询过KEY，其他事物再操作，二次查询相同的KEY不阻塞，不会查询到其他事务新增的未提交KEY。
--读取申请共享锁保持到当前事务完成，阻塞其他事务更新当前事务查询过的数据。
--读取申请域锁，阻塞其他事务新增当前事务查询过的域(where条件的域，比如当前事务进行用户手机号验重，第二个事物添加相同手机号阻塞)。
--新增和更新申请域锁X，阻塞第二个序列化事务查询当前事务set修改的字段。
--共享锁造成的阻塞才可以降低事务隔离级别。因为排他锁都是修改或新增申请，直到事务结束才释放。
--////使用事务TransactionScope-Serializable或可重复读级别，//如果在事务内查询表A，再修改或新增表A，并发会死锁。两个线程申请共享锁S，又同时申请排他锁X。
--////单程序系统可以使用快照隔离级别加代码锁lock，实现数据验重和查旧数据计算新值。是把事务阻塞转移到了代码锁，但代码锁只适合单程序系统。
--////查询旧值最好用主键减少行锁，计如果算新值允许负值，可以在更新语句中计算，减少事务内查询。
--////验重有条件的还是用唯一索引限制，事务内查询验重还是容易有阻塞。
--要在事务中实现数据验重和查旧数据计算新值,可以在事务中先查询需要锁住的数据，并手动使用锁。注意给验重查询where的字段增加索引或用主键查询，减少行锁。
--手动加更新锁U阻止并发事务更新。对于查询旧值重新计算的情况，应该加排他锁X，其他事务不能查询了尽量用主键查询避免阻塞。
set transaction isolation level Serializable -- Repeatable Read
begin tran
--手动加更新锁U并保持不释放，不阻塞其他线程默认查询申请S锁。并发线程会阻塞更新。
--查询字段是主键时TAB:IX，PAG:IU，KEY:U。
--select * from Temp_Json WITH (UPDLOCK, HOLDLOCK) where IntId=20
--查询条件字段没有索引时TAB:X。即使加了索引没查到数据也容易有阻塞，最好还是用唯一索引限制重复。
--select * from Temp_PakResult WITH (UPDLOCK, HOLDLOCK) where PakResult='333'
--查询字段有索引，没查询到数据PAG:IU，PAG:IU，TAB:IX，KEY:RangeS-U
--select * from Order_Main WITH (UPDLOCK, HOLDLOCK) where OrderNo='333'
--查询字段有索引，有查询到数据PAG:IU，PAG:IU，TAB:IX，KEY:U，KEY:U
--select * from Order_Main WITH (UPDLOCK, HOLDLOCK) where OrderNo='2019072505440286d6'
--手动加排他锁，KEY:X，TAB:IX，PAG:IX
--select * from Temp_Json WITH (XLOCK, HOLDLOCK) where IntId=20
--waitfor delay '00:00:5'--等待秒
--Update Temp_Json set [Json]='222' where IntId=20
-- INSERT INTO [dbo].[Temp_PakResult] ([Id] ,[PakResult]) VALUES (newid(),CONVERT(varchar(100), GETDATE(), 121))
--INSERT INTO [dbo].[Temp_PakResult] ([Id] ,[PakResult]) VALUES (newid(),'333')
waitfor delay '00:00:5'--等待秒
--rollback tran
commit tran
--查看所有锁
EXEC sp_lock;
--查看表的锁
SELECT
request_session_id AS 'Session ID',
resource_type AS 'Resource Type',
resource_associated_entity_id AS 'Resource ID',
resource_database_id AS 'Database ID',
request_mode AS 'Lock Requested',
request_status AS 'Lock Status'
FROM
sys.dm_tran_locks
WHERE
resource_database_id = DB_ID() AND
resource_associated_entity_id = OBJECT_ID('Temp_Json') -- OBJECT_ID 表名
浙公网安备 33010602011771号