help to improve the performance of sql

Question

help to improve the performance of sql

sushil_dwid

Ten Centuries

Points: 1241
More actions
March 7, 2015 at 11:06 am

#298397

Hi,
Below is the sql which we use to fetch the data in report. When this query runs for 1 month data, it gives better result but when it runs for more than that.. it takes time.
Could you please suggest your view to improve the performance of this sql.
WITH TList AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY art.AlertId DESC, t.AgreedTime ASC, t.TradeId ASC, t.TradeEventId ASC) AS [RowNumber],
t.TradeEventId, t.TradeId, t.AgreedTime, bp.PartyCode AS BuyPartyCode,
sp.PartyCode AS SellPartyCode, t.BaseCcyPremium, et.EventTypeName as EventType,
bp.PartyName as BuyPartyName, sp.PartyName as SellPartyName,
s.InstrumentCode,s.InstrumentName as InstrumentShortName , s.Currency,
bt.TraderCode as BuyTraderCode, st.TraderCode as SellTraderCode, bt.TraderName as BuyTraderName, st.TraderName as SellTraderName,
bo.TraderName as BookOwner,t.IsMonitored,t.LegCount,t.SecurityType,
case when (art.AlertId IS NULL) then 0 else art.AlertId end as AlertId,
case when (art.AlertId = @AlertId) then 'Y' else 'N' end as AlertRequested,
--t.TradeTime1 as LocalTime,--need
case when t.Side = 1 then CAST('Buy' AS CHAR(4))
when t.Side = 2 then CAST('Sell' AS CHAR(4))
else CAST('N/A' AS CHAR(4))
end as BuySell,
t.MaturityDate,
t.TransactionSourceSystem,
t.CounterpartyName,
tmr.MarketBid,
tmr.MarketOffer,
tmr.MtMRate,
t.OptionType,
--Adding LegInfo
tl.LegId, tl.BaseCcyPayAmount,tl.BaseCcyReceiveAmount,tl.PayAmount,tl.ReceiveAmount,tl.Price,tl.Quantity,tl.BaseCcyQuantity,
tl.startdate as legstartdate,tl.enddate as legEnddate ,pp.PartyName as PayParty,rp.PartyName as ReceiveParty,tl.payamountcurrency,tl.receiveamountcurrency,
case when tl.Side = 1 then CAST('Buy' AS CHAR(4))
when tl.Side = 2 then CAST('Sell' AS CHAR(4))
else CAST('N/A' AS CHAR(4)) end as LegBuySell
FROM
[dbo].[Tt] t WITH (nolock)
LEFT OUTER JOIN [dbo].[Tl] tl WITH (nolock) ON t.TradeId=tl.TradeId
INNER JOIN [dbo].[RefTradeEventType] et WITH (nolock) ON t.EventType = et.EventTypeCode
INNER JOIN [dbo].[DParty] bp WITH (nolock) ON t.DimBuyPartyId = bp.DimPartyId
INNER JOIN [dbo].[DParty] sp WITH (nolock) ON t.DimSellPartyId = sp.DimPartyId
INNER JOIN [dbo].[DParty] pp WITH (nolock) ON tl.DimPayPartyId = pp.DimPartyId
INNER JOIN [dbo].[DParty] rp WITH (nolock) ON tl.DimReceivePartyId = rp.DimPartyId
INNER JOIN [dbo].[DInstrument] s WITH (nolock) ON t.DimInstrumentId = s.DimInstrumentId
INNER JOIN [dbo].[DSecurityClass] ds WITH(nolock) ON s.SecurityClass=ds.SecurityClassId
LEFT OUTER JOIN [dbo].[DTrader] bt WITH (nolock) ON t.DimBuyTraderId = bt.DimTraderId
LEFT OUTER JOIN [dbo].[DTrader] st WITH (nolock) ON t.DimSellTraderId = st.DimTraderId
LEFT OUTER JOIN [dbo].[DTrader] bo WITH (nolock) ON t.DimBookOwnerId = bo.DimTraderId
LEFT OUTER JOIN [dbo].[ARelatedTt] art WITH (nolock) ON t.TradeEventId = art.TradeEventId
LEFT OUTER JOIN [dbo].[TmR] tmr WITH (nolock) ON t.TradeId = tmr.TradeId
WHERE
t.DimAgreedDate +t.DimAgreedTimeOfDay >=@FromDate + @FromTime AND
t.DimAgreedDate +t.DimAgreedTimeOfDay <=@ToDate + @ToTime AND
((s.InstrumentCode IN ( SELECT InstrumentCode FROM @InstrumentCode_List )) OR (@InstrumentCode IS NULL)) AND -- Match SecurityId if its specified , or match all if its NULL
((bp.PartyCode IN ( SELECT PartyCode FROM @PartyCode_List )) OR (sp.PartyCode IN ( SELECT PartyCode FROM @PartyCode_List )) OR (@PartyCode IS NULL)) AND -- Match PartyId if its specified , or match all if its NULL
((bt.TraderCode = @TraderCode) OR (st.TraderCode = @TraderCode) OR (@TraderCode IS NULL)) AND -- Match Trader if its specified , or match all if its NULL http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif
t.EventType <> 'CR' AND
((ds.SecClassId IN ( SELECT SecurityId FROM @SecurityId_List )) OR (@SecurityIds IS NULL)) -- Match list from temporary table, or all if SecurityId is NULL
AND t.ACs='FX'
)
SELECT * FROM TradeList
WHERE
( RowNumber >= @StartItemIndex AND
RowNumber < (@StartItemIndex + @MaxItemCount) )
ORDER BY --AlertRequested DESC,
AlertId DESC,
RowNumber;

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

