April 5, 2012 at 6:00 am
Hi,
I would like to know how I can improve this sentence
This is last sentence in query:
and da.[Status] in (select Value from Split ('600',',') as da)
some facts : ( when I mean in this section bad performaence I mean no result set (above some hours))
1) split is table value function that return the only value=600
2) this condition is the last condition in quary and sql in executaion plan
do inner join with this table value function to find matching value.
3) when I use in (600) (as constant) with out table value function its work vary fast because sql engine use index seek at first.
4) When I use in (@i) and @i contain value 600 again its work vary fast because sql engine use index seek at first.
5) When I take the return value and assign him to temp table means and da.[Status] in (select Value from temp table) still bad performaence.
this link include the plan :
http://imageshack.us/f/443/planhandlh.png/
thank you vaty much
sharon
April 5, 2012 at 6:24 am
The function, is a multi-statement table valued function? If so, eliminating that may be the best bet for tuning the query.
To share a plan, save it as a .SQLPLAN file and attach it. Just looking at a graphic doesn't supply enough information. Sorry.
"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
April 5, 2012 at 6:30 am
Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
