Is OR so bad?

13 Jan 2006 12:30 PM

tonicvodka

Hi all !

I have the following query which runs ok:

SELECT
estates.l_memberID,
member.l_memberID
FROM
estates
left join companies on estates.l_companyid=companies.l_companyid
left join multimedia on multimedia.l_estateid=estates.l_estateid and
isnull(N_INDEX,1)=1
left join member (NOLOCK) on estates.l_memberid=member.l_memberid
WHERE
    isnull(estates.B_ONLYCOMPANIES,0) = 0 AND
    isnull(estates.B_FIRSTHAND,0)    = 0    AND
    estates.n_state        = 0     AND
    estates.n_type        = 7        AND
    isnull(dat_show,getdate())    <= getdate()    AND
    (ISNULL(MULTIMEDIA.N_INDEX,0)    = 0             AND
    ISNULL(MULTIMEDIA.N_TYPE,0)    = 0)

The thing is if I add the following row:

AND (@RentMin = -1) OR (estates.L_RENT >= @RentMin)

It goes from running under a second to a 20 second query! How is this
possible? I'm just asking SQL to ignore the parameter if it's -1.

Any light shed greatly appreciated,
Niclas

13 Jan 2006 12:47 PM

impslayer

tonicvodka skrev:

Show quote

> Hi all !
>
> I have the following query which runs ok:
>
> SELECT
> estates.l_memberID,
> member.l_memberID
> FROM
> estates
> left join companies on estates.l_companyid=companies.l_companyid
> left join multimedia on multimedia.l_estateid=estates.l_estateid and
> isnull(N_INDEX,1)=1
> left join member (NOLOCK) on estates.l_memberid=member.l_memberid
> WHERE
>     isnull(estates.B_ONLYCOMPANIES,0) = 0 AND
>     isnull(estates.B_FIRSTHAND,0)    = 0    AND
>     estates.n_state        = 0     AND
>     estates.n_type        = 7        AND
>     isnull(dat_show,getdate())    <= getdate()    AND
>     (ISNULL(MULTIMEDIA.N_INDEX,0)    = 0             AND
>     ISNULL(MULTIMEDIA.N_TYPE,0)    = 0)
>
> The thing is if I add the following row:
>
> AND (@RentMin = -1) OR (estates.L_RENT >= @RentMin)
>
> It goes from running under a second to a 20 second query! How is this
> possible? I'm just asking SQL to ignore the parameter if it's -1.
>
> Any light shed greatly appreciated,
> Niclas

Do you mean to OR the last condition to everything else, or do you want
to OR it only to the condition on the same row? If the latter, add a
parenthesis:

AND ((@RentMin = -1) OR (estates.L_RENT >= @RentMin))

Mind you, I didn't really analyze the rest of the query.

/impslayer, aka Birger Johansson

13 Jan 2006 12:48 PM

Rick Sawtell

>
> The thing is if I add the following row:
>
> AND (@RentMin = -1) OR (estates.L_RENT >= @RentMin)
>
> It goes from running under a second to a 20 second query! How is this
> possible? I'm just asking SQL to ignore the parameter if it's -1.
>
> Any light shed greatly appreciated,
> Niclas
>

You are not actually just asking SQL to ignore the paramenter if it's -1.

Your statement comes out like:

condition1 AND
condition2 AND
....
OR Condition#.

You probably need another set of parenthesis. Try the following:

AND ( (@RentMin = -1) OR (estates.L_RENT >= @RentMin) )

Rick Sawtell
MCT, MCSD, MCDBA

13 Jan 2006 12:51 PM

tonicvodka

Thanks both for quick response!

Very true I was quick and sloppy...

13 Jan 2006 12:58 PM

tonicvodka

Though, while I have your attention, which is to prefer;

A stored proc with many rows of e.g.
AND ( (@RentMin = -1) OR (estates.L_RENT >= @RentMin) )

or

building the query in code, being able to filter away those rows, and
then sending it to SQL-server?

13 Jan 2006 4:22 PM

Rick Sawtell

"tonicvodka" <tonicvo***@hotmail.com> wrote in message
news:1137157131.575494.295570@g44g2000cwa.googlegroups.com...
> Though, while I have your attention, which is to prefer;
>
> A stored proc with many rows of e.g.
> AND ( (@RentMin = -1) OR (estates.L_RENT >= @RentMin) )
>
> or
>
> building the query in code, being able to filter away those rows, and
> then sending it to SQL-server?
>

The sproc will probably be faster as it is a series of AND statements. Each
one will continue to limit the data returned.

Rick Sawtell
MCT, MCSD, MCDBA

13 Jan 2006 10:57 PM

Hugo Kornelis

On 13 Jan 2006 04:58:51 -0800, tonicvodka wrote:

>Though, while I have your attention, which is to prefer;
>
>A stored proc with many rows of e.g.
>AND ( (@RentMin = -1) OR (estates.L_RENT >= @RentMin) )
>
>or
>
>building the query in code, being able to filter away those rows, and
>then sending it to SQL-server?

Hi tonicvodka,

http://www.sommarskog.se/dynamic_sql.html

--
Hugo Kornelis, SQL Server MVP