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,companyname,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,city,companyname)
create index ix11 on customers(city,country,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,
LeilaLeila,
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,companyname,country,city from customers
end
else
begin
select customerid,companyname,country,city from customers
where city=@.city
end
end
else
begin
if @.city is null
begin
select customerid,companyname,country,city from customers
where country=@.country
end
else
begin
select customerid,companyname,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@.hotmail.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,companyname,country,city from customers
> end
> else
> begin
> select customerid,companyname,country,city from customers
> where city=@.city
> end
> end
> else
> begin
> if @.city is null
> begin
> select customerid,companyname,country,city from customers
> where country=@.country
> end
> else
> begin
> select customerid,companyname,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,companyname,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,city,companyname)
> create index ix11 on customers(city,country,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,FirstName,MiddleName)
create index ix2 on person.contact(FirstName,LastName,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,FirstName,MiddleName)
create index ix2 on person.contact(FirstName,LastName,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.
>