CODEWITHSUNDEEP
sqlsql-serverquery-optimization
Anurag Tripathi
Anurag Tripathi

Reputation: 1208

Performance comparison of these two queries

I am using this query to get result, where Calander (primary key: DAY_DATE) has all continuous information about time (i.e. date, quarter, year etc.) and EXPENCE and INCOME both have column TXN_DATE as a foreign key to DAY_DATE.

  SELECT
    COALESCE(t1."FISCAL_YEAR",t2."FISCAL_YEAR") "FISCAL_YEAR" ,
    "exp" "exp" ,
    "rev" "rev" 
    FROM
    (SELECT
    Calander.FISCAL_YEAR "FISCAL_YEAR" ,
    (SUM("EXPENCE"."TXN_AMT" )) "exp" 
    FROM
        Calander ,
        EXPENCE 
    WHERE
        "EXPENCE"."TXN_DATE"="Calander"."DAY_DATE" 
    GROUP BY 
        FISCAL_YEAR ) t1 FULL OUTER JOIN (SELECT
        Calander.FISCAL_YEAR "FISCAL_YEAR" ,
        (SUM("INCOME"."TXN_AMT" )) "rev" 
    FROM
        Calander ,
        INCOME 
    WHERE
        "INCOME"."TXN_DATE"="Calander"."DAY_DATE" 
    GROUP BY 
        FISCAL_YEAR ) t2 ON 
        t1."FISCAL_YEAR"=t2."FISCAL_YEAR"  
    ORDER BY
            COALESCE(t1."FISCAL_YEAR",t2."FISCAL_YEAR")

Now to make query more simple to understand I did this

SELECT
    FISCAL_YEAR "FISCAL_YEAR" ,
    (sum("EXPENCE"."TXN_AMT" )) "exp",
    (sum("INCOME"."TXN_AMT" )) "rev" 
FROM
    Calander ,
    EXPENCE FULL OUTER JOIN INCOME ON
    "EXPENCE"."TXN_DATE" = "INCOME"."TXN_DATE"
WHERE
    "EXPENCE"."TXN_DATE"="Calander"."DAY_DATE" and
    "INCOME"."TXN_DATE"="Calander"."DAY_DATE" 
GROUP BY 
    FISCAL_YEAR
ORDER BY
    FISCAL_YEAR

I am getting same result in both queries. Will the result will be difference in any case (Ist query result is the correct result)? What will be performance impact?

Upvotes: 5

Views: 79

Answers (2)

Amit
Amit

Reputation: 195

The second query will be better performance-wise. In your first query,you are using joins in the two sub-queries to filter the data and then again joining them whereas your second query is more optimized as you are filtering the data by joining all the three tables.One more suggestion,avoid using old style joins to make your queries readable and better.

Upvotes: 0

dean
dean

Reputation: 10098

Second one should be more efficient, but to verify it, check actual execution plans. Also set the 

SET STATISTICS IO ON

on the session and check the logical reads for both queries on your real data.

Another habit to kick - stop using double quotes around identifiers, or prepare to get hit by QUOTED_IDENTIFIER setting sometime :)

Upvotes: 1

Related Questions