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/ArticleID/47502/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/ArticleID/47502/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
>>
>|||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.
>|||Sure,
I can make it really short. All solutions have flaws. ;-)
Seriously now; the static solutions include (as you already gathered by
now):
1. col = @.param OR @.param IS NULL
2. col = COALESCE(@.param, col)
3. col LIKE @.param
4. A series of IF statements
And probably others...
1 and 2 simply often yield inadequate query plans. The reason is that the
optimizer currently doesn't have the logic to develop different branches of
execution based on whether the input was or wasn't NULL, and invoking the
relevant ones based on the input in practice.
3 is limited to character strings.
4 is hard to maintain, and becomes harder as the number of parameters grows
larger (simple combinatorial exercise). Though, interestingly, you could
develop code using dynamic execution that auto-creates multiple stored
procedures, each with a static query in charge of a different combination of
supplied values, and one navigating/redirecting stored procedure.
As for a pure dynamic solution; if you develop it wisely, it beats all the
rest in terms of performance. Though it has the known drawbacks involved
with dynamic execution (SQL Injection, ugly code, and so on).
Here's an example of how the solution utilizing dynamic execution might look
like (note that I didn't include input validation, treatment of SQL
Injection attempts, exception handling):
USE Northwind;
GO
CREATE PROC dbo.usp_GetOrders
@.OrderID AS INT = NULL,
@.CustomerID AS NCHAR(5) = NULL,
@.EmployeeID AS INT = NULL,
@.OrderDate AS DATETIME = NULL
AS
DECLARE @.sql AS NVARCHAR(4000);
SET @.sql = N'SELECT OrderID, CustomerID, EmployeeID, OrderDate'
+ N' FROM dbo.Orders'
+ N' WHERE 1 = 1'
+ CASE WHEN @.OrderID IS NOT NULL THEN
N' AND OrderID = @.oid' ELSE N'' END
+ CASE WHEN @.CustomerID IS NOT NULL THEN
N' AND CustomerID = @.cid' ELSE N'' END
+ CASE WHEN @.EmployeeID IS NOT NULL THEN
N' AND EmployeeID = @.eid' ELSE N'' END
+ CASE WHEN @.OrderDate IS NOT NULL THEN
N' AND OrderDate = @.dt' ELSE N'' END;
EXEC sp_executesql
@.sql,
N'@.oid AS INT, @.cid AS NCHAR(5), @.eid AS INT, @.dt AS DATETIME',
@.oid = @.OrderID,
@.cid = @.CustomerID,
@.eid = @.EmployeeID,
@.dt = @.OrderDate;
GO
-- Test proc
EXEC dbo.usp_GetOrders @.OrderID = 10248;
EXEC dbo.usp_GetOrders @.OrderDate = '19970101';
EXEC dbo.usp_GetOrders @.CustomerID = N'CENTC';
EXEC dbo.usp_GetOrders @.EmployeeID = 5;
The trick here is that the same code string will be generated for the same
input parameter lists. This means that the solution will be able to reuse
execution plans for invocations with the same input parameter lists. You can
easily observe this by querying master.dbo.syscacheobjects.
--
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:eQYMdzeJGHA.668@.TK2MSFTNGP11.phx.gbl...
> 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/ArticleID/47502/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 indeed,
Will I need to use EXEC ... WITH RECOMPILE each time or the SP will be
recompiled when the supplied input parameters change? Will the SQL Server
keep the plan for series of parameters or over writes the plan each time?
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%23VsH2NoJGHA.2912@.tk2msftngp13.phx.gbl...
> Sure,
> I can make it really short. All solutions have flaws. ;-)
> Seriously now; the static solutions include (as you already gathered by
> now):
> 1. col = @.param OR @.param IS NULL
> 2. col = COALESCE(@.param, col)
> 3. col LIKE @.param
> 4. A series of IF statements
> And probably others...
> 1 and 2 simply often yield inadequate query plans. The reason is that the
> optimizer currently doesn't have the logic to develop different branches
> of execution based on whether the input was or wasn't NULL, and invoking
> the relevant ones based on the input in practice.
> 3 is limited to character strings.
> 4 is hard to maintain, and becomes harder as the number of parameters
> grows larger (simple combinatorial exercise). Though, interestingly, you
> could develop code using dynamic execution that auto-creates multiple
> stored procedures, each with a static query in charge of a different
> combination of supplied values, and one navigating/redirecting stored
> procedure.
> As for a pure dynamic solution; if you develop it wisely, it beats all the
> rest in terms of performance. Though it has the known drawbacks involved
> with dynamic execution (SQL Injection, ugly code, and so on).
> Here's an example of how the solution utilizing dynamic execution might
> look like (note that I didn't include input validation, treatment of SQL
> Injection attempts, exception handling):
> USE Northwind;
> GO
> CREATE PROC dbo.usp_GetOrders
> @.OrderID AS INT = NULL,
> @.CustomerID AS NCHAR(5) = NULL,
> @.EmployeeID AS INT = NULL,
> @.OrderDate AS DATETIME = NULL
> AS
> DECLARE @.sql AS NVARCHAR(4000);
> SET @.sql => N'SELECT OrderID, CustomerID, EmployeeID, OrderDate'
> + N' FROM dbo.Orders'
> + N' WHERE 1 = 1'
> + CASE WHEN @.OrderID IS NOT NULL THEN
> N' AND OrderID = @.oid' ELSE N'' END
> + CASE WHEN @.CustomerID IS NOT NULL THEN
> N' AND CustomerID = @.cid' ELSE N'' END
> + CASE WHEN @.EmployeeID IS NOT NULL THEN
> N' AND EmployeeID = @.eid' ELSE N'' END
> + CASE WHEN @.OrderDate IS NOT NULL THEN
> N' AND OrderDate = @.dt' ELSE N'' END;
> EXEC sp_executesql
> @.sql,
> N'@.oid AS INT, @.cid AS NCHAR(5), @.eid AS INT, @.dt AS DATETIME',
> @.oid = @.OrderID,
> @.cid = @.CustomerID,
> @.eid = @.EmployeeID,
> @.dt = @.OrderDate;
> GO
> -- Test proc
> EXEC dbo.usp_GetOrders @.OrderID = 10248;
> EXEC dbo.usp_GetOrders @.OrderDate = '19970101';
> EXEC dbo.usp_GetOrders @.CustomerID = N'CENTC';
> EXEC dbo.usp_GetOrders @.EmployeeID = 5;
> The trick here is that the same code string will be generated for the same
> input parameter lists. This means that the solution will be able to reuse
> execution plans for invocations with the same input parameter lists. You
> can easily observe this by querying master.dbo.syscacheobjects.
> --
> 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:eQYMdzeJGHA.668@.TK2MSFTNGP11.phx.gbl...
>> 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/ArticleID/47502/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
>>
>>
>>
>|||Just a short with respect to
> 3. col LIKE @.param
> 3 is limited to character strings.
Yes, LIKE will only work for (n)(var)char. But for other data types, the
combination of COALESCE and BETWEEN can be used. For an int, this could
be
col BETWEEN COALESCE(@.param, -2147483648) AND COALESCE(@.param,
2147483647)
For a smalldatetime, this could be
col BETWEEN COALESCE(@.param, '19000101') AND COALESCE(@.param,
'20790606 23:59')
Etcetera.
Gert-Jan
Itzik Ben-Gan wrote:
> Sure,
> I can make it really short. All solutions have flaws. ;-)
> Seriously now; the static solutions include (as you already gathered by
> now):
> 1. col = @.param OR @.param IS NULL
> 2. col = COALESCE(@.param, col)
> 3. col LIKE @.param
> 4. A series of IF statements
> And probably others...
> 1 and 2 simply often yield inadequate query plans. The reason is that the
> optimizer currently doesn't have the logic to develop different branches of
> execution based on whether the input was or wasn't NULL, and invoking the
> relevant ones based on the input in practice.
> 3 is limited to character strings.
> 4 is hard to maintain, and becomes harder as the number of parameters grows
> larger (simple combinatorial exercise). Though, interestingly, you could
> develop code using dynamic execution that auto-creates multiple stored
> procedures, each with a static query in charge of a different combination of
> supplied values, and one navigating/redirecting stored procedure.
> As for a pure dynamic solution; if you develop it wisely, it beats all the
> rest in terms of performance. Though it has the known drawbacks involved
> with dynamic execution (SQL Injection, ugly code, and so on).
> Here's an example of how the solution utilizing dynamic execution might look
> like (note that I didn't include input validation, treatment of SQL
> Injection attempts, exception handling):
> USE Northwind;
> GO
> CREATE PROC dbo.usp_GetOrders
> @.OrderID AS INT = NULL,
> @.CustomerID AS NCHAR(5) = NULL,
> @.EmployeeID AS INT = NULL,
> @.OrderDate AS DATETIME = NULL
> AS
> DECLARE @.sql AS NVARCHAR(4000);
> SET @.sql => N'SELECT OrderID, CustomerID, EmployeeID, OrderDate'
> + N' FROM dbo.Orders'
> + N' WHERE 1 = 1'
> + CASE WHEN @.OrderID IS NOT NULL THEN
> N' AND OrderID = @.oid' ELSE N'' END
> + CASE WHEN @.CustomerID IS NOT NULL THEN
> N' AND CustomerID = @.cid' ELSE N'' END
> + CASE WHEN @.EmployeeID IS NOT NULL THEN
> N' AND EmployeeID = @.eid' ELSE N'' END
> + CASE WHEN @.OrderDate IS NOT NULL THEN
> N' AND OrderDate = @.dt' ELSE N'' END;
> EXEC sp_executesql
> @.sql,
> N'@.oid AS INT, @.cid AS NCHAR(5), @.eid AS INT, @.dt AS DATETIME',
> @.oid = @.OrderID,
> @.cid = @.CustomerID,
> @.eid = @.EmployeeID,
> @.dt = @.OrderDate;
> GO
> -- Test proc
> EXEC dbo.usp_GetOrders @.OrderID = 10248;
> EXEC dbo.usp_GetOrders @.OrderDate = '19970101';
> EXEC dbo.usp_GetOrders @.CustomerID = N'CENTC';
> EXEC dbo.usp_GetOrders @.EmployeeID = 5;
> The trick here is that the same code string will be generated for the same
> input parameter lists. This means that the solution will be able to reuse
> execution plans for invocations with the same input parameter lists. You can
> easily observe this by querying master.dbo.syscacheobjects.
> --
> 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.
[snip]|||Here's the beauty--no need to create or execute the proc WITH RECOMPILE.
Dynamic execution operates in a separate batch than the outer level's batch
(the proc's batch in this case), meaning that the dynamic batch is parsed
and optimized separately.
This fact may sometimes be a disadvantage, but in our case it is an
advantage; each unique code string (one per unique parameters list) will
yield a separate execution plan, which will be reused only by the same code
string invoked again. You will end up with as many plans as the unique
parameter lists used in practice.
I suggested querying master.dbo.syscacheobjects to witness this behavior.
--
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:OZUgtJpJGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Thanks indeed,
> Will I need to use EXEC ... WITH RECOMPILE each time or the SP will be
> recompiled when the supplied input parameters change? Will the SQL Server
> keep the plan for series of parameters or over writes the plan each time?
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message news:%23VsH2NoJGHA.2912@.tk2msftngp13.phx.gbl...
>> Sure,
>> I can make it really short. All solutions have flaws. ;-)
>> Seriously now; the static solutions include (as you already gathered by
>> now):
>> 1. col = @.param OR @.param IS NULL
>> 2. col = COALESCE(@.param, col)
>> 3. col LIKE @.param
>> 4. A series of IF statements
>> And probably others...
>> 1 and 2 simply often yield inadequate query plans. The reason is that the
>> optimizer currently doesn't have the logic to develop different branches
>> of execution based on whether the input was or wasn't NULL, and invoking
>> the relevant ones based on the input in practice.
>> 3 is limited to character strings.
>> 4 is hard to maintain, and becomes harder as the number of parameters
>> grows larger (simple combinatorial exercise). Though, interestingly, you
>> could develop code using dynamic execution that auto-creates multiple
>> stored procedures, each with a static query in charge of a different
>> combination of supplied values, and one navigating/redirecting stored
>> procedure.
>> As for a pure dynamic solution; if you develop it wisely, it beats all
>> the rest in terms of performance. Though it has the known drawbacks
>> involved with dynamic execution (SQL Injection, ugly code, and so on).
>> Here's an example of how the solution utilizing dynamic execution might
>> look like (note that I didn't include input validation, treatment of SQL
>> Injection attempts, exception handling):
>> USE Northwind;
>> GO
>> CREATE PROC dbo.usp_GetOrders
>> @.OrderID AS INT = NULL,
>> @.CustomerID AS NCHAR(5) = NULL,
>> @.EmployeeID AS INT = NULL,
>> @.OrderDate AS DATETIME = NULL
>> AS
>> DECLARE @.sql AS NVARCHAR(4000);
>> SET @.sql =>> N'SELECT OrderID, CustomerID, EmployeeID, OrderDate'
>> + N' FROM dbo.Orders'
>> + N' WHERE 1 = 1'
>> + CASE WHEN @.OrderID IS NOT NULL THEN
>> N' AND OrderID = @.oid' ELSE N'' END
>> + CASE WHEN @.CustomerID IS NOT NULL THEN
>> N' AND CustomerID = @.cid' ELSE N'' END
>> + CASE WHEN @.EmployeeID IS NOT NULL THEN
>> N' AND EmployeeID = @.eid' ELSE N'' END
>> + CASE WHEN @.OrderDate IS NOT NULL THEN
>> N' AND OrderDate = @.dt' ELSE N'' END;
>> EXEC sp_executesql
>> @.sql,
>> N'@.oid AS INT, @.cid AS NCHAR(5), @.eid AS INT, @.dt AS DATETIME',
>> @.oid = @.OrderID,
>> @.cid = @.CustomerID,
>> @.eid = @.EmployeeID,
>> @.dt = @.OrderDate;
>> GO
>> -- Test proc
>> EXEC dbo.usp_GetOrders @.OrderID = 10248;
>> EXEC dbo.usp_GetOrders @.OrderDate = '19970101';
>> EXEC dbo.usp_GetOrders @.CustomerID = N'CENTC';
>> EXEC dbo.usp_GetOrders @.EmployeeID = 5;
>> The trick here is that the same code string will be generated for the
>> same input parameter lists. This means that the solution will be able to
>> reuse execution plans for invocations with the same input parameter
>> lists. You can easily observe this by querying
>> master.dbo.syscacheobjects.
>> --
>> 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:eQYMdzeJGHA.668@.TK2MSFTNGP11.phx.gbl...
>> 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/ArticleID/47502/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
>>
>>
>>
>>
>