Optimizing a query

Hi,
I have created the following SP and indexes, but the execution plan for SP
shows that query optimizer always uses 'index scan'.
--------
use northwind
go

create proc usp_search
@country varchar(100)=null,
@city varchar(100)=null
as
select  customerid,companyna
me,country,city from customers where
(@country is null OR country=@country)
and
(@city is null OR city=@city)
go

create index ix10 on  customers(country,ci
ty,companyname)
create index ix11 on  customers(city,count
ry,companyname)
go

exec usp_search 'uk','london' with recompile
---------


If I remove any of the ORs, one of my indxes will be used. Are there any
solution to keep both ORs and optimizer uses my indexes? Should I force
optimizer to use any index?
Thanks in advance,
Leila

Leila,

in this case it helps to be specific. Since you only have 4 cases, a
nested IF ... ELSE will do the trick

*untested*:

if (@country is null)
begin
if @city is null
begin
select  customerid,companyna
me,country,city from customers
end
else
begin
select  customerid,companyna
me,country,city from customers
where city=@city
end
end
else
begin
if @city is null
begin
select  customerid,companyna
me,country,city from customers
where country=@country
end
else
begin
select  customerid,companyna
me,country,city from customers
where city=@city
and country=@country
end
end

Good luck!

You might want to start from:
http://www.sommarskog.se/dyn-search.html

--
Anith

Thanks Alexander,
But my real SP has 20 parameters. This SP was only a sample of what I want
to do.




"Alexander Kuznetsov" < AK_TIREDOFSPAM@hotma
il.COM> wrote in message
news:1138634724.575101.70310@g44g2000cwa.googlegroups.com...
> Leila,
>
> in this case it helps to be specific. Since you only have 4 cases, a
> nested IF ... ELSE will do the trick
>
> *untested*:
>
> if (@country is null)
> begin
>  if @city is null
>  begin
>    select  customerid,companyna
me,country,city from customers
>  end
>  else
>  begin
>    select  customerid,companyna
me,country,city from customers
>    where city=@city
>  end
> end
> else
> begin
>  if @city is null
>  begin
>    select  customerid,companyna
me,country,city from customers
>    where country=@country
>  end
>  else
>  begin
>    select  customerid,companyna
me,country,city from customers
>    where city=@city
>    and country=@country
>  end
> end
>
> Good luck!
>

Leila,

In that case I would concur with Anith. I would utilize dynamic SQL, as
it is described in Erland's article he mentioned.

Yet I have a question for you. How are you testing your SP with 20
parameters? With 8 ro 10 parameters I would do something like this:

create table test_log(country varchar(25), city varchar(25))
go
create procedure myproc(@country varchar(25), @city varchar(25))
as
insert into test_log values(@country, @city)
go
declare @country varchar(25), @city varchar(25)
declare test_cases cursor
for
select * from
(
--- more than 50% customers
select 'USA' country_name
union all
---- less then 1% customers
select 'New Zealand'
union all
select NULL) country,
(select 'Boston' city
union all
---- city inconsistent with any country from the list above
select 'Kharkiv' city
union all
select null
) city
open test_cases
fetch next from test_cases into @country, @city
while @@fetch_status=0
begin
exec myproc @country, @city
fetch next from test_cases into @country, @city
end
go
select * from test_log

country                   city
------------------------- -------------------------
USA                       Boston
USA                       Kharkiv
USA                       NULL
New Zealand               Boston
New Zealand               Kharkiv
New Zealand               NULL
NULL                      Boston
NULL                      Kharkiv
NULL                      NULL

(9 row(s) affected)


go
drop table test_log
drop procedure myproc

So, for 2 parameters I needed 9 calls to do a unit test. Of course,
there is no need to open a cursor for mere 9 calls, I just wanted to
demostrate the technique used to make 1K calls.

Are you making 1 million calls for unit testing of your procedure with
20 parameters?

This is a classic example where dynamic execution should be considered.

You can find details here, assuming you have a subscription to SQLMag:
http://www.windowsitpro.com/Article...7502/47502.html

If you don't, let me know and I'll try to summarize.

--
BG, SQL Server MVP
www. SolidQualityLearning
.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.


"Leila" <Leilas@hotpop.com> wrote in message
news:O1PW%23$aJGHA.1424@TK2MSFTNGP12.phx.gbl...
> Hi,
> I have created the following SP and indexes, but the execution plan for SP
> shows that query optimizer always uses 'index scan'.
> --------
> use northwind
> go
>
> create proc usp_search
> @country varchar(100)=null,
> @city varchar(100)=null
> as
> select  customerid,companyna
me,country,city from customers where
> (@country is null OR country=@country)
> and
> (@city is null OR city=@city)
> go
>
> create index ix10 on  customers(country,ci
ty,companyname)
> create index ix11 on  customers(city,count
ry,companyname)
> go
>
> exec usp_search 'uk','london' with recompile
> ---------
>
>
> If I remove any of the ORs, one of my indxes will be used. Are there any
> solution to keep both ORs and optimizer uses my indexes? Should I force
> optimizer to use any index?
> Thanks in advance,
> Leila
>
>
>

Thanks Itzik,
I'll be most grateful if you could do that.
BTW, what's your idea about this manner:

