Extracting WHERE clause to speed up query

The query shown in “Section 1” below takes over 2.5 hrs to complete. I have been tasked with speeding it up and I have a question about whether a change that I made is legitimate (i.e. will not change the result). My modified code completes in < 30 mins. Many thanks.

1) ORIGINAL QUERY

  • How does Sql server will identify Tomcat server?
  • If I restore a database and overwrite the original is there any way to go back? SQL Management Studio
  • CREATE TABLE with FK and index options declarations
  • MERGE INTO conversion from db2 to SQL Server
  • Build Window Server 2012 R2 to host MS SQL Server 2015 capacity 200GB
  • sql for Active and Inactive status
  • SELECT i.FundCD
        ,i.MaxDate
        ,v1.InfoCodeName AS ParentInfoCodeName
        ,v2.InfoCodeName
        ,FieldValue
        ,i.NotFormattedDecimalValue AS FieldValue
        ,i.AsOfDate
    INTO #tmp_hfri
    FROM pmw.dbo.vInfoCodeWithHierarchy v1
    INNER JOIN pmw.dbo.vInfoCodeWithHierarchy v2 ON v1.CodeNode = v2.ParentCodeNode
    INNER JOIN pmw.dbo.vFundInfo i ON v2.InfoCodeID = i.CodeID
    WHERE v1.InfoCodeID IN (
            692857
            ,693600
            )
    

    2) I saw that the WHERE clause in the last line requires one of two values be present in v1.InfoCodeID. v1 So I figured that I could do a prior select of rows in v1 with those values of v1.InfoCodeID and use that in the query rather than all the rows in v1 (1049 rows) in order to speed up the full query.

    3) I did:

    SELECT *
       INTO #t1
       FROM pmw.dbo.vInfoCodeWithHierarchy v1 
       WHERE v1.InfoCodeID in (692857,693600)
    

    4) Then ran this query. It takes < 30mins.

    SELECT i.FundCD
        ,i.MaxDate
        ,v1.InfoCodeName AS ParentInfoCodeName
        --convert(float,replace(i.FieldValue,'%',''))*0.01 as FieldValue,
        ,v2.InfoCodeName
        ,i.NotFormattedDecimalValue AS FieldValue
        ,i.AsOfDate
    INTO #tmp_hfri
    FROM #t1 v1
    INNER JOIN #t1 v2 ON v1.CodeNode = v2.ParentCodeNode
    INNER JOIN pmw.dbo.vFundInfo i ON v2.InfoCodeID = i.CodeID
    WHERE v1.InfoCodeID IN (
            692857
            ,693600
            )
    

    One Solution collect form web for “Extracting WHERE clause to speed up query”

    Yes it is a legitimate technique, but in this case you are potentially excluding rows. In the first query you are only limiting the results of v1 with WHERE v1.InfoCodeID IN (692857,693600), but in the second query because you are referencing the temporary table in place of v2, you are also limiting the results here, your second query is equivalent to:

    SELECT  i.FundCD,
            i.MaxDate,
            v1.InfoCodeName AS ParentInfoCodeName,
            --convert(float,replace(i.FieldValue,'%',''))*0.01 as FieldValue,
            v2.InfoCodeName,
            i.NotFormattedDecimalValue AS FieldValue,
            i.AsOfDate
    INTO    #tmp_hfri
    FROM    pmw.dbo.vInfoCodeWithHierarchy v1
            INNER JOIN pmw.dbo.vInfoCodeWithHierarchy v2 
                ON v1.CodeNode = v2.ParentCodeNode
            INNER JOIN pmw.dbo.vFundInfo i 
                ON v2.InfoCodeID = i.CodeID
    WHERE   v1.InfoCodeID IN (692857 ,693600)
    AND     v2.InfoCodeID IN (692857 ,693600); -- ADDITIONAL FILTER 
    

    The reason you are seeing the performance difference is down to intermediate materialisation. It is a legitimate technique to force intermediate materialisation by executing smaller parts and storing the result in a temp table though. Adam Machanic has written a pretty good article on it, and there is
    also a connect item open for sql server to add a query hint to allow users to force the intermediate materialisation of a result set to avoid the need for a temp table.

    You may still find that using the temp table is faster, but you would still need to refer to the view for v2:

    SELECT CodeNode, InfoCodeName -- ONLY PUT FIELDS YOU NEED INTO THE TEMP TABLE
    INTO #t1
    FROM pmw.dbo.vInfoCodeWithHierarchy v1 
    WHERE v1.InfoCodeID in (692857,693600)
    
    SELECT  i.FundCD,
            i.MaxDate,
            v1.InfoCodeName AS ParentInfoCodeName,
            --convert(float,replace(i.FieldValue,'%',''))*0.01 as FieldValue,
            v2.InfoCodeName,
            i.NotFormattedDecimalValue AS FieldValue,
            i.AsOfDate
    INTO    #tmp_hfri
    FROM    #t1 v1
            INNER JOIN pmw.dbo.vInfoCodeWithHierarchy v2 
                ON v1.CodeNode = v2.ParentCodeNode
            INNER JOIN pmw.dbo.vFundInfo i 
                ON v2.InfoCodeID = i.CodeID;
    

    If you are still encountering performance issues (which even with the query reduced to 30 minutes I would guess you are), then you need to look at the execution plan, identify the bottlenecks and possibly create some indexes.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.