sushil_dwid (3/7/2015)
Hi,
Below is the sql which we use to fetch the data in report. When this query runs for 1 month data, it gives better result but when it runs for more than that.. it takes time.
Could you please suggest your view to improve the performance of this sql.
WITH TList AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY art.AlertId DESC, t.AgreedTime ASC, t.TradeId ASC, t.TradeEventId ASC) AS [RowNumber],
t.TradeEventId, t.TradeId, t.AgreedTime, bp.PartyCode AS BuyPartyCode,
sp.PartyCode AS SellPartyCode, t.BaseCcyPremium, et.EventTypeName as EventType,
bp.PartyName as BuyPartyName, sp.PartyName as SellPartyName,
s.InstrumentCode,s.InstrumentName as InstrumentShortName , s.Currency,
bt.TraderCode as BuyTraderCode, st.TraderCode as SellTraderCode, bt.TraderName as BuyTraderName, st.TraderName as SellTraderName,
bo.TraderName as BookOwner,t.IsMonitored,t.LegCount,t.SecurityType,
case when (art.AlertId IS NULL) then 0 else art.AlertId end as AlertId,
case when (art.AlertId = @AlertId) then 'Y' else 'N' end as AlertRequested,
--t.TradeTime1 as LocalTime,--need
case when t.Side = 1 then CAST('Buy' AS CHAR(4))
when t.Side = 2 then CAST('Sell' AS CHAR(4))
else CAST('N/A' AS CHAR(4))
end as BuySell,
t.MaturityDate,
t.TransactionSourceSystem,
t.CounterpartyName,
tmr.MarketBid,
tmr.MarketOffer,
tmr.MtMRate,
t.OptionType,
--Adding LegInfo
tl.LegId, tl.BaseCcyPayAmount,tl.BaseCcyReceiveAmount,tl.PayAmount,tl.ReceiveAmount,tl.Price,tl.Quantity,tl.BaseCcyQuantity,
tl.startdate as legstartdate,tl.enddate as legEnddate ,pp.PartyName as PayParty,rp.PartyName as ReceiveParty,tl.payamountcurrency,tl.receiveamountcurrency,
case when tl.Side = 1 then CAST('Buy' AS CHAR(4))
when tl.Side = 2 then CAST('Sell' AS CHAR(4))
else CAST('N/A' AS CHAR(4)) end as LegBuySell
FROM
[dbo].[Tt] t WITH (nolock)
LEFT OUTER JOIN [dbo].[Tl] tl WITH (nolock) ON t.TradeId=tl.TradeId
INNER JOIN [dbo].[RefTradeEventType] et WITH (nolock) ON t.EventType = et.EventTypeCode
INNER JOIN [dbo].[DParty] bp WITH (nolock) ON t.DimBuyPartyId = bp.DimPartyId
INNER JOIN [dbo].[DParty] sp WITH (nolock) ON t.DimSellPartyId = sp.DimPartyId
INNER JOIN [dbo].[DParty] pp WITH (nolock) ON tl.DimPayPartyId = pp.DimPartyId
INNER JOIN [dbo].[DParty] rp WITH (nolock) ON tl.DimReceivePartyId = rp.DimPartyId
INNER JOIN [dbo].[DInstrument] s WITH (nolock) ON t.DimInstrumentId = s.DimInstrumentId
INNER JOIN [dbo].[DSecurityClass] ds WITH(nolock) ON s.SecurityClass=ds.SecurityClassId
LEFT OUTER JOIN [dbo].[DTrader] bt WITH (nolock) ON t.DimBuyTraderId = bt.DimTraderId
LEFT OUTER JOIN [dbo].[DTrader] st WITH (nolock) ON t.DimSellTraderId = st.DimTraderId
LEFT OUTER JOIN [dbo].[DTrader] bo WITH (nolock) ON t.DimBookOwnerId = bo.DimTraderId
LEFT OUTER JOIN [dbo].[ARelatedTt] art WITH (nolock) ON t.TradeEventId = art.TradeEventId
LEFT OUTER JOIN [dbo].[TmR] tmr WITH (nolock) ON t.TradeId = tmr.TradeId
WHERE
t.DimAgreedDate +t.DimAgreedTimeOfDay >=@FromDate + @FromTime AND
t.DimAgreedDate +t.DimAgreedTimeOfDay <=@ToDate + @ToTime AND
((s.InstrumentCode IN ( SELECT InstrumentCode FROM @InstrumentCode_List )) OR (@InstrumentCode IS NULL)) AND -- Match SecurityId if its specified , or match all if its NULL
((bp.PartyCode IN ( SELECT PartyCode FROM @PartyCode_List )) OR (sp.PartyCode IN ( SELECT PartyCode FROM @PartyCode_List )) OR (@PartyCode IS NULL)) AND -- Match PartyId if its specified , or match all if its NULL
((bt.TraderCode = @TraderCode) OR (st.TraderCode = @TraderCode) OR (@TraderCode IS NULL)) AND -- Match Trader if its specified , or match all if its NULL http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif
t.EventType <> 'CR' AND
((ds.SecClassId IN ( SELECT SecurityId FROM @SecurityId_List )) OR (@SecurityIds IS NULL)) -- Match list from temporary table, or all if SecurityId is NULL
AND t.ACs='FX'
)
SELECT * FROM TradeList
WHERE
( RowNumber >= @StartItemIndex AND
RowNumber < (@StartItemIndex + @MaxItemCount) )
ORDER BY --AlertRequested DESC,
AlertId DESC,
RowNumber;

