Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Query performance tuning - Slow execution time RE: Query performance tuning - Slow execution time

  • August 16, 2017 at 6:04 pm

    #1955592

    Jonathan AC Roberts - Tuesday, August 15, 2017 11:52 AM

    With these type of query you can sometimes get a lot of success from breaking it up into several queries inserting the data into temporary tables, adding indexes to the temporary tables and then querying the temporary tables to get the results. You could start off by inserting the results of he function into a temporary table:
    IF OBJECT_ID('temdb..#ReportDealCore','U') IS NOT NULL
      DROP TABLE #ReportDealCore

    SELECT FDC.DealID,
       FDC.StageID,
       FDC.PlatformID
    INTO #ReportDealCore
    FROM report.fnReportDealCore(@StartDate, @EndDate, @Type) AS FDC

    Then query the able  #ReportDealCore instead of calling the function in the query.

    Then just select several tables from the query join them together and insert the results into another temporary table, then build up the whole query from joins to the temporary tables,

    Preach it, Brother! 😉  A whole lot of people don't understand that "set based" doesn't actually mean "all in one query" especially when a DISTICT has to be used to overcome the accidental many-to-many joins formed by such monsters.

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