Optimizing SQL Filtering - Conceptual question

I expose a simple example of what I'm trying to figure it out:
table1:
====
field1: PK, indexed
field2: FK, indexed
field3: numeric field, non indexed
field4: date field, non indexed
I want to know if this SQL is slower than the second:
select ... from table
where field3 = value // non indexed field first
and field1 = value // indexed field last
select ... from table
where field1 = value // indexed field first
and field3 = value // non indexed field last
Which is faster? Does it matter which field is before? Or the server
organize the fields automatically?
Is the order of fields in the 'where' part important to the query execution
speed?
If I put indexed fields first in the list, the query is executing faster
than if I put an non indexed field first?
I'm using SQL Server 2000.
Thank you very much
Daniel E. Alvarez
IMS Soluciones Tecnolgicas S.A.
quilate@.kropol.com.ar
Daniel,
The order of fields in the WHERE clause should not affect the order that the
optimizer uses.
Are you seeing that behavior? (I was unclear from your text below.) I
would suggest rerunning your tests several times and take timings from each
test cycle.
Russell Fields
(For a very complex set of joins the optimizer must eventually stop
optimizing and execute. In a case like that, the order of the FROM and
WHERE clause MAY have the side-effect of changing where the optimizer
decides to move on.)
"Daniel Alvarez" <dalvarez@.flashmail.com> wrote in message
news:#NluJkTKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I expose a simple example of what I'm trying to figure it out:
> table1:
> ====
> field1: PK, indexed
> field2: FK, indexed
> field3: numeric field, non indexed
> field4: date field, non indexed
> I want to know if this SQL is slower than the second:
> select ... from table
> where field3 = value // non indexed field first
> and field1 = value // indexed field last
> select ... from table
> where field1 = value // indexed field first
> and field3 = value // non indexed field last
> Which is faster? Does it matter which field is before? Or the server
> organize the fields automatically?
> Is the order of fields in the 'where' part important to the query
execution
> speed?
> If I put indexed fields first in the list, the query is executing faster
> than if I put an non indexed field first?
> I'm using SQL Server 2000.
> Thank you very much
> --
> Daniel E. Alvarez
> IMS Soluciones Tecnolgicas S.A.
> quilate@.kropol.com.ar
>
sql

Optimizing SQL Filtering - Conceptual question

I expose a simple example of what I'm trying to figure it out:
table1:
====
field1: PK, indexed
field2: FK, indexed
field3: numeric field, non indexed
field4: date field, non indexed
I want to know if this SQL is slower than the second:
select ... from table
where field3 = value // non indexed field first
and field1 = value // indexed field last
select ... from table
where field1 = value // indexed field first
and field3 = value // non indexed field last
Which is faster? Does it matter which field is before? Or the server
organize the fields automatically?
Is the order of fields in the 'where' part important to the query execution
speed?
If I put indexed fields first in the list, the query is executing faster
than if I put an non indexed field first?
I'm using SQL Server 2000.
Thank you very much
Daniel E. Alvarez
IMS Soluciones Tecnolgicas S.A.
quilate@.kropol.com.arDaniel,
The order of fields in the WHERE clause should not affect the order that the
optimizer uses.
Are you seeing that behavior? (I was unclear from your text below.) I
would suggest rerunning your tests several times and take timings from each
test cycle.
Russell Fields
(For a very complex set of joins the optimizer must eventually stop
optimizing and execute. In a case like that, the order of the FROM and
WHERE clause MAY have the side-effect of changing where the optimizer
decides to move on.)
"Daniel Alvarez" <dalvarez@.flashmail.com> wrote in message
news:#NluJkTKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I expose a simple example of what I'm trying to figure it out:
> table1:
> ====
> field1: PK, indexed
> field2: FK, indexed
> field3: numeric field, non indexed
> field4: date field, non indexed
> I want to know if this SQL is slower than the second:
> select ... from table
> where field3 = value // non indexed field first
> and field1 = value // indexed field last
> select ... from table
> where field1 = value // indexed field first
> and field3 = value // non indexed field last
> Which is faster? Does it matter which field is before? Or the server
> organize the fields automatically?
> Is the order of fields in the 'where' part important to the query
execution
> speed?
> If I put indexed fields first in the list, the query is executing faster
> than if I put an non indexed field first?
> I'm using SQL Server 2000.
> Thank you very much
> --
> Daniel E. Alvarez
> IMS Soluciones Tecnolgicas S.A.
> quilate@.kropol.com.ar
>

Optimizing SQL Filtering - Conceptual question

