Performance Issue

Question

Performance Issue

gagandeep.singh

Newbie

Points: 1
More actions
April 19, 2010 at 2:45 pm

#139685

Is there a better way to write the following query in terms of performance without adding any indexes?
SELECT T.amount, R.RATE, T.amount * R.RATE AS AMOUNT_CA, trade_date, FWR_DATE, trade_ccy, CCY FROM
OPD.S_RATES R INNER JOIN [OPD].TRADE T
ON FWR_DATE = (SELECT MAX(R1.FWR_DATE) FROM OPD.S_RATES R1 WHERE FWR_DATE<=T.trade_date AND T.trade_ccy = R1.CCY
)
AND T.trade_ccy = R.CCY
order by trade_date, trade_ccy

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

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Can you post the table DDL for the 2 tables in question as well as some sample data? I have some recommendations for how to do that in the link in my signature line.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 2

gagandeep.singh (4/19/2010)
Is there a better way to write the following query in terms of performance without adding any indexes?
SELECT T.amount, R.RATE, T.amount * R.RATE AS AMOUNT_CA, trade_date, FWR_DATE, trade_ccy, CCY FROM
OPD.S_RATES R INNER JOIN [OPD].TRADE T
ON FWR_DATE = (SELECT MAX(R1.FWR_DATE) FROM OPD.S_RATES R1 WHERE FWR_DATE<=T.trade_date AND T.trade_ccy = R1.CCY
)
AND T.trade_ccy = R.CCY
order by trade_date, trade_ccy

What you have there is a classic "Triangular Join" and, depending on the data and the number of rows actually affected, can be devastating to performance and resource usage. Please see the following link for why it's so bad.

http://www.sqlservercentral.com/articles/T-SQL/61539/

Your problem may be solvable using ROW_NUMBER() OVER with a descending sort and a "partition" but, as has already been suggested, most of us like to test our code against some tables and data. Please see the link that John pointed out in his post above for how to accomplish that. Done correctly, people will trip over each other trying to help you with your problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)