SQL Server Performance

Urgent Problem

Discussion in 'T-SQL Performance Tuning for Developers' started by aspire, Oct 28, 2005.

  1. aspire New Member

    Hi All,

    I am facing very strange problem with one query. The query is generated by the application
    itself(adhoc query). This query is combination of 3 queries. If we execute all the 3 queries seperately say D1,D2 and D3.
    Then query D1 gives 13 records
    query D2 gives 6 records
    query D3 gives 13 records.
    But when we combine query D1 and D2(D) it again gives 13 records. When we combine D3 with D1 and D2 and execute complete query
    it gives only 4 records whereas it should give same 13 records.

    Where condition in all the queries are absolutely same.

    complete query using "first_name" and "last_name" as joining condition between query D3 and (D1+D2)(D).

    At the same time when i add "ltrim" and "rtrim" in the joining clause complete query gives expected 13 records

    Please see below the query and let me know if any suggestion.

    select * from
    --Start of D(combination D1 & D2 inline queries)
    (SELECT * FROM
    --Start of D1
    (select
    count(distinct T161609."OPTY_ID") as c1,
    sum(T161609."PRIM_REP_REVN_AMT") as c2,
    T40043."LVL6ANC_DIVN" as c3,
    T40043."LVL7ANC_DIVN" as c4,
    T158095."PRIM_REP_GRD_CD" as c5,
    T38942."EMP_LAST_NAME" + ' '+T38942."EMP_FST_NAME" as c6,
    T40043."LVL5ANC_DIVN" as c7
    from
    "W_SSTAGE_D" T31842,
    "W_DAY_D" T33083,
    "W_POSITION_D" T38942,
    "WCX_RPTG_PARM" T148553,
    "WCX_OPPORTUNITY_SCD" T158095,
    "WCX_PIPELINE_POSITION_SALES_F_SNAP" T161609,
    "W_POSITION_DH" T40043
    where
    T148553."ROW_WID" >= T161609."FACT_STRT_DT_WID"
    and T158095."ROW_WID" = T161609."OPTY_WID"
    and T148553."ROW_WID" < T161609."FACT_END_DT_WID"
    and T31842."ROW_WID" = T161609."CURR_STG_WID"
    and T33083."ROW_WID" = T161609."PRIM_REP_REVN_DT_WID"
    and T38942."ROW_WID" = T40043."ROW_WID"
    and T38942."ROW_WID" = T161609."POS_WID"
    and case when T158095."PRIM_REP_CMTD_INDC" = 1 then 'Y' else 'N' end = 'Y'
    and T158095."PRIM_REP_GRD_CD" = '1'
    and T148553."RPTG_PARM_DT" = convert(datetime, '2005-10-26 00:00:00', 120)
    and case 8.0 when 1 then T40043."LVL1ANC_POSTN_ID" when 2 then T40043."LVL2ANC_POSTN_ID"
    when 3 then T40043."LVL3ANC_POSTN_ID" when 4 then T40043."LVL4ANC_POSTN_ID"
    when 5 then T40043."LVL5ANC_POSTN_ID" when 6 then T40043."LVL6ANC_POSTN_ID"
    when 7 then T40043."LVL7ANC_POSTN_ID" when 8 then T40043."LVL8ANC_POSTN_ID"
    when 9 then T40043."TOP_LVL_POSTN_ID" end = '1-14ORTY'
    and T31842."METHOD_NAME" = 'Default Sales Methodology'
    and case when T161609."RPTB_INDC" = 1 then 'Y' else 'N' end = 'Y'
    and (T158095."STAT_TXT" = 'Closed' or T158095."STAT_TXT" = 'Open')
    and (T158095."OPTY_TYP_TXT" = 'New Contract' or T158095."OPTY_TYP_TXT" = 'Renewal')
    and T33083."PER_NAME_MONTH" between '2005 / 10' and '2005 / 10'
    group by T40043."LVL5ANC_DIVN", T40043."LVL6ANC_DIVN",
    T40043."LVL7ANC_DIVN", T158095."PRIM_REP_GRD_CD",
    T38942."EMP_LAST_NAME" + ' '+T38942."EMP_FST_NAME"
    ) D1,-- End of D1
    -- Start of D2
    (select
    count(distinct T161609."OPTY_ID") as c8,
    T40043."LVL5ANC_DIVN" as c9,
    T158095."PRIM_REP_GRD_CD" as c10
    from
    "W_SSTAGE_D" T31842,
    "W_DAY_D" T33083,
    "W_POSITION_D" T38942,
    "WCX_RPTG_PARM" T148553,
    "WCX_OPPORTUNITY_SCD" T158095,
    "WCX_PIPELINE_POSITION_SALES_F_SNAP" T161609,
    "W_POSITION_DH" T40043
    where
    T148553."ROW_WID" >= T161609."FACT_STRT_DT_WID"
    and T158095."ROW_WID" = T161609."OPTY_WID"
    and T148553."ROW_WID" < T161609."FACT_END_DT_WID"
    and T31842."ROW_WID" = T161609."CURR_STG_WID"
    and T33083."ROW_WID" = T161609."PRIM_REP_REVN_DT_WID"
    and T38942."ROW_WID" = T40043."ROW_WID"
    and T38942."ROW_WID" = T161609."POS_WID"
    and case when T158095."PRIM_REP_CMTD_INDC" = 1 then 'Y' else 'N' end = 'Y'
    and T158095."PRIM_REP_GRD_CD" = '1'
    and T148553."RPTG_PARM_DT" = convert(datetime, '2005-10-26 00:00:00', 120)
    and case 8.0 when 1 then T40043."LVL1ANC_POSTN_ID" when 2 then T40043."LVL2ANC_POSTN_ID"
    when 3 then T40043."LVL3ANC_POSTN_ID" when 4 then T40043."LVL4ANC_POSTN_ID"
    when 5 then T40043."LVL5ANC_POSTN_ID" when 6 then T40043."LVL6ANC_POSTN_ID"
    when 7 then T40043."LVL7ANC_POSTN_ID" when 8 then T40043."LVL8ANC_POSTN_ID"
    when 9 then T40043."TOP_LVL_POSTN_ID" end = '1-14ORTY'
    and T31842."METHOD_NAME" = 'Default Sales Methodology'
    and case when T161609."RPTB_INDC" = 1 then 'Y' else 'N' end = 'Y'
    and (T158095."STAT_TXT" = 'Closed' or T158095."STAT_TXT" = 'Open')
    and (T158095."OPTY_TYP_TXT" = 'New Contract' or T158095."OPTY_TYP_TXT" = 'Renewal')
    and T33083."PER_NAME_MONTH" between '2005 / 10' and '2005 / 10'
    group by T40043."LVL5ANC_DIVN", T158095."PRIM_REP_GRD_CD"
    ) D2--End of D2
    where
    (D1.c5 = D2.c10 or (D1.c5 is null and D2.c10 is null)) and (D1.c7 = D2.c9)
    )
    D,--End of D
    --Start of D3
    (select
    count(distinct T161609."OPTY_ID") as c4,
    T40043."LVL5ANC_DIVN" as c5,
    T38942."EMP_LAST_NAME" + ' '+T38942."EMP_FST_NAME" as c6
    from
    "W_SSTAGE_D" T31842,
    "W_DAY_D" T33083,
    "W_POSITION_D" T38942,
    "WCX_RPTG_PARM" T148553,
    "WCX_OPPORTUNITY_SCD" T158095,
    "WCX_PIPELINE_POSITION_SALES_F_SNAP" T161609,
    "W_POSITION_DH" T40043
    where
    T148553."ROW_WID" >= T161609."FACT_STRT_DT_WID"
    and T158095."ROW_WID" = T161609."OPTY_WID"
    and T148553."ROW_WID" < T161609."FACT_END_DT_WID"
    and T31842."ROW_WID" = T161609."CURR_STG_WID"
    and T33083."ROW_WID" = T161609."PRIM_REP_REVN_DT_WID"
    and T38942."ROW_WID" = T40043."ROW_WID"
    and T38942."ROW_WID" = T161609."POS_WID"
    and case when T158095."PRIM_REP_CMTD_INDC" = 1 then 'Y' else 'N' end = 'Y'
    and T158095."PRIM_REP_GRD_CD" = '1'
    and T148553."RPTG_PARM_DT" = convert(datetime, '2005-10-26 00:00:00', 120)
    and case 8.0 when 1 then T40043."LVL1ANC_POSTN_ID" when 2 then T40043."LVL2ANC_POSTN_ID"
    when 3 then T40043."LVL3ANC_POSTN_ID" when 4 then T40043."LVL4ANC_POSTN_ID"
    when 5 then T40043."LVL5ANC_POSTN_ID" when 6 then T40043."LVL6ANC_POSTN_ID"
    when 7 then T40043."LVL7ANC_POSTN_ID" when 8 then T40043."LVL8ANC_POSTN_ID"
    when 9 then T40043."TOP_LVL_POSTN_ID" end = '1-14ORTY'
    and T31842."METHOD_NAME" = 'Default Sales Methodology'
    and case when T161609."RPTB_INDC" = 1 then 'Y' else 'N' end = 'Y'
    and (T158095."STAT_TXT" = 'Closed' or T158095."STAT_TXT" = 'Open')
    and (T158095."OPTY_TYP_TXT" = 'New Contract' or T158095."OPTY_TYP_TXT" = 'Renewal')
    and T33083."PER_NAME_MONTH" between '2005 / 10' and '2005 / 10'
    group by T40043."LVL5ANC_DIVN", T38942."EMP_LAST_NAME" + ' '+T38942."EMP_FST_NAME"
    ) D3--End of D3
    WHERE
    D.C6=D3.C6


    Thanks in advance,
    aspire

  2. Madhivanan Moderator

    When you say combine do you mean JOIN?

    Madhivanan

    Failing to plan is Planning to fail
  3. aspire New Member

    Yes Madhivanan,

    We combined query D1 and D2 adding where clause in the end..
  4. Madhivanan Moderator

    Then there is chance that data will be filtered according to the condion at the Join

    Madhivanan

    Failing to plan is Planning to fail
  5. Chappy New Member

    When you use this sort of joining

    SELECT
    TABLE1,
    TABLE2
    WHERE
    TABLE1.ID = TABLE2.ID

    You are using an implicit INNER JOIN. This will filter out any records from TABLE1 which do not have corresponding records in TABLE2, and vice versa.

    Its more readable to do this..

    SELECT
    TABLE1
    INNER JOIN TABLE2 ON (TABLE1.ID = TABLE2.ID)

    Then you can change to a LEFT JOIN, or FULL OUTER JOIN easily and see if that is why your result set is smaller than expected

  6. ranjitjain New Member

    Use FULL OUTER JOIN to get all the 13 records
  7. aspire New Member

    Thanks everybody for your response..

    Yes we can easily modify the query and get our desired resultset..

    But here we just want to figure out why this query is producing different resultset.The same query is working fine on QA box and giving expected 13 records, but issues are being seen on Production box only..
    This is giving us jitters..

    Thanks
    Aspire
  8. Adriaan New Member

    If you would restore a backup of the production database on your development box, then you would see real-life data, and you would inevitably see the same results.

    In your development system, you probably have matching values on all JOINs, so it doesn't matter if you use an INNER JOIN or a LEFT JOIN, you get the same number of rows.

    The production system probably has unmatched values, so there an INNER JOIN will return less rows than a LEFT JOIN.

    This is really something that you should be on the lookout for in the development stages.
  9. kpayne New Member

    quote:Originally posted by aspire

    Thanks everybody for your response..

    Yes we can easily modify the query and get our desired resultset..

    But here we just want to figure out why this query is producing different resultset.The same query is working fine on QA box and giving expected 13 records, but issues are being seen on Production box only..
    This is giving us jitters..

    Thanks
    Aspire


    There are a number of SQL Server configuration options that can affect the results. Compatibility level, case-sensitivity, ANSI NULLS, and ANSI PADDING can all make identical queries give different results when run on identical data.

    Check out sp_dbcmptlevel, case-sensitive instance of SQL Server, and ANSI compliance options in BOL (SQL Server help files).

    Keith Payne
    Technical Marketing Solutions
    www.tms-us.com
  10. aspire New Member

    Hi,

    This is getting more interesting now. I took another query and executed the query on QA box and Prod box. On QA query is consistently showing 4 records. While on Prod query behaving strangely. Sometime it gives 3 records sometime it gives 7 records.

    We checked out all the configuration settings (server configuration, database configuration). Settings are same on QA and Prod.

    This is really surprising behaviour from sql server 2000.

    I hope i m not bothering you guys..

    Thanks,
    Aspire.
  11. Adriaan New Member

    You don't mention restoring a recent back-up of the production database in your test environment.

    This should help clarifying whether it's an issue to do with actual data not conforming to the assumptions present in the design of the query.
  12. aspire New Member

    Yes Adriaan,

    We are performing regular backup and restore from prod to QA before the testing. But the results are still surprising.

    Same Data, Same Server Configuration, Same Database Configuration.

    Only difference is tempdb space. Production has around 4 GB free space in tempdb whereas in QA temp db free space is little less than 1 GB.

    Thanks,
    Aspire
  13. Chappy New Member

    So if you do a count(*) on the affected tables, on dev and on production, both value are identical ?

    When you back up production server, make sure you choose to overwrite the existing file, if it exists. I wonder if you are choosing to append the backup to an existing file, and then when you restore onto dev server, you are restoring an out of date backup?

Share This Page