g.raghunathan (7/29/2008)
Hi Thank you for teh update.But still O am facing the same problem.My main table XXX_FLOW has : 125638737 records in it and
My SP : Billing_Completed_Flow access a view Vw_XXX_FLOW created using the following correlated sub query:
create view Vw_XXX_FLOW as
select * from XXX_FLOW sf2 where
(mstr_ord_id) = (select max(mstr_ord_id) from XXX_FLOW b
where b.Ord_Num = sf2.Ord_Num
and convert(varchar(10),b.Inserted_time,101) = convert(varchar(10),sf2.Inserted_time,101)
due to the above view, my SP has taken 23 hours to complete.
Please suggest me the way to tune this query.
Thnaks in advance....
Raghu
First of all that is a HORRIBLE view. You need to get rid of the select * first. Then as David said try to find a way to get rid of the conversion on the dates. The QP is likely to ignore any indexes because of these 2 things. Then try to find a way to limit the data in the subquery. You are giving it no filters it HAS to scan every row.
If you can explain what you need for a result with some table schemas and some test data we will probably be able to find a better way.
Did you try the query I provided earlier and did it perform any better?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question