Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    4

    Unanswered: Optimizing/Improving WHERE

    Here's a sample SELECT statement:

    Code:
    SELECT T1.F3 
    FROM T1 INNER JOIN T2 ON T1.F4 = T2.F4 
    WHERE 
    (T1.F1 > @iNum AND T2.F1 > @iNum) 
    OR 
    ( @iNum2 * (T1.F1 - T2.F1)/(T1.F2 - T2.F2) ) + (T1.F1 - ((T1.F1 - T2.F1)/(T1.F2 - T2.F2) * T1.F2) ) > @INum
    As you can see, the second part of the WHERE (after the OR) is much more complicated than the part before the OR. My query would run a lot faster if it tried the first part of the OR and didn't bother with the second part if the first part was satisfied. Is there any way to do this?

    Thanks!

    Tyler

  2. #2
    Join Date
    May 2002
    Posts
    299
    try:

    select *
    from t1 join t2 on ....
    where <first filter>

    union

    select *
    from t1 join t2 on ...
    where <second filter>
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    Oct 2005
    Posts
    4
    hey! interesting idea... I just played with that and it would work fine. but... I can't figure out how to use it when I want to group by a field that exists in both selects and do counts based on those groupings. it will end up with two different results for each group (one from each select)... but I'd like them together because they are actually grouping on the same field. make sense?

  4. #4
    Join Date
    May 2002
    Posts
    299
    according to your sample query, you only have 1 column. so, your query would be as simple as this:


    select t3
    from ... where ...
    union
    select t3
    from ... where ...

    and if you want to do further processing, you can just derive it.
    select count(t3)
    from (
    select t3
    from ... where ...
    union
    select t3
    from ... where ...
    ) derived
    --
    -oj
    http://www.rac4sql.net

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •