Would like query to run a little faster

[alwayslrN]

This query takes about 10 to 11 seconds to run, is there something I can do differently to make it run a little faster? Please note the hard coded dates will be changed to a variable. Right now I am just testing.

Thanks

select * from nyl_sfp_balances nsb2 
    where nsb2.pool not in 
          (select nsr.pool 
           from nyl_sfp_balances nsr
           where hist_date = '2007-01-31') or
           nsb2.loan not in
          (select nsr.loan 
           from nyl_sfp_balances nsr
           where hist_date = '2007-01-31')  
          and 
          nsb2.hist_date=
         (select max(hist_date) from nyl_sfp_balances where hist_date < '2007-01-31')

 

[SQLSister]

Avoid using not in. Try using a left join instead.

Questions about posting. See faq183-874

 

[onpnt]

And avoid using *

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[dandot]

Avoid using * and try to use joins in your queries rather then subqueries.

 

[SQLSister]

make sure you have good indexes

Questions about posting. See faq183-874

 

[alwayslrN]

I tried doing SQLSister's first suggestion, but I was either getting the incorrect results and I made the query about 3 seconds slower.

I'm giving my brain a rest from it, for now.

 

[onpnt]

Oh and don't forget to upgrade to the biggest and fastest server money can buy. That's always bound to speed up things ;-)

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[r937]

make sure you have your ANDs and ORs properly parenthesized

here's what you wrote --

where nsb2.pool not in ([red]x[/red])
   or nsb2.loan not in ([red]y[/red])  
  and nsb2.hist_date = ([red]z[/red])

which is equivalent to --

where nsb2.pool not in ([red]x[/red])
   or (
      nsb2.loan not in ([red]y[/red])  
  and nsb2.hist_date = ([red]z[/red])
      )

however, i'm guessing that what you might actually want is --

where (
      nsb2.pool not in ([red]x[/red])
   or nsb2.loan not in ([red]y[/red])  
      )
  and nsb2.hist_date = ([red]z[/red])

this difference could easily account for the poor performance


r937.com | rudy.ca

 

[SQLSister]

When you did the left join, you need to add a where clause stating that some field in the left joined table is null to make it the equivalent of the not in subquery.
soemthing like

select t.field1, t.field2 from table1 t 
left join table2 t2 on t1.field1 = t2.field1
where t2.field1 is null

Also check your indexes, having fields in a where clause that aren't indexed can cause huge performance delays in selects.


Questions about posting. See faq183-874