New SQL Guy need help

  • I understand what most of this query is doing.  The part i dont understand is the "{ oj...}.  I also noticed the query runs significantly longer the first time.  I believe this has to do with the overhead of parallelism execution.   Does making this a stored procedure reduce how frequently sql server recreates the execution path?

    SELECT DISTINCT

        XVYReportType.xvyrtCode, XVYReportType.xvyrtName, XVYReportType.xvyrtValXName, XVYReportType.xvyrtValYName, XVYReportType.xvyrtPTDName,

        XVY.xvyroBudgetYear, XVY.xvyroMonthNumber, XVY.xvyroMonthName, XVY.xvyroReportPeriod, XVY.xvyroLevel1Account, XVY.xvyroLevel1Name, XVY.xvyroLevel2Account, XVY.xvyroLevel2Name, XVY.xvyroLevel3Account, XVY.xvyroLevel3Name, XVY.xvyroLevelNumber, XVY.xvyroOrd, XVY.xvyroReportType, XVY.xvyroReportControl, XVY.xvyroSubAccount, XVY.xvyroDescrip, XVY.ValXAmount, XVY.ValXPercent, XVY.ValYAmount, XVY.ValYPercent, XVY.Variance, XVY.ValXAmountPTD, XVY.ValXPercentPTD, XVY.ValYAmountPTD, XVY.ValYPercentPTD, XVY.VariancePTD, XVY.xvyroBold, XVY.xvyroRowFormat, XVY.xvyroSuppressPercent

    FROM

        { oj myDatabase.dbo.tblBudgetPlanningXVYReportType XVYReportType INNER JOIN Mydatabase.dbo.tblBudgetPlanningXVYReportOutput XVY ON

            XVYReportType.xvyrtID = XVY.xvyroRTID} WHERE

        XVYReportType.xvyrtCode = 'BVA' AND

        XVY.xvyroMonthNumber = 1 AND

        XVY.xvyroBudgetYear = 2005 AND

        XVY.xvyroReportControl = 'Short1' AND

        XVY.xvyroLevelNumber <= 3 AND

        XVY.xvyroReportType = 'InternalSummary'

     

  • This is ODBC Outer Join Escape Sequence http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcouter_join_escape_sequence.asp

    change your code like below and try if it works:

    "...FROM

        myDatabase.dbo.tblBudgetPlanningXVYReportType XVYReportType INNER JOIN Mydatabase.dbo.tblBudgetPlanningXVYReportOutput XVY ON

            XVYReportType.xvyrtID = XVY.xvyroRTID WHERE..."

    Best Regards,

    Vlad

  • Removing the odbc outer join escape sequence worked, but didnt improve query performance. I guess i need to find a way to optimize this query or reduce the number of times SQL Server recreates the execution plan.

  • If you want some tips how to optimize your query, you should post result of this script here.

    set showplan_text on

    go

    SELECT ... (your select)

    go

    set showplan_text off

    go

    OR You can do it yourself. The main point - you should play with indexes.

    All Index Scan in result of showplan_text should be replaced with Index Seek

    Best Regards,

    Vlad

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

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