SQL 性能调优

1. Select(1) 优于 Select(*)

2.In and Exist

in是给外表和内表做hash链接,而Exist是对外表做Loop循环,每次loop循环再做内表查询,如果两个表大小相似,in和Exists差别不大.

如果两个表中一个表大一个表小,子查询大的用Exist,子查询小的用in.

3.计算表中指定时间段的行数,通过先挑出这段时间的最大最小值然后count(id),如下:DataPointPerSensor.sql (33 minutes) DPNumberPerSensor.sql(16 minutes)

DataPointPerSensor.sql

--this script used to calculate different sensor type of datapoint
select
count(Mll.ID) as [Loc]
from [Tracks].[dbo].[MonitorLocationLog] MLL
where MLL.RowCreatedOn >= '2016-01-01' 
and MLL.RowCreatedOn <= '2017-01-01' 

select 
count(1) as [Latitude]
from [Tracks].[dbo].[MonitorSensorLog] MSL
where msl.SensorType='Latitude' and 
MSL.RowCreatedOn >= '2016-01-01' 
and MSL.RowCreatedOn <= '2017-01-01'

select 
count(1) as [TemperatureExternal]
from [Tracks].[dbo].[MonitorSensorLog] MSL
where msl.SensorType='TemperatureExternal' and 
MSL.RowCreatedOn >= '2016-01-01' 
and MSL.RowCreatedOn <= '2017-01-01'

select 
count(1) as [TemperatureInternal]
from [Tracks].[dbo].[MonitorSensorLog] MSL
where msl.SensorType='TemperatureInternal' and 
MSL.RowCreatedOn >= '2016-01-01' 
and MSL.RowCreatedOn <= '2017-01-01'


select 
count(1) as [BatteryExternal]
from [Tracks].[dbo].[MonitorSensorLog] MSL
where msl.SensorType='BatteryExternal' and 
MSL.RowCreatedOn >= '2016-01-01' 
and MSL.RowCreatedOn <= '2017-01-01'



select 
count(1) as [BatteryInternal]
from [Tracks].[dbo].[MonitorSensorLog] MSL
where msl.SensorType='BatteryInternal' and 
MSL.RowCreatedOn >= '2016-01-01' 
and MSL.RowCreatedOn <= '2017-01-01'

select 
count(1) as [Rssi]
from [Tracks].[dbo].[MonitorSensorLog] MSL
where msl.SensorType='Rssi' and 
MSL.RowCreatedOn >= '2016-01-01' 
and MSL.RowCreatedOn <= '2017-01-01'


select 
count(1) as [Motion]
from [Tracks].[dbo].[MonitorSensorLog] MSL
where msl.SensorType='Motion' and 
MSL.RowCreatedOn >= '2016-01-01' 
and MSL.RowCreatedOn <= '2017-01-01'


select 
count(1) as [MotionInferred]
from [Tracks].[dbo].[MonitorSensorLog] MSL
where msl.SensorType='MotionInferred' and 
MSL.RowCreatedOn >= '2016-01-01' 
and MSL.RowCreatedOn <= '2017-01-01'

View Code

DPNumberPerSensor.sql

--this script used to calculate different sensor type of datapoint
declare @firstLocID nvarchar(100)
declare @lastLocID nvarchar(100)
declare @firstSensorID nvarchar(100)
declare @lastSensorID nvarchar(100)


set @firstLocID=(select top 1 (ID) from [Tracks].[dbo].[MonitorLocationLog] MLL
where mll.RowCreatedOn>='2016-01-01'
order by id)

set @lastLocID=(select top 1(ID) as lastID from [Tracks].[dbo].[MonitorLocationLog] MLL
where mll.RowCreatedOn<='2017-01-01'
order by id desc)


set @firstSensorID=(select top 1 (ID) from [Tracks].[dbo].[MonitorSensorLog] MSL
where MSL.RowCreatedOn>='2016-01-01'
order by id)

set @lastSensorID=(select top 1(ID) as lastID from [Tracks].[dbo].[MonitorSensorLog] MSL
where MSL.RowCreatedOn<='2017-01-01'
order by id desc)

begin

select
count(Mll.ID) as [Loc]
from [Tracks].[dbo].[MonitorLocationLog] MLL
where MLL.ID >= @firstLocID
and MLL.ID <= @lastLocID 


