Очень долгое выполнение хранимой процедуры. Sql Server 2008
Доброе утро, уважаемые.
Появилась проблема. Есть хранимая процедура, которая используется в работе с MapInfo(MapBasic).
Ее код выглядит следующим образом:
читать дальше »
Код:
ALTER PROCEDURE mipGet2gWeekDifference
@starttime date,
@colNumber int
AS
BEGIN
IF OBJECT_ID('dbo.mi2gDATA','U') IS NOT NULL
DROP TABLE dbo.mi2gDATA
SELECT CellId = this_t.CellId, CellName = this_t.Cell_Name, Data = this_t.this - prev_t.prev,
PriValues = this_t.this, SlValues = prev_t.prev
INTO mi2gDATA
FROM
(SELECT data.starttime, info.CellId, info.Cell_Name,
this = CASE @colNumber
WHEN 1 THEN AVG(data.SDCCH_traffic_ave)
WHEN 2 THEN AVG(data.SDCCH_congest_time)
WHEN 3 THEN AVG(CAST(data.SDCCH_real_blocking AS Real) / 100)
WHEN 4 THEN AVG((100 - CAST(data.Total_HO_success As REAL) / 100))
WHEN 5 THEN AVG(CAST(data.Total_HO_success As REAL) / 100)
WHEN 6 THEN AVG(CAST(data.TCH_drop_call AS Real) / 100)
WHEN 7 THEN AVG(CAST(data.TCH_call_block AS REAL) / 100)
WHEN 8 THEN AVG(data.Total_FTCH_seizure_time)
WHEN 9 THEN AVG(data.Total_HTCH_seizure_time)
WHEN 10 THEN AVG(CAST(data.CSSR_voice As Real) / 100)
WHEN 11 THEN AVG(data.TCH_traffic_sum)
WHEN 12 THEN AVG(data.UL_gprs_RLC)
WHEN 13 THEN AVG(data.DL_gprs_RLC)
WHEN 14 THEN AVG(data.UL_egprs_RLC)
WHEN 15 THEN AVG(data.DL_egprs_RLC)
WHEN 16 THEN AVG(CAST(data.Drop_Call_Cell As Real) / 100)
WHEN 17 THEN AVG(data.Drop_Call_Bss)
WHEN 18 THEN AVG(data.Drop_Call_Plmn)
END
FROM Data2GEvD data, t2gCells info
WHERE data.starttime = @starttime AND info.id = data.cell_Id
GROUP BY data.starttime, info.CellId, info.Cell_Name) this_t
LEFT JOIN
(SELECT data.starttime, info.CellId,
prev = CASE @colNumber
WHEN 1 THEN AVG(data.SDCCH_traffic_ave)
WHEN 2 THEN AVG(data.SDCCH_congest_time)
WHEN 3 THEN AVG(CAST(data.SDCCH_real_blocking AS Real) / 100)
WHEN 4 THEN AVG((100 - CAST(data.Total_HO_success As REAL) / 100))
WHEN 5 THEN AVG(CAST(data.Total_HO_success As REAL) / 100)
WHEN 6 THEN AVG(CAST(data.TCH_drop_call AS Real) / 100)
WHEN 7 THEN AVG(CAST(data.TCH_call_block AS REAL) / 100)
WHEN 8 THEN AVG(data.Total_FTCH_seizure_time)
WHEN 9 THEN AVG(data.Total_HTCH_seizure_time)
WHEN 10 THEN AVG(CAST(data.CSSR_voice As Real) / 100)
WHEN 11 THEN AVG(data.TCH_traffic_sum)
WHEN 12 THEN AVG(data.UL_gprs_RLC)
WHEN 13 THEN AVG(data.DL_gprs_RLC)
WHEN 14 THEN AVG(data.UL_egprs_RLC)
WHEN 15 THEN AVG(data.DL_egprs_RLC)
WHEN 16 THEN AVG(CAST(data.Drop_Call_Cell As Real) / 100)
WHEN 17 THEN AVG(data.Drop_Call_Bss)
WHEN 18 THEN AVG(data.Drop_Call_Plmn)
END
FROM Data2GEvD data, t2gCells info
WHERE data.starttime = DATEADD(day,-7,@starttime) AND info.id = data.cell_Id
GROUP BY data.starttime, info.CellId) prev_t
ON prev_t.CellId = this_t.CellId
END
Структура таблицы откуда берутся данные:
читать дальше »
Код:
CREATE TABLE dbo.Data2GEvD
(
id INT IDENTITY NOT NULL,
cell_Id INT NOT NULL,
starttime DATE NOT NULL,
SDCCH_traffic_ave REAL,
SDCCH_congest_time REAL,
SDCCH_real_blocking SMALLINT,
Total_HO_success SMALLINT,
TCH_drop_call SMALLINT,
TCH_call_block SMALLINT,
Total_FTCH_seizure_time REAL,
Total_HTCH_seizure_time REAL,
CSSR_voice SMALLINT,
TCH_traffic_sum REAL,
UL_gprs_RLC REAL,
DL_gprs_RLC REAL,
UL_egprs_RLC REAL,
DL_egprs_RLC REAL,
Drop_Call_Cell SMALLINT,
Drop_Call_Bss REAL,
Drop_Call_Plmn REAL,
CONSTRAINT PK_Data2GEvD PRIMARY KEY (id)
)
GO
CREATE INDEX idateevd
ON dbo.Data2GEvD (starttime)
GO
В таблице записей, на данный момент, порядка 2 миллионов. Представленная выше процедура выполняется около 15 минут. И это очень очень много. При выполнении обычного запроса:
читать дальше »
Код:
DECLARE @starttime date
DECLARE @colNumber int
SET @starttime = '2013-01-28'
SET @colNumber = 16
IF OBJECT_ID('dbo.mi2gDATA','U') IS NOT NULL
DROP TABLE dbo.mi2gDATA
SELECT CellId = this_t.CellId, CellName = this_t.Cell_Name, Data = this_t.this - prev_t.prev,
PriValues = this_t.this, SlValues = prev_t.prev
INTO mi2gDATA
FROM
(SELECT data.starttime, info.CellId, info.Cell_Name,
this = CASE @colNumber
WHEN 1 THEN AVG(data.SDCCH_traffic_ave)
WHEN 2 THEN AVG(data.SDCCH_congest_time)
WHEN 3 THEN AVG(CAST(data.SDCCH_real_blocking AS Real) / 100)
WHEN 4 THEN AVG((100 - CAST(data.Total_HO_success As REAL) / 100))
WHEN 5 THEN AVG(CAST(data.Total_HO_success As REAL) / 100)
WHEN 6 THEN AVG(CAST(data.TCH_drop_call AS Real) / 100)
WHEN 7 THEN AVG(CAST(data.TCH_call_block AS REAL) / 100)
WHEN 8 THEN AVG(data.Total_FTCH_seizure_time)
WHEN 9 THEN AVG(data.Total_HTCH_seizure_time)
WHEN 10 THEN AVG(CAST(data.CSSR_voice As Real) / 100)
WHEN 11 THEN AVG(data.TCH_traffic_sum)
WHEN 12 THEN AVG(data.UL_gprs_RLC)
WHEN 13 THEN AVG(data.DL_gprs_RLC)
WHEN 14 THEN AVG(data.UL_egprs_RLC)
WHEN 15 THEN AVG(data.DL_egprs_RLC)
WHEN 16 THEN AVG(CAST(data.Drop_Call_Cell As Real) / 100)
WHEN 17 THEN AVG(data.Drop_Call_Bss)
WHEN 18 THEN AVG(data.Drop_Call_Plmn)
END
FROM Data2GEvD data, t2gCells info
WHERE data.starttime = @starttime AND info.id = data.cell_Id
GROUP BY data.starttime, info.CellId, info.Cell_Name) this_t
LEFT JOIN
(SELECT data.starttime, info.CellId,
prev = CASE @colNumber
WHEN 1 THEN AVG(data.SDCCH_traffic_ave)
WHEN 2 THEN AVG(data.SDCCH_congest_time)
WHEN 3 THEN AVG(CAST(data.SDCCH_real_blocking AS Real) / 100)
WHEN 4 THEN AVG((100 - CAST(data.Total_HO_success As REAL) / 100))
WHEN 5 THEN AVG(CAST(data.Total_HO_success As REAL) / 100)
WHEN 6 THEN AVG(CAST(data.TCH_drop_call AS Real) / 100)
WHEN 7 THEN AVG(CAST(data.TCH_call_block AS REAL) / 100)
WHEN 8 THEN AVG(data.Total_FTCH_seizure_time)
WHEN 9 THEN AVG(data.Total_HTCH_seizure_time)
WHEN 10 THEN AVG(CAST(data.CSSR_voice As Real) / 100)
WHEN 11 THEN AVG(data.TCH_traffic_sum)
WHEN 12 THEN AVG(data.UL_gprs_RLC)
WHEN 13 THEN AVG(data.DL_gprs_RLC)
WHEN 14 THEN AVG(data.UL_egprs_RLC)
WHEN 15 THEN AVG(data.DL_egprs_RLC)
WHEN 16 THEN AVG(CAST(data.Drop_Call_Cell As Real) / 100)
WHEN 17 THEN AVG(data.Drop_Call_Bss)
WHEN 18 THEN AVG(data.Drop_Call_Plmn)
END
FROM Data2GEvD data, t2gCells info
WHERE data.starttime = DATEADD(day,-7,@starttime) AND info.id = data.cell_Id
GROUP BY data.starttime, info.CellId) prev_t
ON prev_t.CellId = this_t.CellId
выборка и вставка происходят моментально практически (максимум 2 секунды).
Как побороть эту проблему?
Спасибо. |
