|
Оптимизация запроса в Quest Central - WTF...
#35860177
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Марк, еще вопросик.
Вот решил я последовать рекомендациям "централи" и изменить запрос.
Мне было предложено в основном JOINы заменить обычными выборками из нескольких таблиц + WHERE
Самые "тяжелые" части запроса касаются одной таблицы (TSM.ACTLOG), в которой около миллиона записей (что-то вроде "лога"). Индексы там есть, но... :)
В общем, оптимизатор мне "обещал" 140%-прирост производительности.
На деле почему-то измененный запрос получился более долгий! :(
В чем же дело?
Исходный
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
SELECT distinct
Wizard.AlertConfig.ALERT_ID,
TSMALERTCONFIG.ACTIVITYLOGMESSAGE.ID as Alert_Config_ID,
TSM.TSMSERVER.CLIENT_ID,
TSM.ACTLOG.*
FROM
TSMALERTCONFIG.ACTIVITYLOGMESSAGE
JOIN Wizard.AlertConfig ON Wizard.AlertConfig.ID = TSMALERTCONFIG.ACTIVITYLOGMESSAGE.ALERT_CONFIG_ID AND Wizard.AlertConfig.Active = 1
JOIN Wizard.Alert ON Wizard.Alert.ID = Wizard.AlertConfig.ALERT_ID AND Wizard.Alert.ACTIVE = 1
JOIN TSM.TSMSERVER ON TSM.TSMSERVER.CLIENT_ID = Wizard.AlertConfig.CLIENT_ID
JOIN TSM.ACTLOG ON TSM.ACTLOG.SERVER_ID = TSM.TSMSERVER.ID
left join TSMALERTCONFIG.ACTIVITYLOGMESSAGEINCLUSIONLIKE as d on SYSFUN.LOCATE(UPPER(REPLACE(d.NODENAME, '*', '')), UPPER(TSM.ACTLOG.NODENAME)) > 0 and d.ALERT_CONFIG_ID = TSMALERTCONFIG.ACTIVITYLOGMESSAGE.ID
left join TSMALERTCONFIG.ACTIVITYLOGMESSAGEINCLUSIONLIKE as g on ( 1 = 1 ) and g.ALERT_CONFIG_ID = TSMALERTCONFIG.ACTIVITYLOGMESSAGE.ID
WHERE
(TSMALERTCONFIG.ACTIVITYLOGMESSAGE.SERVER_ID = TSM.ACTLOG.SERVER_ID OR TSMALERTCONFIG.ACTIVITYLOGMESSAGE.SERVER_ID IS NULL)
AND (TSMALERTCONFIG.ACTIVITYLOGMESSAGE.ORIGINATOR = TSM.ACTLOG.ORIGINATOR OR TSMALERTCONFIG.ACTIVITYLOGMESSAGE.ORIGINATOR IS NULL)
AND (TSMALERTCONFIG.ACTIVITYLOGMESSAGE.SCHEDNAME = TSM.ACTLOG.SCHEDNAME OR TSMALERTCONFIG.ACTIVITYLOGMESSAGE.SCHEDNAME IS NULL)
AND (TSMALERTCONFIG.ACTIVITYLOGMESSAGE.SEVERITY = TSM.ACTLOG.SEVERITY OR TSMALERTCONFIG.ACTIVITYLOGMESSAGE.SEVERITY IS NULL)
AND (TSMALERTCONFIG.ACTIVITYLOGMESSAGE.TSMMSG_MASK = TSM.ACTLOG.TSMMSG OR TSMALERTCONFIG.ACTIVITYLOGMESSAGE.TSMMSG_MASK = '')
AND (WIZARD.LOCATE_LIKE(TSMALERTCONFIG.ACTIVITYLOGMESSAGE.MESSAGE_MASK, TSM.ACTLOG.MESSAGE) <> 'not matches')
AND (TSM.HOURSDIFF(CURRENT_TIMESTAMP, TSM.ACTLOG.DATE_TIME) <= TSMALERTCONFIG.ACTIVITYLOGMESSAGE.HOURS)
AND ((g.NODENAME is null) or (g.NODENAME is not null and d.NODENAME is not null))
AND WIZARD.LOCATE_LIKE(d.NODENAME, TSM.ACTLOG.NODENAME) <> 'not matches';
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
SELECT *
FROM
(SELECT
Q2.alert_id,
Q2.alert_config_id,
Q2.tsmmsg_mask,
Q2.message_mask,
Q2.client_id,
Q2.server_id,
Q2.date_time,
Q2.severity,
Q2.schedname,
Q2.originator,
Q2.nodename,
Q2.d_nodename,
Q2.tsmmsg,
Q2.message,
g.nodename as g_nodename
FROM
(SELECT
Q1.alert_id,
Q1.alert_config_id,
Q1.tsmmsg_mask,
Q1.message_mask,
Q1.client_id,
Q1.server_id,
Q1.date_time,
Q1.severity,
Q1.schedname,
Q1.originator,
Q1.nodename,
Q1.tsmmsg,
Q1.message,
d.nodename as d_nodename
FROM
(SELECT DISTINCT
wizard.alertconfig.alert_id,
tsmalertconfig.activitylogmessage.id AS alert_config_id,
tsmalertconfig.activitylogmessage.tsmmsg_mask,
tsmalertconfig.activitylogmessage.message_mask,
tsm.tsmserver.client_id,
tsm.actlog.server_id,
tsm.actlog.date_time,
tsm.actlog.severity,
tsm.actlog.schedname,
tsm.actlog.originator,
tsm.actlog.nodename,
tsm.actlog.tsmmsg,
tsm.actlog.message
FROM
wizard.alertconfig,
tsmalertconfig.activitylogmessage,
wizard.alert,
tsm.tsmserver,
tsm.actlog
WHERE
wizard.alertconfig.id = tsmalertconfig.activitylogmessage.alert_config_id
AND wizard.alertconfig.active = 1
AND wizard.alert.id = wizard.alertconfig.alert_id
AND wizard.alert.active = 1
AND tsm.tsmserver.client_id = wizard.alertconfig.client_id
AND tsm.actlog.server_id = tsm.tsmserver.id
AND (tsm.Hoursdiff(CURRENT_TIMESTAMP,tsm.actlog.date_time) <= tsmalertconfig.activitylogmessage.hours)
AND (tsmalertconfig.activitylogmessage.severity = tsm.actlog.severity OR tsmalertconfig.activitylogmessage.severity is null)
AND (tsmalertconfig.activitylogmessage.schedname = tsm.actlog.schedname OR tsmalertconfig.activitylogmessage.schedname IS NULL)
AND (tsmalertconfig.activitylogmessage.originator = tsm.actlog.originator OR tsmalertconfig.activitylogmessage.originator IS NULL)
AND (tsmalertconfig.activitylogmessage.server_id = tsm.actlog.server_id OR tsmalertconfig.activitylogmessage.server_id IS NULL)) Q1
LEFT JOIN tsmalertconfig.activitylogmessageinclusionlike AS d
ON sysfun.Locate(Upper(Replace(d.nodename,'*','')),Upper(Q1.nodename)) > 0 AND d.alert_config_id = Q1.alert_config_id) Q2
LEFT JOIN tsmalertconfig.activitylogmessageinclusionlike AS g
ON g.alert_config_id = Q2.alert_config_id) Q3
WHERE
(Q3.tsmmsg_mask = Q3.tsmmsg OR Q3.tsmmsg_mask = '')
AND (wizard.Locate_like(Q3.message_mask, Q3.message) <> 'not matches')
AND ((Q3.g_nodename IS NULL) OR (Q3.g_nodename IS NOT NULL AND Q3.d_nodename IS NOT NULL))
AND wizard.Locate_like(Q3.d_nodename,Q3.nodename) <> 'not matches';
|
|
|