select 
count(1) as [TemperatureExternal]
from [Tracks].[dbo].[MonitorSensorLog] MSL
where msl.SensorType='TemperatureExternal' and 
MSL.ID >= @firstSensorID
and MSL.ID <= @lastSensorID 

select 
count(1) as [TemperatureInternal]
from [Tracks].[dbo].[MonitorSensorLog] MSL
where msl.SensorType='TemperatureInternal' and 
MSL.ID >= @firstSensorID
and MSL.ID <= @lastSensorID 


select 
count(1) as [Light]
from [Tracks].[dbo].[MonitorSensorLog] MSL
where msl.SensorType='Light' and 
MSL.ID >= @firstSensorID
and MSL.ID <= @lastSensorID 

select 
count(1) as [BatteryExternal]
from [Tracks].[dbo].[MonitorSensorLog] MSL
where msl.SensorType='BatteryExternal' and 
MSL.ID >= @firstSensorID
and MSL.ID <= @lastSensorID 



select 
count(1) as [BatteryInternal]
from [Tracks].[dbo].[MonitorSensorLog] MSL
where msl.SensorType='BatteryInternal' and 
MSL.ID >= @firstSensorID
and MSL.ID <= @lastSensorID 

select 
count(1) as [Rssi]
from [Tracks].[dbo].[MonitorSensorLog] MSL
where msl.SensorType='Rssi' and 
MSL.ID >= @firstSensorID
and MSL.ID <= @lastSensorID 


select 
count(1) as [Motion]
from [Tracks].[dbo].[MonitorSensorLog] MSL
where msl.SensorType='Motion' and 
MSL.ID >= @firstSensorID
and MSL.ID <= @lastSensorID 


select 
count(1) as [MotionInferred]
from [Tracks].[dbo].[MonitorSensorLog] MSL
where msl.SensorType='MotionInferred' and 
MSL.ID >= @firstSensorID
and MSL.ID <= @lastSensorID

end

View Code

4.Union VS Union All
Union:对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序。

Union All:对两个结果集进行并集操作,包含重复行,不进行排序。

INTERSECT:是两个查询结果的交集对两个结果集进行交集操作，不包括重复行，重复的会被过滤，同时进行默认规则的排序。

Minus:对两个结果集进行差操作，返回的总是左边表中的数据且不包括重复行，重复的会被过滤，同时进行默认规则的排序。

来看下列：表scfrd_type

id code

1 A

2 B

表scfrd_type1

id code

2 B

3 C

查询语句select id,code fromscfrd_type union select id,code from scfrd_type1。结果过滤了重复的行，如下：

id code

1 A

2 B

3 C

查询语句select id,code fromscfrd_type union all select id,code from scfrd_type1。结果没有过滤了重复的行，如下：

id code

1 A

2 B

3 C

查询语句select id,code fromscfrd_type intersect select id,code from scfrd_type1。结果如下：

id code

2 B

查询语句select id,code fromscfrd_type minus select id,code from scfrd_type1。结果如下：

id code

1 A

5.游标的用法,实例:

--this script used to calculate # of shipments prior to monitor creation,# of shipment in middle of monitor creation,# of shipment later than monitor creation
drop table #TempDevice
drop table #TempDeviceState

--select * from #TempDevice with (nolock)
--select * from #TempDeviceState with (nolock)

create table #TempDevice (
        DeviceID nvarchar(100),
        ShipmentID uniqueidentifier,
        ShipmentCreatedOn datetime)

-- Filter the device from MonitorSensorLog and MonitorLocationLog based on the associated shipment created time
insert into #TempDevice (DeviceID, ShipmentID, ShipmentCreatedOn)
select distinct MSL.DeviceID, S.ShipmentId, S.RowCreatedOn from MonitorSensorLog MSL
    join SerialDeviceMap SDM on MSL.DeviceID=SDM.DeviceId
    join ShippedMonitorTable SMT on SMT.SerialNumber=SDM.SerialNumber
    join Shipment S on S.ShipmentId=SMT.ShipmentId
        where S.RowCreatedOn >= '2016-01-01' and S.RowCreatedOn <= '2017-01-01' and S.Disabled = 0
