SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Correlated sub query takes more time to return result


Correlated sub query takes more time to return result

Author
Message
g.raghunathan
g.raghunathan
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 94
Correlated sub query takes more time to return result...Please help in spiltting this into join...

select * from xxxxflow f where
(mstr_ordid) = (select max(mstr_ordid) from xxxxflow b
where b.Ord_Num = f.Ord_Num
and convert(varchar(10),b.date,101) = convert(varchar(10),f.date_time,101)
)

Thanks in advance
Raghu
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18768 Visits: 14899
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







Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
g.raghunathan
g.raghunathan
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 94
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
David Webb-CDS
David Webb-CDS
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1588 Visits: 8586
Are you converting the dates because you want to match on the date, but the time is immaterial? If so, check out this thread for some ideas on changing the date compare to look at a range:

http://www.sqlservercentral.com/Forums/Topic529603-8-1.aspx#bm529668



And then again, I might be wrong ...
David Webb
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7889 Visits: 25280
It was said once by Jack
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


Suggest again ... read it and then post your question

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18768 Visits: 14899
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
jcrawf02
jcrawf02
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2300 Visits: 19324
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





Jack - so the function against columns is causing the table scan in the WHERE, but not in the JOIN in your re-write? Does SQL not have to scan to evaluate the date from F to compare to B.date_string?

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18768 Visits: 14899
Sure it is still an issue in my query, but by using the join and doing the conversion on 1 side in the Derived table I reduce it to 1/2 the equation. Actually, now that I re-examine my code, it won't work as posted anyway because I have no group by in the derived table. You would need to add:


Group By
ord_num,
CONVERT(VARCHAR(10),b.date,101)



to the derived table.

Without seeing the table definitions and having more information about the desired results with some test data makes it hard to give a better answer.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39856 Visits: 38563
I think it would help to know more about the the table structure (at least the fields involved in the join/where clauses), some sample data to test against, and an expected results based on the sample data (to check tests against).

it looks like a self join using a correlated subquery. Knowing more about the data will help in figuring out how to join things together.

Cool

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
jcrawf02
jcrawf02
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2300 Visits: 19324
Thanks Jack, just wanted to make sure I understood how the use of functions was limiting the performance correctly.

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search