I expose a simple example of what I'm trying to figure it out:
table1:
==== field1: PK, indexed
field2: FK, indexed
field3: numeric field, non indexed
field4: date field, non indexed
I want to know if this SQL is slower than the second:
select ... from table
where field3 = value // non indexed field first
and field1 = value // indexed field last
select ... from table
where field1 = value // indexed field first
and field3 = value // non indexed field last
Which is faster? Does it matter which field is before? Or the server
organize the fields automatically?
Is the order of fields in the 'where' part important to the query execution
speed?
If I put indexed fields first in the list, the query is executing faster
than if I put an non indexed field first?
I'm using SQL Server 2000.
Thank you very much
--
Daniel E. Alvarez
IMS Soluciones Tecnológicas S.A.
quilate@.kropol.com.arDaniel,
The order of fields in the WHERE clause should not affect the order that the
optimizer uses.
Are you seeing that behavior? (I was unclear from your text below.) I
would suggest rerunning your tests several times and take timings from each
test cycle.
Russell Fields
(For a very complex set of joins the optimizer must eventually stop
optimizing and execute. In a case like that, the order of the FROM and
WHERE clause MAY have the side-effect of changing where the optimizer
decides to move on.)
"Daniel Alvarez" <dalvarez@.flashmail.com> wrote in message
news:#NluJkTKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I expose a simple example of what I'm trying to figure it out:
> table1:
> ====> field1: PK, indexed
> field2: FK, indexed
> field3: numeric field, non indexed
> field4: date field, non indexed
> I want to know if this SQL is slower than the second:
> select ... from table
> where field3 = value // non indexed field first
> and field1 = value // indexed field last
> select ... from table
> where field1 = value // indexed field first
> and field3 = value // non indexed field last
> Which is faster? Does it matter which field is before? Or the server
> organize the fields automatically?
> Is the order of fields in the 'where' part important to the query
execution
> speed?
> If I put indexed fields first in the list, the query is executing faster
> than if I put an non indexed field first?
> I'm using SQL Server 2000.
> Thank you very much
> --
> Daniel E. Alvarez
> IMS Soluciones Tecnológicas S.A.
> quilate@.kropol.com.ar
>

Optimizing Clustered Index Scan

I am trying to tune a query that has as its execution output as a Clustered
Index Scan. There is a Clustered Index on field [dt], which is a datetim
e
field. The query is structured as such:
IF @.date < '1/1/1901'
--...then make it NULL, so that it works in the query
SET @.date = NULL
SELECT
t1.dt_UID
FROM Table1 AS t1
WHERE ( ( @.date IS NULL )
OR ( t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date) ) )
When I remove the ( @.date IS NULL ) portion from the WHERE clause I get a
Clustered Index Seek. Is there any way to rework the WHERE clause to where i
t
would always be a seek?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200604/1Why are you setting @.date to null if @.date is < '1/1/1901'?
MG
"cbrichards" wrote:

> I am trying to tune a query that has as its execution output as a Clustere
d
> Index Scan. There is a Clustered Index on field [dt], which is a datet
ime
> field. The query is structured as such:
>
> IF @.date < '1/1/1901'
> --...then make it NULL, so that it works in the query
> SET @.date = NULL
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
> WHERE ( ( @.date IS NULL )
> OR ( t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date) ) )
> When I remove the ( @.date IS NULL ) portion from the WHERE clause I get a
> Clustered Index Seek. Is there any way to rework the WHERE clause to where
it
> would always be a seek?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200604/1
>|||Try,
IF @.date < '1/1/1901'
--...then make it NULL, so that it works in the query
SET @.date = NULL
SELECT
t1.dt_UID
FROM
Table1 AS t1
WHERE
t1.dt BETWEEN isnull(@.date, '17530101') AND isnull(DateAdd(d, 1, @.date),
'9999-12-30T23:59:59')
go
Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
AMB
"cbrichards" wrote:

> I am trying to tune a query that has as its execution output as a Clustere
d
> Index Scan. There is a Clustered Index on field [dt], which is a datet
ime
> field. The query is structured as such:
>
> IF @.date < '1/1/1901'
> --...then make it NULL, so that it works in the query
> SET @.date = NULL
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
> WHERE ( ( @.date IS NULL )
> OR ( t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date) ) )
> When I remove the ( @.date IS NULL ) portion from the WHERE clause I get a
> Clustered Index Seek. Is there any way to rework the WHERE clause to where
it
> would always be a seek?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200604/1
>|||"cbrichards" <u3288@.uwe> wrote in message news:5f760d81fa01c@.uwe...
>I am trying to tune a query that has as its execution output as a Clustered
> Index Scan. There is a Clustered Index on field [dt], which is a datet
ime
> field. The query is structured as such:
>
> IF @.date < '1/1/1901'
> --...then make it NULL, so that it works in the query
> SET @.date = NULL
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
> WHERE ( ( @.date IS NULL )
> OR ( t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date) ) )
> When I remove the ( @.date IS NULL ) portion from the WHERE clause I get a
> Clustered Index Seek. Is there any way to rework the WHERE clause to where
> it
> would always be a seek?
>
This is a classic case of pseudo-dynamic SQL. THis is really two different
queries crammed into one. With this query you must do a scan because the
same plan is used whether or not @.date is null. If it is, obviously only a
clustered index scan will do.
IF @.date < '1/1/1901'
BEGIN
SELECT
t1.dt_UID
FROM Table1 AS t1
END
ELSE
BEGIN
SELECT
t1.dt_UID
FROM Table1 AS t1
WHERE t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date)
END
david|||David,
Thanks for your response. I ran this in test and (true enough!) the same pla
n
is used whether or not @.date is null. At this point, I am trying to
understand why the same plan is used. Could you explain further, please,
specifically why the same plan is used?
David Browne wrote:
>[quoted text clipped - 14 lines]
>This is a classic case of pseudo-dynamic SQL. THis is really two different
>queries crammed into one. With this query you must do a scan because the
>same plan is used whether or not @.date is null. If it is, obviously only a
>clustered index scan will do.
>IF @.date < '1/1/1901'
>BEGIN
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
>END
>ELSE
>BEGIN
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
> WHERE t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date)
>END
>david
Message posted via http://www.droptable.com|||"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5f9b39b4a6681@.uwe...
> David,
> Thanks for your response. I ran this in test and (true enough!) the same
> plan
> is used whether or not @.date is null. At this point, I am trying to
> understand why the same plan is used. Could you explain further, please,
> specifically why the same plan is used?
>
Each query gets only one plan. The values you happen to bind to the
parameters don't change that. So whatever the plan is for the query, it has
to work for all possible values of the parameters.
Read:
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/pr...005/recomp.mspx
David|||Thanks Alejandro. That appears to resolve the scan regardless of the value o
f
@.date. Much appreciated. Thanks for the link too.
Alejandro Mesa wrote:[vbcol=seagreen]
>Try,
>IF @.date < '1/1/1901'
> --...then make it NULL, so that it works in the query
> SET @.date = NULL
>SELECT
> t1.dt_UID
>FROM
> Table1 AS t1
>WHERE
> t1.dt BETWEEN isnull(@.date, '17530101') AND isnull(DateAdd(d, 1, @.date),
>'9999-12-30T23:59:59')
>go
>Dynamic Search Conditions in T-SQL
>http://www.sommarskog.se/dyn-search.html
>AMB
>
>[quoted text clipped - 13 lines]
Message posted via http://www.droptable.com|||David,
I read through the article you provided as a link. I understand better what
you mean by "each query gets only one plan" and "parameters do not change
that."
I am trying to internalize this knowledge and understand it within the
context of my attached statement, which contains the OR condition. As you
originally mentioned, I really have "two different queries crammed into one.
"
As I originally noted, when I remove the ( @.date IS NULL ) portion from the
WHERE clause I get a Clustered Index Seek. Does this mean, that when the pla
n
is created, that in order for the optimizer to cache only one plan (in this
case, using my pseudo dynamic SQL), the optimizer, in essence, will go with
a
plan that will satisfy both conditions, and the plan that satisfies both
conditions is an Index Scan? Is my understanding correct?
David Browne wrote:
>Each query gets only one plan. The values you happen to bind to the
>parameters don't change that. So whatever the plan is for the query, it ha
s
>to work for all possible values of the parameters.
>Read:
>Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 200
5
>http://www.microsoft.com/technet/pr...005/recomp.mspx
>David
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5f9e9495ccc86@.uwe...
> David,
> I read through the article you provided as a link. I understand better
> what
> you mean by "each query gets only one plan" and "parameters do not change
> that."
> I am trying to internalize this knowledge and understand it within the
> context of my attached statement, which contains the OR condition. As you
> originally mentioned, I really have "two different queries crammed into
> one."
> As I originally noted, when I remove the ( @.date IS NULL ) portion from
> the
> WHERE clause I get a Clustered Index Seek. Does this mean, that when the
> plan
> is created, that in order for the optimizer to cache only one plan (in
> this
> case, using my pseudo dynamic SQL), the optimizer, in essence, will go
> with a
> plan that will satisfy both conditions, and the plan that satisfies both
> conditions is an Index Scan? Is my understanding correct?
>
Yes. That is it, exactly.
David|||you would have a LOT better luck if you added anohter variable, and did
the dateadd before the select statement.
The way you wrote it, the system has to calculate the dateadd for each
and every row, and it might not be obvious to the engine how simple
your querty could be.
set @.date2 =DateAdd(d, 1, @.date)
SELECT
t1.dt_UID
FROM Table1 AS t1
WHERE ( t1.dt BETWEEN @.date AND @.date2)
OR ( @.date IS NULL )
sounds weird, but sometimes moving the null part around encourages or
discourages the use of hte index. Depends on how that particular engine
parses - you want it to parse and sue the index first.
Worst case you can use a hint to force the engine to use the index.

Optimizing Clustered Index Scan

