Is OR so bad?

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,
Niclastonicvodka skrev:

> 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|||>
> 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|||Thanks both for quick response!
Very true I was quick and sloppy...|||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?|||"tonicvodka" <tonicvodka@.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|||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