SQL Server Performance

Query 2 tables indvidually then in a union all view shows timings and execution plan.

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Moonwalker2000, Oct 15, 2007.

  1. Moonwalker2000 New Member

    I query:
    first table: comes back in 6 seconds with 17K rows in results set
    Second table: comes back in < 1 second with 0 rows in results set.
    Both tables in union all view, comes back in 1 min 10 seconds with 17K rows in results set. The index seek on the first table only shows 24K rows but in the union-all view shows 41Mil rows. Can somebody help me analyze this and point out what I'm missing. Thanks Patrick
  2. MichaelB Member

    Are the tables on the same server?
  3. Moonwalker2000 New Member

    Yes. And both in the same DB also. The views are located in a diff DB but that shouldn’t matter.
  4. Adriaan New Member

    Are we talking
    SELECT * FROM table1
    UNION ALL
    SELECT * FROM table2
    ... or is it more complicated than that?
  5. Moonwalker2000 New Member

    That's about it. included below. Using columns rather than *
    select Sk_Date_ID,
    Sk_Store_ID,
    Sk_Item_ID,
    Transact_Num,
    RegisterNum,
    Seq_Num--, SK_Parent_Store_ID
    --, SK_Parent_Item_ID,
    SK_Store_Employee_ID,
    Sk_Customer_ID,
    Sk_Promo_ID,
    SK_Model_Strategy_ID,
    SK_Discount_ID,
    SK_Coupon_ID,
    SK_TLC_Coupon_ID,
    SK_Buyer_ID,
    NULL as Acct_ID,
    0 as Sk_Tran_Type_Code,
    0 as Sk_Certificate_Type_Code,
    NULL as Auth_Code,
    0 as Sk_Auth_Method_Code,
    0 as Sk_Cancel_Code,
    NULL as Process_Date,
    NULL as Orig_Tran_ID,
    NULL as Tran_Key_ID,
    Transact_Date,
    Transaction_Hour,
    Sales_Qty,
    Sales_Amt,
    0 as Gift_Card_Qty,
    0 as Gift_Card_Amt,
    Discount_Amt,
    Markdown_Amt,
    Member_Disc_Amt,
    Item_Coupon_Amt,
    Trans_Coupon_Amt,
    Class_Markdown_Amt,
    Item_Coupon_Num,
    TLC_Coupon_Num,
    POS_Price_Type,
    PPLU_Seq_ID,
    Class_Promo_ID,
    Run_Seq_Num,
    ETL_Load_ID,
    Last_Modified_Date,
    Modified_by_User,
    BookFair,
    0 as Gift_Flagfrom
    dws_sales_mart.dbo.Tbl_Fact_Store_Sales_Transunion
    allselect SK_Date_ID,
    SK_Store_ID,
    SK_Item_ID,
    Transact_Num,
    RegisterNum,
    Seq_num--, SK_Parent_Store_ID
    --, Sk_Item_ID as SK_Parent_Item_ID,
    NULL as SK_Store_Employee_ID,
    0 as Sk_Customer_ID,
    0 as Sk_Promo_ID,
    0 as SK_Model_Strategy_ID,
    0 as SK_Discount_ID,
    0 as SK_Coupon_ID,
    0 as SK_TLC_Coupon_ID,
    0 as SK_Buyer_ID,
    Acct_ID,
    Sk_Tran_Type_Code,
    Sk_Certificate_Type_Code,
    Auth_Code,
    Sk_Auth_Method_Code,
    Sk_Cancel_Code,
    Process_Date,
    Orig_Tran_ID,
    Tran_Key_ID,
    Transact_Date,
    Transaction_Hour,
    0 as Sales_Qty,
    0 as Sales_Amt,
    Sales_Qty as Gift_Card_Qty,
    Sales_Amt as Gift_Card_Amt,
    0 as Discount_Amt,
    0 as Markdown_Amt,
    0 as Member_Disc_Amt,
    0 as Item_Coupon_Amt,
    0 as Trans_Coupon_Amt,
    0 as Class_Markdown_Amt,
    0 as Item_Coupon_Num,
    0 as TLC_Coupon_Num,
    0 as POS_Price_Type,
    0 as PPLU_Seq_ID,
    0 as Class_Promo_ID,
    NULL as Run_Seq_Num,
    ETL_Load_ID,
    Last_Modified_Date,
    Modified_By_User,
    NULL as BookFair,
    1 as Gift_Flagfrom
    dws_sales_mart.dbo.Tbl_Fact_Store_Sales_Trans_Gift_Card
  6. MichaelB Member

    what kind of speed do you get if you select both, insert into table and take distinct? Are either heaps?
  7. Moonwalker2000 New Member

    Both tables are not heaps.
    In this case select from table 1 into a new table takes 6 seconds.
    1 second from table 2 (no rows produced)
    Select * from new table takes < 1 sec result is 17K rows.
    ------------------
    The query below is the same query for each indvidual table or the union all view.select
    a11.SK_Date_ID SK_Date_ID,a11
    .SK_Store_ID SK_Store_ID,a11
    .Transact_Num Transact_Num,a11
    .RegisterNum RegisterNum,
    sum(a11.Sales_Qty) Sales_Qty-- into DWS_Sales_Mart..PJL_Test_gift_card
    -- from vTbl_Fact_Store_Sales_Trans a11 -- first table 6 sec 17k rowsfrom
    DWS_Sales_Mart..Tbl_Fact_Store_Sales_Trans_gift_card a11 -- second table < 1sec 0 rows-- from vTbl_Fact_Store_Sales_Trans_Gift_Test a11 -- Union all view 1 min 10 sec 17k rowsjoin vTbl_Dim_Store a12
    on (a11.SK_Store_ID = a12.SK_Store_ID)join vTbl_Dim_Date_MSTR a13
    on (a11.SK_Date_ID = a13.SK_Date_ID)join vTbl_Dim_Item a14
    on (a11.SK_Item_ID = a14.SK_Item_ID)join vTbl_Dim_Item_Named_Set a15
    on (a14.SK_Parent_Item_ID = a15.SK_Parent_Item_ID)where
    (a13.Fiscal_P_ID in (200706)
    and a15.ListID in (340)
    and a12.Merch_Div_Num in (12))group
    by a11.SK_Date_ID,a11
    .SK_Store_ID,a11
    .Transact_Num,a11
    .RegisterNum OPTION (MAXDOP 4)
  8. Adriaan New Member

    So your problem is when you query a view that is a UNION ALL query?
    I also notice you put in a MAXDOP option - try setting it inside the view definition, and try setting it to 1 instead of 4.
  9. anandchatterjee New Member

    Hi,
    As per my understanding (MAXDOP 4), distribute this particular query to multiple processor and try to use the processor in the optimize level.
    My comments.
    1. That particular server should have more than 1 processor.
    2. Some time instead of (MAXDOP 1), (MAXDOP 4) returns error if any lock escalation taken place.
    I dont think this is a good solution. Please correct me if I am wrong any where.
    Arijit
  10. Adriaan New Member

    Not sure about SQL 2005, but in 7.0 and 2000 sometimes SQL Server uses too many processors to execute fairly simple queries. This "unwanted parallellism" can lead to execution times that are far too long, and it can provoke locking conflicts.
    The work-around for this (in 7.0 and 2000) is to use the MAXDOP option for the query, setting it to 1.
  11. anandchatterjee New Member

    Yes that is the point I want to highlight
  12. Adriaan New Member

    And so ... ?
  13. Moonwalker2000 New Member

    Guys, I’m in New York so behind on your time zones. Thanks for all suggestions. I set MAXDOP = 1 and it took 1 min 30 sec.Removed MAXDOP and it took 47 sec. Also I’m getting errors setting the option MAXDOP 1 inside the view.
    This server is our QA box and has 8 cpus. Our production server is a 20 way (with MAXDOP = 16 on the SQL option side) and we need to reduce cpu's as some users taking up all cpu's running large reports.Thanks for all your suggestions. I’m still puzzeled as to why the index seek on the large table shows actual no of rows = 41 mil and when I query it indvidually it shows rows = 27K.
    Apologies for the formatting. Not taking enter key to include line breaks etc.
  14. Adriaan New Member

    If I go back a few steps, I find that your problem occurred when you used a view, and this view included a UNION.
    Have you tried replacing the view name with the query from the view as a derived table?
  15. Moonwalker2000 New Member

    No luck or change using derived tables. Same results and timings as before. New query below.
    -- maxdop = 1 -- 1 min 30
    -- maxdop = 4 -- 1 min 15
    -- no maxdop -- 50 secselect
    derived_table_1.SK_Date_ID SK_Date_ID,derived_table_1
    .SK_Store_ID SK_Store_ID,derived_table_1
    .Transact_Num Transact_Num,derived_table_1
    .RegisterNum RegisterNum,
    sum(derived_table_1.Sales_Qty) Sales_Qtyfrom( select Sk_Date_ID,
    Sk_Store_ID,
    Sk_Item_ID,
    Transact_Num,
    RegisterNum,
    Seq_Num--, SK_Parent_Store_ID
    --, SK_Parent_Item_ID,
    SK_Store_Employee_ID,
    Sk_Customer_ID,
    Sk_Promo_ID,
    SK_Model_Strategy_ID,
    SK_Discount_ID,
    SK_Coupon_ID,
    SK_TLC_Coupon_ID,
    SK_Buyer_ID,
    NULL as Acct_ID,
    0 as Sk_Tran_Type_Code,
    0 as Sk_Certificate_Type_Code,
    NULL as Auth_Code,
    0 as Sk_Auth_Method_Code,
    0 as Sk_Cancel_Code,
    NULL as Process_Date,
    NULL as Orig_Tran_ID,
    NULL as Tran_Key_ID,
    Transact_Date,
    Transaction_Hour,
    Sales_Qty,
    Sales_Amt,
    0 as Gift_Card_Qty,
    0 as Gift_Card_Amt,
    Discount_Amt,
    Markdown_Amt,
    Member_Disc_Amt,
    Item_Coupon_Amt,
    Trans_Coupon_Amt,
    Class_Markdown_Amt,
    Item_Coupon_Num,
    TLC_Coupon_Num,
    POS_Price_Type,
    PPLU_Seq_ID,
    Class_Promo_ID,
    Run_Seq_Num,
    ETL_Load_ID,
    Last_Modified_Date,
    Modified_by_User,
    BookFair,
    0 as Gift_Flagfrom
    dws_sales_mart.dbo.Tbl_Fact_Store_Sales_Trans union
    allselect SK_Date_ID,
    SK_Store_ID,
    SK_Item_ID,
    Transact_Num,
    RegisterNum,
    Seq_num--, SK_Parent_Store_ID
    --, Sk_Item_ID as SK_Parent_Item_ID,
    NULL as SK_Store_Employee_ID,
    0 as Sk_Customer_ID,
    0 as Sk_Promo_ID,
    0 as SK_Model_Strategy_ID,
    0 as SK_Discount_ID,
    0 as SK_Coupon_ID,
    0 as SK_TLC_Coupon_ID,
    0 as SK_Buyer_ID,
    Acct_ID,
    Sk_Tran_Type_Code,
    Sk_Certificate_Type_Code,
    Auth_Code,
    Sk_Auth_Method_Code,
    Sk_Cancel_Code,
    Process_Date,
    Orig_Tran_ID,
    Tran_Key_ID,
    Transact_Date,
    Transaction_Hour,
    0 as Sales_Qty,
    0 as Sales_Amt,
    Sales_Qty as Gift_Card_Qty,
    Sales_Amt as Gift_Card_Amt,
    0 as Discount_Amt,
    0 as Markdown_Amt,
    0 as Member_Disc_Amt,
    0 as Item_Coupon_Amt,
    0 as Trans_Coupon_Amt,
    0 as Class_Markdown_Amt,
    0 as Item_Coupon_Num,
    0 as TLC_Coupon_Num,
    0 as POS_Price_Type,
    0 as PPLU_Seq_ID,
    0 as Class_Promo_ID,
    NULL as Run_Seq_Num,
    ETL_Load_ID,
    Last_Modified_Date,
    Modified_By_User,
    NULL as BookFair,
    1 as Gift_Flagfrom dws_sales_mart.dbo.Tbl_Fact_Store_Sales_Trans_Gift_Card ) derived_table_1 join vTbl_Dim_Store a12
    on (derived_table_1.SK_Store_ID = a12.SK_Store_ID)join vTbl_Dim_Date_MSTR a13
    on (derived_table_1.SK_Date_ID = a13.SK_Date_ID)join vTbl_Dim_Item a14
    on (derived_table_1.SK_Item_ID = a14.SK_Item_ID)join vTbl_Dim_Item_Named_Set a15
    on (a14.SK_Parent_Item_ID = a15.SK_Parent_Item_ID)where
    (a13.Fiscal_P_ID in (200706)
    and a15.ListID in (340)
    and a12.Merch_Div_Num in (12))group
    by derived_table_1.SK_Date_ID,derived_table_1
    .SK_Store_ID,derived_table_1
    .Transact_Num,derived_table_1
    .RegisterNum -- OPTION (MAXDOP 4)
  16. Adriaan New Member

    Isn't that vTbl_Dim_Store actually a view too?
    Try and combine as much as you can into a single set of JOINs.
  17. Moonwalker2000 New Member

    Yes, they're all views. That's a requirement the reporting guys need using Micro Strategy.
    I'm going to try a CTE next. Will let you know.
  18. Moonwalker2000 New Member

    Here's the CTE that looks like it works. And yes, setting maxDOP to less than actual no of cpu's works faster. 4 is faster than 1.WITH
    Tbl_Fact_Store_Sales_Trans_CTE (SK_Date_ID, SK_Store_ID, Transact_Num, RegisterNum, Sales_Qty ) AS(
    SELECTa11
    .SK_Date_ID SK_Date_ID,a11
    .SK_Store_ID SK_Store_ID,a11
    .Transact_Num Transact_Num,a11
    .RegisterNum RegisterNum,sum(a11.Sales_Qty) Sales_Qty
    FROM vTbl_Fact_Store_Sales_Trans a11join vTbl_Dim_Store a12
    on (a11.SK_Store_ID = a12.SK_Store_ID)join vTbl_Dim_Date_MSTR a13
    on (a11.SK_Date_ID = a13.SK_Date_ID)join vTbl_Dim_Item a14
    on (a11.SK_Item_ID = a14.SK_Item_ID)join vTbl_Dim_Item_Named_Set a15
    on (a14.SK_Parent_Item_ID = a15.SK_Parent_Item_ID)where
    (a13.Fiscal_P_ID in (200706)
    and a15.ListID in (340)
    and a12.Merch_Div_Num in (12))group
    by a11.SK_Date_ID,a11
    .SK_Store_ID,a11
    .Transact_Num,a11
    .RegisterNum ),Tbl_Fact_Store_Sales_Trans_Gift_Card_CTE
    (SK_Date_ID, SK_Store_ID, Transact_Num, RegisterNum, Sales_Qty) AS(
    SELECTa11
    .SK_Date_ID SK_Date_ID,a11
    .SK_Store_ID SK_Store_ID,a11
    .Transact_Num Transact_Num,a11
    .RegisterNum RegisterNum ,sum(a11.Gift_Card_Qty) Sales_Qty
    FROM vTbl_Fact_Store_Sales_Trans_Gift_Card a11join vTbl_Dim_Store a12
    on (a11.SK_Store_ID = a12.SK_Store_ID)join vTbl_Dim_Date_MSTR a13
    on (a11.SK_Date_ID = a13.SK_Date_ID)join vTbl_Dim_Item a14
    on (a11.SK_Item_ID = a14.SK_Item_ID)join vTbl_Dim_Item_Named_Set a15
    on (a14.SK_Parent_Item_ID = a15.SK_Parent_Item_ID)where
    (a13.Fiscal_P_ID in (200706)
    and a15.ListID in (340)
    and a12.Merch_Div_Num in (12))group
    by a11.SK_Date_ID,a11
    .SK_Store_ID,a11
    .Transact_Num,a11
    .RegisterNum )
    SELECTb11
    .SK_Date_ID SK_Date_ID,b11
    .SK_Store_ID SK_Store_ID,b11
    .Transact_Num Transact_Num,b11
    .RegisterNum RegisterNum ,
    sum(b11.Sales_Qty) Sales_QtyFROM
    Tbl_Fact_Store_Sales_Trans_CTE b11
    full outer join Tbl_Fact_Store_Sales_Trans_Gift_Card_CTE c11 on
    b11.SK_Date_ID = c11.SK_Date_IDand
    b11.SK_Store_ID = c11.SK_Store_IDand
    b11.Transact_Num = c11.Transact_Numand
    b11.RegisterNum = c11.RegisterNum group
    by b11.SK_Date_ID,b11
    .SK_Store_ID,b11
    .Transact_Num,b11
    .RegisterNum OPTION (MAXDOP 4)

Share This Page