如何减少使用更多连接的Sql查询的时间，并且执行时间超过1.5分钟 [英] How Can I Reduce The Time Of My Sql Query Which Has More Join's Used And It Takes More Than A 1.5 Mins To Execute

问题描述

SELECT  prh.header_id  , prh.staff_id  , prh.name  , prh.description  , prh.create_date  , prh.timezone_id  , m.market_id
    , m.market_name  , agy.agency_id , agy.agency_name  , adv.advertiser_id  , adv.advertiser_name  , prh.product
    , prh.survey_id  , o.office_id  , o.office_name  , ae_staff_id  , st.first_name AS ae_first_name  ,
 st.last_name AS ae_last_name , prh.flight_start_date  , prh.flight_end_date  , prh.flight_month  , prh.demo_id 
 , d.demo_name  , prh.points_goal  , prh.syscode_summary , prh.network_summary  , prh.daypart_summary , 
prh.weekly_summary, prh.bmonth_summary  , prh.client_code  , prh.product_code  , prh.estimate_code  , prh.geography_id  , 
g.description as geography_name  , prh.survey_source_id , ss.source as survey_source_name, prh.show_hh
    , isnull(prh.show_survey, 0) as show_survey  , isnull(prh.display_type, 0) as display_type  , 
isnull(prh.Show_Ratings_Imps, 3) as Show_Ratings_Imps , isnull(prh.Zone_Consolidate, 0) as Zone_Consolidate , 
isnull(prh.Order_Num_Display, prh.survey_id) as Order_Num_Display , isnull(prh.Total_GRPs, 0) as Total_GRPs
   , (SELECT EST.iPlus FROM NCCCableBuy.dbo.Survey SUR JOIN NCCCableBuy.dbo.Estimate EST ON 
SUR.Estimate_ID = EST.Estimate_ID
    WHERE (SUR.Survey_ID = prh.Survey_ID)) AS iPlus
        FROM post_saved_report_header prh
         LEFT JOIN survey_source ss on prh.survey_source_id = ss.survey_type_id
         LEFT JOIN geo_type g on prh.geography_id = g.geo_id
         LEFT JOIN market m ON prh.market_id = m.market_id
         LEFT JOIN ac_agency agy ON prh.agency_id = agy.agency_id
         LEFT JOIN ac_advertiser adv ON prh.advertiser_id = adv.advertiser_id
         LEFT JOIN staff st ON prh.ae_staff_id = st.staff_id
         LEFT JOIN office o ON prh.office_id = o.office_id
         LEFT JOIN demo d ON prh.demo_id = d.demo_id
         WHERE prh.header_id = 246

推荐答案

快速浏览一下，尝试删除查询以获取 iPlus 列的数据。尝试添加联接以获取该数据。除此之外，您可能需要查看这些表具有的数据量，数据库设计以及硬件。



查看执行计划以确定哪个操作需要更多时间。

A a quick glance, try to remove the query to get data for iPlus column. Try and add a join to get that data. Other than that, you might need to look into amount of data these tables have, your database design and also the hardware.

Take a look at the execution plan to identify which operation takes more time.

检查这个...





如何加快我的选择*查询530,000行 [ ^ ]





希望这会对你有所帮助。



干杯

Hi,

Check this...


How Do I Speed Up My Select * Query Of "530,000" Rows[^]


Hope this will help you.

Cheers

这篇关于如何减少使用更多连接的Sql查询的时间，并且执行时间超过1.5分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！