Help with query rewrite

I have the following nonindexable query due to the "<>" operater. Column "id
"
in the following scenario is a clustered index.
DECLARE @.tid
SET @.tid = 1000
SELECT t.id
FROM table t
WHERE t.id <> @.tid
Is there a way to rewrite such a search condition so as to make it an
indexexable search condition?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1How large is the table? How much of the table is equal to @.tid? Is
t.id unique?
If the test is going to eliminate only a small percentage of the table
then a table scan is the fastest way to get through it. In that case
the most you can hope for is that perhaps clustering on t.id will make
a small difference.
If the test will eliminate a major percentage of the table then a
clustering on t.id would probably help.
Roy Harvey
Beacon Falls, CT
On Wed, 03 May 2006 23:04:56 GMT, "cbrichards" <u3288@.uwe> wrote:

>I have the following nonindexable query due to the "<>" operater. Column "i
d"
>in the following scenario is a clustered index.
>DECLARE @.tid
>SET @.tid = 1000
>SELECT t.id
>FROM table t
>WHERE t.id <> @.tid
>
>Is there a way to rewrite such a search condition so as to make it an
>indexexable search condition?|||If you have a clustered index on the "id" column, the index will get used in
the search, but it will be an index scan rather than a index seek(which will
be in case of =), for the simple reason that you are not trying to find one
value, you are trying to eleminate a value, so it has to compare it against
every value in the index. So I am assuming you are looking for an index see
k
rather than a scan,
You can try doing this
DECLARE @.tid int
SET @.tid = 1000
SELECT t.id
FROM table t
WHERE t.id <> @.tid
and t.id>0
I am not sure if you can make this assumption that "id" will always be > 0,
this actually runs an index seek on the table. The plan improves if you have
this inside a stored proc, as the query plan gets cached.
As Roy pointed out this can be a very expensive query without anything else
in your where clause depending on the size of the data. It almost took 31
seconds for me to run this on 11 Million records. If I was you I would look
at changing the query and including some more filtering in the where clause.
HTH
RA
"Roy Harvey" wrote:

> How large is the table? How much of the table is equal to @.tid? Is
> t.id unique?
> If the test is going to eliminate only a small percentage of the table
> then a table scan is the fastest way to get through it. In that case
> the most you can hope for is that perhaps clustering on t.id will make
> a small difference.
> If the test will eliminate a major percentage of the table then a
> clustering on t.id would probably help.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 03 May 2006 23:04:56 GMT, "cbrichards" <u3288@.uwe> wrote:
>
>|||Thanks Rocky.
The "id" column is a nonclustered composite index with another column name
"col_k". The index was created with in this order (id, col_k).
Column "id" is an identity column and column "col_k" is not very unique.
There are approximately 15,000 records in the table.
Since Column "id" is first in the composite index, it seems like it could be
used in the revised query you wrote, but perhaps the optimizer believes a
scan is still faster than using the query (and yes, I have run sp_updatestat
s)
.
I further rewrote the query to give it an extra filter in the WHERE clause:
DECLARE @.tid int
DECLARE @.colk int
SET @.tid = 1000
SET @.colk = 5
SELECT t.id
FROM table t
WHERE t.id <> @.tid
and t.id>0
and t.col_k = @.colk
However, my logical reads have not improved from the original and it is stil
l
performing a Clustered Index Scan (using the clustered index which is a
datetime field), which to me in this case, is in essence a table scan.
Any further ideas or suggestions to have it use the composite index would be
appreciated.
Rocky A wrote:[vbcol=seagreen]
>If you have a clustered index on the "id" column, the index will get used i
n
>the search, but it will be an index scan rather than a index seek(which wil
l
>be in case of =), for the simple reason that you are not trying to find one
>value, you are trying to eleminate a value, so it has to compare it against
>every value in the index. So I am assuming you are looking for an index se
ek
>rather than a scan,
>You can try doing this
>DECLARE @.tid int
>SET @.tid = 1000
>SELECT t.id
>FROM table t
>WHERE t.id <> @.tid
>and t.id>0
>I am not sure if you can make this assumption that "id" will always be > 0,
>this actually runs an index seek on the table. The plan improves if you hav
e
>this inside a stored proc, as the query plan gets cached.
>As Roy pointed out this can be a very expensive query without anything else
>in your where clause depending on the size of the data. It almost took 31
>seconds for me to run this on 11 Million records. If I was you I would look
>at changing the query and including some more filtering in the where clause
.
>HTH
>RA
>
>[quoted text clipped - 22 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||RA,
Is this your real query, or a simplified version?
If id is the only column you are selecting, then one would expect that
SQL-Server would scan the smallest index that contains the id column.
You did not post DDL, so we cannot check this.
If you are selecting other columns than the ones in the nonclustered
index and clustered index, then the nonclustered index will (most
likely) not be used, because that would require bookmark lookups for too
many rows. In that case, a clustered index scan would simply be faster.
Gert-Jan
"cbrichards via droptable.com" wrote:
> Thanks Rocky.
> The "id" column is a nonclustered composite index with another column name
> "col_k". The index was created with in this order (id, col_k).
> Column "id" is an identity column and column "col_k" is not very unique.
> There are approximately 15,000 records in the table.
> Since Column "id" is first in the composite index, it seems like it could
be
> used in the revised query you wrote, but perhaps the optimizer believes a
> scan is still faster than using the query (and yes, I have run sp_updatest
ats)
> .
> I further rewrote the query to give it an extra filter in the WHERE clause
:
> DECLARE @.tid int
> DECLARE @.colk int
> SET @.tid = 1000
> SET @.colk = 5
> SELECT t.id
> FROM table t
> WHERE t.id <> @.tid
> and t.id>0
> and t.col_k = @.colk
> However, my logical reads have not improved from the original and it is st
ill
> performing a Clustered Index Scan (using the clustered index which is a
> datetime field), which to me in this case, is in essence a table scan.
> Any further ideas or suggestions to have it use the composite index would
be
> appreciated.
> Rocky A wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1|||given the specific example, i would expect the engine to use any index
it can.
however, sending the data across the wire will be the bottleneck, so no
matter what it probably take as long as it takes to send the data.

Help with query rewrite

I have the following nonindexable query due to the "<>" operater. Column "id"
in the following scenario is a clustered index.
DECLARE @.tid
SET @.tid = 1000
SELECT t.id
FROM table t
WHERE t.id <> @.tid
Is there a way to rewrite such a search condition so as to make it an
indexexable search condition?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1How large is the table? How much of the table is equal to @.tid? Is
t.id unique?
If the test is going to eliminate only a small percentage of the table
then a table scan is the fastest way to get through it. In that case
the most you can hope for is that perhaps clustering on t.id will make
a small difference.
If the test will eliminate a major percentage of the table then a
clustering on t.id would probably help.
Roy Harvey
Beacon Falls, CT
On Wed, 03 May 2006 23:04:56 GMT, "cbrichards" <u3288@.uwe> wrote:
>I have the following nonindexable query due to the "<>" operater. Column "id"
>in the following scenario is a clustered index.
>DECLARE @.tid
>SET @.tid = 1000
>SELECT t.id
>FROM table t
>WHERE t.id <> @.tid
>
>Is there a way to rewrite such a search condition so as to make it an
>indexexable search condition?|||If you have a clustered index on the "id" column, the index will get used in
the search, but it will be an index scan rather than a index seek(which will
be in case of =), for the simple reason that you are not trying to find one
value, you are trying to eleminate a value, so it has to compare it against
every value in the index. So I am assuming you are looking for an index seek
rather than a scan,
You can try doing this
DECLARE @.tid int
SET @.tid = 1000
SELECT t.id
FROM table t
WHERE t.id <> @.tid
and t.id>0
I am not sure if you can make this assumption that "id" will always be > 0,
this actually runs an index seek on the table. The plan improves if you have
this inside a stored proc, as the query plan gets cached.
As Roy pointed out this can be a very expensive query without anything else
in your where clause depending on the size of the data. It almost took 31
seconds for me to run this on 11 Million records. If I was you I would look
at changing the query and including some more filtering in the where clause.
HTH
RA
"Roy Harvey" wrote:
> How large is the table? How much of the table is equal to @.tid? Is
> t.id unique?
> If the test is going to eliminate only a small percentage of the table
> then a table scan is the fastest way to get through it. In that case
> the most you can hope for is that perhaps clustering on t.id will make
> a small difference.
> If the test will eliminate a major percentage of the table then a
> clustering on t.id would probably help.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 03 May 2006 23:04:56 GMT, "cbrichards" <u3288@.uwe> wrote:
> >I have the following nonindexable query due to the "<>" operater. Column "id"
> >in the following scenario is a clustered index.
> >
> >DECLARE @.tid
> >SET @.tid = 1000
> >
> >SELECT t.id
> >FROM table t
> >WHERE t.id <> @.tid
> >
> >
> >Is there a way to rewrite such a search condition so as to make it an
> >indexexable search condition?
>|||Thanks Rocky.
The "id" column is a nonclustered composite index with another column name
"col_k". The index was created with in this order (id, col_k).
Column "id" is an identity column and column "col_k" is not very unique.
There are approximately 15,000 records in the table.
Since Column "id" is first in the composite index, it seems like it could be
used in the revised query you wrote, but perhaps the optimizer believes a
scan is still faster than using the query (and yes, I have run sp_updatestats)
.
I further rewrote the query to give it an extra filter in the WHERE clause:
DECLARE @.tid int
DECLARE @.colk int
SET @.tid = 1000
SET @.colk = 5
SELECT t.id
FROM table t
WHERE t.id <> @.tid
and t.id>0
and t.col_k = @.colk
However, my logical reads have not improved from the original and it is still
performing a Clustered Index Scan (using the clustered index which is a
datetime field), which to me in this case, is in essence a table scan.
Any further ideas or suggestions to have it use the composite index would be
appreciated.
Rocky A wrote:
>If you have a clustered index on the "id" column, the index will get used in
>the search, but it will be an index scan rather than a index seek(which will
>be in case of =), for the simple reason that you are not trying to find one
>value, you are trying to eleminate a value, so it has to compare it against
>every value in the index. So I am assuming you are looking for an index seek
>rather than a scan,
>You can try doing this
>DECLARE @.tid int
>SET @.tid = 1000
>SELECT t.id
>FROM table t
>WHERE t.id <> @.tid
>and t.id>0
>I am not sure if you can make this assumption that "id" will always be > 0,
>this actually runs an index seek on the table. The plan improves if you have
>this inside a stored proc, as the query plan gets cached.
>As Roy pointed out this can be a very expensive query without anything else
>in your where clause depending on the size of the data. It almost took 31
>seconds for me to run this on 11 Million records. If I was you I would look
>at changing the query and including some more filtering in the where clause.
>HTH
>RA
>> How large is the table? How much of the table is equal to @.tid? Is
>> t.id unique?
>[quoted text clipped - 22 lines]
>> >Is there a way to rewrite such a search condition so as to make it an
>> >indexexable search condition?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||RA,
Is this your real query, or a simplified version?
If id is the only column you are selecting, then one would expect that
SQL-Server would scan the smallest index that contains the id column.
You did not post DDL, so we cannot check this.
If you are selecting other columns than the ones in the nonclustered
index and clustered index, then the nonclustered index will (most
likely) not be used, because that would require bookmark lookups for too
many rows. In that case, a clustered index scan would simply be faster.
Gert-Jan
"cbrichards via SQLMonster.com" wrote:
> Thanks Rocky.
> The "id" column is a nonclustered composite index with another column name
> "col_k". The index was created with in this order (id, col_k).
> Column "id" is an identity column and column "col_k" is not very unique.
> There are approximately 15,000 records in the table.
> Since Column "id" is first in the composite index, it seems like it could be
> used in the revised query you wrote, but perhaps the optimizer believes a
> scan is still faster than using the query (and yes, I have run sp_updatestats)
> .
> I further rewrote the query to give it an extra filter in the WHERE clause:
> DECLARE @.tid int
> DECLARE @.colk int
> SET @.tid = 1000
> SET @.colk = 5
> SELECT t.id
> FROM table t
> WHERE t.id <> @.tid
> and t.id>0
> and t.col_k = @.colk
> However, my logical reads have not improved from the original and it is still
> performing a Clustered Index Scan (using the clustered index which is a
> datetime field), which to me in this case, is in essence a table scan.
> Any further ideas or suggestions to have it use the composite index would be
> appreciated.
> Rocky A wrote:
> >If you have a clustered index on the "id" column, the index will get used in
> >the search, but it will be an index scan rather than a index seek(which will
> >be in case of =), for the simple reason that you are not trying to find one
> >value, you are trying to eleminate a value, so it has to compare it against
> >every value in the index. So I am assuming you are looking for an index seek
> >rather than a scan,
> >
> >You can try doing this
> >
> >DECLARE @.tid int
> >SET @.tid = 1000
> >
> >SELECT t.id
> >FROM table t
> >WHERE t.id <> @.tid
> >and t.id>0
> >
> >I am not sure if you can make this assumption that "id" will always be > 0,
> >this actually runs an index seek on the table. The plan improves if you have
> >this inside a stored proc, as the query plan gets cached.
> >
> >As Roy pointed out this can be a very expensive query without anything else
> >in your where clause depending on the size of the data. It almost took 31
> >seconds for me to run this on 11 Million records. If I was you I would look
> >at changing the query and including some more filtering in the where clause.
> >
> >HTH
> >RA
> >
> >> How large is the table? How much of the table is equal to @.tid? Is
> >> t.id unique?
> >[quoted text clipped - 22 lines]
> >> >Is there a way to rewrite such a search condition so as to make it an
> >> >indexexable search condition?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||given the specific example, i would expect the engine to use any index
it can.
however, sending the data across the wire will be the bottleneck, so no
matter what it probably take as long as it takes to send the data.

Help with Query

I am struggling to build the appropriate query for the following scenario. A
simplified version of my problem is... consider the following table
EEID EPID StartDate
1 11 1/1/2005
1 13 2/1/2005 *
2 13 1/1/2005 *
3 14 2/1/2005
3 11 4/1/2005 *
The star indicates the records that should be pulled, and I need all 3
fields. The condition is, for each EEID pull the record with the largest
date.
If i do something like
select EEID, max(StartDate) from table group by EEID
I get the appropriate row, but i am missing one field.
Please help. I have struggled long and hard on this one.
thank You.
PooravI am , what field are you missing?
Message posted via http://www.webservertalk.com|||Select * From Table T
Where StartDate = (Select Max(StartDate)
From Table
Where EEID = T.EEID)
"haiiyaa" wrote:

> I am struggling to build the appropriate query for the following scenario.
A
> simplified version of my problem is... consider the following table
> EEID EPID StartDate
> 1 11 1/1/2005
> 1 13 2/1/2005 *
> 2 13 1/1/2005 *
> 3 14 2/1/2005
> 3 11 4/1/2005 *
> The star indicates the records that should be pulled, and I need all 3
> fields. The condition is, for each EEID pull the record with the largest
> date.
> If i do something like
> select EEID, max(StartDate) from table group by EEID
> I get the appropriate row, but i am missing one field.
> Please help. I have struggled long and hard on this one.
> thank You.
> Poorav
>