SQL statement optimization and the last one minus the previous one

SQL statement optimization: 1. Add index 2 to the table. Use less like and directly use all values. the where statement writes a large number of conditions for screening in the front 4. when the data volume is large, the values involved in the calculation can be subtracted from the previous one at the same time. selecthouec, [houdate], houtimes, twISNULL (convert (decimal (), houtw-(selecttop1qiant

SQL statement optimization: 1. Add index 2 to the table. Use less like, directly use = all values 3. the where statement writes a large number of conditions for screening in the front 4. when the data volume is large, the values involved in the calculation can be subtracted from the previous one at the same time. select houec, [houdate], houtimes, tw = ISNULL (convert (decimal ), houtw-(select top 1 qiant

SQL statement optimization:

1. Add an index to a table

2. Use less like, directly use = all values

3. The where statement writes a large number of Screening Conditions in front.

4. When the data volume is large, only one value is used for calculation.

The last one minus the previous one ,,

Select houec, [houdate], houtimes
, Tw = ISNULL (convert (decimal (18, 2), houtw-(select top 1 qiantw from # qian where houec = qianec and houcid> qiancid order by qiancid desc), 0)
, Mins = isnull (DATEDIFF (minute, (select top 1 qiandatetimes from # qian where houec = qianec and houcid> qiancid order by qiancid desc), houdatetimes), 0)
From # hou

Where:

1. Temporary table # hou and # qian are the data in the same table, and two temporary tables are written for ease of computation;

2 ec is the device number, and id is the table id. the id of the same device is unique. houec = qianec and houcid> qiancid limits the number of adjacent IDs in the same device.

Select top 1 qiantw from # qian where houec = qianec and houcid> qiancid order by qiancid desc

To query records of houec = qianec and houcid> qiancid in the same device

Daily report

Drop table # qian

Create table # qian (qiancid nvarchar (20), qianec nvarchar (20) null, qiandatetimes smalldatetime null, qiandate datetime null,
Qiantimes varchar (20) null, qiantw float null,
CONSTRAINT [PK_Electricqian] PRIMARY KEY CLUSTERED
(
[Qiancid] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Insert into # qian
Select min (cid) as qiancid, cequipmentcode as qianec,
Min (dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120) as qiandatetimes,
Convert (varchar (10), dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120), 120) as qiandate,
Str (datepart (hh, dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120) + ': 00' as qiantimes,
Cast (ctotalyield as float) as qiantw
From InverterData_201310
Where convert (varchar (10), dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''' + ctime), 120), 120) = '2014-11-12 'and ctotalyield <> 'A' and ctotalyield <> ''and ctotalyield is not null and ctotalyield <> '0' and ctotalyield <>' # INV #' and ctotalyield <> 'nan'
Group by cequipmentcode, convert (varchar (10), dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120), 120 ), datepart (hh, dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''' + ctime), 120), ctotalyield
UNION select min (cid) as qiancid, cequipmentcode as qianec,
Min (dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120) as qiandatetimes,
Convert (varchar (10), dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120), 120) as qiandate,
Str (datepart (hh, dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120) + ': 00' as qiantimes,
Cast (ctotalyield as float) as qiantw
From InverterData_201311
Where convert (varchar (10), dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''' + ctime), 120), 120) = '2014-11-12 'and ctotalyield <> 'A' and ctotalyield <> ''and ctotalyield is not null and ctotalyield <> '0' and ctotalyield <>' # INV #' and ctotalyield <> 'nan'
Group by cequipmentcode, convert (varchar (10), dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120), 120 ), datepart (hh, dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''' + ctime), 120), ctotalyield
UNION select min (cid) as qiancid, cequipmentcode as qianec,
Min (dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120) as qiandatetimes,
Convert (varchar (10), dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120), 120) as qiandate,
Str (datepart (hh, dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120) + ': 00' as qiantimes,
Cast (ctotalyield as float) as qiantw
From InverterData_201312
Where convert (varchar (10), dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''' + ctime), 120), 120) = '2014-11-12 'and ctotalyield <> 'A' and ctotalyield <> ''and ctotalyield is not null and ctotalyield <> '0' and ctotalyield <>' # INV #' and ctotalyield <> 'nan'
Group by cequipmentcode, convert (varchar (10), dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120), 120 ), datepart (hh, dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''' + ctime), 120), ctotalyield

Drop table # hou

Create table # hou (houcid nvarchar (20), houec nvarchar (20) null, houdatetimes smalldatetime null, houdate smalldatetime null, houtimes varchar (20) null, houtw float null,
CONSTRAINT [PK_Electrichou] PRIMARY KEY CLUSTERED
(
[Houcid] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Insert into # hou
Select min (cid) as houcid, cequipmentcode as houec, min (dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120) as houdatetimes, convert (varchar (10), dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120), 120) as houdate,
Str (datepart (hh, dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120) + ': 00' as houtimes,
Cast (ctotalyield as float) as houtw
From InverterData_201310
Where convert (varchar (10), dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''' + ctime), 120), 120) = '2014-11-12 'and ctotalyield <> 'A' and ctotalyield <> ''and ctotalyield is not null and ctotalyield <> '0' and ctotalyield <>' # INV #' and ctotalyield <> 'nan'
Group by cequipmentcode, convert (varchar (10), dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120), 120 ), datepart (hh, dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''' + ctime), 120), ctotalyield
UNION
Select min (cid) as houcid, cequipmentcode as houec, min (dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120) as houdatetimes, convert (varchar (10), dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120), 120) as houdate,
Str (datepart (hh, dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120) + ': 00' as houtimes,
Cast (ctotalyield as float) as houtw
From InverterData_201311
Where convert (varchar (10), dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''' + ctime), 120), 120) = '2014-11-12 'and ctotalyield <> 'A' and ctotalyield <> ''and ctotalyield is not null and ctotalyield <> '0' and ctotalyield <>' # INV #' and ctotalyield <> 'nan'
Group by cequipmentcode, convert (varchar (10), dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120), 120 ), datepart (hh, dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''' + ctime), 120), ctotalyield
UNION
Select min (cid) as houcid, cequipmentcode as houec, min (dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120) as houdatetimes, convert (varchar (10), dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120), 120) as houdate,
Str (datepart (hh, dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120) + ': 00' as houtimes,
Cast (ctotalyield as float) as houtw
From InverterData_201312
Where convert (varchar (10), dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''' + ctime), 120), 120) = '2014-11-12 'and ctotalyield <> 'A' and ctotalyield <> ''and ctotalyield is not null and ctotalyield <> '0' and ctotalyield <>' # INV #' and ctotalyield <> 'nan'
Group by cequipmentcode, convert (varchar (10), dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''+ ctime), 120), 120 ), datepart (hh, dateadd (hh, 0, convert (SMALLDATETIME, (cdate + ''' + ctime), 120), ctotalyield