Optimizing Query

• Arun karthik

  SSC Rookie

  Points: 27

  More actions

  August 23, 2005 at 12:12 am

  #88638

  Hi Everybody..

  I have a table which has 1 million records in it i am doing a select on that table with a where condition it is taking a very long time as 1:45 mins it takes to get me 28259 record...

  Question : How can i reduce the exection time..

  Code: Select * from TempReport Where DATETIMELASTMODIFIED Between '06/23/2005' And '08/22/2005' And IDLocation='{2D93FD6E-64F7-4A0A-A7F9-0B806D8907ED}'

  Note: I have no UNIQUE field in it so no field is indexed ..

  Thanks in advance

  Arun karthik

   

   

   

   

• ramesh_kondaparthy

  Ten Centuries

  Points: 1069

  More actions

  August 23, 2005 at 1:05 am

  #584149

  Hi Arun,

   

  Create a covered index on

  DATETIMELASTMODIFIED , IDLocation columns and executre the query.

  Script to create index

   

  Create NonClustered index IX_DATE_ID ON TempReport (DATETIMELASTMODIFIED ,IDLocation )

   

  Reagrds,

  ramesh

• Vasc

  SSCarpal Tunnel

  Points: 4623

  More actions

  August 23, 2005 at 7:58 am

  #584183

  Also try to change the IDLocation data type to be an INT (wich should cover your location) but will MUCH faster than have UniqueIdentifier

  IDLocation='{2D93FD6E-64F7-4A0A-A7F9-0B806D8907ED}'

  ---

  Kindest Regards,

  Vasc

• Max Mulawa

  SSC Enthusiast

  Points: 174

  More actions

  August 23, 2005 at 8:18 am

  #584196

  Note: I have no UNIQUE field in it so no field is indexed ..

  Not every index has to be based on unique set of fields, only Unique Index.

  Check this link:

  Creating Index