Expert advice on slow running query

shafeershafeer
in General Discussion

Hi,

Is there any performance impact on below query? delay running this query is evident. Delay noticed when analytic function added in the main query.

select
ifmet.netif_qname,
ifmet.netif_name,
ifmet.netif_alias,
ifmet.pw,
ifmet.netif_speed_in_out_bit_per_s,
ifmet.node_name,
ifmet.timestamp1 as timestamp,
(case when (round(max(ifmet.MaxInBpsRef),2) >= round(max(ifmet.MaxOutBpsRef),2)) then
(case when (round(max(ifmet.MaxInBpsRef),2) < 1000) then 'bps'
when (round(max(ifmet.MaxInBpsRef),2) < 1000000) then 'Kbps'
when (round(max(ifmet.MaxInBpsRef),2) < 1000000000) then 'Mbps'
else 'Gbps' end)
else (case when (round(max(ifmet.MaxOutBpsRef),2) < 1000) then 'bps'
when (round(max(ifmet.MaxOutBpsRef),2) < 1000000) then 'Kbps'
when (round(max(ifmet.MaxOutBpsRef),2) < 1000000000) then 'Mbps'
else 'Gbps' end)
end) as Label,
(case when (round(max(ifmet.MaxInBpsRef),2) >= round(max(ifmet.MaxOutBpsRef),2)) then
(case when (round(max(ifmet.MaxInBpsRef),2) < 1000) then round(avg(ifmet.Inbps),2)
when (round(max(ifmet.MaxInBpsRef),2) < 1000000) then (round(avg(ifmet.Inbps),2)/1000)
when (round(max(ifmet.MaxInBpsRef),2) < 1000000000) then (round(avg(ifmet.Inbps),2)/1000000)
else (round(avg(ifmet.Inbps),2)/1000000000) end)
else (case when (round(max(ifmet.MaxOutBpsRef),2) < 1000) then round(avg(ifmet.Inbps),2)
when (round(max(ifmet.MaxOutBpsRef),2) < 1000000) then (round(avg(ifmet.Inbps),2)/1000)
when (round(max(ifmet.MaxOutBpsRef),2) < 1000000000) then (round(avg(ifmet.Inbps),2)/1000000)
else (round(avg(ifmet.Inbps),2)/1000000000) end)
end) as avgInbps,
(case when (round(max(ifmet.MaxInBpsRef),2) >= round(max(ifmet.MaxOutBpsRef),2)) then
(case when (round(max(ifmet.MaxInBpsRef),2) < 1000) then round(avg(ifmet.Outbps),2)
when (round(max(ifmet.MaxInBpsRef),2) < 1000000) then (round(avg(ifmet.Outbps),2)/1000)
when (round(max(ifmet.MaxInBpsRef),2) < 1000000000) then (round(avg(ifmet.Outbps),2)/1000000)
else (round(avg(ifmet.Outbps),2)/1000000000) end)
else (case when (round(max(ifmet.MaxOutBpsRef),2) < 1000) then round(avg(ifmet.Outbps),2)
when (round(max(ifmet.MaxOutBpsRef),2) < 1000000) then (round(avg(ifmet.Outbps),2)/1000)
when (round(max(ifmet.MaxOutBpsRef),2) < 1000000000) then (round(avg(ifmet.Outbps),2)/1000000)
else (round(avg(ifmet.MaxOutBpsRef),2)/1000000000) end)
end) as avgOutbps
from (
select distinct
a.netif_qname,
a.netif_name,
a.netif_alias,
split_part(a.netif_alias,':',3) as pw,
a.netif_type,
a.netif_physical_add,
a.netif_speed_in_out_bit_per_s,
a.node_name,
case when ((datediff(day,'2023-05-22 00:00',now())) < 7) then (max(b.throughput_in_bit_per_s) over ()) else (max(c.throughput_in_max_bit_per_s) over ()) end MaxInBpsRef,
case when ((datediff(day,'2023-05-22 00:00',now())) < 7) then (max(b.throughput_out_bit_per_s) over ()) else (max(c.throughput_out_max_bit_per_s) over ()) end MaxOutBpsRef,
case when ((datediff(day,'2023-05-22 00:00',now())) < 7) then to_timestamp(b.timestamp_utc_s) else to_timestamp(c.timestamp_utc_s) end timestamp1,
case when ((datediff(day,'2023-05-22 00:00',now())) < 7) then b.throughput_in_bit_per_s else c.throughput_in_avg_bit_per_s end Inbps,
case when ((datediff(day,'2023-05-22 00:00',now())) < 7) then b.throughput_out_bit_per_s else c.throughput_out_avg_bit_per_s end Outbps,
case when ((datediff(day,'2023-05-22 00:00',now())) < 7) then b.throughput_in_bit_per_s else c.throughput_in_max_bit_per_s end Inbpsmax,
case when ((datediff(day,'2023-05-22 00:00',now())) < 7) then b.throughput_out_bit_per_s else c.throughput_out_max_bit_per_s end Outbpsmax
from mf_shared_provider_default.nom_interface_health b
inner join mf_shared_provider_default.nom_interface_health_1h c
on b.netif_unique_id = c.netif_unique_id
left join mf_shared_provider_default.nom_entity_interface_raw a
on a.netif_unique_id = b.netif_unique_id
where a.netif_unique_id in ('1cd80d2e-4893-4198-aaca-3c4f602245df')
and ((b.throughput_out_bit_per_s is not null)
and (c.throughput_out_avg_bit_per_s is not null))
and ((to_timestamp(b.timestamp_utc_s) >= '2023-05-22 00:00') and (to_timestamp(b.timestamp_utc_s) < '2023-05-28 23:59'))
and ((to_timestamp(c.timestamp_utc_s) >= '2023-05-22 00:00') and (to_timestamp(c.timestamp_utc_s) < '2023-05-28 23:59'))) ifmet
group by ifmet.netif_qname,ifmet.netif_name,ifmet.netif_alias,ifmet.pw,ifmet.netif_speed_in_out_bit_per_s,ifmet.node_name,ifmet.timestamp1
order by ifmet.timestamp1

