Slow Query becaose of Self Joining

[tqeonline]

Hey everyone! I have a query that I have to make a little faster and I need some help.

From what I can collect the owner has forced the table to join itself like 3 or 4 times. For the data that is needed from this query it seems to very extensive.

    SelectCommand="SELECT TOP 14
        BG_USER_02 AS 'Application', 
        BG_USER_03 AS 'Environment',
        BG_BUG_ID AS 'Defect',
        RCYC_NAME AS 'DetectedInCycle',
        SUB.MIN_AU_TIME AS 'StartTime',
        CLOSED.MAX_AU_TIME AS 'EndTime',
        Case When (DATEDIFF(ss, SUB.MIN_AU_TIME, CLOSED.MAX_AU_TIME)) >= 86400
        Then Convert(VarChar(5), (DATEDIFF(ss, SUB.MIN_AU_TIME, CLOSED.MAX_AU_TIME))/86400)+ ':'
        Else '0:'
        End
        + Convert(VarChar(8), DateAdd(Second, (DATEDIFF(ss, SUB.MIN_AU_TIME, CLOSED.MAX_AU_TIME)), 0), 108) AS 'DURATION',

        Case 
        When BG_USER_03 = 'QA 01' Then 'QA01'
        When BG_USER_03 = 'QA 02' Then 'QA02'
        When BG_USER_03 = 'QA 03' Then 'QA03'
        When BG_USER_03 = 'QA 04' Then 'QA04'
        When BG_USER_03 = 'QA 84' Then 'QA84'
        When BG_USER_03 = 'QA 89' Then 'QA89'
        Else 'Default'
        End AS 'EnvStyles'
        FROM RELEASE_CYCLES, 

        (SELECT AU_ENTITY_ID, MIN(AU_TIME)AS MIN_AU_TIME
        FROM AUDIT_LOG, AUDIT_PROPERTIES (NOLOCK)
        WHERE AU_ENTITY_TYPE = 'BUG'
        AND AP_FIELD_NAME = 'BG_STATUS'
        AND AU_ACTION_ID = AP_ACTION_ID
        AND AP_NEW_VALUE = 'Submitted'
        GROUP BY AU_ENTITY_ID)AS SUB,

        (SELECT AU_ENTITY_ID, MAX(AU_TIME)AS MAX_AU_TIME
        FROM AUDIT_LOG, AUDIT_PROPERTIES (NOLOCK)
        WHERE AU_ENTITY_TYPE = 'BUG'
        AND AP_FIELD_NAME = 'BG_STATUS'
        AND AU_ACTION_ID = AP_ACTION_ID
        AND AP_NEW_VALUE = 'Closed'
        GROUP BY AU_ENTITY_ID) AS CLOSED,

        BUG LEFT OUTER JOIN
        (SELECT AU_ENTITY_ID, MAX(AU_TIME)AS MAX_AU_TIME
        FROM AUDIT_LOG, AUDIT_PROPERTIES (NOLOCK)
        WHERE AU_ENTITY_TYPE = 'BUG'
        AND AP_FIELD_NAME = 'BG_STATUS'
        AND AU_ACTION_ID = AP_ACTION_ID
        AND AP_NEW_VALUE = 'Deferred'
        GROUP BY AU_ENTITY_ID) AS DEFERRED
        ON DEFERRED.AU_ENTITY_ID = BUG.BG_BUG_ID 

        WHERE BUG.BG_STATUS = 'Closed'
        AND BUG.BG_USER_02 LIKE @Application
        AND BUG.BG_USER_03 LIKE @Environment
        AND ((CLOSED.MAX_AU_TIME <= @EndDate AND CLOSED.MAX_AU_TIME >= @StartDate)
        OR (SUB.MIN_AU_TIME <= @EndDate AND SUB.MIN_AU_TIME >= @StartDate)
        OR (SUB.MIN_AU_TIME > @StartDate AND CLOSED.MAX_AU_TIME < @EndDate))
        AND SUB.AU_ENTITY_ID = BUG.BG_BUG_ID
        AND CLOSED.AU_ENTITY_ID = BUG.BG_BUG_ID
        AND BG_DETECTED_IN_RCYC = RCYC_ID
        AND DEFERRED.MAX_AU_TIME IS NULL
        AND BG_SEVERITY = '0-Critical Show Stopper'
        ORDER BY SUB.MIN_AU_TIME">
        <SelectParameters>
            <asp:ControlParameter ControlID="ddlApplication" Name="Application" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="ddlEnvironment" Name="Environment" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="lblEnd" Name="EndDate" PropertyName="Text" Type="String" />
            <asp:ControlParameter ControlID="lblStart" Name="StartDate" PropertyName="Text" Type="String" />
        </SelectParameters>

All I need from it is: Application, Environment, Defect, DetectedInCycle, StartTime, EndTime, Duration...

any quick tips?

 

[SQLSister]

OK let's start by running this while profiler is running and send us the real SQL that SQL Server will run, I can't make heads or tails out of this.

One thing though that you desperately need to do to make this make sense is to get rid fothe implied joins. Implied joins combined with explicit joins are bad news and can have inconsistent results, so your result set may not even be correct. There is no excuse ever to use an implied join. Nor is it readable with this horrible combination of joins, what is joining to what?

Next once you have the real SQl that is being run, put it into a window in SSMS and get an execution plan. You need to see that to optimize. YOu can then see if it is using indexes or tables scalns etc. Report back to us when you have a better idea of the problem as well as a readable query with explicit joins.

"NOTHING is more important in a database than integrity." ESquared