Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Correlated sub query takes more time to return result Expand / Collapse
Author
Message
Posted Friday, July 25, 2008 1:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 15, 2010 11:25 AM
Points: 3, 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
Post #541268
Posted Friday, July 25, 2008 2:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 11,157, Visits: 12,899
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

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
Post #541276
Posted Tuesday, July 29, 2008 3:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 15, 2010 11:25 AM
Points: 3, 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
Post #543091
Posted Tuesday, July 29, 2008 4:18 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 896, Visits: 7,078
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
Post #543105
Posted Tuesday, July 29, 2008 4:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:18 AM
Points: 5,566, Visits: 24,729
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
Post #543116
Posted Tuesday, July 29, 2008 7:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 11,157, Visits: 12,899
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

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
Post #543147
Posted Wednesday, July 30, 2008 10:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:09 AM
Points: 2,656, Visits: 19,184
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."
Post #543706
Posted Wednesday, July 30, 2008 10:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 11,157, Visits: 12,899
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

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
Post #543717
Posted Wednesday, July 30, 2008 11:01 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 23,000, Visits: 31,482
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.




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)
Post #543720
Posted Wednesday, July 30, 2008 11:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:09 AM
Points: 2,656, Visits: 19,184
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."
Post #543722
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse