performance using like

• Azizah -235210

  SSC Rookie

  Points: 30

  More actions

  May 25, 2005 at 11:34 pm

  #69089

  What is the difference in  performance/response time if I query by;

  Select * from customer where cust_id='ANNE'

  or

  Select * from customer where cust_id like 'ANNE%'

  or

  Select * from customer where cust_id='ANNE'

  Assumption : 500,000 records in table customer

                  : record length=500

                  : cust_id is not unique

   

• David.Poole

  SSC Guru

  Points: 75656

  More actions

  May 26, 2005 at 2:34 am

  #561151

  I've just tested this against a 50,000 record table with record length 4468 and a none unique varchar column that is part of a compound index.

  In both cases the execution plan was identical and the time to run was identical even though the queries produced a different number of records.

  On a table with 250,000 records with record length 4096 with a non-indexed VARCHAR column of length 4000

  The = had a cost of 0.0971

  The LIKE had a cost of 0.178

  The difference was in the FILTER portion of the execution plan.

  In terms of physical time both took about 2 seconds.

  Not really a lot in it.

  LinkedIn Profile

• nileshsane

  Default port

  Points: 1465

  More actions

  May 26, 2005 at 5:43 am

  #561181

  I tried the query on a table which contains 40K records (not much) which has a PK - clustered. Tried 2 scenarios, the first one in which the column has a clustered index and the other in which its a not clustered index. In both the cases the "=" had less than 5% of the total query cost. The execution times were similar.

  ---

  What I hear I forget, what I see I remember, what I do I understand