Skip to content
Advertisement

SQL query optimization for speed

So I was working on the problem of optimizing the following query I have already optimized this to the fullest from my side can this be further optimized?

select distinct name ad_type 
from dim_ad_type x  where exists  ( select 1 
                                    from sum_adserver_dimensions sum 
                                    left join dim_ad_tag_map on dim_ad_tag_map.id=sum.ad_tag_map_id and dim_ad_tag_map.client_id=sum.client_id 
                                    left join dim_site on dim_site.id = dim_ad_tag_map.site_id 
                                    left join dim_geo on dim_geo.id = sum.geo_id 
                                    left join dim_region on dim_region.id=dim_geo.region_id 
                                    left join dim_device_category on dim_device_category.id=sum.device_category_id 
                                    left join dim_ad_unit on dim_ad_unit.id=dim_ad_tag_map.ad_unit_id 
                                    left join dim_monetization_channel on dim_monetization_channel.id=dim_ad_tag_map.monetization_channel_id 
                                    left join dim_os on dim_os.id = sum.os_id 
                                    left join dim_ad_type on dim_ad_type.id = dim_ad_tag_map.ad_type_id 
                                    left join dim_integration_type on dim_integration_type.id = dim_ad_tag_map.integration_type_id 
                                    where sum.client_id = 50 
                                    and dim_ad_type.id=x.id 
                                    ) 
order by 1 

Advertisement

Answer

Your query although joined ok, is an overall bloat. You are using the dim_ad_type table on the outside, just to make sure it exists on the inside as well. You have all those left-joins that have NO bearing on the final outcome, why are they even there. I would simplify by reversing the logic. By tracing your INNER query for the same dim_ad_type table, I find the following is the direct line. sum -> dim_ad_tag_map -> dim_ad_type. Just run that.

select distinct
        dat.name Ad_Type
    from
        sum_adserver_dimensions sum
            join dim_ad_tag_map tm
                on sum.ad_tag_map_id = tm.id
                and sum.client_id = tm.client_id
                join dim_ad_type dat
                    on tm.ad_type_id = dat.id 
    where
        sum.client_id = 50 
    order by 
        1

Your query was running ALL dim_ad_types, then finding all the sums just to find those that matched. Run it direct starting with the one client, then direct with JOINs.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement