Solved

Dataware house query tuning

Posted on 2016-11-17
9
29 Views
Last Modified: 2016-11-19
Hi Team,

Need help on the below query . I attached the excel sheet which contains the data and the expected output.  For the requirement in the excel sheet , I have written the below query , I need help on other best alternatives of writing the below query , since the fact  table contains millions of rows.


Requirement for the data in the excel sheet:
Write a parameterized SQL query to find which store made highest sales of ‘Iphone-6’ till end of Oct-2016 as per the given format in ‘Expected Result’.FACT_SALES’ has millions of records, hence expecting the SQL query is fine-tuned and shouldn’t be give any performance issues.

below is the query i have written for the above requirement, any help in this regard is really appreciated.

With sales_data as (
Select 
fs.store_id, fs.prod_id , fs.zone_id,sum(qty_sold) qty_sold,
dense_rank() over (order by max(qty_sold) desc ) rk
from fact_sales fs  INNER JOIN DIM_PROD dp on (dp.prod_id=fs.prod_id)
where to_Char(fs.sale_date,'MON-YYYY') <=  to_char(to_date('&1','YYYYMMDD'),'MON-YYYY')
and dp.prod_name='&2'
group by fs.store_id, fs.prod_id , fs.zone_id
)
Select dp.prod_name, ds.store_name , dz.zone_code , sd.qty_sold * dp.Unit_Price_Rs HIGHEST_SALE ,(dp.Unit_Price_Rs * (sd.qty_sold * dp.Unit_Price_Rs)) Total_Unit_Price_Rs
from sales_data sd
JOIN DIM_STORES ds on (ds.store_id=sd.store_id and ds.zone_id=sd.zone_id)
JOIN DIM_ZONE dz on (dz.zone_id=ds.zone_id)
JOIN DIM_PROD dp on (dp.prod_id=sd.prod_id)
where rk=1;

Open in new window

0
Comment
Question by:sam_2012
  • 5
  • 4
9 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
where to_Char(fs.sale_date,'MON-YYYY') <=  to_char(to_date('&1','YYYYMMDD'),'MON-YYYY')

NEVER use  function on data to suit the where clause; instead adjust the where clause to suit the data

ALSO, when dealing with dates don't compare them to char values, compare dates to dates
0
 

Author Comment

by:sam_2012
Comment Utility
Hi Portlet Paul,

Can you help me on the   below condition ,  how to change it or better way of writing the condition below

where to_Char(fs.sale_date,'MON-YYYY') <=  to_char(to_date('&1','YYYYMMDD'),'MON-YYYY')
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
if you give me a sample of the data, and an example of the date parameter, and the expected result I can give you a more considered answer.

perhaps in the meantime try this:
                  where fs.sale_date <  add_months(TRUNC(to_date('&1','YYYYMMDD').'MONTH'),1)

e.g. if the parameter is 2016-11-17, the data selected would be less than 2016-12-01

to_date('20161117','YYYYMMDD') = 2016-11-17
TRUNC(    2016-11-17   , 'MONTH') = 2016-11-01
add_months(    2016-11-01   ,1) = 2016-12-01
so:

where fs.sale_date < 2016-12-01
0
 

Author Comment

by:sam_2012
Comment Utility
The data parameter wouldbe entered as OCT-2016 i.e MON-YYYY in this format
IPhone_Sales.xlsx
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
just change the text pattern of to_date to match your parameter

If your parameter is always in that format:

WHERE fs.sale_date < add_months(to_date('OCT-2016', 'MON-YYYY'),1)

which INCLUDES October 2016 in the data to search

===
nb: I do NOT recommend using language dependent month names but that is your decisions
, and if you do change, just change to_date to suit
0
 

Author Comment

by:sam_2012
Comment Utility
Will this condition have better performance on the fact table with million rows.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
I cannot test any assertion I make here - only you can do that.

I also can only make assumptions about the indexing in your data, and this is critical to the execution efficiency.

So I cannot KNOW it will be quicker, but it SHOULD be quicker.

You should also learn to use explain plans. These are the best tool to use to investigate what indexes are being used (or not) and to identify performance problems.

So a long answer. Yes it will be better, but maybe there are still problems.
0
 

Author Closing Comment

by:sam_2012
Comment Utility
awesome. thank you
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Great. Pleased it helped.

Keep this in mind: avoid using functions on data to suit a where clause condition
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Steve Terp was featured in a video created by CRN about how "Channel Is Crucial To Market Disruption". Click on View source to see the video and article
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now