I am trying to tune a query that has as its execution output as a Clustered
Index Scan. There is a Clustered Index on field [dt], which is a datetime
field. The query is structured as such:
IF @.date < '1/1/1901'
--...then make it NULL, so that it works in the query
SET @.date = NULL
SELECT
t1.dt_UID
FROM Table1 AS t1
WHERE ( ( @.date IS NULL )
OR ( t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date) ) )
When I remove the ( @.date IS NULL ) portion from the WHERE clause I get a
Clustered Index Seek. Is there any way to rework the WHERE clause to where it
would always be a seek?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1Why are you setting @.date to null if @.date is < '1/1/1901'?
--
MG
"cbrichards" wrote:
> I am trying to tune a query that has as its execution output as a Clustered
> Index Scan. There is a Clustered Index on field [dt], which is a datetime
> field. The query is structured as such:
>
> IF @.date < '1/1/1901'
> --...then make it NULL, so that it works in the query
> SET @.date = NULL
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
> WHERE ( ( @.date IS NULL )
> OR ( t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date) ) )
> When I remove the ( @.date IS NULL ) portion from the WHERE clause I get a
> Clustered Index Seek. Is there any way to rework the WHERE clause to where it
> would always be a seek?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1
>|||Try,
IF @.date < '1/1/1901'
--...then make it NULL, so that it works in the query
SET @.date = NULL
SELECT
t1.dt_UID
FROM
Table1 AS t1
WHERE
t1.dt BETWEEN isnull(@.date, '17530101') AND isnull(DateAdd(d, 1, @.date),
'9999-12-30T23:59:59')
go
Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
AMB
"cbrichards" wrote:
> I am trying to tune a query that has as its execution output as a Clustered
> Index Scan. There is a Clustered Index on field [dt], which is a datetime
> field. The query is structured as such:
>
> IF @.date < '1/1/1901'
> --...then make it NULL, so that it works in the query
> SET @.date = NULL
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
> WHERE ( ( @.date IS NULL )
> OR ( t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date) ) )
> When I remove the ( @.date IS NULL ) portion from the WHERE clause I get a
> Clustered Index Seek. Is there any way to rework the WHERE clause to where it
> would always be a seek?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1
>|||"cbrichards" <u3288@.uwe> wrote in message news:5f760d81fa01c@.uwe...
>I am trying to tune a query that has as its execution output as a Clustered
> Index Scan. There is a Clustered Index on field [dt], which is a datetime
> field. The query is structured as such:
>
> IF @.date < '1/1/1901'
> --...then make it NULL, so that it works in the query
> SET @.date = NULL
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
> WHERE ( ( @.date IS NULL )
> OR ( t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date) ) )
> When I remove the ( @.date IS NULL ) portion from the WHERE clause I get a
> Clustered Index Seek. Is there any way to rework the WHERE clause to where
> it
> would always be a seek?
>
This is a classic case of pseudo-dynamic SQL. THis is really two different
queries crammed into one. With this query you must do a scan because the
same plan is used whether or not @.date is null. If it is, obviously only a
clustered index scan will do.
IF @.date < '1/1/1901'
BEGIN
SELECT
t1.dt_UID
FROM Table1 AS t1
END
ELSE
BEGIN
SELECT
t1.dt_UID
FROM Table1 AS t1
WHERE t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date)
END
david|||David,
Thanks for your response. I ran this in test and (true enough!) the same plan
is used whether or not @.date is null. At this point, I am trying to
understand why the same plan is used. Could you explain further, please,
specifically why the same plan is used?
David Browne wrote:
>>I am trying to tune a query that has as its execution output as a Clustered
>> Index Scan. There is a Clustered Index on field [dt], which is a datetime
>[quoted text clipped - 14 lines]
>> it
>> would always be a seek?
>This is a classic case of pseudo-dynamic SQL. THis is really two different
>queries crammed into one. With this query you must do a scan because the
>same plan is used whether or not @.date is null. If it is, obviously only a
>clustered index scan will do.
>IF @.date < '1/1/1901'
>BEGIN
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
>END
>ELSE
>BEGIN
> SELECT
> t1.dt_UID
> FROM Table1 AS t1
> WHERE t1.dt BETWEEN @.date AND DateAdd(d, 1, @.date)
>END
>david
--
Message posted via http://www.sqlmonster.com|||"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5f9b39b4a6681@.uwe...
> David,
> Thanks for your response. I ran this in test and (true enough!) the same
> plan
> is used whether or not @.date is null. At this point, I am trying to
> understand why the same plan is used. Could you explain further, please,
> specifically why the same plan is used?
>
Each query gets only one plan. The values you happen to bind to the
parameters don't change that. So whatever the plan is for the query, it has
to work for all possible values of the parameters.
Read:
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
David|||Thanks Alejandro. That appears to resolve the scan regardless of the value of
@.date. Much appreciated. Thanks for the link too.
Alejandro Mesa wrote:
>Try,
>IF @.date < '1/1/1901'
> --...then make it NULL, so that it works in the query
> SET @.date = NULL
>SELECT
> t1.dt_UID
>FROM
> Table1 AS t1
>WHERE
> t1.dt BETWEEN isnull(@.date, '17530101') AND isnull(DateAdd(d, 1, @.date),
>'9999-12-30T23:59:59')
>go
>Dynamic Search Conditions in T-SQL
>http://www.sommarskog.se/dyn-search.html
>AMB
>> I am trying to tune a query that has as its execution output as a Clustered
>> Index Scan. There is a Clustered Index on field [dt], which is a datetime
>[quoted text clipped - 13 lines]
>> Clustered Index Seek. Is there any way to rework the WHERE clause to where it
>> would always be a seek?
--
Message posted via http://www.sqlmonster.com|||David,
I read through the article you provided as a link. I understand better what
you mean by "each query gets only one plan" and "parameters do not change
that."
I am trying to internalize this knowledge and understand it within the
context of my attached statement, which contains the OR condition. As you
originally mentioned, I really have "two different queries crammed into one."
As I originally noted, when I remove the ( @.date IS NULL ) portion from the
WHERE clause I get a Clustered Index Seek. Does this mean, that when the plan
is created, that in order for the optimizer to cache only one plan (in this
case, using my pseudo dynamic SQL), the optimizer, in essence, will go with a
plan that will satisfy both conditions, and the plan that satisfies both
conditions is an Index Scan? Is my understanding correct?
David Browne wrote:
>> David,
>> Thanks for your response. I ran this in test and (true enough!) the same
>> plan
>> is used whether or not @.date is null. At this point, I am trying to
>> understand why the same plan is used. Could you explain further, please,
>> specifically why the same plan is used?
>Each query gets only one plan. The values you happen to bind to the
>parameters don't change that. So whatever the plan is for the query, it has
>to work for all possible values of the parameters.
>Read:
>Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
>http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>David
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5f9e9495ccc86@.uwe...
> David,
> I read through the article you provided as a link. I understand better
> what
> you mean by "each query gets only one plan" and "parameters do not change
> that."
> I am trying to internalize this knowledge and understand it within the
> context of my attached statement, which contains the OR condition. As you
> originally mentioned, I really have "two different queries crammed into
> one."
> As I originally noted, when I remove the ( @.date IS NULL ) portion from
> the
> WHERE clause I get a Clustered Index Seek. Does this mean, that when the
> plan
> is created, that in order for the optimizer to cache only one plan (in
> this
> case, using my pseudo dynamic SQL), the optimizer, in essence, will go
> with a
> plan that will satisfy both conditions, and the plan that satisfies both
> conditions is an Index Scan? Is my understanding correct?
>
Yes. That is it, exactly.
David|||you would have a LOT better luck if you added anohter variable, and did
the dateadd before the select statement.
The way you wrote it, the system has to calculate the dateadd for each
and every row, and it might not be obvious to the engine how simple
your querty could be.
set @.date2 =DateAdd(d, 1, @.date)
SELECT
t1.dt_UID
FROM Table1 AS t1
WHERE ( t1.dt BETWEEN @.date AND @.date2)
OR ( @.date IS NULL )
sounds weird, but sometimes moving the null part around encourages or
discourages the use of hte index. Depends on how that particular engine
parses - you want it to parse and sue the index first.
Worst case you can use a hint to force the engine to use the index.

Optimizing a JOIN

I have two tables.

One has approx 90,000 rows with a field .. let's call in BigInt (and it
is defined as a bigint data type).

I have a reference table, with approx 10,000,000 rows. In this
reference table, I have starting_bigint and ending_bigint fields. I
want to pull out all of the reference data from the reference table for
all 90,000 rows in the transaction table where the BigInt from the
transaction table is between the starting_bigint and ending_bigint in
the reference table.

I have the join working now, but it is not as optimized as I would
like. It appears no matter what I do, the query does a full table scan
on the 10,000,000 rows in the reference table.

Sample code

SELECT ref.*, tran.bigint
from transactiontable tran
INNER JOIN referencetable ref on tran.bigint between
ref.starting_bigint and ending_bigint

Yes, all 3 of the fields are indexed. I even have a composite index on
the reference table with the starting_bigint and ending_bigint fields
selected as the composite.

Any help would be appreciated.

Robert H. Kershberg
IT Director
Tax Credit Company
RKershberg@.taxcc.com or RKershberg@.pobox.com or RKershberg@.gmail.comIf the starting and ending bigints ranges are not overlapping, then I
would classify this as the "zipcode problem".

If this is the case, you could try the following approach:

SELECT R1.*,T1.bigint
FROM transactiontable T1
CROSS JOIN referencetable R1
WHERE R1.starting_bigint = (
SELECT MAX(starting_bigint)
FROM referencetable R2
WHERE R2.starting_bigint <= T1.bigint
)
AND R1.ending_bigint >= T1.bigint

Hope this helps,
Gert-Jan

"rkershberg@.gmail.com" wrote:
> I have two tables.
> One has approx 90,000 rows with a field .. let's call in BigInt (and it
> is defined as a bigint data type).
> I have a reference table, with approx 10,000,000 rows. In this
> reference table, I have starting_bigint and ending_bigint fields. I
> want to pull out all of the reference data from the reference table for
> all 90,000 rows in the transaction table where the BigInt from the
> transaction table is between the starting_bigint and ending_bigint in
> the reference table.
> I have the join working now, but it is not as optimized as I would
> like. It appears no matter what I do, the query does a full table scan
> on the 10,000,000 rows in the reference table.
> Sample code
> SELECT ref.*, tran.bigint
> from transactiontable tran
> INNER JOIN referencetable ref on tran.bigint between
> ref.starting_bigint and ending_bigint
> Yes, all 3 of the fields are indexed. I even have a composite index on
> the reference table with the starting_bigint and ending_bigint fields
> selected as the composite.
> Any help would be appreciated.
> Robert H. Kershberg
> IT Director
> Tax Credit Company
> RKershberg@.taxcc.com or RKershberg@.pobox.com or RKershberg@.gmail.com|||try skipping the "between." something like
SELECT ref.*, tran.bigint
from transactiontable tran
INNER JOIN referencetable ref
on tran.bigint >= ref.starting_bigint
and tran.bigint <=ref.ending_bigint

