| SQL tuning [message #329866] |
Thu, 26 June 2008 17:40  |
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
|
Member |
|
|
I am trying to tune the Query but not with much success
All the tables are less 500 records except the view xx_ips_sku_view which has over 300,000 recs. I am attaching the explain plan for reference.
Select
distinct e.id, e.dynamic_seq, h.primary_vendor_id, h.division, h.group_no, h.dept, h.class, h.subclass, h.sku
from xx_ips_basket_dyn_flags e, xx_ips_basket_category ,xx_ips_sku_view h
where g e.id(+) = g.id and e.dynamic_seq = g.dynamic_seq
and (((select count(*) from xx_ips_basket_vendor i where i.id = e.id and i.dynamic_seq = e.dynamic_seq and i.ie_code = 'I') = 0) OR
((h.primary_vendor_id in (select i.vendor from xx_ips_basket_vendor i where i.id = e.id and i.dynamic_seq = e.dynamic_seq and i.ie_code = 'I'))))
and (((select count(*) from xx_ips_basket_category j where j.id = e.id and j.dynamic_seq = e.dynamic_seq and j.ie_code = 'I') = 0) OR
((g.division = '0' or h.division = g.division) and
(g.group_no = '0' or h.group_no = g.group_no) and
(g.dept = '0' or h.dept = g.dept) and
(g.class = '0' or h.class = g.class) and
(g.subclass = '0' or h.subclass = g.subclass)))
and h.sku not in (select k.sku_id from xx_ips_basket_sku_ie k where k.id = e.id and k.dynamic_seq = e.dynamic_seq and k.ie_code = 'E')
|
|
|
|
|
|
| Re: SQL tuning [message #330086 is a reply to message #329866] |
Fri, 27 June 2008 09:57  |
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
|
Member |
|
|
|
I also find that if we remove distinct the query comes back very quick. Is there a way to get around that. Please help.
|
|
|
|
|
|
|
|
| Re: SQL tuning [message #330096 is a reply to message #329866] |
Fri, 27 June 2008 10:19 |
Kriptas
Messages: 118
Registered: June 2008
|
Senior Member |
|
|
what do you mean wery quickly?
wihout distinct query returns first rows quickly
use create table as statement to insert all record set which returning query with distinct and without distinct. and compare time.
query with distinct must be be slower, bet not dramaticaly slower.
|
|
|
|
| Re: SQL tuning [message #330109 is a reply to message #329867] |
Fri, 27 June 2008 10:42 |
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
|
Member |
|
|
From clause
xx_ips_basket_category g
xx_ips_basket_dyn_flags e
Where clause
AND e.dynamic_seq = g.dynamic_seq
|
|
|
|
| Re: SQL tuning [message #330122 is a reply to message #330094] |
Fri, 27 June 2008 11:08 |
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
|
Member |
|
|
The SQL is perfectly correct.
I have formatted it this time.
SELECT DISTINCT e.ID, e.dynamic_seq, h.primary_vendor_id, h.division,
h.group_no, h.dept, h.CLASS, h.subclass, h.sku
FROM xx_ips_basket_dyn_flags e,
xx_ips_basket_category g,
xx_ips_sku_view h,
xx_ips_basket_vendor i
where e.ID(+) = g.ID
AND e.dynamic_seq = g.dynamic_seq
and (((SELECT COUNT (*)
FROM xx_ips_basket_vendor i
WHERE i.ID = e.ID
AND i.dynamic_seq = e.dynamic_seq
AND i.ie_code = 'I') = 0
)
OR ((h.primary_vendor_id = i.vendor
and i.ID = e.ID
AND i.dynamic_seq = e.dynamic_seq
AND i.ie_code = 'I')
)
)
AND ( ((SELECT COUNT (*)
FROM xx_ips_basket_category j
WHERE j.ID = e.ID
AND j.dynamic_seq = e.dynamic_seq
AND j.ie_code = 'I') = 0
)
OR ( (g.division = '0' OR h.division = g.division)
AND (g.group_no = '0' OR h.group_no = g.group_no)
AND (g.dept = '0' OR h.dept = g.dept)
AND (g.CLASS = '0' OR h.CLASS = g.CLASS)
AND (g.subclass = '0' OR h.subclass = g.subclass)
)
)
AND h.sku NOT IN (
SELECT k.sku_id
FROM xx_ips_basket_sku_ie k
WHERE k.ID = e.ID
AND k.dynamic_seq = e.dynamic_seq
AND k.ie_code = 'E')
|
|
|
|
| Re: SQL tuning [message #330139 is a reply to message #329866] |
Fri, 27 June 2008 11:45 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
It appears we disagree upon what proper formatting is.
SELECT DISTINCT e.Id,
e.Dynamic_seq,
h.Primary_Vendor_Id,
h.Division,
h.Group_No,
h.dept,
h.Class,
h.SubClass,
h.sku
FROM xx_ips_Basket_dyn_Flags e,
xx_ips_Basket_Category g,
xx_ips_sku_View h,
xx_ips_Basket_Vendor i
WHERE e.Id (+) = g.Id
AND e.Dynamic_seq = g.Dynamic_seq
AND (((SELECT COUNT(* )
FROM xx_ips_Basket_Vendor i
WHERE i.Id = e.Id
AND i.Dynamic_seq = e.Dynamic_seq
AND i.ie_Code = 'I') = 0)
OR ((h.Primary_Vendor_Id = i.Vendor
AND i.Id = e.Id
AND i.Dynamic_seq = e.Dynamic_seq
AND i.ie_Code = 'I')))
AND (((SELECT COUNT(* )
FROM xx_ips_Basket_Category j
WHERE j.Id = e.Id
AND j.Dynamic_seq = e.Dynamic_seq
AND j.ie_Code = 'I') = 0)
OR ((g.Division = '0'
OR h.Division = g.Division)
AND (g.Group_No = '0'
OR h.Group_No = g.Group_No)
AND (g.dept = '0'
OR h.dept = g.dept)
AND (g.Class = '0'
OR h.Class = g.Class)
AND (g.SubClass = '0'
OR h.SubClass = g.SubClass)))
AND h.sku NOT IN (SELECT k.sku_Id
FROM xx_ips_Basket_sku_ie k
WHERE k.Id = e.Id
AND k.Dynamic_seq = e.Dynamic_seq
AND k.ie_Code = 'E')
Some other Senior Members disagree with the following approach
but In My Opinion xx_ips_Basket_Category g & Xx_ips_Basket_Vendor i
should NOT be in the FROM clause because they contribute no data to the SELECT clause.
Subordinating them into the WHERE might improve performance.
[Updated on: Fri, 27 June 2008 11:49] by Moderator Report message to a moderator |
|
|
|
| Re: SQL tuning [message #330145 is a reply to message #330139] |
Fri, 27 June 2008 11:57 |
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
|
Member |
|
|
thanks for your response. I will evaluate it.
Copying from one editor to another often makes the formatting bad.It also appears differently in message window and when it actually gets posted.
|
|
|
|
|
|
| Re: SQL tuning [message #330273 is a reply to message #329866] |
Sat, 28 June 2008 12:35 |
Kriptas
Messages: 118
Registered: June 2008
|
Senior Member |
|
|
I tried to rewrite query in ANSI SQL
but I think, that something is wrong with your query algorithm.
query can be tuned by precalculating counts for all cases
like
LEFT JOIN (SELECT j.Id, j.Dynamic_seq, COUNT(1) cnt
FROM xx_ips_Basket_Category j
WHERE j.ie_Code = 'I'
group by j.Id, j.Dynamic_seq) v ON v.id = e.id
AND v.Dynamic_seq = e.Dynamic_seq
and then use HASH join to join table and inline view, and after that filter result set.
but I think, mots poroblems are in this part of query
LEFT JOIN xx_ips_sku_View h ON (h.Division = g.Division OR g.Division=0 )
AND (h.Group_No = g.Group_No OR g.Group_No=0)
AND (h.dept = g.dept OR g.dept=0)
AND (h.Class = g.Class OR g.Class=0)
AND (h.SubClass = g.SubClass OR g.SubClass=0)
Like you wrote, xx_ips_sku_View has 300 000 records.
and every time when g.Division=0 and g.Group_No=0 and g.dept=0 and g.Class=0 and g.SubClass=0 ju join on every record in table h all 300 000 table g records.
so if I think that if you remove DISTINC from select, you wil get hundred times (or more) larger recorset compare to the recordset you geting using DISTINCT.
SELECT DISTINCT e.Id,
e.Dynamic_seq,
h.Primary_Vendor_Id,
h.Division,
h.Group_No,
h.dept,
h.Class,
h.SubClass,
h.sku
FROM xx_ips_Basket_Category g
LEFT JOIN xx_ips_Basket_dyn_Flags e ON e.Id = g.Id
AND e.Dynamic_seq = g.Dynamic_seq
LEFT JOIN (SELECT i.Id, i.Dynamic_seq, COUNT(1) cnt
from xx_ips_Basket_Vendor i
WHERE I.ie_Code = 'I'
group by i.Id, i.Dynamic_seq) z ON e.id = z.id
AND e.Dynamic_seq = z.Dynamic_seq
LEFT JOIN (SELECT j.Id, j.Dynamic_seq, COUNT(1) cnt
FROM xx_ips_Basket_Category j
WHERE j.ie_Code = 'I'
group by j.Id, j.Dynamic_seq) v ON v.id = e.id
AND v.Dynamic_seq = e.Dynamic_seq
LEFT JOIN xx_ips_sku_View h ON (h.Division = g.Division OR g.Division=0 )
AND (h.Group_No = g.Group_No OR g.Group_No)
AND (h.dept = g.dept OR g.dept=0)
AND (h.Class = g.Class OR g.Class=0)
AND (h.SubClass = g.SubClass OR g.SubClass=0)
LEFT JOIN xx_ips_Basket_sku_ie k ON h.sku = k.sku_Id
AND k.Id = e.Id
AND k.Dynamic_seq = e.Dynamic_seq
AND k.ie_Code = 'E'
LEFT JOIN xx_ips_Basket_Vendor i ON h.Primary_Vendor_Id = i.Vendor
AND v.id = e.id
AND v.Dynamic_seq = e.Dynamic_seq
WHERE e.Dynamic_seq IS NOT NULL
AND k.sku_Id IS NULL
AND (NVL(z.cnt,0)=0 OR i.Vendor IS NOT NULL)
AND (NVL(j.cnt,0)=0 OR h.Division IS NOT NULL)
You need to explain what you selecting, from what tables, what you need to get in result, and etc.
You need to rewrite query joins !!
|
|
|
|
| Re: SQL tuning [message #330610 is a reply to message #330273] |
Mon, 30 June 2008 10:12 |
chintu00
Messages: 91
Registered: February 2007
Location: NJ, US
|
Member |
|
|
Yes without distinct I get much larger recordset.
There are 2 counts both of them use tables xx_ips_basket_dyn_flags to compute their counts along with one other table
xx_ips_basket_category is in the outer from clause but with xx_ips_basket_vendor is not. Do you think it is good to
move the table xx_ips_basket_vendor to the outer SQL as it may result in scan larger number of records.
I think I originally did not give the correct SQL I apologize
the join should be
FROM xx_ips_Basket_dyn_Flags e
LEFT JOIN
xx_ips_Basket_Category g ON e.Id = g.Id
AND e.Dynamic_seq = g.Dynamic_seq
I appreciate all your effort and advice.
|
|
|
|
| Re: SQL tuning [message #330634 is a reply to message #329866] |
Mon, 30 June 2008 12:08 |
Kriptas
Messages: 118
Registered: June 2008
|
Senior Member |
|
|
I made LEFT JOIN of "count" queries, becouse I want to precalculate counts for all Id and Dynamic_seq combinations.
when use hash join.
if there is no Id and Dynamic_seq combination that exist in table e, with inner join you will eliminate columns from table e.
so I use statament in where clause
AND (NVL(z.cnt,0)=0 OR i.Vendor IS NOT NULL)
AND (NVL(j.cnt,0)=0 OR h.Division IS NOT NULL)
Like I said, please look at this part
LEFT JOIN xx_ips_sku_View h ON (h.Division = g.Division OR g.Division=0 )
AND (h.Group_No = g.Group_No OR g.Group_No)
AND (h.dept = g.dept OR g.dept=0)
AND (h.Class = g.Class OR g.Class=0)
AND (h.SubClass = g.SubClass OR g.SubClass=0)
do you really need such join, maybe there is some other posibilities to join. What is purpose of that join in your query ?
in ANSI SQL writing stile you can fast identify problems of output record grow
please use such thing:
run select
select count(1)
FROM xx_ips_Basket_Category g
save somethere number of counts ant execution time
after that run
FROM xx_ips_Basket_Category g
LEFT JOIN xx_ips_Basket_dyn_Flags e ON e.Id = g.Id
AND e.Dynamic_seq = g.Dynamic_seq
save number of counts and execution time
and grow query like that..
then you can see at what place there is problems
you can examine at each step execution plan, and tune it.
you can identify on what part is huge output recorset count increase, and look at the joins to eliminate that.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
