Optimizing daylight savings query by not using UNION

I was interested in optimizing a query I created in SQL Server 2000 for
adjusting time zones for daylight savings time (only USA DST for simplicity)
.
In essence I have one table "tblDaylightSavingsTime" where the PK is Year,
with fields Spring_Forward (ex: 4/3/2005 2:00:00 AM), and Fall_back (ex:
10/30/2005 2:00:00 AM).
I am joining the Daylight Savings Table to another database view, the logic
goes like this:
If the view's datetime falls inside of the DST range for that year, then
adjust for Daylight Savings.
*UNION*
If the view's datetime falls outside of the DST range for that year, then
adjust for Regular Time.
The volume of data pushed through this query is large (in the millions). Is
there any different way to optimize this query by not using UNION?
Thanks!
--
SELECT h.*, DATEADD(hh, h.regularTime, h.indatetime) AS indatetime_adj
FROM qryhsplit_formatted h INNER JOIN
tblDaylightSavingsTime dst ON year(h.indatetime) =
dst.inYear AND ((h.indatetime < dst.Spring_Forward) OR (h.indatetime >
dst.Fall_Back))
UNION ALL
SELECT h.*, DATEADD(hh, h.savingsTime, h.indatetime) AS indatetime_adj
FROM qryhsplit_formatted h INNER JOIN
tblDaylightSavingsTime dst ON year(h.indatetime) =
dst.inYear AND (h.indatetime BETWEEN dst.Spring_Forward AND dst.Fall_Back)How about using a calendar table? This specific example is treated:
http://www.aspfaq.com/2519
"br" <br@.discussions.microsoft.com> wrote in message
news:CA0D1166-D8D5-4C97-8DA1-2E06FDB212B5@.microsoft.com...
>I was interested in optimizing a query I created in SQL Server 2000 for
> adjusting time zones for daylight savings time (only USA DST for
> simplicity).
> In essence I have one table "tblDaylightSavingsTime" where the PK is Year,
> with fields Spring_Forward (ex: 4/3/2005 2:00:00 AM), and Fall_back (ex:
> 10/30/2005 2:00:00 AM).
> I am joining the Daylight Savings Table to another database view, the
> logic
> goes like this:
> If the view's datetime falls inside of the DST range for that year, then
> adjust for Daylight Savings.
> *UNION*
> If the view's datetime falls outside of the DST range for that year, then
> adjust for Regular Time.
> The volume of data pushed through this query is large (in the millions).
> Is
> there any different way to optimize this query by not using UNION?
> Thanks!
> --
> SELECT h.*, DATEADD(hh, h.regularTime, h.indatetime) AS indatetime_adj
> FROM qryhsplit_formatted h INNER JOIN
> tblDaylightSavingsTime dst ON year(h.indatetime) =
> dst.inYear AND ((h.indatetime < dst.Spring_Forward) OR (h.indatetime >
> dst.Fall_Back))
> UNION ALL
> SELECT h.*, DATEADD(hh, h.savingsTime, h.indatetime) AS indatetime_adj
> FROM qryhsplit_formatted h INNER JOIN
> tblDaylightSavingsTime dst ON year(h.indatetime) =
> dst.inYear AND (h.indatetime BETWEEN dst.Spring_Forward AND dst.Fall_Back)
>|||Try this instead; sorry I couldn't figure out all of your date fields, so I
wrote the below with my own column labels.
SELECT "adjusted_date" =
CASE
WHEN t1.[your datetime field] BETWEEN t2.Spring_Forward AND t2.Fall_Back
THEN DATEADD(hh, 1, t1.[your datetime field])
ELSE t1.[your datetime field]
END
FROM qryhsplit_formatted
INNER JOIN tblDaylightSavingsTime t2
ON YEAR(t1.[your datetime field] = t2.Year
Keep in mind that the dates for DST clock changes will shift starting in 200
7!
"br" wrote:

> I was interested in optimizing a query I created in SQL Server 2000 for
> adjusting time zones for daylight savings time (only USA DST for simplicit
y).
> In essence I have one table "tblDaylightSavingsTime" where the PK is Year
,
> with fields Spring_Forward (ex: 4/3/2005 2:00:00 AM), and Fall_back (ex:
> 10/30/2005 2:00:00 AM).
> I am joining the Daylight Savings Table to another database view, the logi
c
> goes like this:
> If the view's datetime falls inside of the DST range for that year, then
> adjust for Daylight Savings.
> *UNION*
> If the view's datetime falls outside of the DST range for that year, then
> adjust for Regular Time.
> The volume of data pushed through this query is large (in the millions).
Is
> there any different way to optimize this query by not using UNION?
> Thanks!
> --
> SELECT h.*, DATEADD(hh, h.regularTime, h.indatetime) AS indatetime_adj
> FROM qryhsplit_formatted h INNER JOIN
> tblDaylightSavingsTime dst ON year(h.indatetime) =
> dst.inYear AND ((h.indatetime < dst.Spring_Forward) OR (h.indatetime >
> dst.Fall_Back))
> UNION ALL
> SELECT h.*, DATEADD(hh, h.savingsTime, h.indatetime) AS indatetime_adj
> FROM qryhsplit_formatted h INNER JOIN
> tblDaylightSavingsTime dst ON year(h.indatetime) =
> dst.inYear AND (h.indatetime BETWEEN dst.Spring_Forward AND dst.Fall_Back)
>|||Forgot to put in t1 table alias for the qryhsplit_formatted view, and closin
g
) for YEAR function:
SELECT "adjusted_date" =
CASE
WHEN t1.[your datetime field] BETWEEN t2.Spring_Forward AND t2.Fall_Back
THEN DATEADD(hh, 1, t1.[your datetime field])
ELSE t1.[your datetime field]
END
FROM qryhsplit_formatted t1
INNER JOIN tblDaylightSavingsTime t2
ON YEAR(t1.[your datetime field]) = t2.Year
"Mark Williams" wrote:
> Try this instead; sorry I couldn't figure out all of your date fields, so
I
> wrote the below with my own column labels.
> SELECT "adjusted_date" =
> CASE
> WHEN t1.[your datetime field] BETWEEN t2.Spring_Forward AND t2.Fall_Back
> THEN DATEADD(hh, 1, t1.[your datetime field])
> ELSE t1.[your datetime field]
> END
> FROM qryhsplit_formatted
> INNER JOIN tblDaylightSavingsTime t2
> ON YEAR(t1.[your datetime field] = t2.Year
> Keep in mind that the dates for DST clock changes will shift starting in 2
007!
> --
>
> "br" wrote:
>|||Way faster!! Thanks very much. Sometimes I get bogged down in the syntax
for the Case / If statements, but this makes it much more clear.
Thanks for the heads up on the DST changes for 2007. The constant
historical changes for DST was one of the reasons why I chose to have a
tblDaylightSavingsTime that denotes the start times and end times of DST for
each year.
FYI - my adjustments "h.savingsTime", and "h.regularTime" are Time Zone
specific adjustments for bringing all data elements into Arizona Time - MST
(AZ).
"Mark Williams" wrote:
> Forgot to put in t1 table alias for the qryhsplit_formatted view, and clos
ing
> ) for YEAR function:
> SELECT "adjusted_date" =
> CASE
> WHEN t1.[your datetime field] BETWEEN t2.Spring_Forward AND t2.Fall_Back
> THEN DATEADD(hh, 1, t1.[your datetime field])
> ELSE t1.[your datetime field]
> END
> FROM qryhsplit_formatted t1
> INNER JOIN tblDaylightSavingsTime t2
> ON YEAR(t1.[your datetime field]) = t2.Year
>
> --
>
> "Mark Williams" wrote:
>

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 S'. 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 s, 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 s 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.
>

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,
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,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.googlegro ups.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/...502/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/...502/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.googlegr oups.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.googlegr oups.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.
>

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.
>

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
>>
>>
>>
>>
>

Optimize sql statements / find usefull indices

Dear all,

I try to find an easy method (like 'explain' in MySQL) to optimize my SQL statements and create usefull indices. I have created a trace table with the PROFILER and filtered the SQL statements by long DURATION time.

In the next step I used:

set SHOWPLAN_ALL ON;
my_sql_statment

to find the correct indices. Is there any tutorial available describes how to analyse such an output to find correct indices?

Best regards
febel

If you have SQL Profiler trace you could use Database Engine Tuning Advisor. Its wizard, that analyze trace and propose indexes, statictics etc

As tutorial you could use this book http://www.microsoft.com/MSPress/books/8565.aspx

|||Dear Konstantin,

thanks for your answer, but I have only the trace table and no possibility to use Database Engine Tuning Advisor. In addition I want use this in a programm which should set the indices in a automatic way depending on the results of the analysis of the trace table.

Best regards
febel
sql

Optimizations job failure - SQLServer2000

Hi all,
I created a database maintenance plan with backup + transsactionlog backup+optimizations. The first two jobs work fine but the Optimizations job is failing with error:
sqlmaint.exe failed. SQLSTATE 42000, Error: 22029. The step failed.

User is admin on local box and "sysadmin" in db server role. Any suggestions to resolve this?

Thanks
Vinnie...check out this link.

http://support.microsoft.com/default.aspx?scid=kb;en-us;326485

Optimizations job failure

Anyone's help is appreciated in resolving this error:

I created a Database Maintenance plan with backup+transactionlog backup+ optimizations. The first two jobs run fine but optimizations job fails with:

sqlmaint.exe failed [SQLSTATE 42000] ( Error 22029). The step failed.

The user that is running all the jobs is "sysadmin" in database and member of administrator on local machine.

I am trying to run this when the users are using the system.

Any thoughts how I can fix this?

Thanks
VinnieIn the Job History dialog box, check the "show details" box in the upper right corner. If you know why the job failed, you'll have a much better shot at fixing it!

-PatP|||Thanks for the suggestion. The error message I wrote down is from the steps details after checking the box on right hand corner and looking through the steps information why it failed. The step 1 gave that info
sqlmaint.exe failed. SQLSTATE 42000 error 22029. The step failed.

Any other thoughts?

Thanks
Vinnie|||OK What's the second line say?|||This is the second line.

The first line was: The job Failed. The Job was invoked by User HBOCD01\VKaramc. The last step to run was step 1 (Step 1).|||...I think you've posted this subject twice. Check out the link I posted in the
other post.|||Ohhh..you mean this Link (http://support.microsoft.com/default.aspx?scid=kb;en-us;326485)

Why don't you show us the code in one step that didn't fail, and one that did...|||Thanks for the link. Though the resolutions expressed in the link text have only 2 workarounds for my problem, I guess I can work it out.

Vinnie

Optimization Jobs Fails

My SQL Server 2000 database optimization job fails each time it runs. This
job was created from the SQL Server database maintenance plan.
The "Reorganize data and index pages" and "Reorganize pages with the
original amount of free space" are checked parameters.
Error
Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 42000]
[Error 22029]. The step failed.
Please help me resolve the error listed above.
Thanks,
That error message doesn't give us anything to go on, it is only Agent telling us it failed. Specify
a report file for the plan and look in that report file. Or open the maint wiz folder in EM, and
look at the history for a failed execution from there.
My guess is that you have some indexes on views or computed columns and maint wiz doesn't set the
needed SET setting in order to reorg such indexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:9343D047-7572-4A1F-A9BA-0CD9644B6550@.microsoft.com...
> My SQL Server 2000 database optimization job fails each time it runs. This
> job was created from the SQL Server database maintenance plan.
> The "Reorganize data and index pages" and "Reorganize pages with the
> original amount of free space" are checked parameters.
> Error
> Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 42000]
> [Error 22029]. The step failed.
> Please help me resolve the error listed above.
> Thanks,
>

Optimization Jobs Fails

My SQL Server 2000 database optimization job fails each time it runs. This
job was created from the SQL Server database maintenance plan.
The "Reorganize data and index pages" and "Reorganize pages with the
original amount of free space" are checked parameters.
Error
Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 420
00]
[Error 22029]. The step failed.
Please help me resolve the error listed above.
Thanks,That error message doesn't give us anything to go on, it is only Agent telli
ng us it failed. Specify
a report file for the plan and look in that report file. Or open the maint w
iz folder in EM, and
look at the history for a failed execution from there.
My guess is that you have some indexes on views or computed columns and main
t wiz doesn't set the
needed SET setting in order to reorg such indexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:9343D047-7572-4A1F-A9BA-0CD9644B6550@.microsoft.com...
> My SQL Server 2000 database optimization job fails each time it runs. Thi
s
> job was created from the SQL Server database maintenance plan.
> The "Reorganize data and index pages" and "Reorganize pages with the
> original amount of free space" are checked parameters.
> Error
> Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 4
2000]
> [Error 22029]. The step failed.
> Please help me resolve the error listed above.
> Thanks,
>

Optimization Jobs Fails

