'SELECT TOP...' killing query performance?

Dev Shed Forums Sponsor:

July 26th, 2012, 07:53 AM

scheffetz

Registered User

Join Date: Jul 2012

Posts: 2

Time spent in forums: 1 h 22 m 51 sec
Reputation 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.

July 26th, 2012, 08:03 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,373

Time spent in forums: 3 Months 1 Week 2 Days 6 h 54 m 49 sec
Reputation Power: 4140

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?

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

July 26th, 2012, 08:23 AM

scheffetz

Registered User

Join Date: Jul 2012

Posts: 2

Time spent in forums: 1 h 22 m 51 sec
Reputation Power: 0

Quote:

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

July 26th, 2012, 08:59 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,373

Time spent in forums: 3 Months 1 Week 2 Days 6 h 54 m 49 sec
Reputation Power: 4140

you'll want to examine the execution plan for both queries, with TOP and without

'SELECT TOP...' killing query performance?

Developer Shed Advertisers and Affiliates