performance tuning

  • Hi everyone,

    Any good suggestion to tune below query:


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

    --(Infocode, ExchIntCode)


    WITH remove_zeros AS (
    , 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 = (
    / (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

    , 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


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

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

    1. 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}
    2. 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Jeffrey Williams wrote:

    ... you can probably just remove the CTE.


    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:

    , TR.MarketDate
    , TR.ExchIntCode
    --, RI = CASE WHEN RI = 0 THEN NULL ELSE RI END /* Avoid divide by zero error */
    , return_total = (
    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.

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

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