try creating a composite index on ref, containing starting_bigint plus
ending_bigint
if that doesn't work, try zapping your existing indexes on ref.

thinking outside the box, try creating staging tables, or ghost tables.
The ghost tables are for a selected period taht you are currently
working on. You copy pieces of your trans table off to a temp table,
and do your lookups against it. you eat the one copy, you eat the
creation of the indexon the temp table, but you get the benefits of not
having to do complex searches against a 100,000,000 row table. this is
RARELY needed, but I've done it on rare occasion.|||Thank you for your time. I had tried a cross join, but not quite the
same way. It'll be interesting to see if there is a significant
difference. I appreciate your intelligent input.

Rob|||Thanks again .. this logic improved performance 3 to 4 fold (I had
already cut a 2 min 30 sec query to 15 seconds .. your logic
implemented on top of mine cut that down to 3 seconds .. this on 88,000
row transacation table running against a 9.6 million row reference
table .. not zip codes, but the right idea <g>).

Thank YOU very much!!!

Rob|||3 seconds is a long time.

how do we make it faster??

i am not a huge fan of clustered indexes. However for a reference
table where you will ALWAYS be looking up data utilizing a specific
column, and rarely inserting into the middle, I'd sure consider it.

For grins, try creating a clustered, composite index on starting_bigint
+ ending_bigint.

For grins after that, consider a clustered index on trans.bigint.
warning: clustered indexes on trans tables are usually not a great idea
IMO, unless the clustered index is based on a timestamp or an identity
column.

-doug

Optimize SQL

