#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    2
    Rep Power
    0

    'SELECT TOP...' killing query performance?


    This query takes about 7 seconds to execute

    Select * into testtable from (
    select 'unmapped local product' as 'output', a.dashboardDescription as dashboardDescription
    ,a.[Product Global Category Name] as Product_Global_Category_Name
    ,a.[Product Local Id] as Product_Local_Id
    ,a.[Product Local Name] as product_local_name
    ,SUM(DASHBOARD_PREMIUM_USD) as premium
    from pm_Policies_Live a
    left join build_premiumShareSubProductMapping_temp b
    on a.dashboardDescription = b.dashboardDescription
    and a.[Product Local Id] = b.[Product Local Id]
    and a.[Product Global Category Name] = b.[Product Global Category Name]
    where b.dashboardDescription is null
    and dashboard_exclusion <> 1 and DASHBOARD_FLAG =1
    group by
    a.dashboardDescription
    ,a.[Product Global Category Name]
    ,a.[Product Local Id]
    ,a.[Product Local Name]
    )a



    Now if i change the first line to

    Select top 10 * into testtable from (

    the query took over 10 mins to complete....

    Why would using the 'top 10' affect the performance so much?
    I am trying to limit the number of results I get back. With some queries, using 'select top' works fine, but in others it severely affects performance.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    i don't understand why you need the outer query...
    Code:
    select top 100
           'unmapped local product' as 'output'
         , a.dashboardDescription as dashboardDescription
         , a.[Product Global Category Name] as Product_Global_Category_Name
         , a.[Product Local Id] as Product_Local_Id
         , a.[Product Local Name] as product_local_name
         , SUM(DASHBOARD_PREMIUM_USD) as premium
      into testtable     
      from pm_Policies_Live a
    left 
      join build_premiumShareSubProductMapping_temp b
        on a.dashboardDescription = b.dashboardDescription
       and a.[Product Local Id] = b.[Product Local Id]
       and a.[Product Global Category Name] = b.[Product Global Category Name]
     where b.dashboardDescription is null
       and dashboard_exclusion <> 1 
       and DASHBOARD_FLAG =1
    group 
        by a.dashboardDescription
         , a.[Product Global Category Name] 
         , a.[Product Local Id]
         , a.[Product Local Name]
    btw, which table do DASHBOARD_PREMIUM_USD, dashboard_exclusion, and DASHBOARD_FLAG belong to?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    2
    Rep Power
    0
    Originally Posted by r937
    i don't understand why you need the outer query...
    Code:
    select top 100
           'unmapped local product' as 'output'
         , a.dashboardDescription as dashboardDescription
         , a.[Product Global Category Name] as Product_Global_Category_Name
         , a.[Product Local Id] as Product_Local_Id
         , a.[Product Local Name] as product_local_name
         , SUM(DASHBOARD_PREMIUM_USD) as premium
      into testtable     
      from pm_Policies_Live a
    left 
      join build_premiumShareSubProductMapping_temp b
        on a.dashboardDescription = b.dashboardDescription
       and a.[Product Local Id] = b.[Product Local Id]
       and a.[Product Global Category Name] = b.[Product Global Category Name]
     where b.dashboardDescription is null
       and dashboard_exclusion <> 1 
       and DASHBOARD_FLAG =1
    group 
        by a.dashboardDescription
         , a.[Product Global Category Name] 
         , a.[Product Local Id]
         , a.[Product Local Name]
    btw, which table do DASHBOARD_PREMIUM_USD, dashboard_exclusion, and DASHBOARD_FLAG belong to?
    good catch, i missed that. they belong to table pm_Policies_Live.

    i fixed that but it did not help with the performance issue. those columns were unique to pm_Policies_Live so it never caused an issue.

    its complicated to explain, but i do need the outer query....

    I gave 1 specific query as an example of where i am running into problems. in my stored procedure it would actually look like:

    set @query2 = 'select * into tablename from (' + @query + ')a'

    exex sp_executesql query2
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,261
    Rep Power
    4279
    you'll want to examine the execution plan for both queries, with TOP and without
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo