Select Top 1 Query

Question

Select Top 1 Query

priya_patel

SSC Veteran

Points: 202
More actions
April 7, 2008 at 6:16 am

#188710

Hi, I'm fairly new to SQL and was wondering if anybody could help make the following query more efficient. Thanks in advance, Priya
Select Distinct Country As Country, IsNull(ProductCode,'') As LocalProductCode, isNull(PackCode,'') As LocalPackCode
into #t1 From ILSRep..PL_Sales
Select top 100 *,
(Select Top 1 Category From WorkArea..PL_All_Sales_Deleted m Where m.Country = t.Country And m.ProductCode = t.LocalProductCode) As ManualCategory,
(Select Top 1 FusionCode From WorkArea..PL_All_Sales_Deleted m Where m.Country = t.Country And m.ProductCode = t.LocalProductCode) As ManualFusionProductCode,
(Select Top 1 Comments From WorkArea..PL_All_Sales_Deleted m Where m.Country = t.Country And m.ProductCode = t.LocalProductCode) As ManualComments,
(Select top 1 Productname from ILSRep..PL_Sales s where s.country = t.Country and s.productcode = t.LocalProductCode and s.packcode = t.LocalPackCode) as LocalProductName,
(Select top 1 PackName from ILSRep..PL_Sales s where s.country = t.Country and s.productcode = t.LocalProductCode and s.packcode = t.LocalPackCode) as LocalPackName,
(Select top 1 FusionProductCode from ILSRep..PL_Sales s Where s.country = t.Country and s.productcode = t.LocalProductCode and s.packcode = t.LocalPackCode) as MatchedFusionProductCode,
(Select top 1 FusionSKUCode from ILSRep..PL_Sales s Where s.country = t.Country and s.productcode = t.LocalProductCode and s.packcode = t.LocalPackCode) as MatchedFusionSKUCode,
IsNull((Select Sum(IsNull(Volume,0)) From ILSRep..PL_Sales s where s.country = t.Country and s.productcode = t.LocalProductCode and s.packcode = t.LocalPackCode),0) As Vol,
IsNull((Select Sum(IsNull(RevenueUSD,0)) From ILSRep..PL_Sales s where s.country = t.Country and s.productcode = t.LocalProductCode and s.packcode = t.LocalPackCode),0) As Rev,
IsNull((Select Sum(IsNull(MarginUSD,0)) From ILSRep..PL_Sales s where s.country = t.Country and s.productcode = t.LocalProductCode and s.packcode = t.LocalPackCode),0) As Marg
Into #t2 From #t1 t
Select Country, LocalProductCode, LocalProductName, LocalPackCode, MatchedFusionProductCode, Vol, Rev, Marg,
ManualCategory, ManualFusionProductCode, MatchedFusionSKUCode, ManualComments
From #t2 WHERE 1=1 AND Country = 'JP' DROP TABLE #t1, #t2

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]In what context you need help from us? Does the query is taking much time to execute? What exact the problem you are facing? Does it throwing any error? Please specify in detail

Thanks,

Mahesh[/font]

MH-09-AM-8694

priya_patel SSC Veteran Points: 202 More actions · Answer 2

H

i sorry for being a bit ambiguous, the query takes to long to run so I was looking for another way of rewriting the query to make it faster.

Thnks Priya

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Priya

Before you worry about getting it to run faster, make sure it's correct. TOP doesn't mean anything without an ORDER BY clause, unless you're not worried which value is returned. Probably best to use MAX instead (if you are indeed looking for the maximum value). If you want help with making the query faster, please provide DDL in the form of CREATE TABLE statements, sample data in the form of INSERT statements, and the results that you expect to see.

John

priya_patel SSC Veteran Points: 202 More actions · Answer 4

Hi, thnx for the quick responses, here are the create table DDLs:

CREATE TABLE [dbo].[Pl_All_Sales_Deleted]([Country] [char](15) NULL,

[Year] [int] NULL,[Month] [int] NULL,[AccountNo] [char](15) NULL,

[ProductCode] [char](25) NULL,[ProductName] [char](100) NULL,

[Margin] [decimal](11, 2) NULL,[Volume] [int] NULL,

[Revenue] [decimal](11, 2) NULL[PackName] [char](100) NULL,

[PackCode] [char](25) NULL,[Category] [nvarchar](5) NULL,

[FusionCode] [nvarchar](10) NULL,[Comments] [ntext] NULL,

[ID] [int] IDENTITY(1,1) NOT NULL,[ModifiedBy] [nvarchar](20) NULL,

[ModifiedWhen] [datetime] NULL,

PRIMARY KEY CLUSTERED

([ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[PL_Sales]([RowID] [int] NOT NULL,

[Country] [char](2) NULL,[Year] [int] NULL,

[Month] [int] NULL,[AccountNo] [nvarchar](20) NULL,

[Volume] [decimal](11, 2) NULL,[Margin] [money] NULL,

[Revenue] [money] NULL,[RevenueUSD] [money] NULL,

[MarginUSD] [money] NULL,[PackCode] [nvarchar](20) NULL,

[PackName] [nvarchar](20) NULL,[ProductCode] [nvarchar](25) NULL,

[ProductName] [nvarchar](75) NULL,[MarginEst] [money] NULL,

[RevenueEst] [money] NULL,[RevenueUSDEst] [money] NULL,

[MarginUSDEst] [money] NULL,[FusionMatchingRun] [int] NULL,

[FusionProductCode] [nvarchar](25) NULL,[FusionSKUCode] [nvarchar](25) NULL,

[FusionCountrySKUCode] [nvarchar](25) NULL,[IsCMS] [bit] NULL DEFAULT ((0)),

[IsInternal] [bit] NULL DEFAULT ((0))

) ON [PRIMARY]

The problem is data integrity, the only way I can get as close to the correct records as I need, from the PL_All_Sales_Deleted table, is by using the Country, ProductCode and PackCode as my primary keys. The PackCode is only held in the PL_Sales table.

I hope I'm giving you the right information.

Thnx Priya

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Could you please post some sample data and expected output. Also the index definitions would be useful

Looking at your query, the multiple subqueries are very likely the cause of the performance problem. Unless the optimiser's very clever, subqueries in the select clause must be run once for every row in the main result set. It's a cursor in disguise.

Most likely, moving those into the from clause as derived tables will improve the performance greatly

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Priya

That's a good start, but we still need sample data and expected results, please.

I think you might be struggling from what you've told me so far, though. If rows in PL_All_Sales_Deleted are uniquely identified by Country, ProductCode and PackCode, then how can you hope to join to that table from a table that doesn't have all those three columns in it?

John

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 7

Or without anything else, an execution plan will identify the worst bottlenecks. Can you post one?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Six correlated sub-queries is going to make anything squeal in pain....Wow.

How about this as a rewrite?

Select Distinct Country As Country, IsNull(ProductCode,'') As LocalProductCode, isNull(PackCode,'') As LocalPackCode

into #t1 From ILSRep..PL_Sales

Select top 100 *,

Category As ManualCategory,

FusionCode As ManualFusionProductCode,

Comments As ManualComments,

Prod as LocalProductName,

Pack as LocalPackName,

FusionProd as MatchedFusionProductCode,

FusionSKU as MatchedFusionSKUCode,

Vol,

Rev,

Marg

Into #t2 From #t1 t

inner join (Select Top 1 Country,

ProductCode,

Category,

FusionCode,

Comments

From WorkArea..PL_All_Sales_Deleted) m

ON m.Country = t.Country Andm.ProductCode = t.LocalProductCode

inner join (SelectCountry,

productcode,

packcode,

Min(Productname) Prod,

Min(PackName) Pack,

Min(FusionProductCode) FusionProd,

Min(FusionSKUCode) FusionSKU,

Sum(Volume) as Vol,

Sum(revenueUSD) as Rev,

sum(MarginUSD) as Marg

from ILSRep..PL_Sales

Group by Country,Productcode,packcode

) s

ON s.country = t.Country and

s.productcode = t.LocalProductCode and

s.packcode = t.LocalPackCode

Select Country,

LocalProductCode,

LocalProductName,

LocalPackCode,

MatchedFusionProductCode,

Vol,

Rev,

Marg,

ManualCategory,

ManualFusionProductCode,

MatchedFusionSKUCode,

ManualComments

From #t2 WHERE Country = 'JP'

DROP TABLE #t1, #t2

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

priya_patel SSC Veteran Points: 202 More actions · Answer 9

Thanks for all the responses and sorry for replying back so late. Matt's rewrite seemed to help speed things up, thanks Matt.

Brgrds Priya