SQL Performance Tuning

1. Select (1) is better than select (*)

2.In and Exist

In is to give the appearance and inner table to do a hash link, and exist is to do Loop loop, each loop loop to do inner table query, if two table size similar, in and exists difference is not small.

If one table in two tables is large and a table is small, the subquery is large with exist, and the subquery is small in.

3. Calculate the number of lines for the specified time period in the table by first selecting the maximum minimum value for this time and then count (ID), as follows: Datapointpersensor.sql (minutes) Dpnumberpersensor.sql ( minutes)

Datapointpersensor.sql

-- Thisscript used to calculate different sensor type of datapointSelectcount (mll.id) as[Loc] from[Tracks]. [dbo]. [Monitorlocationlog] MLLwhereMLL. Rowcreatedon >='2016-01-01'and MLL. Rowcreatedon<='2017-01-01' SelectCount (1) as[Latitude] from[Tracks]. [dbo]. [Monitorsensorlog] MSLwhereMsl. Sensortype='Latitude'and MSL. Rowcreatedon>='2016-01-01'and MSL. Rowcreatedon<='2017-01-01'SelectCount (1) as[Temperatureexternal] from[Tracks]. [dbo]. [Monitorsensorlog] MSLwhereMsl. Sensortype='temperatureexternal'and MSL. Rowcreatedon>='2016-01-01'and MSL. Rowcreatedon<='2017-01-01'SelectCount (1) as[Temperatureinternal] from[Tracks]. [dbo]. [Monitorsensorlog] MSLwhereMsl. Sensortype='temperatureinternal'and MSL. Rowcreatedon>='2016-01-01'and MSL. Rowcreatedon<='2017-01-01'SelectCount (1) as[Batteryexternal] from[Tracks]. [dbo]. [Monitorsensorlog] MSLwhereMsl. Sensortype='batteryexternal'and MSL. Rowcreatedon>='2016-01-01'and MSL. Rowcreatedon<='2017-01-01'SelectCount (1) as[Batteryinternal] from[Tracks]. [dbo]. [Monitorsensorlog] MSLwhereMsl. Sensortype='batteryinternal'and MSL. Rowcreatedon>='2016-01-01'and MSL. Rowcreatedon<='2017-01-01'SelectCount (1) as[Rssi] from[Tracks]. [dbo]. [Monitorsensorlog] MSLwhereMsl. Sensortype='Rssi'and MSL. Rowcreatedon>='2016-01-01'and MSL. Rowcreatedon<='2017-01-01'SelectCount (1) as[Motion] from[Tracks]. [dbo]. [Monitorsensorlog] MSLwhereMsl. Sensortype='Motion'and MSL. Rowcreatedon>='2016-01-01'and MSL. Rowcreatedon<='2017-01-01'SelectCount (1) as[motioninferred] from[Tracks]. [dbo]. [Monitorsensorlog] MSLwhereMsl. Sensortype='motioninferred'and MSL. Rowcreatedon>='2016-01-01'and MSL. Rowcreatedon<='2017-01-01'

View Code

Dpnumberpersensor.sql

-- Thisscript used to calculate different sensor type of datapointdeclare @firstLocID nvarchar ( -) declare @lastLocID nvarchar ( -) declare @firstSensorID nvarchar ( -) declare @lastSensorID nvarchar ( -)Set@firstLocID = (SelectTop1(ID) from[Tracks]. [dbo]. [Monitorlocationlog] MLLwhereMll. rowcreatedon>='2016-01-01'order by ID)Set@lastLocID = (SelectTop1(ID) asLastID from[Tracks]. [dbo]. [Monitorlocationlog] MLLwhereMll. rowcreatedon<='2017-01-01'ORDER BY id DESC)Set@firstSensorID = (SelectTop1(ID) from[Tracks]. [dbo]. [Monitorsensorlog] MSLwhereMSL. rowcreatedon>='2016-01-01'order by ID)Set@lastSensorID = (SelectTop1(ID) asLastID from[Tracks]. [dbo]. [Monitorsensorlog] MSLwhereMSL. rowcreatedon<='2017-01-01'ORDER BY id DESC) BEGINSelectcount (mll.id) as[Loc] from[Tracks]. [dbo]. [Monitorlocationlog] MLLwhereMll.id >=@firstLocIDand mll.id<=@lastLocIDSelectCount (1) as[Temperatureexternal] from[Tracks]. [dbo]. [Monitorsensorlog] MSLwhereMsl. Sensortype='temperatureexternal'and msl.id>=@firstSensorIDand msl.id<=@lastSensorIDSelectCount (1) as[Temperatureinternal] from[Tracks]. [dbo]. [Monitorsensorlog] MSLwhereMsl. Sensortype='temperatureinternal'and msl.id>=@firstSensorIDand msl.id<=@lastSensorIDSelectCount (1) as[Light] from[Tracks]. [dbo]. [Monitorsensorlog] MSLwhereMsl. Sensortype=' Light'and msl.id>=@firstSensorIDand msl.id<=@lastSensorIDSelectCount (1) as[Batteryexternal] from[Tracks]. [dbo]. [Monitorsensorlog] MSLwhereMsl. Sensortype='batteryexternal'and msl.id>=@firstSensorIDand msl.id<=@lastSensorIDSelectCount (1) as[Batteryinternal] from[Tracks]. [dbo]. [Monitorsensorlog] MSLwhereMsl. Sensortype='batteryinternal'and msl.id>=@firstSensorIDand msl.id<=@lastSensorIDSelectCount (1) as[Rssi] from[Tracks]. [dbo]. [Monitorsensorlog] MSLwhereMsl. Sensortype='Rssi'and msl.id>=@firstSensorIDand msl.id<=@lastSensorIDSelectCount (1) as[Motion] from[Tracks]. [dbo]. [Monitorsensorlog] MSLwhereMsl. Sensortype='Motion'and msl.id>=@firstSensorIDand msl.id<=@lastSensorIDSelectCount (1) as[motioninferred] from[Tracks]. [dbo]. [Monitorsensorlog] MSLwhereMsl. Sensortype='motioninferred'and msl.id>=@firstSensorIDand msl.id<=@lastSensorIDend

View Code

4.Union VS Union All
Union: Sets the two result sets, excluding duplicate rows, and sorting the default rules.

Union All: Set operation on two result sets, containing duplicate rows, without sorting.

INTERSECT: Is the intersection of two query results for two result sets, excluding duplicate rows, repeated filtering, and sorting of the default rules.

Minus: Differential operation on two result sets, returning always data from the left table and excluding duplicate rows, repeated filtering, and sorting of the default rules.

Look at the following: Table Scfrd_type

ID Code

1 A

2 B

Table Scfrd_type1

ID Code

2 B

3 C

Query statement Select Id,code Fromscfrd_type Union select Id,code from Scfrd_type1. The result filters the duplicate rows, as follows:

ID Code

1 A

2 B

3 C

Query statement Select Id,code Fromscfrd_type UNION ALL select Id,code from Scfrd_type1. The result does not filter the duplicate rows, as follows:

ID Code

1 A

2 B

2 B

3 C

Query statement Select Id,code fromscfrd_type intersect select Id,code from Scfrd_type1. The results are as follows:

ID Code

2 B

Query statement Select Id,code fromscfrd_type minus select Id,code from Scfrd_type1. The results are as follows:

ID Code

1 A

SQL Performance Tuning