2

I know that both Wild Cards and Functions must be avoided in the WHERE clauses if possible. My question is: which one is yet better in terms of performance, assuming that we have to choose between one of them? More specifically, I have the following two queries and I want to know which type of query is preferred in general: 

select FirstName from person.person where FirstName like 'S%'

select FirstName from person.person where LEFT(FirstName,1) = 'S'

PS. I have checked the execution time of both queries and there was no noticeable difference in this specific case. Here are the execution plans:

enter image description here

enter image description here

enter image description here

This is the code for the index IX_Person_LastName_FirstName_MiddleName is defined:

USE [AdventureWorks2012]
GO

/****** Object:  Index [IX_Person_LastName_FirstName_MiddleName]       Script Date: 11/30/2016 12:53:05 PM ******/
CREATE NONCLUSTERED INDEX [IX_Person_LastName_FirstName_MiddleName] ON [Person].[Person]
(
[LastName] ASC,
[FirstName] ASC,
[MiddleName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO;

Here is a few links I found about this issue, though they do not completely answer my question:

AvoidFunctionsInWereClause_1

AvoidFunctionsInWereClause_2

AvoidLikeClause

Community
  • 1
  • 1
TheEsnSiavashi
  • 1,245
  • 1
  • 14
  • 29
  • 1
    You should show us how you create the index. – Juan Carlos Oropeza Nov 30 '16 at 20:03
  • 1
    Wildcards are usually only an issue if you start the string with them (or the character sequence preceding them start the majority of the compared field's values); most function use ends up with table scans unless the RDBMS has special optimizations for those functions. – Uueerdo Nov 30 '16 at 20:07
  • 1
    How many rows are there? `LIKE 'S%'` will use an index (while `LIKE '%s'`) won't. I'd think, that the engine is smart enough to handle `LEFT` similarly... – Shnugo Nov 30 '16 at 20:08
  • @Uueerdo So, do you mean in this case using LIKE is prefered since it doesn't start with the wild cards? – TheEsnSiavashi Nov 30 '16 at 20:21
  • @Juan Carlos Oropeza I didn't create the indexes. Its AdventureWorks2012. I'll add a picture of all the indexes on the table. – TheEsnSiavashi Nov 30 '16 at 20:23
  • 1
    @TheEsisia yes, in this case, and assuming the field is indexed; if the field isn't indexed it really doesn't matter either way. (Edit: Also, I am not sure how much the wildcard adage applies to MSSQL; though I am familiar with MSSQL, most my experience is in MySQL.) – Uueerdo Nov 30 '16 at 20:30
  • 1
    In this case you need to show how `IX_Person_LastName_FirstName_MiddleName` is defined – Juan Carlos Oropeza Nov 30 '16 at 20:30
  • @Juan Carlos Oropeza I'm adding the code for that index into the question. – TheEsnSiavashi Nov 30 '16 at 20:54

1 Answers1

1

Both queries are doing index scan so those are OK. But we dont know how you setup your index or table sizes.

First version can use a basic index on firstName because the % is at the end.

 FirstName like 'S%'

 FirstName Like '%S%' -- cant use index

Second Version cant use the index on firstName, but you can create a calculated index for LEFT(FirstName,1) that is why you should show us your index.

This is article have the best tips to create index. MySQL index TIPS

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118