query take long time

• SIVAM_SOLAI

  Newbie

  Points: 3

  More actions

  December 5, 2005 at 9:54 pm

  #110954

  hai

  i have two table . the name of Mt and  win. the mt table have 26,00,000 record . the win table have 56,000 record.

  the mt table field is ID, username , password ,iP address

  the   win table field is ID, UrlDetails,status,

  the UrlDetails values is   username+ password+ iP address (mixed of the 3 values) 

  my query is

  SELECT    status  FROM         win  WHERE     EXISTS

                 (SELECT     iP address  FROM        MT                            WHERE      ((UrlDetails LIKE '%IP%'))                  

  the query take long time 20 hours. how is reduce time pls advice  me

  with reagrds

  kumar

   

   

• David.Poole

  SSC Guru

  Points: 76214

  More actions

  December 6, 2005 at 2:05 am

  #607878

  Create a temporary table that holds the extracted IP Address from MT then do an inner join between status and your temp table

  LinkedIn Profile

• colin.Leversuch-Roberts

  SSC Guru

  Points: 52643

  More actions

  December 7, 2005 at 4:13 am

  #608138

  any sarg that uses a leading % will have to table scan which is very expensive. Do you need the leading % ?

  [font="Comic Sans MS"]The GrumpyOldDBA[/font]
  www.grumpyolddba.co.uk
  http://sqlblogcasts.com/blogs/grumpyolddba/