My SQL Server 2000 database optimization job fails each time it runs. This
job was created from the SQL Server database maintenance plan.
The "Reorganize data and index pages" and "Reorganize pages with the
original amount of free space" are checked parameters.
Error
Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 42000]
[Error 22029]. The step failed.
Please help me resolve the error listed above.
Thanks,That error message doesn't give us anything to go on, it is only Agent telling us it failed. Specify
a report file for the plan and look in that report file. Or open the maint wiz folder in EM, and
look at the history for a failed execution from there.
My guess is that you have some indexes on views or computed columns and maint wiz doesn't set the
needed SET setting in order to reorg such indexes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:9343D047-7572-4A1F-A9BA-0CD9644B6550@.microsoft.com...
> My SQL Server 2000 database optimization job fails each time it runs. This
> job was created from the SQL Server database maintenance plan.
> The "Reorganize data and index pages" and "Reorganize pages with the
> original amount of free space" are checked parameters.
> Error
> Executed as user TEAM\SQL_ADMIN_ACCT. sqlmaint.exe failed. [SQLSTATE 42000]
> [Error 22029]. The step failed.
> Please help me resolve the error listed above.
> Thanks,
>

Optimization and integrity check errors

At an installation of SharePointPortal Server 2003 were created automatically
3 databases.
The jobs for optimizations and integrity check for one of these databases
produce always errors:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver] [SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER'
I changed repeatedly QUOTED_IDENTIFIER, but the errors appeared again.
I used directly the DBCC statements for all tables of the database and I
became no errors.
What can I do?
Thank you in advance for your help
Andreas Marner
Sounds like you have a computed column or an Indexed view in the database.
There is a bug with the MP that does not allow it to handle these very well.
I recommend you create your own scheduled job to issue the backups and
reindexing for at least those db's.
Andrew J. Kelly SQL MVP
"Marner" <marner@.discussions.microsoft.com> wrote in message
news:6F859E50-AE75-4587-95F8-F4697BF4FD6F@.microsoft.com...
> At an installation of SharePointPortal Server 2003 were created
automatically
> 3 databases.
> The jobs for optimizations and integrity check for one of these databases
> produce always errors:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
SQL
> Server Driver] [SQL Server]DBCC failed because the following SET options
have
> incorrect settings: 'QUOTED_IDENTIFIER'
> I changed repeatedly QUOTED_IDENTIFIER, but the errors appeared again.
> I used directly the DBCC statements for all tables of the database and I
> became no errors.
> What can I do?
> Thank you in advance for your help
> Andreas Marner

Optimization and integrity check errors

At an installation of SharePointPortal Server 2003 were created automatically
3 databases.
The jobs for optimizations and integrity check for one of these databases
produce always errors:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver] [SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER'
I changed repeatedly QUOTED_IDENTIFIER, but the errors appeared again.
I used directly the DBCC statements for all tables of the database and I
became no errors.
What can I do?
Thank you in advance for your help
Andreas MarnerSounds like you have a computed column or an Indexed view in the database.
There is a bug with the MP that does not allow it to handle these very well.
I recommend you create your own scheduled job to issue the backups and
reindexing for at least those db's.
--
Andrew J. Kelly SQL MVP
"Marner" <marner@.discussions.microsoft.com> wrote in message
news:6F859E50-AE75-4587-95F8-F4697BF4FD6F@.microsoft.com...
> At an installation of SharePointPortal Server 2003 were created
automatically
> 3 databases.
> The jobs for optimizations and integrity check for one of these databases
> produce always errors:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
SQL
> Server Driver] [SQL Server]DBCC failed because the following SET options
have
> incorrect settings: 'QUOTED_IDENTIFIER'
> I changed repeatedly QUOTED_IDENTIFIER, but the errors appeared again.
> I used directly the DBCC statements for all tables of the database and I
> became no errors.
> What can I do?
> Thank you in advance for your help
> Andreas Marner

Optimise Select Statement

Hi
I have this select statement that I need to optimise:
SELECT Created, Code, tblEvents.Ref
FROM tblCustomers, tblEvents
WHERE tblEvents.description LIKE 'Type ' +
dbo.fcn_GetShortCode(tblCustomer.Code) + '%'
The tblCustomer.Code is a string like 'STAR00000001' the function removes
the padded zeros.
The tblEvents table contains information in a string including the
contracted Customer.Code in this format "Type STAR1 ........"
Any help would be much appreciated.
Thanks
BDon=B4t know how your function works but what about this:
SELECT Created, Code, tblEvents.Ref
FROM tblCustomers, tblEvents
WHERE tblEvents.description LIKE 'Type ' +
LEFT(tblCustomer.Code,CHARINDEX('0',tblCustomer.Code)-1) + '%'
HTH, Jens Suessmeyer.|||"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
news:OsCnbqevFHA.3688@.tk2msftngp13.phx.gbl...
> Hi
> I have this select statement that I need to optimise:
> SELECT Created, Code, tblEvents.Ref
> FROM tblCustomers, tblEvents
> WHERE tblEvents.description LIKE 'Type ' +
> dbo.fcn_GetShortCode(tblCustomer.Code) + '%'
> The tblCustomer.Code is a string like 'STAR00000001' the function removes
> the padded zeros.
No offense, but I think you need to optimise the design, not the query. Why
are you storing padded zeros if they're irrelevant or different from the
data you're actually modeling? Why are these tables quasi-related via a
string that changes?|||Hi Aaron
I would love to change the design but it is a third party product, although
i have requested the change in a future release I need a temporary solution.
Thanks
B
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:#d7CV1evFHA.2292@.TK2MSFTNGP12.phx.gbl...
> "Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
> news:OsCnbqevFHA.3688@.tk2msftngp13.phx.gbl...
removes
> No offense, but I think you need to optimise the design, not the query.
Why
> are you storing padded zeros if they're irrelevant or different from the
> data you're actually modeling? Why are these tables quasi-related via a
> string that changes?
>|||On Tue, 20 Sep 2005 14:51:05 +0100, Ben wrote:

>I have this select statement that I need to optimise:
>SELECT Created, Code, tblEvents.Ref
>FROM tblCustomers, tblEvents
>WHERE tblEvents.description LIKE 'Type ' +
>dbo.fcn_GetShortCode(tblCustomer.Code) + '%'
>The tblCustomer.Code is a string like 'STAR00000001' the function removes
>the padded zeros.
>The tblEvents table contains information in a string including the
>contracted Customer.Code in this format "Type STAR1 ........"
>Any help would be much appreciated.
Hi Ben,
User-defined functions can be slow. If possible, use builtin functions
that achieve the same effect.
SELECT Created, Code, tblEvents.Ref
FROM tblCustomers, tblEvents
WHERE tblEvents.description LIKE
'Type ' + REPLACE(tblCustomer.Code, '0', '') + '%'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Jens
Would this work for codes such as:
STAR00000102?
Thanks
B
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1127225495.861651.299360@.g47g2000cwa.googlegroups.com...
Dont know how your function works but what about this:
SELECT Created, Code, tblEvents.Ref
FROM tblCustomers, tblEvents
WHERE tblEvents.description LIKE 'Type ' +
LEFT(tblCustomer.Code,CHARINDEX('0',tblCustomer.Code)-1) + '%'
HTH, Jens Suessmeyer.|||DECLARE @.String varchar(2000)
SEt @.String = 'STAR00000102'
SELECT LEFT(@.String,CHARINDEX('0',@.String)-1) + '%'
results in "STAR%"|||Hi Jens
Thanks for your post.
The problem is that we have codes stored in tblEvents.description as STAR1,
STAR2, STAR3......STAR102, STAR103
and for example we need to distict "Type STAR103 ...." from "Type STAR1
...."
Thanks
B
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1127294257.162562.55160@.z14g2000cwz.googlegroups.com...
> DECLARE @.String varchar(2000)
> SEt @.String = 'STAR00000102'
> SELECT LEFT(@.String,CHARINDEX('0',@.String)-1) + '%'
> results in "STAR%"
>|||That=B4s not easy, there has to be a delimiter or something where you
can tell that the trailing zeros start, how do you want to differ
perhaps
STARS100 and STARS1 ?|||On Wed, 21 Sep 2005 09:36:47 +0100, Ben wrote:

>Hi Jens
>Would this work for codes such as:
>STAR00000102?
Hi Ben,
I assume that this has to be "shortened" to STAR102?
DECLARE @.a varchar(20)
SET @.a = 'STAR00000102'
SELECT STUFF(@.a, CHARINDEX('0', @.a),
PATINDEX('%[1-9]%', @.a) - CHARINDEX('0', @.a), '')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)