Need help understanding execution plan

Question

Need help understanding execution plan

nadersam

SSCertifiable

Points: 6542
More actions
October 25, 2015 at 4:08 am

#304451

Dear All,
I was checking the execution plan of attached sql query and i found a strange behavior at least to me.
For the following sql , the cost of execution of first sql is 3 % while second one is 97 %.
select tradename,sys_key, 1 as Section from namingtrades
where tradename like 'arg%'
select tradename,sys_key, 1 as Section from namingtrades
where tradename like '%gja'
if i change where condition in first one to like '%arg' they become equal at 50 %.
The operation at beginning is clustered index seek versus clustered index scan.
Thanks
Nader

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

Hover over the table scan and see the estimated and actual number of rows , that should explain it. Else post the actual sqlplan file.

Jayanth Kurup[/url]

nadersam SSCertifiable Points: 6542 More actions · Answer 2

Jayanth_Kurup (10/25/2015)
Hover over the table scan and see the estimated and actual number of rows , that should explain it. Else post the actual sqlplan file.

Thanks Jayanth_Kurup for your reply.

In first option Actual number of rows is 4 while in second one it's one.

But i still don't understand why should the execution plan change from scan to seek based on numbers of rows, if using the same index and where condition.

Thanks

Nader

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

nadersam (10/26/2015)
Jayanth_Kurup (10/25/2015)
Hover over the table scan and see the estimated and actual number of rows , that should explain it. Else post the actual sqlplan file.
Thanks Jayanth_Kurup for your reply.
In first option Actual number of rows is 4 while in second one it's one.
But i still don't understand why should the execution plan change from scan to seek based on numbers of rows, if using the same index and where condition.
Thanks
Nader

This -

select tradename,sys_key, 1 as Section from namingtrades

where tradename like '%gja'

is not SARGable. To understand what SARGable means and why it matters to the two versions of your query, read this article: http://www.brentozar.com/archive/2010/06/sargable-why-string-is-slow/

[/url]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ed Wagner SSC Guru Points: 287024 More actions · Answer 4

Chris already identified the non-SARGable predicate, so I won't pile on.

If you want to understand execution plans, I'd recommend reading Grant's book SQL Server Execution Plans. It's available for download from this site at http://www.sqlservercentral.com/articles/books/94937/.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

nadersam SSCertifiable Points: 6542 More actions · Answer 5

Thanks Chris and Ed for your replies, i will check those articles.