July 9, 2012 at 11:39 pm
Hi all
I have one Query with 3 tables Joins
I want to Reduce these joins N write in subquery Form Because From Two tables i am retrieving only one record called (m.value) with sum aggregate function..
If there is any alternate method then do post it
DECLARE @item_id VARCHAR(MAX)
SET @item_id = (SELECT metric_template_id FROM view_metric_template WHERE item_name = N'Hard Savings' AND template_name = N'Savings / Controller Validation')
SELECT vh.parent_work_id As ParentWorkId,
vh.parent_name As ParentName,
vh.child_work_id As ChildWorkId,
vh.child_name As ChildName,
vh.child_sequence_id As SequencesNo,
vh.parent_owner_id As OwnerNo,
vh.child_work_type_code As ChildworkCode,
vh.child_status_current As ChildStatus,
vh.child_planned_end_date As PlannedEndDate,
vh.child_actual_end_date As ActualEndDate,
ISNULL(SUM(m.value),0) As HardSaving,
(SELECT (first_name+ ' ' + last_name)
from view_user
where user_id = vh.parent_owner_id) as ProjectManager
FROM View_Work_Hierarchy vh
INNER JOIN View_Metric_Instance mi ON mi.linked_project_id = vh.child_work_id
INNER JOIN fn_Metrics(null, null )m ON m.metric_instance_id = mi.metric_instance_id
WHERE vh.child_work_type_code Like 'Tollgate%'
AND vh.child_visibility = 'CURR'
AND vh.child_status_current != LTRIM(RTRIM('Canceled'))
AND mi.metric_template_id = @item_id
GROUP BY vh.parent_work_id,vh.parent_name,vh.child_work_id,vh.child_name,vh.child_sequence_id,vh.parent_owner_id,
vh.child_work_type_code,vh.child_status_current,vh.child_planned_end_date,vh.child_actual_end_date
July 9, 2012 at 11:57 pm
Any reason why you want to eliminate joins?
A subquery won't perform faster.
If I were you, I would rather get rid of the subquery that's already in the query and join on view_user instead.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 10, 2012 at 12:04 am
Thanks for the reply,
A subquery returns result very fast compare to joins so...
There are 1 lakh + record in the database object table
Is it Possilbe to Retrieve record in the Join portion
like From vieW_work_hierarchy ( Select sum(value) From so n so tables)) ???
July 10, 2012 at 12:08 am
Where did you get the idea that a subquery is faster than a join?
A correlated subquery certainly isn't. If you query is slow, it's probably because you don't have the right indexes.
Please post the actual query plan, table DDL's and sample data so that it's easier to troubleshoot. (see the first link in my signature).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 10, 2012 at 12:56 am
This function seems an issue to me. fn_Metrics(null, null )
Here you are joining with this function on metric_instance_id. It looks like that optimzier has to process the full data of this function and then using metric_instance_id to join with view View_Metric_Instance.
It might be pssible that View_Metric_Instance mi retrunrs very few rows as you have a SARG
AND mi.metric_template_id = @item_id and then for those metric_instance_id the function is called so that less data is processed and It might make things run faster.Also, it will be more like IVF function and thus will be better from performance point of view. But that is just a guess and I could confirm once you will post the DDL of views,tables,functions involved in this as well as the actual execution pla of the above query.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 10, 2012 at 1:12 am
Hi,
Please look at the modified SQL below:
DECLARE @item_id VARCHAR(MAX)
SET @item_id = (SELECT metric_template_id FROM view_metric_template WHERE item_name = N'Hard Savings' AND template_name = N'Savings / Controller Validation')
SELECT vh.parent_work_id As ParentWorkId,
vh.parent_name As ParentName,
vh.child_work_id As ChildWorkId,
vh.child_name As ChildName,
vh.child_sequence_id As SequencesNo,
vh.parent_owner_id As OwnerNo,
vh.child_work_type_code As ChildworkCode,
vh.child_status_current As ChildStatus,
vh.child_planned_end_date As PlannedEndDate,
vh.child_actual_end_date As ActualEndDate,
ISNULL(SUM(m.value),0) As HardSaving,
(SELECT (first_name+ ' ' + last_name)
from view_user
where user_id = vh.parent_owner_id) as ProjectManager
FROM View_Work_Hierarchy vh
INNER JOIN View_Metric_Instance mi ON mi.linked_project_id = vh.child_work_id
INNER JOIN fn_Metrics(null, null )m ON m.metric_instance_id = mi.metric_instance_id
WHERE mi.metric_template_id = @item_id
AND vh.child_work_type_code Like 'Tollgate%'
AND vh.child_visibility = 'CURR'
AND vh.child_status_current <> LTRIM(RTRIM('Canceled'))
GROUP BY vh.parent_work_id,vh.parent_name,vh.child_work_id,vh.child_name,vh.child_sequence_id,vh.parent_owner_id,
vh.child_work_type_code,vh.child_status_current,vh.child_planned_end_date,vh.child_actual_end_date
Changing JOINs to sub-queries is obviously a bad idea, because JOINs perform better than sub-queries.
Apart from the above changes, I would like to know the below:
1. What is definition of fn_Metrics()?
2. Table definition
3. View definition
4. You can use a inline query (not a subquery) to get the SUM(m.value) in your query
Regards,
Ravi.
July 10, 2012 at 2:55 am
Based on the names of the objects in your query, I'm assuming you're doing JOINS against views, not tables. That means any one view could actually be hitting multiple other tables. This is considered to be a very bad coding practice and shouldn't be done in general. Also, you have a function there. Is that a multi-statement user defined function? If so, those have notorious performance issues because of the fact that they're working using table variables which have not statistics.
Those are the areas where I would concentrate first, not in trying to eliminate JOIN operations.
By the way, several other people have pointed it out, but I'll add to it. JOIN operations can, and do, perform very ably. Trying to eliminate JOINs from your T-SQL code in a relational database engine is not a good approach in general.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 11, 2012 at 8:54 am
You have views AND a table valued function in play here. There is no telling how complex and messed up the actual query is given those two things. This almost certainly goes way beyond what a free forum is for based on the likely complexity of helping you optimize this thing. Find a good performance tuning professional to help you figure out what is going on here and get it fixed up while also teaching you how to do some tuning yourself.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply