Optimizing Data Warehouse Query Performance Through Bitmap Filtering...

Hi, as per the BOL, In the "Optimizing Data Warehouse Query Performance Through Bitmap Filtering"

the cost of Clustered Index Scan as well as Table Scan takes more time in Katmai according to sql Server 2008.

Can you verify it please.

Hi Akash,

What statements in the topic led you to that conclusion? I wrote the topic, so I want to make sure I correct any such sentences that can be perceived incorrectly.

Thanks,

Gail

|||

Hi Akash,

technically, there is no reason for higher cost of scans. Could you please point Gail to the BOL topic so that we can make sure that it is accurate?

Thanks,

Torsten Grabs [MSFT]

Program Manager

SQL Server Query Processor

|||

Hi Gail,

As i've mentioned that in the BOL when you type "Bitmap Index", you let the user understand how optimized bitmap filtering is implemented. You also demonstrate an example for it comparing both the SQL Server 2005 along with Katmai and in that very example you did mention that the cost of Clustered Index Scan as well as Table Scan takes more time in Katmai when compared to sql Server 2005. Just have a look at the figures.

Clustered index scan costs 3% for DimProduct & 12% for DimCustomer in case of SQL Server 2008 where as the same clustering index scan costs 2% and 11% respectively in SQL Server 2005.

Similarly Table Scan costs 14% in SQL Server 2008 and 13% in case of SQL Server 2005.

Should not there be a decrese in scanning cost in Katmai when compared to SQL Server 2005 ?

|||

Hi Akash,

Thanks very much for the pointer to the BOL entry you were referring to. There are two considerations two keep in mind when comparing the table scan cost information as you do:

- Costing functions may change between releases. Hence, when you compare query plan costs and operator costs across releases, you are on unstable ground as you make the assumption that the costing function has not been changed.

- The percentages are relative costs. The less expensive the remaining query plan, i.e., the plan on top of the table scan, gets the higher this percentage value will be - assuming that the cost of the scan is the same. If you look into the subtree cost information of the scan, you will see that the cost is the same for both plans. But, the remaining operators, in particular the hash joins on top, have become cheaper since there are fewer rows to process - which explain why you see the percentages increase.

A further note on why the absolute costs between the scan operators remain the same: even with bitmap filters, the table scan operation has to read all the rows from the disk or buffer pool initially. However, as soon as the row is read into the query processor, we can apply the bitmap filter. The main cost, however, is spent in disk or buffer pool reads - the bitmap filter processing itself is very cheap. Therefore, we do not model the bitmap filtering cost in plan generation. However, optimized bitmap filters do model the cardinality changes of the bitmap filter on top of the scan operation.

Hope this explains the showplan information in the BOL topic.

Best regards,

Torsten Grabs [MSFT]

Program Manager

Microsoft SQL Server Query Processor

|||Thanks Torsten. It was really a great help,appreciate your effort in letting me understand this

Optimizing Data Warehouse Query Performance Through Bitmap Filtering...

Hi, as per the BOL, In the "Optimizing Data Warehouse Query Performance Through Bitmap Filtering"

the cost of Clustered Index Scan as well as Table Scan takes more time in Katmai according to sql Server 2008.

Can you verify it please.

Hi Akash,

What statements in the topic led you to that conclusion? I wrote the topic, so I want to make sure I correct any such sentences that can be perceived incorrectly.

Thanks,

Gail

|||

Hi Akash,

technically, there is no reason for higher cost of scans. Could you please point Gail to the BOL topic so that we can make sure that it is accurate?

Thanks,

Torsten Grabs [MSFT]

Program Manager

SQL Server Query Processor

|||

Hi Gail,

As i've mentioned that in the BOL when you type "Bitmap Index", you let the user understand how optimized bitmap filtering is implemented. You also demonstrate an example for it comparing both the SQL Server 2005 along with Katmai and in that very example you did mention that the cost of Clustered Index Scan as well as Table Scan takes more time in Katmai when compared to sql Server 2005. Just have a look at the figures.

Clustered index scan costs 3% for DimProduct & 12% for DimCustomer in case of SQL Server 2008 where as the same clustering index scan costs 2% and 11% respectively in SQL Server 2005.

Similarly Table Scan costs 14% in SQL Server 2008 and 13% in case of SQL Server 2005.

Should not there be a decrese in scanning cost in Katmai when compared to SQL Server 2005 ?

|||

Hi Akash,

Thanks very much for the pointer to the BOL entry you were referring to. There are two considerations two keep in mind when comparing the table scan cost information as you do:

- Costing functions may change between releases. Hence, when you compare query plan costs and operator costs across releases, you are on unstable ground as you make the assumption that the costing function has not been changed.

- The percentages are relative costs. The less expensive the remaining query plan, i.e., the plan on top of the table scan, gets the higher this percentage value will be - assuming that the cost of the scan is the same. If you look into the subtree cost information of the scan, you will see that the cost is the same for both plans. But, the remaining operators, in particular the hash joins on top, have become cheaper since there are fewer rows to process - which explain why you see the percentages increase.

A further note on why the absolute costs between the scan operators remain the same: even with bitmap filters, the table scan operation has to read all the rows from the disk or buffer pool initially. However, as soon as the row is read into the query processor, we can apply the bitmap filter. The main cost, however, is spent in disk or buffer pool reads - the bitmap filter processing itself is very cheap. Therefore, we do not model the bitmap filtering cost in plan generation. However, optimized bitmap filters do model the cardinality changes of the bitmap filter on top of the scan operation.

Hope this explains the showplan information in the BOL topic.

Best regards,

Torsten Grabs [MSFT]

Program Manager

Microsoft SQL Server Query Processor

|||Thanks Torsten. It was really a great help,appreciate your effort in letting me understand this

Optimizing Data Warehouse Query Performance Through Bitmap Filtering...

Hi, as per the BOL, In the "Optimizing Data Warehouse Query Performance Through Bitmap Filtering"

the cost of Clustered Index Scan as well as Table Scan takes more time in Katmai according to sql Server 2008.

Can you verify it please.

Hi Akash,

What statements in the topic led you to that conclusion? I wrote the topic, so I want to make sure I correct any such sentences that can be perceived incorrectly.

Thanks,

Gail

|||

Hi Akash,

technically, there is no reason for higher cost of scans. Could you please point Gail to the BOL topic so that we can make sure that it is accurate?

Thanks,

Torsten Grabs [MSFT]

Program Manager

SQL Server Query Processor

|||

Hi Gail,

As i've mentioned that in the BOL when you type "Bitmap Index", you let the user understand how optimized bitmap filtering is implemented. You also demonstrate an example for it comparing both the SQL Server 2005 along with Katmai and in that very example you did mention that the cost of Clustered Index Scan as well as Table Scan takes more time in Katmai when compared to sql Server 2005. Just have a look at the figures.

Clustered index scan costs 3% for DimProduct & 12% for DimCustomer in case of SQL Server 2008 where as the same clustering index scan costs 2% and 11% respectively in SQL Server 2005.

Similarly Table Scan costs 14% in SQL Server 2008 and 13% in case of SQL Server 2005.

Should not there be a decrese in scanning cost in Katmai when compared to SQL Server 2005 ?

|||

Hi Akash,

Thanks very much for the pointer to the BOL entry you were referring to. There are two considerations two keep in mind when comparing the table scan cost information as you do:

- Costing functions may change between releases. Hence, when you compare query plan costs and operator costs across releases, you are on unstable ground as you make the assumption that the costing function has not been changed.

- The percentages are relative costs. The less expensive the remaining query plan, i.e., the plan on top of the table scan, gets the higher this percentage value will be - assuming that the cost of the scan is the same. If you look into the subtree cost information of the scan, you will see that the cost is the same for both plans. But, the remaining operators, in particular the hash joins on top, have become cheaper since there are fewer rows to process - which explain why you see the percentages increase.

A further note on why the absolute costs between the scan operators remain the same: even with bitmap filters, the table scan operation has to read all the rows from the disk or buffer pool initially. However, as soon as the row is read into the query processor, we can apply the bitmap filter. The main cost, however, is spent in disk or buffer pool reads - the bitmap filter processing itself is very cheap. Therefore, we do not model the bitmap filtering cost in plan generation. However, optimized bitmap filters do model the cardinality changes of the bitmap filter on top of the scan operation.

Hope this explains the showplan information in the BOL topic.

Best regards,

Torsten Grabs [MSFT]

Program Manager

Microsoft SQL Server Query Processor

|||

Thanks Torsten. It was really a great help,appreciate your effort in letting me understand this

Optimizing Data Warehouse Query Performance Through Bitmap Filtering...

Hi, as per the BOL, In the "Optimizing Data Warehouse Query Performance Through Bitmap Filtering"

the cost of Clustered Index Scan as well as Table Scan takes more time in Katmai according to sql Server 2008.

Can you verify it please.

Hi Akash,

What statements in the topic led you to that conclusion? I wrote the topic, so I want to make sure I correct any such sentences that can be perceived incorrectly.

Thanks,

Gail

|||

Hi Akash,

technically, there is no reason for higher cost of scans. Could you please point Gail to the BOL topic so that we can make sure that it is accurate?

Thanks,

Torsten Grabs [MSFT]

Program Manager

SQL Server Query Processor

|||

Hi Gail,

As i've mentioned that in the BOL when you type "Bitmap Index", you let the user understand how optimized bitmap filtering is implemented. You also demonstrate an example for it comparing both the SQL Server 2005 along with Katmai and in that very example you did mention that the cost of Clustered Index Scan as well as Table Scan takes more time in Katmai when compared to sql Server 2005. Just have a look at the figures.

Clustered index scan costs 3% for DimProduct & 12% for DimCustomer in case of SQL Server 2008 where as the same clustering index scan costs 2% and 11% respectively in SQL Server 2005.

Similarly Table Scan costs 14% in SQL Server 2008 and 13% in case of SQL Server 2005.

Should not there be a decrese in scanning cost in Katmai when compared to SQL Server 2005 ?

|||

Hi Akash,

Thanks very much for the pointer to the BOL entry you were referring to. There are two considerations two keep in mind when comparing the table scan cost information as you do:

- Costing functions may change between releases. Hence, when you compare query plan costs and operator costs across releases, you are on unstable ground as you make the assumption that the costing function has not been changed.

- The percentages are relative costs. The less expensive the remaining query plan, i.e., the plan on top of the table scan, gets the higher this percentage value will be - assuming that the cost of the scan is the same. If you look into the subtree cost information of the scan, you will see that the cost is the same for both plans. But, the remaining operators, in particular the hash joins on top, have become cheaper since there are fewer rows to process - which explain why you see the percentages increase.

A further note on why the absolute costs between the scan operators remain the same: even with bitmap filters, the table scan operation has to read all the rows from the disk or buffer pool initially. However, as soon as the row is read into the query processor, we can apply the bitmap filter. The main cost, however, is spent in disk or buffer pool reads - the bitmap filter processing itself is very cheap. Therefore, we do not model the bitmap filtering cost in plan generation. However, optimized bitmap filters do model the cardinality changes of the bitmap filter on top of the scan operation.

Hope this explains the showplan information in the BOL topic.

Best regards,

Torsten Grabs [MSFT]

Program Manager

Microsoft SQL Server Query Processor

|||Thanks Torsten. It was really a great help,appreciate your effort in letting me understand this sql

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.

Optimizer using the "wrong" index

This query runs in 7 minutes and uses the clustered index on Integer1. When I
force the query to use the non-clustered index on (Integer2, DateTime1) using
index hints, it runs under a minute. The table has 87 million rows. The stats
for the table are updated and the indexes have been rebuilt (reindexed and
defragged for good measure).
Does anyone have ideas to as to why the Optimizer refuses to use the more
ideal index?
select integer1, intrger2, integer3, integer4, integer5
from MainTable
where interger2 in (1,2)
and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
Indexes:
Clustered index on Integer1
Non-Clustered on (Integer2, DateTime1)
Non-Clustered on (DateTime1)
Thanks!
Consider clustering on DateTime1.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Zashel" <Zashel@.discussions.microsoft.com> wrote in message
news:A388C3D6-0D31-44B7-8446-FA7598130653@.microsoft.com...
This query runs in 7 minutes and uses the clustered index on Integer1. When
I
force the query to use the non-clustered index on (Integer2, DateTime1)
using
index hints, it runs under a minute. The table has 87 million rows. The
stats
for the table are updated and the indexes have been rebuilt (reindexed and
defragged for good measure).
Does anyone have ideas to as to why the Optimizer refuses to use the more
ideal index?
select integer1, intrger2, integer3, integer4, integer5
from MainTable
where interger2 in (1,2)
and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
Indexes:
Clustered index on Integer1
Non-Clustered on (Integer2, DateTime1)
Non-Clustered on (DateTime1)
Thanks!

Optimizer using the "wrong" index

This query runs in 7 minutes and uses the clustered index on Integer1. When I
force the query to use the non-clustered index on (Integer2, DateTime1) using
index hints, it runs under a minute. The table has 87 million rows. The stats
for the table are updated and the indexes have been rebuilt (reindexed and
defragged for good measure).
Does anyone have ideas to as to why the Optimizer refuses to use the more
ideal index?
--
select integer1, intrger2, integer3, integer4, integer5
from MainTable
where interger2 in (1,2)
and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
Indexes:
Clustered index on Integer1
Non-Clustered on (Integer2, DateTime1)
Non-Clustered on (DateTime1)
Thanks!"Zashel" <Zashel@.discussions.microsoft.com> wrote in message
news:A388C3D6-0D31-44B7-8446-FA7598130653@.microsoft.com...
> This query runs in 7 minutes and uses the clustered index on Integer1.
> When I
> force the query to use the non-clustered index on (Integer2, DateTime1)
> using
> index hints, it runs under a minute. The table has 87 million rows. The
> stats
> for the table are updated and the indexes have been rebuilt (reindexed and
> defragged for good measure).
> Does anyone have ideas to as to why the Optimizer refuses to use the more
> ideal index?
> --
> select integer1, intrger2, integer3, integer4, integer5
> from MainTable
> where interger2 in (1,2)
> and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
> Indexes:
> Clustered index on Integer1
> Non-Clustered on (Integer2, DateTime1)
> Non-Clustered on (DateTime1)
>
Try with @.StartDate and @.EndDate replaced with hard-coded date literals.
SQL Server is reluctant to commit to using a date index for a date range
with bind variables specifying the ends, because the using the index might
be pretty cheap when @.StartDate and @.EndDate are close together, but
horribly expensive when @.StartDate and @.EndDate are far apart. So this
might be one of those rare times when query hints are desirable.
You can also change the indexing scheme to make this query more attractive.
Options include changing the clustered index or adding
(integer3,integer4,integer5) to the non-clustered index to make it a
covering index for this query.
David|||Consider clustering on DateTime1.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Zashel" <Zashel@.discussions.microsoft.com> wrote in message
news:A388C3D6-0D31-44B7-8446-FA7598130653@.microsoft.com...
This query runs in 7 minutes and uses the clustered index on Integer1. When
I
force the query to use the non-clustered index on (Integer2, DateTime1)
using
index hints, it runs under a minute. The table has 87 million rows. The
stats
for the table are updated and the indexes have been rebuilt (reindexed and
defragged for good measure).
Does anyone have ideas to as to why the Optimizer refuses to use the more
ideal index?
--
select integer1, intrger2, integer3, integer4, integer5
from MainTable
where interger2 in (1,2)
and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
Indexes:
Clustered index on Integer1
Non-Clustered on (Integer2, DateTime1)
Non-Clustered on (DateTime1)
Thanks!

Optimizer using the "wrong" index

This query runs in 7 minutes and uses the clustered index on Integer1. When
I
force the query to use the non-clustered index on (Integer2, DateTime1) usin
g
index hints, it runs under a minute. The table has 87 million rows. The stat
s
for the table are updated and the indexes have been rebuilt (reindexed and
defragged for good measure).
Does anyone have ideas to as to why the Optimizer refuses to use the more
ideal index?
select integer1, intrger2, integer3, integer4, integer5
from MainTable
where interger2 in (1,2)
and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
Indexes:
Clustered index on Integer1
Non-Clustered on (Integer2, DateTime1)
Non-Clustered on (DateTime1)
Thanks!Consider clustering on DateTime1.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Zashel" <Zashel@.discussions.microsoft.com> wrote in message
news:A388C3D6-0D31-44B7-8446-FA7598130653@.microsoft.com...
This query runs in 7 minutes and uses the clustered index on Integer1. When
I
force the query to use the non-clustered index on (Integer2, DateTime1)
using
index hints, it runs under a minute. The table has 87 million rows. The
stats
for the table are updated and the indexes have been rebuilt (reindexed and
defragged for good measure).
Does anyone have ideas to as to why the Optimizer refuses to use the more
ideal index?
select integer1, intrger2, integer3, integer4, integer5
from MainTable
where interger2 in (1,2)
and DateTime1 >= @.Startdate and DateTime1 < @.EndDate
Indexes:
Clustered index on Integer1
Non-Clustered on (Integer2, DateTime1)
Non-Clustered on (DateTime1)
Thanks!

optimizer problem

Hi,
We have a table having 3.2 million rows having primary key
clustered index on id column ...update statistics is done
with fullscan(100%)...
when we are running:
select count(*) from table1 ...it is taking about 4
minutes to return the result...when i see the statistics
io it shows that it is doing scan count:728...
How can this be doing scan count 728 on 2 cpu machine and
takes 4 min just to return count?
Thanks
--HarvinderIf it actually is a scan count of 728, that is not the same as Logical
reads. It means that SQL Server is accessing the table 728 times, and this
usually implies some sort of join.
Can you SET STATISTICS PROFILE ON and show us the output so we can see the
query plan in addition to the statistics?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"harvinder" <hs@.metratech.com> wrote in message
news:026401c3522b$6b4cea20$a601280a@.phx.gbl...
> Hi,
> We have a table having 3.2 million rows having primary key
> clustered index on id column ...update statistics is done
> with fullscan(100%)...
> when we are running:
> select count(*) from table1 ...it is taking about 4
> minutes to return the result...when i see the statistics
> io it shows that it is doing scan count:728...
> How can this be doing scan count 728 on 2 cpu machine and
> takes 4 min just to return count?
> Thanks
> --Harvinder
>|||That was my other question...howcome it is doing 728 scan
count instead of 1 clustered index scan...i am pasting the
output of showplan :
select count(*) from tab1
|--Compute Scalar(DEFINE:([Expr1002]=Convert
([globalagg1004])))
|--Stream Aggregate(DEFINE:([globalagg1004]=SUM
([partialagg1003])))
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE:
([partialagg1003]=Count(*)))
|--Clustered Index Scan(OBJECT:([dm].
[dbo].[tab1].[pk_tab1]))
Thanks
--Harvinder
>--Original Message--
>If it actually is a scan count of 728, that is not the
same as Logical
>reads. It means that SQL Server is accessing the table
728 times, and this
>usually implies some sort of join.
>Can you SET STATISTICS PROFILE ON and show us the output
so we can see the
>query plan in addition to the statistics?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"harvinder" <hs@.metratech.com> wrote in message
>news:026401c3522b$6b4cea20$a601280a@.phx.gbl...
>> Hi,
>> We have a table having 3.2 million rows having primary
key
>> clustered index on id column ...update statistics is
done
>> with fullscan(100%)...
>> when we are running:
>> select count(*) from table1 ...it is taking about 4
>> minutes to return the result...when i see the
statistics
>> io it shows that it is doing scan count:728...
>> How can this be doing scan count 728 on 2 cpu machine
and
>> takes 4 min just to return count?
>> Thanks
>> --Harvinder
>
>.
>|||I was actually hoping for the STATISTICS PROFILE output in addition to the
exact STATISTICS IO that I assumed you were already collecting.
My guess at this point (without seeing the STATISTICS IO output) is that
the high scan count is related to the fact that the query is being processed
in parallel.
The large amount of time is probably because of the clustered index scan. A
clustered index scan is exactly the same as a table scan, so to get the
results of count(*) SQL Server has to look at every row on every page. How
many rows and how many pages are in this table? Does the query include a
WHERE clause? What is the result of your count(*) query?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"harvinder" <hs@.metratech.com> wrote in message
news:051501c35230$b8aed800$a301280a@.phx.gbl...
> That was my other question...howcome it is doing 728 scan
> count instead of 1 clustered index scan...i am pasting the
> output of showplan :
> select count(*) from tab1
> |--Compute Scalar(DEFINE:([Expr1002]=Convert
> ([globalagg1004])))
> |--Stream Aggregate(DEFINE:([globalagg1004]=SUM
> ([partialagg1003])))
> |--Parallelism(Gather Streams)
> |--Stream Aggregate(DEFINE:
> ([partialagg1003]=Count(*)))
> |--Clustered Index Scan(OBJECT:([dm].
> [dbo].[tab1].[pk_tab1]))
> Thanks
> --Harvinder
> >--Original Message--
> >If it actually is a scan count of 728, that is not the
> same as Logical
> >reads. It means that SQL Server is accessing the table
> 728 times, and this
> >usually implies some sort of join.
> >Can you SET STATISTICS PROFILE ON and show us the output
> so we can see the
> >query plan in addition to the statistics?
> >
> >--
> >HTH
> >--
> >Kalen Delaney
> >SQL Server MVP
> >www.SolidQualityLearning.com
> >
> >
> >"harvinder" <hs@.metratech.com> wrote in message
> >news:026401c3522b$6b4cea20$a601280a@.phx.gbl...
> >> Hi,
> >>
> >> We have a table having 3.2 million rows having primary
> key
> >> clustered index on id column ...update statistics is
> done
> >> with fullscan(100%)...
> >> when we are running:
> >> select count(*) from table1 ...it is taking about 4
> >> minutes to return the result...when i see the
> statistics
> >> io it shows that it is doing scan count:728...
> >> How can this be doing scan count 728 on 2 cpu machine
> and
> >> takes 4 min just to return count?
> >>
> >> Thanks
> >> --Harvinder
> >>
> >
> >
> >.
> >|||if your system is a Xeon or Xeon MP, and HT is enabled,
and you have a parallel execution plan
try OPTION (MAXDOP 1)
better yet, disabled HT
>--Original Message--
>Hi,
>We have a table having 3.2 million rows having primary
key
>clustered index on id column ...update statistics is done
>with fullscan(100%)...
>when we are running:
>select count(*) from table1 ...it is taking about 4
>minutes to return the result...when i see the statistics
>io it shows that it is doing scan count:728...
>How can this be doing scan count 728 on 2 cpu machine and
>takes 4 min just to return count?
>Thanks
>--Harvinder
>.
>

Optimizer not using the good index

Please,
Using SQL 7.0
The optimizer is not using the good index for this query
It's using the clustered index X2 (on WOT_DATE instead of
index X1 on WO_REF.
I did reindex but it's still the same. It looks to
happen every monday after i run sp_updatestats
What should i do or i should not do
SELECT 0
FROM WOTransit (readuncommitted)
INNER JOIN WO (readuncommitted) On
WoTransit.Wo_ref=Wo.Wo_ref
WHERE WO.Cmd_No=814352
AND ISNULL(WOTransit.Charge_ref,0) <> 0
thanks
Don
Don wrote:
> Please,
> Using SQL 7.0
> The optimizer is not using the good index for this query
> It's using the clustered index X2 (on WOT_DATE instead of
> index X1 on WO_REF.
> I did reindex but it's still the same. It looks to
> happen every monday after i run sp_updatestats
> What should i do or i should not do
> SELECT 0
> FROM WOTransit (readuncommitted)
> INNER JOIN WO (readuncommitted) On
> WoTransit.Wo_ref=Wo.Wo_ref
> WHERE WO.Cmd_No=814352
> AND ISNULL(WOTransit.Charge_ref,0) <> 0
> thanks
> Don
When you say "Using the clustered index", I assume you mean you are
seeing a clustered index scan operation instead of in Index Seek on
whatever index you have on the Wo_ref column. Correct? If the Wo_ref
index is there, but is not being used, can you let us know how many rows
are in the table and how many rows the query returns.
David Gugick
Imceda Software
www.imceda.com
|||My answer to your question is YES
And there is 4160164 rows in WO and 2666718 rows in
WOTRANSIT
The query returns 11 rows.
I appreciate your help
Don
[vbcol=seagreen]
>--Original Message--
>Don wrote:
of
>When you say "Using the clustered index", I assume you
mean you are
>seeing a clustered index scan operation instead of in
Index Seek on
>whatever index you have on the Wo_ref column. Correct? If
the Wo_ref
>index is there, but is not being used, can you let us
know how many rows
>are in the table and how many rows the query returns.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>
|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:2ca001c4b179$6be119d0$a501280a@.phx.gbl...
> My answer to your question is YES
> And there is 4160164 rows in WO and 2666718 rows in
> WOTRANSIT
> The query returns 11 rows.
How many rows in WO with Cmd_No=814352? If the number is small then you
might try an index on Cmd_No.
David
|||I allready have an index on wo.cmd_no
Here, there seems to be a problem using the good index on
the join clause (wo.wo_ref = wotransit.wo_ref)
Please some help, the production teem cannot work because
it so slow
thanks !
Don

>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2ca001c4b179$6be119d0$a501280a@.phx.gbl...
>
>How many rows in WO with Cmd_No=814352? If the number is
small then you
>might try an index on Cmd_No.
>David
>
>.
>
|||As David Browne asked, how many rows are there in WO with Cmd_No=814352?
Assuming that you are 100% sure that using the nonclustered index is better,
I would guess that the statistics are inaccurate.
To test this, use Query Analyzer and look at the estimated execution plan
for the query. Open another window and get the actual execution plan.
Compare the estimated number of rows against the actual number of rows
retrieved for each process. Somewhere in there, there should be a wide
divergence, which should indicate inaccurate statistics. Run the UPDATE
STATISTICS command for the relevant index with the FULLSCAN option. Might
not work always since SQL Server only maintains distribution stats for 200
steps and you have millions of rows in the table, but worth a shot.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:2c8801c4b170$5415b9e0$a501280a@.phx.gbl...
> Please,
> Using SQL 7.0
> The optimizer is not using the good index for this query
> It's using the clustered index X2 (on WOT_DATE instead of
> index X1 on WO_REF.
> I did reindex but it's still the same. It looks to
> happen every monday after i run sp_updatestats
> What should i do or i should not do
> SELECT 0
> FROM WOTransit (readuncommitted)
> INNER JOIN WO (readuncommitted) On
> WoTransit.Wo_ref=Wo.Wo_ref
> WHERE WO.Cmd_No=814352
> AND ISNULL(WOTransit.Charge_ref,0) <> 0
> thanks
> Don
|||<anonymous@.discussions.microsoft.com> wrote in message
news:021801c4b17c$6596cfe0$a301280a@.phx.gbl...
>I allready have an index on wo.cmd_no
> Here, there seems to be a problem using the good index on
> the join clause (wo.wo_ref = wotransit.wo_ref)
> Please some help, the production teem cannot work because
> it so slow
>
Well you better post the DDL. You haven't even told us which table the
index is on.
And what is the execution plan?
David
|||Here's a shot in the dark ...
Have you tried setting the ISOLATION LEVEL prior to the query and implement
and index hint forcing the index you want to be used ?
Like this ...
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRANSACTION
SELECT 0
FROM WOTransit WITH (INDEX = idx1)
INNER JOIN WO WITH (INDEX = idx2) On
WoTransit.Wo_ref=Wo.Wo_ref
WHERE WO.Cmd_No=814352
AND ISNULL(WOTransit.Charge_ref,0) <> 0
COMMIT TRANSACTION
Hope this helps... Let me know ?
Anthony E. Castro
"Peter Yeoh" wrote:

> As David Browne asked, how many rows are there in WO with Cmd_No=814352?
> Assuming that you are 100% sure that using the nonclustered index is better,
> I would guess that the statistics are inaccurate.
> To test this, use Query Analyzer and look at the estimated execution plan
> for the query. Open another window and get the actual execution plan.
> Compare the estimated number of rows against the actual number of rows
> retrieved for each process. Somewhere in there, there should be a wide
> divergence, which should indicate inaccurate statistics. Run the UPDATE
> STATISTICS command for the relevant index with the FULLSCAN option. Might
> not work always since SQL Server only maintains distribution stats for 200
> steps and you have millions of rows in the table, but worth a shot.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:2c8801c4b170$5415b9e0$a501280a@.phx.gbl...
>
>
|||"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:%23WnlNxZsEHA.2316@.TK2MSFTNGP12.phx.gbl...
> As David Browne asked, how many rows are there in WO with Cmd_No=814352?
> Assuming that you are 100% sure that using the nonclustered index is
better,
> I would guess that the statistics are inaccurate.
> To test this, use Query Analyzer and look at the estimated execution plan
> for the query. Open another window and get the actual execution plan.
Actually, the actual execution plan shows the estmated row count, too. Very
handy. What would be even better is if large discrepancies were reported
similarly to missing statistics.
|||Thx for pointing that out. I should have mentioned that I was referring to
the graphical plan. Don, if you're still with us, you can view the actual
and estimated row counts side-by-side (well, almost) using the textual
execution plan. Just run SET STATISTICS PROFILE ON before running the query
in QA.
Mark, you just gave me an idea to add to our Query Analyzer Tool tool.
Thanks.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!

Optimizer not using the good index