Answers

  • VValdarVValdar Vertica Employee Employee

    I'm a bit worried on the max over() without any partition by.
    You should also publish DDL and explain of this query. Profiling will also give you a nice view of the consuming part of the query.

  • shafeershafeer

    Hi Valdar,

    This is related to Microfocus product NNMi OPTIC DL. I'm trying to query the data for the reporting purpose.
    https://docs.microfocus.com/doc/Network_Operations_Management/2022.11/PerfMericsTable

    This is bandwidth utilization data I'm trying to graph. In order to convert the data to a common unit (e.g. Kbps/Mbps/Gbps) first we need to find out the max value in query window then use that base line to define the "Unit".

    Tried to use "partition by" and "order by" in over(), but that cause increased delay while running query.

    In short, I want to get the max value of a column within the query window and use that value inside the query.

  • marcothesanemarcothesane - Select Field - Administrator

    I agree with @VValdar - the OVER() can be costly.

    Try this workaround:

    Add this WITH clause at the beginning of the query:

    WITH
maxb AS (
  SELECT 
    max(throughput_in_bit_per_s)  AS b_max_throughput_in_bit_per_s 
  , max(throughput_out_bit_per_s) AS b_max_throughput_out_bit_per_s
  FROM mf_shared_provider_default.nom_interface_health 
  WHERE (b.throughput_out_bit_per_s IS NOT NULL)
    AND (to_timestamp(b.timestamp_utc_s) >= '2023-05-22 00:00')
    AND (to_timestamp(b.timestamp_utc_s) < '2023-05-28 23:59')
)
,
maxc AS (
  SELECT
    max(c.throughput_in_max_bit_per_s)  AS c_max_throughput_in_bit_per_s 
  , max(c.throughput_out_max_bit_per_s) AS c_max_throughput_out_bit_per_s
  FROM
  WHERE (c.throughput_out_avg_bit_per_s IS NOT NULL)
    AND (to_timestamp(c.timestamp_utc_s) >= '2023-05-22 00:00')
    AND (to_timestamp(c.timestamp_utc_s) < '2023-05-28 23:59')
)

    CROSS JOIN maxb and maxc with the main query, and use the columns in the Common Table Expressions above instead of MAX(...) OVER() .

    If it does not (yet) help, use WITH /*+ENABLE_WITH_CLAUSE_MATERIALIZATION*/ . The GROUP BY needs to (and should) be run only once. The hint makes sure of that if it does not already.

    Then, get rid of all round() function calls. You should have done that even before adding the OLAP functions.
    Instead of
    round(max(ifmet.maxinbpsref), 2)
    Write:
    max(ifmet.maxinbpsref)::NUMERIC(18,2)
    or, whenever you compare with an INTEGER value, even:
    max(ifmet.maxinbpsref)::INTEGER

    round() is a floating point based function. They are expensive. Then, you compare an integer with a floating point function return value. That's expensive, too. Comparing with INTEGERs is fastest. Comparing with NUMERICs is a close second.

  • shafeershafeer

    @marcothesane @VValdar

    Thanks for your answers.

    I did the query based on your inputs; still the performance if very poor when I query the data beyond one week.
    Please advice how can I further improve this.

    ======================================
    WITH
    bMaxBpsRef AS (select b.netif_unique_id,max(b.throughput_in_bit_per_s) as MaxInBpsRef, max(b.throughput_out_bit_per_s) as MaxOutBpsRef
    from mf_shared_provider_default.nom_interface_health b
    left join mf_shared_provider_default.nom_entity_interface_raw a
    on a.netif_unique_id = b.netif_unique_id
    where a.netif_unique_id in ('1cd80d2e-4893-4198-aaca-3c4f602245df')
    and ((to_timestamp(b.timestamp_utc_s) >= '2023-05-22 00:00') and (to_timestamp(b.timestamp_utc_s) < '2023-06-13 00:00'))
    GROUP BY b.netif_unique_id),
    cMaxBpsRef AS (select c.netif_unique_id,max(c.throughput_in_avg_bit_per_s) as MaxInBpsRef, max(c.throughput_out_avg_bit_per_s) as MaxOutBpsRef
    from mf_shared_provider_default.nom_interface_health_1h c
    left join mf_shared_provider_default.nom_entity_interface_raw a
    on a.netif_unique_id = c.netif_unique_id
    where a.netif_unique_id in ('1cd80d2e-4893-4198-aaca-3c4f602245df')
    and ((to_timestamp(c.timestamp_utc_s) >= '2023-05-22 00:00') and (to_timestamp(c.timestamp_utc_s) < '2023-06-13 00:00'))
    GROUP BY c.netif_unique_id)
    select distinct
    a.netif_qname,
    a.netif_name,
    a.netif_alias,
    split_part(a.netif_alias,':',3) as pw,
    a.netif_type,
    a.netif_physical_add,
    a.netif_speed_in_out_bit_per_s,
    a.node_name,
    (case when ((datediff(day,'2023-05-22 00:00',now())) < 7) then
    (case when (bMaxBpsRef.MaxInBpsRef >= bMaxBpsRef.MaxOutBpsRef) then
    (case when bMaxBpsRef.MaxInBpsRef < 1000 then 'bps'
    when bMaxBpsRef.MaxInBpsRef < 1000000 then 'Kbps'
    when bMaxBpsRef.MaxInBpsRef < 1000000000 then 'Mbps'
    else 'Gbps' end)
    else (case when bMaxBpsRef.MaxOutBpsRef < 1000 then 'bps'
    when bMaxBpsRef.MaxOutBpsRef < 1000000 then 'Kbps'
    when bMaxBpsRef.MaxOutBpsRef < 1000000000 then 'Mbps'
    else 'Gbps' end)end)
    else (case when (cMaxBpsRef.MaxInBpsRef >= cMaxBpsRef.MaxOutBpsRef) then
    (case when cMaxBpsRef.MaxInBpsRef < 1000 then 'bps'
    when cMaxBpsRef.MaxInBpsRef < 1000000 then 'Kbps'
    when cMaxBpsRef.MaxInBpsRef < 1000000000 then 'Mbps'
    else 'Gbps' end)
    else (case when cMaxBpsRef.MaxOutBpsRef < 1000 then 'bps'
    when cMaxBpsRef.MaxOutBpsRef < 1000000 then 'Kbps'
    when cMaxBpsRef.MaxOutBpsRef < 1000000000 then 'Mbps'
    else 'Gbps' end)end)
    end) as Label,
    (case when ((datediff(day,'2023-05-22 00:00',now())) < 7) then
    (case when (bMaxBpsRef.MaxInBpsRef >= bMaxBpsRef.MaxOutBpsRef) then
    (case when bMaxBpsRef.MaxInBpsRef < 1000 then b.throughput_in_bit_per_s
    when bMaxBpsRef.MaxInBpsRef < 1000000 then b.throughput_in_bit_per_s/1000
    when bMaxBpsRef.MaxInBpsRef < 1000000000 then b.throughput_in_bit_per_s/1000000
    else b.throughput_in_bit_per_s/1000000000 end)
    else (case when bMaxBpsRef.MaxOutBpsRef < 1000 then b.throughput_in_bit_per_s
    when bMaxBpsRef.MaxOutBpsRef < 1000000 then b.throughput_in_bit_per_s/1000
    when bMaxBpsRef.MaxOutBpsRef < 1000000000 then b.throughput_in_bit_per_s/1000000
    else b.throughput_in_bit_per_s/1000000000 end)end)
    else (case when (cMaxBpsRef.MaxInBpsRef >= cMaxBpsRef.MaxOutBpsRef) then
    (case when cMaxBpsRef.MaxInBpsRef < 1000 then c.throughput_in_avg_bit_per_s
    when cMaxBpsRef.MaxInBpsRef < 1000000 then c.throughput_in_avg_bit_per_s/1000
    when cMaxBpsRef.MaxInBpsRef < 1000000000 then c.throughput_in_avg_bit_per_s/1000000
    else c.throughput_in_avg_bit_per_s/1000000000 end)
    else (case when cMaxBpsRef.MaxOutBpsRef < 1000 then c.throughput_in_avg_bit_per_s
    when cMaxBpsRef.MaxOutBpsRef < 1000000 then c.throughput_in_avg_bit_per_s/1000
    when cMaxBpsRef.MaxOutBpsRef < 1000000000 then c.throughput_in_avg_bit_per_s/1000000
    else c.throughput_in_avg_bit_per_s/1000000000 end)end)
    end) as avgInbps,
    (case when ((datediff(day,'2023-05-22 00:00',now())) < 7) then
    (case when (bMaxBpsRef.MaxInBpsRef >= bMaxBpsRef.MaxOutBpsRef) then
    (case when bMaxBpsRef.MaxInBpsRef < 1000 then b.throughput_out_bit_per_s
    when bMaxBpsRef.MaxInBpsRef < 1000000 then b.throughput_out_bit_per_s/1000
    when bMaxBpsRef.MaxInBpsRef < 1000000000 then b.throughput_out_bit_per_s/1000000
    else b.throughput_out_bit_per_s/1000000000 end)
    else (case when bMaxBpsRef.MaxOutBpsRef < 1000 then b.throughput_out_bit_per_s
    when bMaxBpsRef.MaxOutBpsRef < 1000000 then b.throughput_out_bit_per_s/1000
    when bMaxBpsRef.MaxOutBpsRef < 1000000000 then b.throughput_out_bit_per_s/1000000
    else b.throughput_in_bit_per_s/1000000000 end)end)
    else (case when (cMaxBpsRef.MaxInBpsRef >= cMaxBpsRef.MaxOutBpsRef) then
    (case when cMaxBpsRef.MaxInBpsRef < 1000 then c.throughput_out_avg_bit_per_s
    when cMaxBpsRef.MaxInBpsRef < 1000000 then c.throughput_out_avg_bit_per_s/1000
    when cMaxBpsRef.MaxInBpsRef < 1000000000 then c.throughput_out_avg_bit_per_s/1000000
    else c.throughput_out_avg_bit_per_s/1000000000 end)
    else (case when cMaxBpsRef.MaxOutBpsRef < 1000 then c.throughput_out_avg_bit_per_s
    when cMaxBpsRef.MaxOutBpsRef < 1000000 then c.throughput_out_avg_bit_per_s/1000
    when cMaxBpsRef.MaxOutBpsRef < 1000000000 then c.throughput_out_avg_bit_per_s/1000000
    else c.throughput_out_avg_bit_per_s/1000000000 end)end)
    end) as avgOutbps,

  • shafeershafeer 
        (case when ((datediff(day,'2023-05-22 00:00',now())) < 7) then 
        (case when (bMaxBpsRef.MaxInBpsRef >= bMaxBpsRef.MaxOutBpsRef) then           
            (case   when bMaxBpsRef.MaxInBpsRef < 1000 then b.throughput_in_bit_per_s
                    when bMaxBpsRef.MaxInBpsRef < 1000000 then b.throughput_in_bit_per_s/1000
                    when bMaxBpsRef.MaxInBpsRef < 1000000000 then b.throughput_in_bit_per_s/1000000  
                    else b.throughput_in_bit_per_s/1000000000 end)
        else (case  when bMaxBpsRef.MaxOutBpsRef < 1000 then b.throughput_in_bit_per_s
                    when bMaxBpsRef.MaxOutBpsRef < 1000000 then b.throughput_in_bit_per_s/1000        
                    when bMaxBpsRef.MaxOutBpsRef < 1000000000 then b.throughput_in_bit_per_s/1000000
                    else b.throughput_in_bit_per_s/1000000000 end)end)
    else (case when (cMaxBpsRef.MaxInBpsRef >= cMaxBpsRef.MaxOutBpsRef) then          
            (case   when cMaxBpsRef.MaxInBpsRef < 1000 then c.throughput_in_max_bit_per_s
                    when cMaxBpsRef.MaxInBpsRef < 1000000 then c.throughput_in_max_bit_per_s/1000     
                    when cMaxBpsRef.MaxInBpsRef < 1000000000 then c.throughput_in_max_bit_per_s/1000000  
                    else c.throughput_in_max_bit_per_s/1000000000 end)
        else (case  when cMaxBpsRef.MaxOutBpsRef < 1000 then c.throughput_in_max_bit_per_s
                    when cMaxBpsRef.MaxOutBpsRef < 1000000 then c.throughput_in_max_bit_per_s/1000        
                    when cMaxBpsRef.MaxOutBpsRef < 1000000000 then c.throughput_in_max_bit_per_s/1000000
                    else c.throughput_in_max_bit_per_s/1000000000 end)end)
    end) as maxInbps,
    (case when ((datediff(day,'2023-05-22 00:00',now())) < 7) then 
        (case when (bMaxBpsRef.MaxInBpsRef >= bMaxBpsRef.MaxOutBpsRef) then           
            (case   when bMaxBpsRef.MaxInBpsRef < 1000 then b.throughput_out_bit_per_s
                    when bMaxBpsRef.MaxInBpsRef < 1000000 then b.throughput_out_bit_per_s/1000
                    when bMaxBpsRef.MaxInBpsRef < 1000000000 then b.throughput_out_bit_per_s/1000000  
                    else b.throughput_out_bit_per_s/1000000000 end)
        else (case  when bMaxBpsRef.MaxOutBpsRef < 1000 then b.throughput_out_bit_per_s
                    when bMaxBpsRef.MaxOutBpsRef < 1000000 then b.throughput_out_bit_per_s/1000       
                    when bMaxBpsRef.MaxOutBpsRef < 1000000000 then b.throughput_out_bit_per_s/1000000
                    else b.throughput_in_bit_per_s/1000000000 end)end)
    else (case when (cMaxBpsRef.MaxInBpsRef >= cMaxBpsRef.MaxOutBpsRef) then          
            (case   when cMaxBpsRef.MaxInBpsRef < 1000 then c.throughput_out_max_bit_per_s
                    when cMaxBpsRef.MaxInBpsRef < 1000000 then c.throughput_out_max_bit_per_s/1000    
                    when cMaxBpsRef.MaxInBpsRef < 1000000000 then c.throughput_out_max_bit_per_s/1000000  
                    else c.throughput_out_max_bit_per_s/1000000000 end)
        else (case  when cMaxBpsRef.MaxOutBpsRef < 1000 then c.throughput_out_max_bit_per_s
                    when cMaxBpsRef.MaxOutBpsRef < 1000000 then c.throughput_out_max_bit_per_s/1000       
                    when cMaxBpsRef.MaxOutBpsRef < 1000000000 then c.throughput_out_max_bit_per_s/1000000
                    else c.throughput_out_max_bit_per_s/1000000000 end)end)
    end) as maxOutbps,
    case when ((datediff(day,'2023-05-22 00:00',now())) < 7) then to_timestamp(b.timestamp_utc_s) else to_timestamp(c.timestamp_utc_s) end timestamp

    from mf_shared_provider_default.nom_interface_health b
    inner join mf_shared_provider_default.nom_interface_health_1h c
    on b.netif_unique_id = c.netif_unique_id
    left join mf_shared_provider_default.nom_entity_interface_raw a
    on a.netif_unique_id = b.netif_unique_id
    cross join bMaxBpsRef
    cross join cMaxBpsRef
    where b.netif_unique_id = bMaxBpsRef.netif_unique_id and c.netif_unique_id = bMaxBpsRef.netif_unique_id and a.netif_unique_id in ('1cd80d2e-4893-4198-aaca-3c4f602245df')
    and ((b.throughput_out_bit_per_s is not null)
    and (c.throughput_out_avg_bit_per_s is not null))
    and ((to_timestamp(b.timestamp_utc_s) >= '2023-05-22 00:00') and (to_timestamp(b.timestamp_utc_s) < '2023-06-13 00:00'))
    and ((to_timestamp(c.timestamp_utc_s) >= '2023-05-22 00:00') and (to_timestamp(c.timestamp_utc_s) < '2023-06-13 00:00'))

    order by timestamp

  • Bryan_HBryan_H Vertica Employee Administrator

    Few thoughts: you might try to materialize the WITH clause with a hint or session setting.
    Date range queries perform much better when the tables are partitioned and ordered by timestamp. What is the partition and order/group setting on mf_shared_provider_default.nom_interface_health b and mf_shared_provider_default.nom_interface_health_1h c?
    It would also help to order or group these tables on "netif_unique_id" since this is used in the join and as a predicate.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...