Solved

Dataware house query tuning

Posted on 2016-11-17
9
64 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
ID: 41892123
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
ID: 41892402
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
ID: 41892410
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
ID: 41892413
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
ID: 41892434
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
ID: 41892445
Will this condition have better performance on the fact table with million rows.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41892476
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
ID: 41894126
awesome. thank you
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41894479
Great. Pleased it helped.

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

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

910 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

23 Experts available now in Live!

Get 1:1 Help Now