Please,
Using SQL 7.0
The optimizer is not using the good index for this query
It's using the clustered index X2 (on WOT_DATE instead of
index X1 on WO_REF.
I did reindex but it's still the same. It looks to
happen every monday after i run sp_updatestats
What should i do or i should not do
SELECT 0
FROM WOTransit (readuncommitted)
INNER JOIN WO (readuncommitted) On
WoTransit.Wo_ref=Wo.Wo_ref
WHERE WO.Cmd_No=814352
AND ISNULL(WOTransit.Charge_ref,0) <> 0
thanks
DonDon wrote:
> Please,
> Using SQL 7.0
> The optimizer is not using the good index for this query
> It's using the clustered index X2 (on WOT_DATE instead of
> index X1 on WO_REF.
> I did reindex but it's still the same. It looks to
> happen every monday after i run sp_updatestats
> What should i do or i should not do
> SELECT 0
> FROM WOTransit (readuncommitted)
> INNER JOIN WO (readuncommitted) On
> WoTransit.Wo_ref=Wo.Wo_ref
> WHERE WO.Cmd_No=814352
> AND ISNULL(WOTransit.Charge_ref,0) <> 0
> thanks
> Don
When you say "Using the clustered index", I assume you mean you are
seeing a clustered index scan operation instead of in Index Seek on
whatever index you have on the Wo_ref column. Correct? If the Wo_ref
index is there, but is not being used, can you let us know how many rows
are in the table and how many rows the query returns.
--
David Gugick
Imceda Software
www.imceda.com|||My answer to your question is YES
And there is 4160164 rows in WO and 2666718 rows in
WOTRANSIT
The query returns 11 rows.
I appreciate your help
Don
>--Original Message--
>Don wrote:
>> Please,
>> Using SQL 7.0
>> The optimizer is not using the good index for this query
>> It's using the clustered index X2 (on WOT_DATE instead
of
>> index X1 on WO_REF.
>> I did reindex but it's still the same. It looks to
>> happen every monday after i run sp_updatestats
>> What should i do or i should not do
>> SELECT 0
>> FROM WOTransit (readuncommitted)
>> INNER JOIN WO (readuncommitted) On
>> WoTransit.Wo_ref=Wo.Wo_ref
>> WHERE WO.Cmd_No=814352
>> AND ISNULL(WOTransit.Charge_ref,0) <> 0
>> thanks
>> Don
>When you say "Using the clustered index", I assume you
mean you are
>seeing a clustered index scan operation instead of in
Index Seek on
>whatever index you have on the Wo_ref column. Correct? If
the Wo_ref
>index is there, but is not being used, can you let us
know how many rows
>are in the table and how many rows the query returns.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:2ca001c4b179$6be119d0$a501280a@.phx.gbl...
> My answer to your question is YES
> And there is 4160164 rows in WO and 2666718 rows in
> WOTRANSIT
> The query returns 11 rows.
How many rows in WO with Cmd_No=814352? If the number is small then you
might try an index on Cmd_No.
David|||I allready have an index on wo.cmd_no
Here, there seems to be a problem using the good index on
the join clause (wo.wo_ref = wotransit.wo_ref)
Please some help, the production teem cannot work because
it so slow
thanks !
Don
>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2ca001c4b179$6be119d0$a501280a@.phx.gbl...
>> My answer to your question is YES
>> And there is 4160164 rows in WO and 2666718 rows in
>> WOTRANSIT
>> The query returns 11 rows.
>
>How many rows in WO with Cmd_No=814352? If the number is
small then you
>might try an index on Cmd_No.
>David
>
>.
>|||As David Browne asked, how many rows are there in WO with Cmd_No=814352?
Assuming that you are 100% sure that using the nonclustered index is better,
I would guess that the statistics are inaccurate.
To test this, use Query Analyzer and look at the estimated execution plan
for the query. Open another window and get the actual execution plan.
Compare the estimated number of rows against the actual number of rows
retrieved for each process. Somewhere in there, there should be a wide
divergence, which should indicate inaccurate statistics. Run the UPDATE
STATISTICS command for the relevant index with the FULLSCAN option. Might
not work always since SQL Server only maintains distribution stats for 200
steps and you have millions of rows in the table, but worth a shot.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:2c8801c4b170$5415b9e0$a501280a@.phx.gbl...
> Please,
> Using SQL 7.0
> The optimizer is not using the good index for this query
> It's using the clustered index X2 (on WOT_DATE instead of
> index X1 on WO_REF.
> I did reindex but it's still the same. It looks to
> happen every monday after i run sp_updatestats
> What should i do or i should not do
> SELECT 0
> FROM WOTransit (readuncommitted)
> INNER JOIN WO (readuncommitted) On
> WoTransit.Wo_ref=Wo.Wo_ref
> WHERE WO.Cmd_No=814352
> AND ISNULL(WOTransit.Charge_ref,0) <> 0
> thanks
> Don|||<anonymous@.discussions.microsoft.com> wrote in message
news:021801c4b17c$6596cfe0$a301280a@.phx.gbl...
>I allready have an index on wo.cmd_no
> Here, there seems to be a problem using the good index on
> the join clause (wo.wo_ref = wotransit.wo_ref)
> Please some help, the production teem cannot work because
> it so slow
>
Well you better post the DDL. You haven't even told us which table the
index is on.
And what is the execution plan?
David|||Here's a shot in the dark ...
Have you tried setting the ISOLATION LEVEL prior to the query and implement
and index hint forcing the index you want to be used ?
Like this ...
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRANSACTION
SELECT 0
FROM WOTransit WITH (INDEX = idx1)
INNER JOIN WO WITH (INDEX = idx2) On
WoTransit.Wo_ref=Wo.Wo_ref
WHERE WO.Cmd_No=814352
AND ISNULL(WOTransit.Charge_ref,0) <> 0
COMMIT TRANSACTION
Hope this helps... Let me know ?
Anthony E. Castro
"Peter Yeoh" wrote:
> As David Browne asked, how many rows are there in WO with Cmd_No=814352?
> Assuming that you are 100% sure that using the nonclustered index is better,
> I would guess that the statistics are inaccurate.
> To test this, use Query Analyzer and look at the estimated execution plan
> for the query. Open another window and get the actual execution plan.
> Compare the estimated number of rows against the actual number of rows
> retrieved for each process. Somewhere in there, there should be a wide
> divergence, which should indicate inaccurate statistics. Run the UPDATE
> STATISTICS command for the relevant index with the FULLSCAN option. Might
> not work always since SQL Server only maintains distribution stats for 200
> steps and you have millions of rows in the table, but worth a shot.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:2c8801c4b170$5415b9e0$a501280a@.phx.gbl...
> > Please,
> >
> > Using SQL 7.0
> >
> > The optimizer is not using the good index for this query
> >
> > It's using the clustered index X2 (on WOT_DATE instead of
> > index X1 on WO_REF.
> >
> > I did reindex but it's still the same. It looks to
> > happen every monday after i run sp_updatestats
> >
> > What should i do or i should not do
> >
> > SELECT 0
> > FROM WOTransit (readuncommitted)
> > INNER JOIN WO (readuncommitted) On
> > WoTransit.Wo_ref=Wo.Wo_ref
> > WHERE WO.Cmd_No=814352
> > AND ISNULL(WOTransit.Charge_ref,0) <> 0
> >
> > thanks
> >
> > Don
>
>|||"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:%23WnlNxZsEHA.2316@.TK2MSFTNGP12.phx.gbl...
> As David Browne asked, how many rows are there in WO with Cmd_No=814352?
> Assuming that you are 100% sure that using the nonclustered index is
better,
> I would guess that the statistics are inaccurate.
> To test this, use Query Analyzer and look at the estimated execution plan
> for the query. Open another window and get the actual execution plan.
Actually, the actual execution plan shows the estmated row count, too. Very
handy. What would be even better is if large discrepancies were reported
similarly to missing statistics.|||Thx for pointing that out. I should have mentioned that I was referring to
the graphical plan. Don, if you're still with us, you can view the actual
and estimated row counts side-by-side (well, almost) using the textual
execution plan. Just run SET STATISTICS PROFILE ON before running the query
in QA.
Mark, you just gave me an idea to add to our Query Analyzer Tool tool.
Thanks.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!|||"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:%23WWTPZmsEHA.1604@.TK2MSFTNGP15.phx.gbl...
> I should have mentioned that I was referring to
> the graphical plan. Don, if you're still with us, you can view the actual
> and estimated row counts side-by-side (well, almost) using the textual
> execution plan. Just run SET STATISTICS PROFILE ON before running the
query
> in QA.
The graphical plan shows both stats, too. Hover over a step and you'll see a
line for Row Count, then another line (at the bottom) for Estimated Row
Count.
> Mark, you just gave me an idea to add to our Query Analyzer Tool tool.
Sounds great!|||On Wed, 13 Oct 2004 15:02:27 -0700, "Don"
<anonymous@.discussions.microsoft.com> wrote:
>Using SQL 7.0
>The optimizer is not using the good index for this query
>It's using the clustered index X2 (on WOT_DATE instead of
>index X1 on WO_REF.
>I did reindex but it's still the same. It looks to
>happen every monday after i run sp_updatestats
>What should i do or i should not do
>SELECT 0
>FROM WOTransit (readuncommitted)
> INNER JOIN WO (readuncommitted) On
>WoTransit.Wo_ref=Wo.Wo_ref
>WHERE WO.Cmd_No=814352
>AND ISNULL(WOTransit.Charge_ref,0) <> 0
The ISNULL() function may be confusing the optimizer.
Or the data.
If there are actually 100k rows in the join with WO.Cmd_No=814352 but
they do have a null or 0 WOTransit.Charge_ref and so are not selected,
then maybe the optimizer doesn't know that. Is there an index on
Charge_ref? I'm not sure what the threshhold is for a scan (so I
guessed 100k), but once the optimizer wants to scan, it would just use
the clustered index, whatever it is. Actually, it's using no index.
I wonder what happens if you recode around the ISNULL?
J.|||Hi every one
Sorry for the delay,
I found that there were statitics (auto created) on table
WOTANSIT
I dropped them and the execution plan went back to normal.
I didn't go further to find a explanation. but for shure i
will never use sp_updatestats again. And i will
uncheck "Auto create statistics".
Does any one can explain why i had to drop these auto
created stats ?
Thanks !!!
>--Original Message--
><anonymous@.discussions.microsoft.com> wrote in message
>news:021801c4b17c$6596cfe0$a301280a@.phx.gbl...
>>I allready have an index on wo.cmd_no
>> Here, there seems to be a problem using the good index
on
>> the join clause (wo.wo_ref = wotransit.wo_ref)
>> Please some help, the production teem cannot work
because
>> it so slow
>Well you better post the DDL. You haven't even told us
which table the
>index is on.
>And what is the execution plan?
>David
>
>.
>|||This help but i don't want to rewrite the query.
See my other post and solution on 2004/10/18
thx
>--Original Message--
>Here's a shot in the dark ...
>Have you tried setting the ISOLATION LEVEL prior to the
query and implement
>and index hint forcing the index you want to be used ?
>Like this ...
>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>GO
>BEGIN TRANSACTION
>SELECT 0
>FROM WOTransit WITH (INDEX = idx1)
> INNER JOIN WO WITH (INDEX = idx2) On
>WoTransit.Wo_ref=Wo.Wo_ref
>WHERE WO.Cmd_No=814352
>AND ISNULL(WOTransit.Charge_ref,0) <> 0
>COMMIT TRANSACTION
>Hope this helps... Let me know ?
>Anthony E. Castro
>
>
>
>"Peter Yeoh" wrote:
>> As David Browne asked, how many rows are there in WO
with Cmd_No=814352?
>> Assuming that you are 100% sure that using the
nonclustered index is better,
>> I would guess that the statistics are inaccurate.
>> To test this, use Query Analyzer and look at the
estimated execution plan
>> for the query. Open another window and get the actual
execution plan.
>> Compare the estimated number of rows against the actual
number of rows
>> retrieved for each process. Somewhere in there, there
should be a wide
>> divergence, which should indicate inaccurate
statistics. Run the UPDATE
>> STATISTICS command for the relevant index with the
FULLSCAN option. Might
>> not work always since SQL Server only maintains
distribution stats for 200
>> steps and you have millions of rows in the table, but
worth a shot.
>> --
>> Peter Yeoh
>> http://www.yohz.com
>> Need smaller SQL2K backup files? Use MiniSQLBackup
Lite, free!
>>
>> "Don" <anonymous@.discussions.microsoft.com> wrote in
message
>> news:2c8801c4b170$5415b9e0$a501280a@.phx.gbl...
>> > Please,
>> >
>> > Using SQL 7.0
>> >
>> > The optimizer is not using the good index for this
query
>> >
>> > It's using the clustered index X2 (on WOT_DATE
instead of
>> > index X1 on WO_REF.
>> >
>> > I did reindex but it's still the same. It looks to
>> > happen every monday after i run sp_updatestats
>> >
>> > What should i do or i should not do
>> >
>> > SELECT 0
>> > FROM WOTransit (readuncommitted)
>> > INNER JOIN WO (readuncommitted) On
>> > WoTransit.Wo_ref=Wo.Wo_ref
>> > WHERE WO.Cmd_No=814352
>> > AND ISNULL(WOTransit.Charge_ref,0) <> 0
>> >
>> > thanks
>> >
>> > Don
>>
>.
>|||Effectively,
Isnull() was confusing the optimizer.
See my post on 2004/10/18 to see what i had to do to fix
the problem
Ttx
>The ISNULL() function may be confusing the optimizer.
>Or the data.
>If there are actually 100k rows in the join with
WO.Cmd_No=814352 but
>they do have a null or 0 WOTransit.Charge_ref and so are
not selected,
>then maybe the optimizer doesn't know that. Is there an
index on
>Charge_ref? I'm not sure what the threshhold is for a
scan (so I
>guessed 100k), but once the optimizer wants to scan, it
would just use
>the clustered index, whatever it is. Actually, it's
using no index.
>I wonder what happens if you recode around the ISNULL?
>J.
>.
>

