July 30, 2015 at 12:49 am
Hi All,
I have attached a screen shot of a part of my plan. I couldn't understand why there is a thick line ( more number of data) in the table spool.
Could some one please help me understand this.
A little bit background. I am having a view with some CTE and this table is being directly referenced in some other part of the query.
Many thanks
July 30, 2015 at 12:55 am
How about showing some code?
July 30, 2015 at 1:57 am
Sorry but its something like this
with wftbl(
select col1,..col5 from objectA
inner join objectB
on cond1 =cond1
where ...
)
....
tbl2(
object c
inner join
objectA
where . ..
)
select col1 .. .
from
maintabl
....
left join (select * from objectA where condtn)worktbl
...
left join objectA
on (worktbl.id = objectA.ID AnD worktbl.order >1000)
OR
(worktbl.anotherid = objectA.ID AnD worktbl.order =1000)
left join
....
July 30, 2015 at 7:24 am
thenewbee (7/30/2015)
Sorry but its something like thiswith wftbl(
select col1,..col5 from objectA
inner join objectB
on cond1 =cond1
where ...
)
....
tbl2(
object c
inner join
objectA
where . ..
)
select col1 .. .
from
maintabl
....
left join (select * from objectA where condtn)worktbl
...
left join objectA
on (worktbl.id = objectA.ID AnD worktbl.order >1000)
OR
(worktbl.anotherid = objectA.ID AnD worktbl.order =1000)
left join
....
You showed everything except for any details about the query. Performance problems are often in things like where predicates. We can help but you are going to have to provide some details. The actual query, table definitions and index definitions along with the actual execution plan. Without these details we can't possibly help. Please take a look at this article for some details about how to find and post this information. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 30, 2015 at 10:25 am
Ya, but sorry query cannot be shared, I know that data will not be there, but sorry.
Will try to annonymize and come back
August 3, 2015 at 12:29 am
HI ,
you can try indexing and some tweeking in the query for object A to avoid spooling for loop join,
for better help please provide actual plan.
Neeraj Prasad Sharma
Sql Server Tutorials
September 23, 2015 at 8:11 am
...
left join objectA
on (worktbl.id = objectA.ID AnD worktbl.order >1000)
OR
(worktbl.anotherid = objectA.ID AnD worktbl.order =1000)
left join
....
Try to rewrite this join.
Using such constructions in joins, especially when OR is involved is asking for trouble.
Try something like this:
...
left join objectA
on objectA.ID = case
when worktbl.order =1000 then worktbl.anotherid
When worktbl.order >1000 then worktbl.id
End
left join
....
But the best way to improve the query performance would be fixing the table design.
_____________
Code for TallyGenerator
September 24, 2015 at 11:29 pm
Thanks all for the support. Discussions are going on how to change the table design
September 30, 2015 at 10:28 am
Try to add a query hint to eliminate the loop join and turn it into a hash or merge. Are statistics current on the tables.
Tom
September 30, 2015 at 11:28 am
yes statistics are updated
September 30, 2015 at 4:14 pm
Can you tell us more about those condition cond1, condtn you use in the query? How do they look like?
And I can see objectA mentioned 4 times in the query.
Is it really like that or you used same substitution name just for this mock-up example?
_____________
Code for TallyGenerator
October 1, 2015 at 1:05 pm
You might want to check out Plan Explorer, there's a free edition: https://www.sqlsentry.com/products/plan-explorer/sql-server-query-view.
Also, I'd recommend Grant Fritchey's book on execution plans, available for free as a PDF from Redgate: https://www.red-gate.com/community/books/sql-server-execution-plans-ed-2. It might help.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
October 2, 2015 at 1:16 am
Try to rewrite this join.
Using such constructions in joins, especially when OR is involved is asking for trouble.
Try something like this:
...
left join objectA
on objectA.ID = case
when worktbl.order = 1000 then worktbl.anotherid
When worktbl.order > 1000 then worktbl.id
End
left join
....
But the best way to improve the query performance would be fixing the table design.
I don't think using conditional JOIN conditions would improve the performance. I suggest you try using UNION (ALL) as an alternative:
LEFT JOIN objectA
ON objectA .ID = worktbl.anotherid
AND worktbl.order = 1000
...
UNION
...
LEFT JOIN objectA
ON objectA .ID = worktbl.anotherid
AND worktbl.order > 1000
Read on Dwain Camps' How to Avoid Conditional Join
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply