SQL 效能調優

標籤：blog   track   ash   ber   inter   varchar   minus   hash   並集   

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 datapointselectcount(Mll.ID) as [Loc]from [Tracks].[dbo].[MonitorLocationLog] MLLwhere MLL.RowCreatedOn >= ‘2016-01-01‘ and MLL.RowCreatedOn <= ‘2017-01-01‘ select count(1) as [Latitude]from [Tracks].[dbo].[MonitorSensorLog] MSLwhere msl.SensorType=‘Latitude‘ and MSL.RowCreatedOn >= ‘2016-01-01‘ and MSL.RowCreatedOn <= ‘2017-01-01‘select count(1) as [TemperatureExternal]from [Tracks].[dbo].[MonitorSensorLog] MSLwhere msl.SensorType=‘TemperatureExternal‘ and MSL.RowCreatedOn >= ‘2016-01-01‘ and MSL.RowCreatedOn <= ‘2017-01-01‘select count(1) as [TemperatureInternal]from [Tracks].[dbo].[MonitorSensorLog] MSLwhere msl.SensorType=‘TemperatureInternal‘ and MSL.RowCreatedOn >= ‘2016-01-01‘ and MSL.RowCreatedOn <= ‘2017-01-01‘select count(1) as [BatteryExternal]from [Tracks].[dbo].[MonitorSensorLog] MSLwhere msl.SensorType=‘BatteryExternal‘ and MSL.RowCreatedOn >= ‘2016-01-01‘ and MSL.RowCreatedOn <= ‘2017-01-01‘select count(1) as [BatteryInternal]from [Tracks].[dbo].[MonitorSensorLog] MSLwhere msl.SensorType=‘BatteryInternal‘ and MSL.RowCreatedOn >= ‘2016-01-01‘ and MSL.RowCreatedOn <= ‘2017-01-01‘select count(1) as [Rssi]from [Tracks].[dbo].[MonitorSensorLog] MSLwhere msl.SensorType=‘Rssi‘ and MSL.RowCreatedOn >= ‘2016-01-01‘ and MSL.RowCreatedOn <= ‘2017-01-01‘select count(1) as [Motion]from [Tracks].[dbo].[MonitorSensorLog] MSLwhere msl.SensorType=‘Motion‘ and MSL.RowCreatedOn >= ‘2016-01-01‘ and MSL.RowCreatedOn <= ‘2017-01-01‘select count(1) as [MotionInferred]from [Tracks].[dbo].[MonitorSensorLog] MSLwhere 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 datapointdeclare @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] MLLwhere mll.RowCreatedOn>=‘2016-01-01‘order by id)set @lastLocID=(select top 1(ID) as lastID from [Tracks].[dbo].[MonitorLocationLog] MLLwhere mll.RowCreatedOn<=‘2017-01-01‘order by id desc)set @firstSensorID=(select top 1 (ID) from [Tracks].[dbo].[MonitorSensorLog] MSLwhere MSL.RowCreatedOn>=‘2016-01-01‘order by id)set @lastSensorID=(select top 1(ID) as lastID from [Tracks].[dbo].[MonitorSensorLog] MSLwhere MSL.RowCreatedOn<=‘2017-01-01‘order by id desc)beginselectcount(Mll.ID) as [Loc]from [Tracks].[dbo].[MonitorLocationLog] MLLwhere MLL.ID >= @firstLocIDand MLL.ID <= @lastLocID select count(1) as [TemperatureExternal]from [Tracks].[dbo].[MonitorSensorLog] MSLwhere msl.SensorType=‘TemperatureExternal‘ and MSL.ID >= @firstSensorIDand MSL.ID <= @lastSensorID select count(1) as [TemperatureInternal]from [Tracks].[dbo].[MonitorSensorLog] MSLwhere msl.SensorType=‘TemperatureInternal‘ and MSL.ID >= @firstSensorIDand MSL.ID <= @lastSensorID select count(1) as [Light]from [Tracks].[dbo].[MonitorSensorLog] MSLwhere msl.SensorType=‘Light‘ and MSL.ID >= @firstSensorIDand MSL.ID <= @lastSensorID select count(1) as [BatteryExternal]from [Tracks].[dbo].[MonitorSensorLog] MSLwhere msl.SensorType=‘BatteryExternal‘ and MSL.ID >= @firstSensorIDand MSL.ID <= @lastSensorID select count(1) as [BatteryInternal]from [Tracks].[dbo].[MonitorSensorLog] MSLwhere msl.SensorType=‘BatteryInternal‘ and MSL.ID >= @firstSensorIDand MSL.ID <= @lastSensorID select count(1) as [Rssi]from [Tracks].[dbo].[MonitorSensorLog] MSLwhere msl.SensorType=‘Rssi‘ and MSL.ID >= @firstSensorIDand MSL.ID <= @lastSensorID select count(1) as [Motion]from [Tracks].[dbo].[MonitorSensorLog] MSLwhere msl.SensorType=‘Motion‘ and MSL.ID >= @firstSensorIDand MSL.ID <= @lastSensorID select count(1) as [MotionInferred]from [Tracks].[dbo].[MonitorSensorLog] MSLwhere msl.SensorType=‘MotionInferred‘ and MSL.ID >= @firstSensorIDand MSL.ID <= @lastSensorIDend 

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

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

SQL 效能調優