Hello All!
Is there a way to write this SQL without a UNION, because where i have to
set one field to null to match the number of field of the second sql :(
SELECT ('Caixa' + ' - ' + Nome) AS Nome, Caixa.CaixaID AS ID, NULL AS
AgenciaID, 'Caixa' AS Tipo
FROM Caixa
UNION
SELECT ('Banco' + ' - ' + Banco.Nome) AS Nome, Banco.ID AS ID,
Agencia.ID AS AgenciaID, 'Banco' AS Tipo
FROM Agencia JOIN
BANCO ON Banco.ID = Agencia.ID
Thank you all!!!
Bruno NThere probably are other solutions but if you want the same columns in
there then you will presumably have to populate the AgenciaID column
with NULLs anyway. I'm not sure what it is you want to do differently.
You should consider using UNION ALL instead of UNION unless it's
required to eliminate duplicates. UNION ALL will typically perform
better.
If you need more help, please post some more info as described here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Nope, unless you want to return 2 separate result sets. Then you can do
without the Tipo column as well.
There's nothing wrong really with setting one column to NULL if it doesn't
exist in that part of the unioned set.
Jacco Schalkwijk
SQL Server MVP
"Bruno N" <nylren@.hotmail.com> wrote in message
news:eHHIkgSNFHA.3788@.tk2msftngp13.phx.gbl...
> Hello All!
> Is there a way to write this SQL without a UNION, because where i have to
> set one field to null to match the number of field of the second sql :(
> SELECT ('Caixa' + ' - ' + Nome) AS Nome, Caixa.CaixaID AS ID, NULL AS
> AgenciaID, 'Caixa' AS Tipo
> FROM Caixa
> UNION
> SELECT ('Banco' + ' - ' + Banco.Nome) AS Nome, Banco.ID AS ID,
> Agencia.ID AS AgenciaID, 'Banco' AS Tipo
> FROM Agencia JOIN
> BANCO ON Banco.ID = Agencia.ID
> Thank you all!!!
> Bruno N
>|||Thanks guys!!
"Bruno N" <nylren@.hotmail.com> escreveu na mensagem
news:eHHIkgSNFHA.3788@.tk2msftngp13.phx.gbl...
> Hello All!
> Is there a way to write this SQL without a UNION, because where i have to
> set one field to null to match the number of field of the second sql :(
> SELECT ('Caixa' + ' - ' + Nome) AS Nome, Caixa.CaixaID AS ID, NULL AS
> AgenciaID, 'Caixa' AS Tipo
> FROM Caixa
> UNION
> SELECT ('Banco' + ' - ' + Banco.Nome) AS Nome, Banco.ID AS ID,
> Agencia.ID AS AgenciaID, 'Banco' AS Tipo
> FROM Agencia JOIN
> BANCO ON Banco.ID = Agencia.ID
> Thank you all!!!
> Bruno N
>

Optimize Query

I need some help with optimizing this qry... The table tbl2, in the field dfrsloanno, has values such as 100024PRP and 10024.. ie some records are with the letters PRP at the end, and some records are without. Hence when I join ths tbl2 with tbl1 table, I have to join on both the occureences, ie look for the recs with the PRP and the ones without it too.

My qry howver, runs for a long time, ie 22 seconds, when I use the one below.

select dfrsloanno, a.* from tbl1 a
left join tbl2 comm
on (convert(varchar,a.acct#))=SubString(COMM.[dfrsloanno], 0, PatIndex('%[A-Z,a-z]%', COMM.[dfrsloanno]))

or ltrim(rtrim(COMM.[dfrsloanno])) =ltrim(rtrim(convert(varchar,a.acct#)))

However, when I take out the or criteria, it runs for only 2 seconds .. The reason I have the or crtieria is to meet both the scenarios ie 10024PRP and 10024... Hence I need the OR criteria. But I foudn that the culprit of the slowness in the qry is the OR condition...

Pl advise, how can I optimze this, and keep the OR condition too?

When you wrap various functions around column values, you negate the possibility of using any indexing on that column and the entire table must be scanned.

Is it possible to add a computed column (that could be indexed) that would be without the 'PRP'?

|||This worked very well. I did jsut like u suggested. On my tbl2, I put case statements, to tae into account both the PRP and without PRP. Then this qry returned results in 3 secs only.That is what I needed. Thanks

Optimization gurus: Help with varchar vs. text fields decision

Hi, I'm trying to improve performance for a db that stores messages. The message is currently stored in an ntext field, but when I look at all the records from the past 3 months, I found that 88% are less than 1000 characters, and 97% are less than 3000 characters.

I don't want to actually limit the message size, but it seems like I might get much better performance using a varchar(3000) field to hold most of the messages, and a separate text field just used for those 3% that really are long. Is this a good idea? If so, is it better to put the Message and LongMessage fields in the same table; or, have a separate table to hold the long messages? If it is in a separate table, it would need to be left joined with the message table each time messages are retrieved.

Also -- I am getting about 700 new messages daily, and right now have over 150,000 messages stored. The vast majority of activity involves new messages. Is this a good situation to look at using horizontal partitioning?

Thanks for any help, I don't really have anyone to discuss this with and it is really helpful to get some other views!!

Are you able to upgrade to SQL2005? VARCHAR(max) would be a simple solution to your problem.

|||

Yay, I'm already on SQL server 2005, so I could use varchar(max) -- now that I've heard of it! Are there performance issues to be aware of with max? Any drawback to a design where the row size will vary wildly from row to row??

|||

Celestine:

when I look at all the records from the past 3 months, I found that 88% are less than 1000 characters, and 97% are less than 3000 characters.

I calculated wrong -- it is an ntext field, so each char is two bytes, not one byte. Meaning 97% of the messages are actually less than 1500 characters, not 3000. All the messages are in English, so I'm not going to continue using ntext or nvarchar.

|||

Varchar(max) allows rows to span physical blocks, hence no row length restriction. Why not look it up on Books-On_line BOL?

|||

I understand that there is no length restriction, I looked it up right away; thanks for making me aware of the max option. What I am asking about is whether there are performance implications to consider with using varchar(max), when you are hoping to get multiple records to fit on a data page.

|||

With varchar(max) most of the records will be fetched with a single read whereas for ntext, two reads will be required for every record irrespective of its size.

OPENXML Invalid column name error on update

I have the stored procedure below which complies and works OK as long
as the field RxID is NOT set as an identity column. When the field is set as
an identity column then I get the error Invalid column name 'RxID'.
It's very easy to reproduce this issue, is this a bug, is there any work
around?
CREATE Procedure UpdateRxDetail
@.doc text
AS
DECLARE @.hdoc int
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.doc
BEGIN TRANSACTION
UPDATE rxDetail
SET
rxDetail.FormattedErrorString = XMLTABLE.FormattedErrorString,
rxDetail.StatusID = XMLTABLE.StatusID
FROM OPENXML(@.hdoc, '//errInfo')
WITH rxDetail XMLTABLE
WHERE rxDetail.RxID = XMLTABLE.RxID
COMMIT
EXEC sp_xml_removedocument @.hdoc
RETURN
GOPlease post the XML you want to parse.
ML
http://milambda.blogspot.com/|||If you replace your UPDATE statement with this
SELECT XMLTABLE.*
FROM OPENXML(@.hdoc, '//errInfo')
WITH rxDetail XMLTABLE
you'll see that RxID only appears when it's
not an identity. AFAIK, there's no way round this.
You'll have to change "WITH rxDetail"
to explicitly list the XML<->column mappings
yourself. Something like this
UPDATE rxDetail
SET
rxDetail.FormattedErrorString = XMLTABLE.FormattedErrorString,
rxDetail.StatusID = XMLTABLE.StatusID
FROM OPENXML(@.hdoc, '//errInfo')
WITH (FormattedErrorString varchar(10),
StatusID int,
RxID int) XMLTABLE
WHERE rxDetail.RxID = XMLTABLE.RxID|||Thanks Mark - that did the trick!
"markc600@.hotmail.com" wrote:

> If you replace your UPDATE statement with this
> SELECT XMLTABLE.*
> FROM OPENXML(@.hdoc, '//errInfo')
> WITH rxDetail XMLTABLE
> you'll see that RxID only appears when it's
> not an identity. AFAIK, there's no way round this.
> You'll have to change "WITH rxDetail"
> to explicitly list the XML<->column mappings
> yourself. Something like this
> UPDATE rxDetail
> SET
> rxDetail.FormattedErrorString = XMLTABLE.FormattedErrorString,
> rxDetail.StatusID = XMLTABLE.StatusID
> FROM OPENXML(@.hdoc, '//errInfo')
> WITH (FormattedErrorString varchar(10),
> StatusID int,
> RxID int) XMLTABLE
> WHERE rxDetail.RxID = XMLTABLE.RxID
>

OPENXML INSERT/UPDATE and NTEXT

Hi,
I'm having problems inserting/updating a NTEXT field using OPENXML.
The field always gets a blank value with any parameters it receives.
Here's the (simplified) query:
---
declare @.doc int
-- Actually, it's a parameter
declare @.xml nvarchar(4000);
set @.xml = N'
<Article>
<Id>be60839f-cc33-4a9f-af91-e3bbcb7617ac</Id>
<Content>yada yada yada</Content>
</Article>'
EXEC sp_xml_preparedocument @.doc OUTPUT, @.xml
UPDATE Article
SET Content = new.Content
FROM OPENXML(@.doc, 'Article', 3) WITH Article new
WHERE Article.Id = new.Id
IF @.@.rowcount = 0
INSERT INTO Article
SELECT *
FROM OPENXML(@.doc, 'Article', 3) WITH Article
EXEC sp_xml_removedocument @.doc
---
Oddly, "SELECT * FROM OPENXML(@.doc, 'Article', 3) WITH Article" shows the
value.
Any ideas?
DiegoI think you need to revise your OPENXML syntax a bit. Try the following
instead of what you have:
UPDATE Article
SET Content = new.Content
FROM OPENXML(@.doc, 'Article', 3)
WITH (
id uniqueidentifier 'Id',
content nvarchar(400) 'Content'
) new
WHERE Article.Id = new.Id
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Diego Mijelshon" <no@.thanks.com> wrote in message
news:ei8UgeEGFHA.1188@.tk2msftngp13.phx.gbl...
> Hi,
> I'm having problems inserting/updating a NTEXT field using OPENXML.
> The field always gets a blank value with any parameters it receives.
> Here's the (simplified) query:
> ---
> declare @.doc int
> -- Actually, it's a parameter
> declare @.xml nvarchar(4000);
> set @.xml = N'
> <Article>
> <Id>be60839f-cc33-4a9f-af91-e3bbcb7617ac</Id>
> <Content>yada yada yada</Content>
> </Article>'
> EXEC sp_xml_preparedocument @.doc OUTPUT, @.xml
> UPDATE Article
> SET Content = new.Content
> FROM OPENXML(@.doc, 'Article', 3) WITH Article new
> WHERE Article.Id = new.Id
> IF @.@.rowcount = 0
> INSERT INTO Article
> SELECT *
> FROM OPENXML(@.doc, 'Article', 3) WITH Article
> EXEC sp_xml_removedocument @.doc
> ---
> Oddly, "SELECT * FROM OPENXML(@.doc, 'Article', 3) WITH Article" shows the
> value.
> Any ideas?
> Diego
>|||Adam,
Thanks for your answer, but that didn't fix it.
Besides, the beauty in the "WITH tablename" clause, combined with FOR XML
AUTO, is that I get "free" O/R-M using XML Serializing.
I'll share my solution. It still looks like a bug to me...
---
SELECT *
INTO ##tmp
FROM OPENXML(@.doc, 'Article', 3) WITH Article
UPDATE Article
SET Content = new.Content
FROM ##tmp new
WHERE Article.Id = new.Id
IF @.@.rowcount = 0
INSERT INTO Article
SELECT *
FROM ##tmp
DROP TABLE ##tmp
---
As you can see, inserting in a temporal table does the trick.
Diego
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OxCpmzEGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> I think you need to revise your OPENXML syntax a bit. Try the following
> instead of what you have:
>
> UPDATE Article
> SET Content = new.Content
> FROM OPENXML(@.doc, 'Article', 3)
> WITH (
> id uniqueidentifier 'Id',
> content nvarchar(400) 'Content'
> ) new
> WHERE Article.Id = new.Id
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Diego Mijelshon" <no@.thanks.com> wrote in message
> news:ei8UgeEGFHA.1188@.tk2msftngp13.phx.gbl...
the
>|||"Diego Mijelshon" <no@.thanks.com> wrote in message
news:um$u8aIGFHA.548@.TK2MSFTNGP14.phx.gbl...
> DROP TABLE ##tmp
> ---
> As you can see, inserting in a temporal table does the trick.
I'm glad you found something that works. FYI, you should probably use a
local temporary table (single #) instead of a global one (##) unless you
need access to this same temp table from other processes...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uWtvvjIGFHA.560@.TK2MSFTNGP15.phx.gbl...
> "Diego Mijelshon" <no@.thanks.com> wrote in message
> news:um$u8aIGFHA.548@.TK2MSFTNGP14.phx.gbl...
> I'm glad you found something that works. FYI, you should probably use a
> local temporary table (single #) instead of a global one (##) unless you
> need access to this same temp table from other processes...
Thanks for the correction, I had it backwards :-)
Diego