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

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

Jack Corbett (7/25/2008)

---

Your biggest problem is that you are using Functions against columns in the where of the subquery which will cause a table scan. You should first look at how you can eliminate that. Can you give us the definition of the table, some test data, and what you are trying to accomplish with the query? Can you convert it to a stored procedure where we can use table variables/temp tables?

This may be what you want:

SELECT 
    F.* 
FROM 
    xxxxflow F JOIN
    (SELECT
        MAX(mstr_ordid) AS max_ord_id,
        Ord_Num,
         CONVERT(VARCHAR(10),b.date,101) AS date_string
    FROM
         xxxflow) B ON 
        f.Ord_Num = B.Ord_Num AND
        CONVERT(VARCHAR(10),f.date_time,101) = B.date_string AND
        F.mstr_ordid = B.max_ord_id