Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
2 different methods of doing the same thing or ???

Answered 2 different methods of doing the same thing or ???

  • Thursday, January 24, 2013 11:51 PM
     
      Has Code
    Sign In to Vote
    0

    Hi,

    Programmer1 is coding thier LIKE clause as the following and Programmer2 is coding their LIKE clause as the 2 point.

    both are using the same DDL, and are getting a different count

    Is Programmer1 method able to produce different results or it is exactly a LIKE?

    Thanks in advance

    Programmer 1

    WHERE     (LEFT(dbo.DimCode.Code, 3) IN ('E89', 'G97', 'H59', 'H95', 'I97', 'J95', 'K91', 'M96', 'N99', 'T80', 'T81', 'T82', 'T83', 'T84', 'T85', 'T86', 'T87', 'T88')) OR
                      (dbo.DimCode.Code = 'T983')

    Programmer 2

    WHERE     
(code LIKE 'E89%') OR
                      (code LIKE 'G97%') OR
                      (code LIKE 'H59%') OR
                      (code LIKE 'H95%') OR
                      (code LIKE 'I97%') OR
                      (code LIKE 'J95%') OR
                      (code LIKE 'K91%') OR
                      (code LIKE 'M96%') OR
                      (code LIKE 'N99%') OR
                      (code LIKE 'T80%') OR
                      (code LIKE 'T81%') OR
                      (code LIKE 'T82%') OR
                      (code LIKE 'T83%') OR
                      (code LIKE 'T84%') OR
                      (code LIKE 'T85%') OR
                      (code LIKE 'T86%') OR
                      (code LIKE 'T87%') OR
                      (code LIKE 'T88%') OR
                      (code = 'T983')

    David

     