Optimizer not using the good index

Please,
Using SQL 7.0
The optimizer is not using the good index for this query
It's using the clustered index X2 (on WOT_DATE instead of
index X1 on WO_REF.
I did reindex but it's still the same. It looks to
happen every monday after i run sp_updatestats
What should i do or i should not do
SELECT 0
FROM WOTransit (readuncommitted)
INNER JOIN WO (readuncommitted) On
WoTransit.Wo_ref=Wo.Wo_ref
WHERE WO.Cmd_No=814352
AND ISNULL(WOTransit.Charge_ref,0) <> 0
thanks
DonDon wrote:
> Please,
> Using SQL 7.0
> The optimizer is not using the good index for this query
> It's using the clustered index X2 (on WOT_DATE instead of
> index X1 on WO_REF.
> I did reindex but it's still the same. It looks to
> happen every monday after i run sp_updatestats
> What should i do or i should not do
> SELECT 0
> FROM WOTransit (readuncommitted)
> INNER JOIN WO (readuncommitted) On
> WoTransit.Wo_ref=Wo.Wo_ref
> WHERE WO.Cmd_No=814352
> AND ISNULL(WOTransit.Charge_ref,0) <> 0
> thanks
> Don
When you say "Using the clustered index", I assume you mean you are
seeing a clustered index scan operation instead of in Index Seek on
whatever index you have on the Wo_ref column. Correct? If the Wo_ref
index is there, but is not being used, can you let us know how many rows
are in the table and how many rows the query returns.
David Gugick
Imceda Software
www.imceda.com|||My answer to your question is YES
And there is 4160164 rows in WO and 2666718 rows in
WOTRANSIT
The query returns 11 rows.
I appreciate your help
Don

>--Original Message--
>Don wrote:
of[vbcol=seagreen]
>When you say "Using the clustered index", I assume you
mean you are
>seeing a clustered index scan operation instead of in
Index Seek on
>whatever index you have on the Wo_ref column. Correct? If
the Wo_ref
>index is there, but is not being used, can you let us
know how many rows
>are in the table and how many rows the query returns.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:2ca001c4b179$6be119d0$a501280a@.phx.gbl...
> My answer to your question is YES
> And there is 4160164 rows in WO and 2666718 rows in
> WOTRANSIT
> The query returns 11 rows.
How many rows in WO with Cmd_No=814352? If the number is small then you
might try an index on Cmd_No.
David|||I allready have an index on wo.cmd_no
Here, there seems to be a problem using the good index on
the join clause (wo.wo_ref = wotransit.wo_ref)
Please some help, the production teem cannot work because
it so slow
thanks !
Don

>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2ca001c4b179$6be119d0$a501280a@.phx.gbl...
>
>How many rows in WO with Cmd_No=814352? If the number is
small then you
>might try an index on Cmd_No.
>David
>
>.
>|||As David Browne asked, how many rows are there in WO with Cmd_No=814352?
Assuming that you are 100% sure that using the nonclustered index is better,
I would guess that the statistics are inaccurate.
To test this, use Query Analyzer and look at the estimated execution plan
for the query. Open another window and get the actual execution plan.
Compare the estimated number of rows against the actual number of rows
retrieved for each process. Somewhere in there, there should be a wide
divergence, which should indicate inaccurate statistics. Run the UPDATE
STATISTICS command for the relevant index with the FULLSCAN option. Might
not work always since SQL Server only maintains distribution stats for 200
steps and you have millions of rows in the table, but worth a shot.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:2c8801c4b170$5415b9e0$a501280a@.phx.gbl...
> Please,
> Using SQL 7.0
> The optimizer is not using the good index for this query
> It's using the clustered index X2 (on WOT_DATE instead of
> index X1 on WO_REF.
> I did reindex but it's still the same. It looks to
> happen every monday after i run sp_updatestats
> What should i do or i should not do
> SELECT 0
> FROM WOTransit (readuncommitted)
> INNER JOIN WO (readuncommitted) On
> WoTransit.Wo_ref=Wo.Wo_ref
> WHERE WO.Cmd_No=814352
> AND ISNULL(WOTransit.Charge_ref,0) <> 0
> thanks
> Don|||<anonymous@.discussions.microsoft.com> wrote in message
news:021801c4b17c$6596cfe0$a301280a@.phx.gbl...
>I allready have an index on wo.cmd_no
> Here, there seems to be a problem using the good index on
> the join clause (wo.wo_ref = wotransit.wo_ref)
> Please some help, the production teem cannot work because
> it so slow
>
Well you better post the DDL. You haven't even told us which table the
index is on.
And what is the execution plan?
David|||Here's a shot in the dark ...
Have you tried setting the ISOLATION LEVEL prior to the query and implement
and index hint forcing the index you want to be used ?
Like this ...
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRANSACTION
SELECT 0
FROM WOTransit WITH (INDEX = idx1)
INNER JOIN WO WITH (INDEX = idx2) On
WoTransit.Wo_ref=Wo.Wo_ref
WHERE WO.Cmd_No=814352
AND ISNULL(WOTransit.Charge_ref,0) <> 0
COMMIT TRANSACTION
Hope this helps... Let me know ?
Anthony E. Castro
"Peter Yeoh" wrote:

