Query Performance tuning

Question

Query Performance tuning

Vijay_Raju

Valued Member

Points: 67
More actions
September 29, 2010 at 3:26 am

#224448

I have self join query which is taking very long time to execute as it is cost about 30000 and executing for about 55 minutes.
Can any one suggest how to reduce the cost effectively.
This table contains 17 million rows .
select a.secid, a.tradeDate,
a.marketCap/b.marketCap - 1
from SecMarketCap a, SecMarketCap b
where b.secId = a.secId
and b.tradeDate
= (select max(tradeDate) from SecMarketCap
where secId = a.secId
and tradeDate < a.tradeDate
)
and b.tradeDate >= a.tradeDate - 3
and b.marketCap > 1.00
and b.tradeDate > '1998-01-01'

Viewing 4 posts - 1 through 3 (of 3 total)

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

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

Please post table definitions, index definitions and execution plan as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Have you tried using CTE's and ROW_NUMBER() for this?

;WITH PartitionedData AS

(SELECT secid, marketCap, tradeDate,

RowID = ROW_NUMBER() OVER(PARTITION BY secid ORDER BY tradeDate DESC)

FROM SecMarketCap

)

SELECT a.secid,

a.tradeDate,

a.marketCap/b.marketCap - 1

FROM PartitionedData a

INNER JOIN PartitionedData b

ON b.secId = a.secId AND b.RowId = a.RowId+1

WHERE b.tradeDate >= a.tradeDate - 3

AND b.marketCap > 1.00

AND b.tradeDate > '1998-01-01'

SecID and TradeDate should be indexed.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Vijay_Raju Valued Member Points: 67 More actions · Answer 3

Yes , thank you for the reply.

Iam dividing the whole query to 20 different iterations and executing like below.

SET nocount on

declare @rownum int

declare @iteration int = 20

select @rownum = count(*) from SecMarketCap

declare @factor int = @rownum/@iteration

declare @i1 int = 0

declare @i2 int

declare @loopcount int = 1

while (@loopcount<=@iteration)

BEGIN

select @i2 = secid from (

SELECT ROW_NUMBER () OVER (ORDER BY secID) AS RowNumber, *

FROM SecMarketCap ) t

where RowNumber= @factor * @loopcount

insert SecMktCapReturn (secId, tradeDate, mcReturn)

select a.secid,

a.tradeDate,

a.marketCap/b.marketCap - 1

from SecMarketCap a, SecMarketCap b

where b.secId = a.secId

and b.tradeDate

= (select max(tradeDate) from SecMarketCap

where secId = a.secId

and tradeDate < a.tradeDate

)

and b.tradeDate >= a.tradeDate - 3

and b.marketCap > 1.00 and b.tradeDate > '1998-01-01'

and a.secId >@i1 and a.secId <= @i2

--print cast(@i1 as varchar(10)) + ' to '+ cast(@i2 as varchar(10))

set @loopcount = @loopcount+1

set @i1=@i2

END