performance tuning

Question

Post reply

performance tuning

rohitkocharda

Say Hey Kid

Points: 687

April 13, 2021 at 2:07 am

#3870330

Hi everyone,

Any good suggestion to tune below query:

--CREATE NONCLUSTERED INDEX nonclust_code ON [dbo].[DS2ExchQtInfo]

--(Infocode, ExchIntCode)

WITH remove_zeros AS (
SELECT
DS2Security.DsSecCode
, vw_ds2TotalReturn.InfoCode
, vw_ds2TotalReturn.MarketDate
, vw_ds2TotalReturn.ExchIntCode
--, RI = CASE WHEN RI = 0 THEN NULL ELSE RI END /* Avoid divide by zero error */
, return_total = (
vw_ds2TotalReturn.RI 
/ (LAG(CASE WHEN RI = 0 THEN NULL ELSE RI END, 1, NULL) OVER (PARTITION BY vw_ds2TotalReturn.Infocode, vw_ds2TotalReturn.ExchIntCode 
ORDER BY vw_ds2TotalReturn.MarketDate))
) - 1

FROM qai.dbo.vw_ds2TotalReturn

INNER JOIN qai.dbo.DS2Security
ON vw_ds2TotalReturn.InfoCode = DS2Security.PrimQtInfoCode

INNER JOIN qai.dbo.Ds2PrimExchQtChg /* Select primary exchanges */
ON DS2Security.PrimQtInfoCode = Ds2PrimExchQtChg.InfoCode
AND vw_ds2TotalReturn.ExchIntCode = Ds2PrimExchQtChg.ExchIntCode
)

SELECT
remove_zeros.DsSecCode
, remove_zeros.MarketDate
, remove_zeros.ExchIntCode
, remove_zeros.return_total

FROM remove_zeros

INNER JOIN qai.dbo.Ds2PrimExchQtChg /* Select primary exchange by date */
ON remove_zeros.InfoCode = Ds2PrimExchQtChg.InfoCode
AND remove_zeros.ExchIntCode = Ds2PrimExchQtChg.ExchIntCode
AND remove_zeros.MarketDate BETWEEN Ds2PrimExchQtChg.StartDate AND Ds2PrimExchQtChg.EndDate

WHERE DsSecCode = 176862
;

This topic was modified 3 years ago by rohitkocharda.

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

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

rohitkocharda Say Hey Kid Points: 687 More actions · Answer 1

query cost was mostly sorting (80%). I created the non-clustered index on columns Infocode and ExchIntCode but no change in performance.

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 2

There's no obvious issues with the code, so it's really a question of the right indexes to support it. That assumes that the value in the WHERE clause provides actual filtering of data (returning a million rows out of a million & one, that kind of thing is a lack of filtering). Post the execution plan to get better advice. If you can, post the execution plan with runtime metrics. That'll help determine how well statistics and estimates are doing in support of the query.

"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

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 3

A couple of things - not tuning advice, but could help identify possibilities:

Don't include the database in 3-part naming - if the code is running in the context of that database. All tables are referenced using qai.dbo.{object}
Define an alias for each table - and use that alias to reference the columns.

You have a join to the table qai.dbo.Ds2PrimExchQtChg in the CTE - and then again in the final query. The join in the CTE doesn't seem to be right - the join for the table Ds2PrimExchQtChg references columns in 2 different tables for the relationship. It looks like this should be related to the view instead - that should help.

You should also be able to remove the join in the final query - it can be done in the CTE or you can probably just remove the CTE.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Sergiy SSC Guru Points: 110206 More actions · Answer 4

Jeffrey Williams wrote:

... you can probably just remove the CTE.

+1

You definitely would do better without CTE.

And I'd also changed the order of appearance of the objects in the query - to follow the logic of better selectivity:

SELECT
DS.DsSecCode
, TR.MarketDate
, TR.ExchIntCode
--, RI = CASE WHEN RI = 0 THEN NULL ELSE RI END /* Avoid divide by zero error */
, return_total = (
TR.RI 
/ (LAG(CASE WHEN RI = 0 THEN NULL ELSE RI END, 1, NULL) 
OVER (PARTITION BY TR.Infocode, TR.ExchIntCode 
ORDER BY TR.MarketDate))
) - 1
FROM dbo.DS2Security DS 
INNER JOIN dbo.Ds2PrimExchQtChg PE /* Select primary exchanges */
ON DS.PrimQtInfoCode = PE.InfoCode
INNER JOIN dbo.vw_ds2TotalReturn TR ON TR.InfoCode = DS.PrimQtInfoCode
AND TR.ExchIntCode = PE.ExchIntCode
AND TR.MarketDate BETWEEN PE.StartDate AND PE.EndDate

WHERE DsSecCode = 176862

There is also a big question about the insides of dbo.vw_ds2TotalReturn . Any effort of tuning the outer query might be nullified by sub-optimal structure of the view.

_____________
Code for TallyGenerator