> As David Browne asked, how many rows are there in WO with Cmd_No=814352?
> Assuming that you are 100% sure that using the nonclustered index is bette
r,
> I would guess that the statistics are inaccurate.
> To test this, use Query Analyzer and look at the estimated execution plan
> for the query. Open another window and get the actual execution plan.
> Compare the estimated number of rows against the actual number of rows
> retrieved for each process. Somewhere in there, there should be a wide
> divergence, which should indicate inaccurate statistics. Run the UPDATE
> STATISTICS command for the relevant index with the FULLSCAN option. Might
> not work always since SQL Server only maintains distribution stats for 200
> steps and you have millions of rows in the table, but worth a shot.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Don" <anonymous@.discussions.microsoft.com> wrote in message
> news:2c8801c4b170$5415b9e0$a501280a@.phx.gbl...
>
>|||"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:%23WnlNxZsEHA.2316@.TK2MSFTNGP12.phx.gbl...
> As David Browne asked, how many rows are there in WO with Cmd_No=814352?
> Assuming that you are 100% sure that using the nonclustered index is
better,
> I would guess that the statistics are inaccurate.
> To test this, use Query Analyzer and look at the estimated execution plan
> for the query. Open another window and get the actual execution plan.
Actually, the actual execution plan shows the estmated row count, too. Very
handy. What would be even better is if large discrepancies were reported
similarly to missing statistics.|||Thx for pointing that out. I should have mentioned that I was referring to
the graphical plan. Don, if you're still with us, you can view the actual
and estimated row counts side-by-side (well, almost) using the textual
execution plan. Just run SET STATISTICS PROFILE ON before running the query
in QA.
Mark, you just gave me an idea to add to our Query Analyzer Tool tool.
Thanks.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!sql

Optimization Question: Date ranges, Between Operaror and Clustered Index

I searched Google and while I'm certain this has been discussed, there
are too many hits for my key words, so I'll ask these questions afresh:
1) Is it reasonable to expect improved performance by putting the
clustered index on the field you most use for RANGE searches.
2) Is there any reason you wouldn't create a clustered index on a
DateTime field, if the data in that field was autogenerated by
the server clock and moved only forward chronologically?
3) Is the BETWEEN operator slower than >= and <= Is this ... CallDate BETWEEN '2004-01-15' AND '2004-01-23'
faster or slower than this ...
CallDate >='2004-01-15' AND CallDate <= '2004-01-23'
Thanks in advance for any feedback you have to give on these issues.
--
Danny J. Lesandrini
dlesandrini@.hotmail.com
http://amazecreations.com/datafast/"Danny J. Lesandrini" <dlesandrini@.hotmail.com> wrote in message
news:eDFKzxTVEHA.2508@.TK2MSFTNGP12.phx.gbl...
> 1) Is it reasonable to expect improved performance by putting the
> clustered index on the field you most use for RANGE searches.
Most likely, yes.
> 2) Is there any reason you wouldn't create a clustered index on a
> DateTime field, if the data in that field was autogenerated by
> the server clock and moved only forward chronologically?
No, in my opinion that would be a good candidate for a clustered index
as it would create a hotspot at the end of the table, which is good for
insert performance.
> 3) Is the BETWEEN operator slower than >= and <=> Is this ... CallDate BETWEEN '2004-01-15' AND '2004-01-23'
> faster or slower than this ...
> CallDate >='2004-01-15' AND CallDate <= '2004-01-23'
No. But there are other issues to consider. See:
http://www.aspfaq.com/show.asp?id=2280|||Thanks Adam, that was exactly what I was looking for.
Danny
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ucJkm8TVEHA.2944@.tk2msftngp13.phx.gbl...
> "Danny J. Lesandrini" <dlesandrini@.hotmail.com> wrote in message
> news:eDFKzxTVEHA.2508@.TK2MSFTNGP12.phx.gbl...
> >
> > 1) Is it reasonable to expect improved performance by putting the
> > clustered index on the field you most use for RANGE searches.
> Most likely, yes.
> > 2) Is there any reason you wouldn't create a clustered index on a
> > DateTime field, if the data in that field was autogenerated by
> > the server clock and moved only forward chronologically?
> No, in my opinion that would be a good candidate for a clustered index
> as it would create a hotspot at the end of the table, which is good for
> insert performance.
> > 3) Is the BETWEEN operator slower than >= and <=> > Is this ... CallDate BETWEEN '2004-01-15' AND '2004-01-23'
> > faster or slower than this ...
> > CallDate >='2004-01-15' AND CallDate <= '2004-01-23'
> No. But there are other issues to consider. See:
> http://www.aspfaq.com/show.asp?id=2280
>
>
>
>sql

Optimization Question: Date ranges, Between Operaror and Clustered Index

I searched Google and while I'm certain this has been discussed, there
are too many hits for my key words, so I'll ask these questions afresh:
1) Is it reasonable to expect improved performance by putting the
clustered index on the field you most use for RANGE searches.
2) Is there any reason you wouldn't create a clustered index on a
DateTime field, if the data in that field was autogenerated by
the server clock and moved only forward chronologically?
3) Is the BETWEEN operator slower than >= and <=
Is this ... CallDate BETWEEN '2004-01-15' AND '2004-01-23'
faster or slower than this ...
CallDate >='2004-01-15' AND CallDate <= '2004-01-23'
Thanks in advance for any feedback you have to give on these issues.
--
Danny J. Lesandrini
dlesandrini@.hotmail.com
http://amazecreations.com/datafast/"Danny J. Lesandrini" <dlesandrini@.hotmail.com> wrote in message
news:eDFKzxTVEHA.2508@.TK2MSFTNGP12.phx.gbl...
> 1) Is it reasonable to expect improved performance by putting the
> clustered index on the field you most use for RANGE searches.
Most likely, yes.

> 2) Is there any reason you wouldn't create a clustered index on a
> DateTime field, if the data in that field was autogenerated by
> the server clock and moved only forward chronologically?
No, in my opinion that would be a good candidate for a clustered index
as it would create a hotspot at the end of the table, which is good for
insert performance.

> 3) Is the BETWEEN operator slower than >= and <=
> Is this ... CallDate BETWEEN '2004-01-15' AND '2004-01-23'
> faster or slower than this ...
> CallDate >='2004-01-15' AND CallDate <= '2004-01-23'
No. But there are other issues to consider. See:
http://www.aspfaq.com/show.asp?id=2280|||Thanks Adam, that was exactly what I was looking for.
Danny
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ucJkm8TVEHA.2944@.tk2msftngp13.phx.gbl...
> "Danny J. Lesandrini" <dlesandrini@.hotmail.com> wrote in message
> news:eDFKzxTVEHA.2508@.TK2MSFTNGP12.phx.gbl...
> Most likely, yes.
>
> No, in my opinion that would be a good candidate for a clustered index
> as it would create a hotspot at the end of the table, which is good for
> insert performance.
>
> No. But there are other issues to consider. See:
> http://www.aspfaq.com/show.asp?id=2280
>
>
>
>