All Replies

  • Thursday, January 24, 2013 11:58 PM
     
     
    Sign In to Vote
    0

    They are functionaly quite different.

    IN ('G97') will match only G97

    LIKE('G97%') will match G97, G978, G97000000, etc.   Like is a wildcard search where % is the wildcard

    Edit - I see he is doing a LEFT 3 with the IN.   I guess I'd personally go with the LIKE

    Chuck Pedretti | Magenic – North Region | magenic.com


     
  • Friday, January 25, 2013 12:02 AM
     
     
    Sign In to Vote
    0

    Hi Chuck,

    can you explain the Part of the code WHERE     (LEFT(dbo.DimCode.Code, 3) IN ('E89')

    the programmer says this takes care of the LIKE portion.

    David

     
  • Friday, January 25, 2013 12:08 AM
     
     
    Sign In to Vote
    0
    What he is doing is taking the left 3 characters of the field and matching on that.   So functionally you will get the same records as the code which is doing the like.  The question that I have is if the LEFT logic will make use of an index if there is one, the LIKE logic will for sure.  I'd have to try it out to know for sure.

    Chuck Pedretti | Magenic – North Region | magenic.com

     
  • Friday, January 25, 2013 12:14 AM
     
      Has Code
    Sign In to Vote
    1

    Just did a little test and the execution plans are identical.  They both would use an index if available.

    CREATE TABLE dbo.DimCode
	(
	DimCodeID int NOT NULL,
	Code varchar(20) NOT NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.DimCode ADD CONSTRAINT
	PK_Table_1 PRIMARY KEY CLUSTERED 
	(
	DimCodeID
	) 
CREATE INDEX IDX01_DIMCODE ON dbo.DimCode (code)
INSERT dbo.DimCode VALUES  (1, 'E891234')
INSERT dbo.DimCode VALUES  (2, 'E891235')
INSERT dbo.DimCode VALUES  (3, 'E891236')
INSERT dbo.DimCode VALUES  (4, 'E891237')
INSERT dbo.DimCode VALUES  (5, 'E812341')
INSERT dbo.DimCode VALUES  (6, 'E812342')
INSERT dbo.DimCode VALUES  (7, 'E812343')
INSERT dbo.DimCode VALUES  (8, 'E812344')
SELECT * from dbo.DimCode where left(Code,3) IN ('E89')
SELECT * from dbo.DimCode where Code like ('E89%')

    Chuck Pedretti | Magenic – North Region | magenic.com

     
  • Friday, January 25, 2013 12:15 AM
     
     
    Sign In to Vote
    0

    Chuck Pedretti | Magenic – North Region | magenic.com

     
  • Friday, January 25, 2013 12:21 AM
     
     
    Sign In to Vote
    0

    Ok, thanks for you help Chuck,

    Much appreciated..

    David

     
  • Friday, January 25, 2013 12:46 AM
     
     Answered
    Sign In to Vote
    1

    Notice that these plans are _not_ the same.  LIKE uses an index seek and LEFT uses a scan.  The only reason they have the same cost is that the seek doesn't get to skip any pages as the E89 rows start on the first page.  In a bigger table the LIKE query would seek to the first page containing an E89 row and scan until it reads the last E89 row, and the LEFT query will still have to scan all the pages.

    David

    David http://blogs.msdn.com/b/dbrowne/

     
  • Friday, January 25, 2013 12:56 AM
     
     
    Sign In to Vote
    0

    Hi David,

    Ok, so the LEFT in a bigger table will cost more, i will look at this.

    I am still getting different numbers with the queries. (both using the same DDL)

    I am going to extract the records and see what is going on

    thanks

    (36341 row(s) affected) LIKE

    (36358 row(s) affected) LEFT

    David

     
  • Friday, January 25, 2013 12:57 AM
     
     
    Sign In to Vote
    0

    Notice that these plans are _not_ the same.  LIKE uses an index seek and LEFT uses a scan.  The only reason they have the same cost is that the seek doesn't get to skip any pages as the E89 rows start on the first page.  In a bigger table the LIKE query would seek to the first page containing an E89 row and scan until it reads the last E89 row, and the LEFT query will still have to scan all the pages.

    David

    David http://blogs.msdn.com/b/dbrowne/


    Jeez, I didn't even notice that.  Thanks

    Chuck Pedretti | Magenic – North Region | magenic.com

     
  • Friday, January 25, 2013 1:02 AM
    Moderator
     
     
    Sign In to Vote
    0

    >SELECT * from dbo.DimCode where left(Code,3) IN ('E89')

    The above code is not SARGable:

    http://www.sqlusa.com/bestpractices/sargable/

    Kalman Toth SQL 2008 GRAND SLAM
    Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


     
  • Friday, January 25, 2013 1:05 AM
     
     
    Sign In to Vote
    0

    Hi Kalman,

    I will look at the link,

    Are you saying that the LEFT could skip codes?

    David

     
  • Friday, January 25, 2013 1:10 AM
    Moderator
     
     
    Sign In to Vote
    0
    No, he is saying that LEFT will result in index scan while LIKE will result in index seek (which Chuck plan demonstrated). So, the second method is definitely better.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Friday, January 25, 2013 1:12 AM
     
      Has Code
    Sign In to Vote
    0

    Intersting,

    I have found 13 records in the recordset that uses the following LEFT where they donot have and of the Codes in this script, is this what you are saying where this is not SARGable so you can pick up other codes?

    WHERE     (LEFT(dbo.DimCode.Code, 3) IN ('E89', 'G97', 'H59', 'H95', 'I97', 'J95', 'K91', 'M96', 'N99', 'T80', 'T81', 'T82', 'T83', 'T84', 'T85', 'T86', 'T87', 'T88')) OR
                      (dbo.DimCode.Code = 'T983')

    David

     
  • Friday, January 25, 2013 1:14 AM
    Moderator
     
     
    Sign In to Vote
    0
    This is strange. Make sure your table is not corrupted and re-build indexes if possible.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Friday, January 25, 2013 1:16 AM
     
     
    Sign In to Vote
    0

    Ok, so,

    I still have to work out why i get records that dont have the code

    Thanks

    David

     
  • Friday, January 25, 2013 2:34 AM
     
     
    Sign In to Vote
    0

    Thanks everyone,

    I have located the issue with the different counts (corrupted table)

    I also learnt more about optimization, thanks

    David

    David

     
  • Wednesday, January 30, 2013 10:29 PM
     
     
    Sign In to Vote
    1

    Hi Kalman, and everyone.

    I wanted to post the outcome of my tests

    The following script is placed in lots of procedures using a LEFT instead of a LIKE

    >SELECT * from dbo.DimCode where left(Code,3) IN ('E89')

    Change to the following

     SELECT * from dbo.DimCode where CODE LIKE ('E89%') which is SARGable.

    In this 1 procedure SELECT * from dbo.DimCode where left(Code,3) IN (multiple codes) took 9min to run

    When we changed the code to SELECT * from dbo.DimCode where CODE LIKE (multiple codes) it now takes 3min to run

    This is an incredible saving, just in 1 procedure.

    Thanks to you and everyone who spends thier time to help others

    David