use AdventureWorks
go
create index ix1 on person. contact(LastName,Fir
stName,MiddleName)
create index ix2 on person. contact(FirstName,La
stName,MiddleName)
go
create proc usp_02
@LastName varchar(100)='%',
@FirstName varchar(100)='%'
AS
SELECT  MiddleName,LastName,
FirstName from person.contact
where (LastName like @LastName)
and
(FirstName like @FirstName)
go

It works very good and performs an 'Index Seek'. But one of the problems
that I noticed is on numeric columns(parameters). It has to implicitly
convert the number to varchar, so it doesn't perform Index seek, rather it
does Index Scan. I mean in the worst situation, its performance is like the
SP which I wrote in my first post (using NULLs)

Leila





"Itzik Ben-Gan" <itzik@REMOVETHIS. SolidQualityLearning
.com> wrote in message
news:eSfAJRdJGHA.1028@TK2MSFTNGP11.phx.gbl...
> This is a classic example where dynamic execution should be considered.
>
> You can find details here, assuming you have a subscription to SQLMag:
> http://www.windowsitpro.com/Article...7502/47502.html
>
> If you don't, let me know and I'll try to summarize.
>
> --
> BG, SQL Server MVP
> www. SolidQualityLearning
.com
> www.insidetsql.com
> Anything written in this message represents my view, my own view, and
> nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
>
>
> "Leila" <Leilas@hotpop.com> wrote in message
> news:O1PW%23$aJGHA.1424@TK2MSFTNGP12.phx.gbl... 
>
>

If you had followed any ISO standards instead of what you made up on
the fly, would it look more like this?   Without the super long
parameters that invite errors?  With an ISO-11179 names?

CREATE PROCEDURE  SearchCity
(@my_country_code  CHAR(3) = NULL,   -- ISO standards!!
@my_city_naem  VARCHAR(25) = NULL  -- postal union standards
AS
SELECT customer_id, company_name, country_code, city_name
FROM Customers
WHERE  COALESCE (@my_country_code, country_code  = country_code)
AND COALESCE (@my_ city_name, city_name) = city_anme ;

Since you never thought to post DDL, can we assume that (company_name,
city_name, country_code) is the key?  The usual rule is to order an
index by the most selective to the least selective column.

SQL Server's optimizer is still a bit behind, so it the COALESCE()
trick does not work as well as it does in other products, such as DB2,
that can spot this form.  I am not sure if SQL-2005 can do it.

Thanks Joe,
The COALESCE function (in SQL Server 2005) produces the same execution plan
as using IS NULL manner (an index scan is performed). But using '%' and
'like' performs index seek when you use it to seach strings:

use AdventureWorks
go
create index ix1 on person. contact(LastName,Fir
stName,MiddleName)
create index ix2 on person. contact(FirstName,La
stName,MiddleName)
go
create proc usp_02
@LastName varchar(100)='%',
@FirstName varchar(100)='%'
AS
SELECT  MiddleName,LastName,
FirstName from person.contact
where (LastName like @LastName)
and
(FirstName like @FirstName)
go



"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1138675949.716153.137090@o13g2000cwo.googlegroups.com...
> If you had followed any ISO standards instead of what you made up on
> the fly, would it look more like this?   Without the super long
> parameters that invite errors?  With an ISO-11179 names?
>
> CREATE PROCEDURE  SearchCity
> (@my_country_code  CHAR(3) = NULL,   -- ISO standards!!
> @my_city_naem  VARCHAR(25) = NULL  -- postal union standards
> AS
> SELECT customer_id, company_name, country_code, city_name
>   FROM Customers
> WHERE  COALESCE (@my_country_code, country_code  = country_code)
>   AND COALESCE (@my_ city_name, city_name) = city_anme ;
>
> Since you never thought to post DDL, can we assume that (company_name,
> city_name, country_code) is the key?  The usual rule is to order an
> index by the most selective to the least selective column.
>
> SQL Server's optimizer is still a bit behind, so it the COALESCE()
> trick does not work as well as it does in other products, such as DB2,
> that can spot this form.  I am not sure if SQL-2005 can do it.
>

Again you show your complete lack of real world implementation experience of
SQL.

The query you present will give a table or index scan and will not scale, if
will cause SIGNIFICANT performance problems on a large table.

You should use IF ELSE at the very least to code for each optional parameter
combination, this can be done in the stored procedure, a single stored
procedure without having to bloat code and go for multiple stored procedures
which would lead to a more complicated design and increase your development
and maintanence costs.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1138675949.716153.137090@o13g2000cwo.googlegroups.com...
> If you had followed any ISO standards instead of what you made up on
> the fly, would it look more like this?   Without the super long
> parameters that invite errors?  With an ISO-11179 names?
>
> CREATE PROCEDURE  SearchCity
> (@my_country_code  CHAR(3) = NULL,   -- ISO standards!!
> @my_city_naem  VARCHAR(25) = NULL  -- postal union standards
> AS
> SELECT customer_id, company_name, country_code, city_name
>   FROM Customers
> WHERE  COALESCE (@my_country_code, country_code  = country_code)
>   AND COALESCE (@my_ city_name, city_name) = city_anme ;
>
> Since you never thought to post DDL, can we assume that (company_name,
> city_name, country_code) is the key?  The usual rule is to order an
> index by the most selective to the least selective column.
>
> SQL Server's optimizer is still a bit behind, so it the COALESCE()
> trick does not work as well as it does in other products, such as DB2,
> that can spot this form.  I am not sure if SQL-2005 can do it.
>