union
select distinct MLL.DeviceID, S.ShipmentId, S.RowCreatedOn from MonitorLocationLog MLL
    join SerialDeviceMap SDM on MLL.DeviceID=SDM.DeviceId
    join ShippedMonitorTable SMT on SMT.SerialNumber=SDM.SerialNumber
    join Shipment S on S.ShipmentId=SMT.ShipmentId
        where S.RowCreatedOn >= '2016-01-01' and S.RowCreatedOn <= '2017-01-01' and S.Disabled = 0
go



-- Create a temp table to store the device first point and last point info)
create table #TempDeviceState (
        DeviceID nvarchar(50),
        FirstPt datetime,
        LastPt datetime)

declare @LocPtTime datetime
declare @SenPtTime datetime
declare @deviceID nvarchar(100)
Declare My_Cursor CURSOR
for (select distinct deviceID from #TempDevice)
open My_Cursor;
fetch next from My_Cursor into @deviceID;
while (@@Fetch_Status =0)

-- Calculate the first point and last point for the device from DataPointGeo table
begin

print @deviceID

insert into #TempDeviceState (DeviceID) values (@deviceID)

set @LocPtTime = (select top 1 RowCreatedOn from MonitorLocationLog where DeviceID = @deviceID order by ID)
set @SenPtTime = (select top 1 RowCreatedOn from MonitorSensorLog where DeviceID = @deviceID order by ID)

update #TempDeviceState set FirstPt = IIF (@LocPtTime < @SenPtTime, @LocPtTime, @senPtTime) where DeviceID = @deviceID

set @LocPtTime = (select top 1 RowCreatedOn from MonitorLocationLog where DeviceID = @deviceID order by ID desc)
set @SenPtTime = (select top 1 RowCreatedOn from MonitorSensorLog where DeviceID = @deviceID order by ID desc)

update #TempDeviceState set LastPt = IIF (@LocPtTime > @SenPtTime, @LocPtTime, @senPtTime) where DeviceID = @deviceID
    

--insert into #TempDeviceState (DeviceID, FirstPt, LastPt)
--    select @deviceID, min(RowCreatedOn), max(RowCreatedOn) from DataPointGeo
--        where DeviceID = @deviceID
fetch next from My_Cursor into @deviceID;
end
close My_Cursor;
deallocate My_Cursor;
go

declare @Shipment_COUNT int
declare @Middle_COUNT int
declare @Monitor_COUNT int

set @Shipment_COUNT=0
set @Middle_COUNT=0
set @Monitor_COUNT=0

declare @shipmentID uniqueidentifier
declare @deviceID nvarchar(100)
Declare My_Cursor CURSOR
for (select ShipmentID from #TempDevice)
open My_Cursor;
fetch next from My_Cursor into @shipmentID;
while (@@Fetch_Status =0)

begin
set @deviceID = (select top 1 DeviceID from #TempDevice where ShipmentID=@shipmentID)

IF ((SELECT firstPt from #TempDeviceState WHERE DeviceID = @deviceID)>(SELECT top 1 ShipmentCreatedOn from #TempDevice WHERE ShipmentID = @shipmentID))
SET @Shipment_COUNT=@Shipment_COUNT+1

IF ((SELECT LastPt from #TempDeviceState WHERE DeviceID = @deviceID)<(SELECT top 1 ShipmentCreatedOn from #TempDevice WHERE ShipmentID = @shipmentID))
SET @Monitor_COUNT=@Monitor_COUNT+1

IF ((SELECT firstPt from #TempDeviceState WHERE DeviceID = @deviceID)<(SELECT top 1 ShipmentCreatedOn from #TempDevice WHERE ShipmentID = @shipmentID) and (SELECT top 1 ShipmentCreatedOn from #TempDevice WHERE ShipmentID = @shipmentID)<(SELECT LastPt from #TempDeviceState WHERE deviceid = @DeviceId))
SET @Middle_COUNT=@Middle_COUNT+1
fetch next from My_Cursor into @shipmentID;
end

select @Shipment_COUNT as ShipPriorCount, @Monitor_COUNT as MonPriorCount, @Middle_COUNT as ShipMidCount

close My_Cursor;
deallocate My_Cursor;
go

View Code

附一个别人写得比较好的Link:http://www.cnblogs.com/moss_tan_jun/archive/2011/11/26/2263988.html