Обсуждение: slow query

Поиск
Список
Период
Сортировка

slow query

От
"Vidhya Bondre"
Дата:




Hi all,
 
   I need a very urgent help from you all in below case.
 
   I have a query
 
  SELECT amp.campaign_id, dam.allocation_map_id,amp.optimize_type,amp.optimize_by_days, amp.rate, amp.action_id,amp.actions_delta, amp.vearned_today, amp.creative_id, amp.channel_code,SUM(CASE dam.sqldate when 20070701 then dam.actions_delivered else 0 end) as action_yest,SUM(CASE sign(20070624 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk1,SUM(CASE sign(20070617 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk2,SUM(CASE sign(20070610 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk3,SUM(CASE sign(20070603 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk4,SUM(CASE sign(20070527 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk5,SUM(CASE sign(20070520 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk6,SUM(CASE sign(20070513 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk7,SUM(CASE sign(20070506 - dam.sqldate) when -1 then dam.actions_delivered else 0 end) as action_wk8,SUM(CASE dam.sqldate when 20070701 then dam.vearned_total else 0 end) as earned_yest,SUM(CASE sign(20070624 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk1,SUM(CASE sign(20070617 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk2,SUM(CASE sign(20070610 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk3,SUM(CASE sign(20070603 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk4,SUM(CASE sign(20070527 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk5,SUM(CASE sign(20070520 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk6,SUM(CASE sign(20070513 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk7,SUM(CASE sign(20070506 - dam.sqldate) when -1 then dam.vearned_total else 0 end) as vearned_wk8,SUM(CASE dam.sqldate when 20070701 then dam.vactions_delivered else 0 end) as vactions_yest,SUM(CASE sign(20070624 - dam.sqldate) when -1 then dam.vactions_delivered else 0 end) as vactionsdel1,SUM(CASE sign(20070617 - dam.sqldate ) when -1 then dam.vactions_delivered else 0 end) as vactionsdel2,SUM(CASE sign(20070610 - dam.sqldate) when -1 then dam.vactions_delivered else 0 end) as vactionsdel3,SUM(CASE sign(20070603 - dam.sqldate) when -1 then dam.vactions_delivered else 0 end) as vactionsdel4,SUM(CASE sign(20070527 - dam.sqldate) when -1 then dam.vactions_delivered else 0 end) as vactionsdel5, SUM(CASE sign(20070520 - dam.sqldate) when -1 then dam.vactions_delivered else 0 end) as vactionsdel6,SUM(CASE sign(20070513 - dam.sqldate) when -1 then dam.vactions_delivered else 0 end) as vactionsdel7,SUM(CASE sign(20070506 - dam.sqldate) when -1 then dam.vactions_delivered else 0 end) as vactionsdel8 FROM delivered_action_map dam  INNER JOIN  (SELECT a.campaign_id, a.optimize_type,a.optimize_by_days,a.rate, a.action_id, am.creative_id, am.channel_code,  amt.actions_delta, amt.vearned_today, am.id AS allocation_map_id FROM  (SELECT c.campaign_id , c.optimize_type, c.optimize_by_days, a1.rate, a1.id AS action_id FROM action a1 INNER JOIN  (SELECT c1.asset_id AS campaign_id, ca.value AS optimize_type,c1.optimize_by_days AS optimize_by_days FROM campaign c1 INNER JOIN (SELECT ca2.campaign_id AS campaign_id, ca3.value AS value FROM campaign_attributes ca2, campaign_attributes ca3 WHERE ca2.campaign_id = ca3.campaign_id AND ca2.attribute='OPTIMIZE_STATUS' AND ca2.value = '1'AND ca3. attribute ='OPTIMIZE_TYPE') as ca ON c1.asset_id=ca.campaign_id  AND 20070702 BETWEEN (c1.start_date - interval '1 day') AND (c1.end_date +interval '1day') AND c1.status = 'A' AND c1.revenue_type != 'FOC' AND c1.action_type >= 1 AND c1.optimize_by_days > 0) AS c ON a1.campaign_id = c.campaign_id AND a1.status = 'A') AS a, allocation_map am, action_metrics amt WHERE a.action_id = amt.action_id AND am.id = amt.allocation_map_id AND am.status = 'A') AS amp ON dam.allocation_map_id= amp.allocation_map_id AND dam.action_id = amp.action_id GROUP BY amp.campaign_id, amp.optimize_type, amp.optimize_by_days, amp.rate , amp.action_id, amp.actions_delta , amp.creative_id, amp.channel_code, dam.allocation_map_id, amp.vearned_today;
 
after vacuuming the db it has become very very slow ... 100 times slow.
 
Please suggest ?
 
Regards
Vidhya

Re: slow query

От
Nis Jørgensen
Дата:
Vidhya Bondre skrev:

> Hi all,
>
>    I need a very urgent help from you all in below case.
>
>    I have a query

[snipped]

> after vacuuming the db it has become very very slow ... 100 times slow.
>
> Please suggest ?

Suggestions for getting more/better responses:

- Format your query nicely before posting it.
- Post the relevant table definitions, including indices
- Tell us what the query is supposed to do.

Suggestions for finding the cause of your problem:

- Run "EXPLAIN ANALYZE" on the query.
- Try to "remove bits" of the query to see which bits slow it down - try
to find a "minimal query" which shows the performance problem. If you
can, use the output of "EXPLAIN ANALYZE" obtained above. For instance,
all the SUMs in the SELECT clause are unlikely to significantly affect
the running time.
- Run "EXPLAIN ANALYZE" on the "minimal query", post the results.

Nis