Embeded case in where clause - causing problems.

Hi.
I have a sp that allows the user to search based on a variable set of parame
ters for example just a home phone or just a buss. phone however when search
ing by last name the user also has to supply the ZIP
optionally he can filter that search by first name or address. I am trying t
o accomplish that by using embede cases - the procedure compiles - but I do
not get the results I want
For example if i pass in '%s%' for @.Lastname and '%1%' for zip - it does not
work - I am sure that are fields with those values in the db
(note: everying worked as expected before I added the embeded cases (and wil
dcard stuff))
the following is the SP - (sorry for the long snippet)
ALTER procedure GetDupCheckResults
(
@.HomePhone varchar(50),
@.BussPhone varchar(50),
@.Email varchar(50),
@.LastName varchar(50),
@.FirstName varchar(50),
@.Address varchar(50),
@.FirmZip varchar(50),
@.PersonZip varchar(50),
@.FirmName varchar(50)
)
as
begin
SELECT nmfid, nmffirst, nmflast, nafcompany, nafadd1, nafadd2, naftype +
' (' + nafdesc + ') ' AS [Adress Type], npfarea + '-' + npfphone AS Phone
FROM
(
select v_nmf_naf_npf_linked.* from
v_nmf_naf_npf_linked inner join
(
Select distinct npfseq from
(
select
npfseq,'HomePhone' FoundSource
from
v_nmf_naf_npf_Linked
where
npfArea + '-' + NPFPhone = @.HomePhone
and
naftype = 'HOME'
and
(
@.HomePhone <> ''
and
@.HomePhone <> '%%'
)
union
select
npfseq,'BussPhone'
from
v_nmf_naf_npf_Linked
where
npfArea+'-'+NPFPhone = @.BussPhone
and
naftype = 'BF'
and
@.BussPhone <> ''
union
select
npfseq,'NameAddress'
from
V_nmf_naf_npf_Linked
where
@.PersonZIP like nafzip
and
@.LastName like nmflast
and
-- optionally allow filter on address and last name
1 =
Case @.Address
When '' then 1
When '%%' then 1 -- when wild cards are in use
Else
(
Case
when @.Address like nafAdd1 then 1
when @.Address like nafAdd2 then 1
else
0
end
)
end
and
1 =
Case @.LastName
When '' then 1
When '%%' then 1
Else
(
Case
when @.Lastname like nmfLast then 1
else 0
end
)
end
and not
(
(@.LastName = '' or @.PersonZip = '')
or
(@.LastName = '%%' or @.PersonZip = '%%')
)
union
select
npfseq,'Company'
from
V_nmf_naf_npf_Linked
where
nafZip like @.FirmZip
and
nafCompany like @.FirmName
and not @.FirmName = ''
and not @.FirmZip = ''
and not @.FirmName = '%%'
and not @.FirmZip = '%%'
)Temptab
)
unionResult on unionResult.npfseq = v_nmf_naf_npf_Linked.npfseq
) dd
end
thank you for slogging thru it.You are sure you have people with the last name '%s%' and zip '%1%'?
What country is this?
Most likely you don't have such data in your database, but that is exactly
what this code will try to find, since your condition (on last name, for
example) is
@.Lastname like nmfLast
While this stored procedure looks far more complicated than it needs to
be, my guess is that you want
nmfLast LIKE @.Lastname
instead of the other way around.
Steve Kass
Drew University
Madler wrote:

>Hi.
>I have a sp that allows the user to search based on a variable set of param
eters for example just a home phone or just a buss. phone however when searc
hing by last name the user also has to supply the ZIP
>optionally he can filter that search by first name or address. I am trying
to accomplish that by using embede cases - the procedure compiles - but I do
not get the results I want
>For example if i pass in '%s%' for @.Lastname and '%1%' for zip - it does no
t work - I am sure that are fields with those values in the db
>(note: everying worked as expected before I added the embeded cases (and wi
ldcard stuff))
>the following is the SP - (sorry for the long snippet)
>ALTER procedure GetDupCheckResults
>(
> @.HomePhone varchar(50),
> @.BussPhone varchar(50),
> @.Email varchar(50),
> @.LastName varchar(50),
> @.FirstName varchar(50),
> @.Address varchar(50),
> @.FirmZip varchar(50),
> @.PersonZip varchar(50),
> @.FirmName varchar(50)
> )
>as
>begin
>SELECT nmfid, nmffirst, nmflast, nafcompany, nafadd1, nafadd2, naftype
+ ' (' + nafdesc + ') ' AS [Adress Type], npfarea + '-' + npfphone AS Phone
>FROM
>(
>select v_nmf_naf_npf_linked.* from
> v_nmf_naf_npf_linked inner join
> (
> Select distinct npfseq from
> (
> select
> npfseq,'HomePhone' FoundSource
> from
> v_nmf_naf_npf_Linked
> where
> npfArea + '-' + NPFPhone = @.HomePhone
> and
> naftype = 'HOME'
> and
> (
> @.HomePhone <> ''
> and
> @.HomePhone <> '%%'
> )
> union
> select
> npfseq,'BussPhone'
> from
> v_nmf_naf_npf_Linked
> where
> npfArea+'-'+NPFPhone = @.BussPhone
> and
> naftype = 'BF'
> and
> @.BussPhone <> ''
> union
> select
> npfseq,'NameAddress'
> from
> V_nmf_naf_npf_Linked
> where
> @.PersonZIP like nafzip
> and
> @.LastName like nmflast
> and
> -- optionally allow filter on address and last name
> 1 =
> Case @.Address
> When '' then 1
> When '%%' then 1 -- when wild cards are in use
> Else
> (
> Case
> when @.Address like nafAdd1 then 1
> when @.Address like nafAdd2 then 1
> else
> 0
> end
> )
> end
> and
> 1 =
> Case @.LastName
> When '' then 1
> When '%%' then 1
> Else
> (
> Case
> when @.Lastname like nmfLast then 1
> else 0
> end
> )
> end
> and not
> (
> (@.LastName = '' or @.PersonZip = '')
> or
> (@.LastName = '%%' or @.PersonZip = '%%')
> )
>
> union
> select
> npfseq,'Company'
> from
> V_nmf_naf_npf_Linked
> where
> nafZip like @.FirmZip
> and
> nafCompany like @.FirmName
> and not @.FirmName = ''
> and not @.FirmZip = ''
> and not @.FirmName = '%%'
> and not @.FirmZip = '%%'
>
> )Temptab
> )
> unionResult on unionResult.npfseq = v_nmf_naf_npf_Linked.npfseq
> ) dd
>end
>
>thank you for slogging thru it.
>
>

effect of more column in where clause

what are the effects of having number of columns in WHERE clause, ie: if we use more columns in where clause, what will be its impact on performance.

this is important for me to design queries

having a lot of unneccessary where clauses will surely slowdown the query if there are.

the best thing that you can do is to streamline the logic into its simplest form

|||Perhaps I misunderstand the question; my normal experience is that if the additional columns contribute to filtering out unwanted records that the additional columns in the where clause usually enhance performance.|||

yeah thats right but a poorly written where clause will definetely slowdown performance.

The trick is to use the best suited functions and keywords in the where clause

here some useful link:

http://www.sql-server-performance.com/transact_sql.asp

regards,

joey

|||

Hi joeydj,

what do you mean by "the trick is to use the best suited where clause by making use of the powerful sql server functions"?

AMB

|||

oh sorry. got a grammar problem

here i have it corrected

"The trick is to use the best suited functions and keywords in the where clause"

hmmm... thats better..

example:

select ... from

where x=1 and x=2 and x=7 and x=9

maybe written as

where x in (1,2,7,9)

|||

Joey is normally better than that; please cut him some slack. I think what he means is

select ... from

where x=1 OR x=2 OR x=7 OR x=9

maybe written as

where x in (1,2,7,9)

|||

hahaha. not been here for sometime.

hmmm thanks kent.

|||

Not to mention that there is no performance benefit in choosing one of these as opposed to the other, anyway. (There is a readability benefit, and the two forms will behave differently if you carelessly tack on AND <another condition> to the query.

Steve Kass

Drew University

http://www.stevekass.com

|||

Can you give an example of the choices you have to make? Usually if you add "more columns in where clause", you change the meaning of the query (but not always), and the first goal of designing a query is for it to ask the right question...

Steve Kass

Drew University

http://www.stevekass.com

|||

One thing that people sometimes forget is that a full table scan can sometimes be better than an indexed scan. If you are ultimately going to read every block of data from disk via an index scan then you might want to forgo the index. Modern databases with good statistics can usually establish an execution plan that is "good enough" but it can still pay to understand your data.

Here is a quick example.

Let's say I have a database of people and for some reason their geneder in overwhelmingly biased in one direction. An optimizer might look at the number of unique values for gender and assume a 50/50 split in the data. It might make good sence to always use an index on gender to access the rows of the table from the optimizer's best guess. However, that might not be the case in practice.

Let's say the table is 100k records with :

90% F

10% M

To find the men an indexed lookup on gender is probably a good thing. On the other hand an indexed lookup to find women will result in more disk IO and slower performance than a full table scan (not counting your network).

Knowing this distribution ahead of time might lead someone looking for all women to do something like

Select * from employees where gender + '' = 'F'

|||

thanks for all your views.

let me give u a specific example.

my database has "branch name" field in all the tables. and we have seperate copy of database for each branch. so a particular branch user will connect to his branch, which has records only for that branch.

in this situation, there is no need to filter the records again with "branch name", but if there is no performance issue , i wish to include it in the WHERE clause to be 100% sure that all the records that the query output does have the same branch name.

|||

In your situation. I think you could add "branch name" column without perfomance issue. But you need to create index or statictics for this column. As a result query optimizer understands that "branch name" same for all records and doesn't use them for plan.

But it any case your could check query plans and only after this decide

|||

This is a second good example of what I am talking about.

If your database is physically segragated by branch already then you want to make sure you know what the optimizer is doing when you add a "failsafe"

Where BranchId = 10

or alternatively

Where BranchName = 'Downtown'

to your queries.

If adding that clause causes the optimizer to include an index on BranchId as part of the execution plan then you will hurt your performance as a result of extra disk i/o and memory use. This might or might not be a concern for your infrastructure. Though it sounds like if you are physically partitioning your database by branch then you might be concerned about database performance.

Although it might seem counter intuative, it might be a good idea to experiment with:

Where BranchId + 0 = 10

or alternatively

Where BranchName + '' = 'Downtown'

This would ensure than no index on BranchId could be used in the execution plan.

|||

Dear AMERMSAMER,

The bottom line is that having more columns in the WHERE clause does not necessarily slow down performance and may, in fact, actually improve performance. It all depends on the indexes on the table and the "selectivity" of the columns in the WHERE. If there is a unique key and all of the columns in the key are "covered" by the WHERE clause, no more may be needed. SQL will try to optimize the query by using these columns where available.

effect of more column in where clause

what are the effects of having number of columns in WHERE clause, ie: if we use more columns in where clause, what will be its impact on performance.

this is important for me to design queries

having a lot of unneccessary where clauses will surely slowdown the query if there are.

the best thing that you can do is to streamline the logic into its simplest form

|||Perhaps I misunderstand the question; my normal experience is that if the additional columns contribute to filtering out unwanted records that the additional columns in the where clause usually enhance performance.|||

yeah thats right but a poorly written where clause will definetely slowdown performance.

The trick is to use the best suited functions and keywords in the where clause

here some useful link:

http://www.sql-server-performance.com/transact_sql.asp

regards,

joey

|||

Hi joeydj,

what do you mean by "the trick is to use the best suited where clause by making use of the powerful sql server functions"?

AMB

|||

oh sorry. got a grammar problem

here i have it corrected

"The trick is to use the best suited functions and keywords in the where clause"

hmmm... thats better..

example:

select ... from

where x=1 and x=2 and x=7 and x=9

maybe written as

where x in (1,2,7,9)

|||

Joey is normally better than that; please cut him some slack. I think what he means is

select ... from

where x=1 OR x=2 OR x=7 OR x=9

maybe written as

where x in (1,2,7,9)

|||

hahaha. not been here for sometime.

hmmm thanks kent.

|||

Not to mention that there is no performance benefit in choosing one of these as opposed to the other, anyway. (There is a readability benefit, and the two forms will behave differently if you carelessly tack on AND <another condition> to the query.

Steve Kass

Drew University

http://www.stevekass.com

|||

Can you give an example of the choices you have to make? Usually if you add "more columns in where clause", you change the meaning of the query (but not always), and the first goal of designing a query is for it to ask the right question...

Steve Kass

Drew University

http://www.stevekass.com

|||

One thing that people sometimes forget is that a full table scan can sometimes be better than an indexed scan. If you are ultimately going to read every block of data from disk via an index scan then you might want to forgo the index. Modern databases with good statistics can usually establish an execution plan that is "good enough" but it can still pay to understand your data.

Here is a quick example.

Let's say I have a database of people and for some reason their geneder in overwhelmingly biased in one direction. An optimizer might look at the number of unique values for gender and assume a 50/50 split in the data. It might make good sence to always use an index on gender to access the rows of the table from the optimizer's best guess. However, that might not be the case in practice.

Let's say the table is 100k records with :

90% F

10% M

To find the men an indexed lookup on gender is probably a good thing. On the other hand an indexed lookup to find women will result in more disk IO and slower performance than a full table scan (not counting your network).

Knowing this distribution ahead of time might lead someone looking for all women to do something like

Select * from employees where gender + '' = 'F'

|||

thanks for all your views.

let me give u a specific example.

my database has "branch name" field in all the tables. and we have seperate copy of database for each branch. so a particular branch user will connect to his branch, which has records only for that branch.

in this situation, there is no need to filter the records again with "branch name", but if there is no performance issue , i wish to include it in the WHERE clause to be 100% sure that all the records that the query output does have the same branch name.

|||

In your situation. I think you could add "branch name" column without perfomance issue. But you need to create index or statictics for this column. As a result query optimizer understands that "branch name" same for all records and doesn't use them for plan.

But it any case your could check query plans and only after this decide

|||

This is a second good example of what I am talking about.

If your database is physically segragated by branch already then you want to make sure you know what the optimizer is doing when you add a "failsafe"

Where BranchId = 10

or alternatively

Where BranchName = 'Downtown'

to your queries.

If adding that clause causes the optimizer to include an index on BranchId as part of the execution plan then you will hurt your performance as a result of extra disk i/o and memory use. This might or might not be a concern for your infrastructure. Though it sounds like if you are physically partitioning your database by branch then you might be concerned about database performance.

Although it might seem counter intuative, it might be a good idea to experiment with:

Where BranchId + 0 = 10

or alternatively

Where BranchName + '' = 'Downtown'

This would ensure than no index on BranchId could be used in the execution plan.

|||

Dear AMERMSAMER,

The bottom line is that having more columns in the WHERE clause does not necessarily slow down performance and may, in fact, actually improve performance. It all depends on the indexes on the table and the "selectivity" of the columns in the WHERE. If there is a unique key and all of the columns in the key are "covered" by the WHERE clause, no more may be needed. SQL will try to optimize the query by using these columns where available.