Any advice to increase efficiency of my query?

Database
Enthusiast

Any advice to increase efficiency of my query?

I'm running the following query on our corporate database, it's extremely slow since the database is constantly under very high load. It takes several hours to run:

LOCKING ROW FOR ACCESS SELECT
Sum (
Case
When bit2.Sales_Quantity IS NULL THEN 1
ELSE bit2.Sales_Quantity END) as Picked,
Retail_Outlet_Number,
Picking_zone_id
FROM
VWI0BIT2_GHS_TPNB_IN_BASKET bit2
INNER JOIN
VWI0BPR_BASE_PRODUCT bpr
ON
bpr.Base_Product_Number = bit2.Base_Product_Number
INNER JOIN VWI0CAL_SMALL_CALENDAR cal
ON bit2.Transaction_Date = cal.Calendar_Date
FULL OUTER JOIN
dxwi_prod_dotcom_play_pen.ce64_product_zones PZ
ON
PZ.sub_group_id = bpr.Product_Sub_Group_Code
WHERE
Year_Week_Number = '201121'
AND
bit2.Sales_Value > 0
GROUP BY
2,3
The query simply counts the number of products sold with a value greater than zero by temperature zone in a particular week.

VWI0BIT2_GHS_TPNB_IN_BASKET is a huge table with billions of rows containing sales data.

VWI0BPR_BASE_PRODUCT contains about 100k rows and allows me to select product subgroup.

VWI0CAL_SMALL_CALENDAR is a simple calendar table witha  few thousand rows that allows me to select week number

dxwi_prod_dotcom_play_pen.ce64_product_zones is a simple lookup table with a few hundred rows that contains product subgroups and temperature zones and allows me to select temperature zone.

Any help to improve the efficiency of the query gratefully received...

4 REPLIES
Enthusiast

Re: Any advice to increase efficiency of my query?

Query looks ok. Few of the basic performace checks can be:

1.Stats should be up to data for joining columns and WHERE clause columns.

2.Joining column should share same data type.

3.Joining columns shouldn't be heavily skewed.

4.Check for the possiblity of adding PPI on column - Year_Week_Number

Enthusiast

Re: Any advice to increase efficiency of my query?

Few Observations/Suggestions

1. If you are only concerned about the sold products and why a FULL OUTER JOIN to the   Lokup table. Just to get the time Zones you could probably use a derived table off the lookup table with just two attributes (Product_Sub_group_Code and Picking_Zone_Id)

Then you can use a inner join to the Sales table.

2. As you said that the Sales table has billions of records and you are just using 2-3 columns in the select and couple in the joins. I would suggest that you create a derived table off the sales table with just these 5-6 columns u need and then join it to the other tables.

Thanks

Manik

Enthusiast

Re: Any advice to increase efficiency of my query?

Yes, I agree with Manik. If you are doing a join on Huge table, it is better to write a subquery fetching only the required columns on unique combination. Or creating a derived table from it. This will definetly reduce the cost of the query and increase the performance.

Also collecting stats on the tables will increase the performance.

Junior Contributor

Re: Any advice to increase efficiency of my query?

The Full Outer Join is probably removed by the optimizer and the SUM might be put in a DerivedTable, but without Explain and some info about PI/partitioning it's hard to tell.

Dieter