Quite possibly, the single largest problem is that there are a couple of statements in the WHERE clause are non-SARGable, which virtually guarantees that there's no chance of index seeks on the affected tables. Only index scans and table scans can be realized. Any time you include a column as part of a formula or wrap it in a function, the WHERE clause becomes non-SARGable. You need to fix that problem first.

As a bit of a sidebar, a lot of the non-SARGability comes from the fact that the Date and Time columns are being added and this is why I stress that you should almost never split dates and times.

My recommendation would be to cuddle up to an "actual execution" plan and look for things like arrows that have many more rows than the source tables (there's bound to be at least 1 accidental many-to-many join in all that) and the absence of index seeks with embedded range scans.

--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)

MadAdmin SSChampion Points: 11261 More actions · Answer 2

Either dynamic sql to get rid of all the the scan inducing OR's or I think you can use with recompile if it is in a proc.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

You also should not litter your database with the NOLOCK hint. It seems to be on every one of your tables here. Are you with missing and/or duplicate data?

Here is a short article that discusses some of the major issues with that hint. There are dozens and dozens more about the pitfalls of that hint.

http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

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

sushil_dwid Ten Centuries Points: 1241 More actions · Answer 5

I appreciate your suggestions. I tried to manage and want to share you that. I removed CTE and put order by on same set of columns that were defined on Row_number(). I selected only number of rows which was required(like top 10k).. same like below..

SELECT

top 10000

--ROW_NUMBER() OVER (ORDER BY art.AlertId DESC, t.AgreedTime ASC, t.TradeId ASC, t.TradeEventId ASC) AS [RowNumber]

from

order by ORDER BY art.AlertId DESC, t.AgreedTime ASC, t.TradeId ASC, t.TradeEventId ASC