Home Forums SQL Server 2008 T-SQL (SS2K8) Migration from MSDE (SQL2K) to SQLEXPRESS (SQL2K8) - Query Performance Degradation RE: Migration from MSDE (SQL2K) to SQLEXPRESS (SQL2K8) - Query Performance Degradation

  • April 11, 2013 at 3:28 am

    #1605491

    -- Here's the last change.

    USE [Build1]

    SELECT

    td.TYPE_NAME

    ,i.DOC_NAME

    ,i.DOC

    ,i.SEC

    ,i.VER

    ,r.JUSTIFICATION

    FROM TBL_INSTANCES i

    INNER JOIN TBL_TYPE_DEF td

    ON i.TYPE_ID = td.TYPE_ID

    LEFT JOIN TBL_RELATIONSHIPS r

    ON i.DOC = r.PARENT_DOC

    AND i.SEC = r.PARENT_SEC

    AND i.VER = r.PARENT_VER

    -- simplification

    WHERE NOT EXISTS (

    SELECT 1

    FROM TBL_RELATIONSHIPS ri

    WHERE ri.PARENT_DOC = i.DOC

    AND ri.PARENT_SEC = i.SEC

    AND ri.PARENT_VER = i.VER

    AND ri.CHILD_DOC IS NOT NULL

    )

    -- simplification

    AND i.TYPE_ID NOT IN (SELECT CHILD_TYPE FROM TBL_RELATIONSHIPS_ALLOWED)

    ORDER BY td.TYPE_ID

    ,i.DOC

    ,i.SEC

    ,i.VER

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden