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

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

Are the tables on the same server?

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

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

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

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

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)

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.

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

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.

Yes that is the point I want to highlight

And so ... ?

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.

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?

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)

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

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.

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)