Query Performance

I have a vendor application running on SQL 2000 sp4. I've got a query
that isn't using any indexes:
select hsi.itemdata.itemnum, hsi.itemdata.itemname,
hsi.itemdata.batchnum, hsi.itemdata.status,
hsi.itemdata.itemtypegroupnum,
hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
hsi.itemdata.datestored, hsi.itemdata.usernum,
hsi.itemdata.deleteusernum,
hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
hsi.itemdata.institution, hsi.itemdata.maxdocrev
from hsi.itemdata
where hsi.itemdata.status + 0 = 0 and
( hsi.itemdata.itemtypenum = 101 or
hsi.itemdata.itemtypenum = 102 or
hsi.itemdata.itemtypenum = 103 or
hsi.itemdata.itemtypenum = 329 )
order by 8 desc
The table has 3,105,135 records in test. (There are 22,905,590 in
production.)
CREATE TABLE [hsi].[itemdata] (
[itemnum] [int] NOT NULL ,
[itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[batchnum] [int] NULL ,
[status] [int] NULL ,
[itemtypegroupnum] [int] NULL ,
[itemtypenum] [int] NULL ,
[itrevnum] [int] NULL ,
[itemdate] [datetime] NULL ,
[datestored] [datetime] NULL ,
[usernum] [int] NULL ,
[deleteusernum] [int] NULL ,
[securityvalue] [int] NULL ,
[doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[institution] [int] NULL ,
[maxdocrev] [int] NULL
) ON [DBSpace2]
GO
CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
[itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
GO
CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
[status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
GO
CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
[itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
[DBSpace2i]
GO
CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
[itemnum]) ON [DBSpace2]
GO
I would have thought it would use one of the existing indexes but it
just does a table scan.
I also tried:
CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
[itemtypenum]) ON [PRIMARY]
GO
But that didn't work either.
Any suggestions?
Thank you,
Toni
*** Sent via Developersdex http://www.codecomments.com ***
The information provided raises more questions than it allows answers.
Indexes will only be used if the optimizer calculates that it will be
more efficient than a table scan. This generally depends on how
selective the indexes are, particularly for the values used in the
query.
How selective is itemtypenum? How selective are the specific values
of itemtypenum? What percentage of the table will the numbers
returned by the query represent?
SELECT hsi.itemdata.itemtypenum, count(*) as Rows
FROM hsi.itemdata
WHERE hsi.itemdata.itemtypenum IN (101, 102, 103, 329)
GROUP BY hsi.itemdata.itemtypenum
How selective is the test " hsi.itemdata.status + 0 = 0"? What data
type is hsi.itemdata.status? Why the + 0? It is possible that IF a
relatively small percentage of rows have a status = 0 that including
status in an index might help - but not while the + 0 is there.
Roy Harvey
Beacon Falls, CT
On Thu, 08 Mar 2007 07:24:39 -0800, Toni <teibner@.allinasql.com>
wrote:

>I have a vendor application running on SQL 2000 sp4. I've got a query
>that isn't using any indexes:
>select hsi.itemdata.itemnum, hsi.itemdata.itemname,
>hsi.itemdata.batchnum, hsi.itemdata.status,
>hsi.itemdata.itemtypegroupnum,
>hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
>hsi.itemdata.datestored, hsi.itemdata.usernum,
>hsi.itemdata.deleteusernum,
>hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
>hsi.itemdata.institution, hsi.itemdata.maxdocrev
>from hsi.itemdata
>where hsi.itemdata.status + 0 = 0 and
>( hsi.itemdata.itemtypenum = 101 or
>hsi.itemdata.itemtypenum = 102 or
>hsi.itemdata.itemtypenum = 103 or
>hsi.itemdata.itemtypenum = 329 )
>order by 8 desc
>The table has 3,105,135 records in test. (There are 22,905,590 in
>production.)
>CREATE TABLE [hsi].[itemdata] (
>[itemnum] [int] NOT NULL ,
>[itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[batchnum] [int] NULL ,
>[status] [int] NULL ,
>[itemtypegroupnum] [int] NULL ,
>[itemtypenum] [int] NULL ,
>[itrevnum] [int] NULL ,
>[itemdate] [datetime] NULL ,
>[datestored] [datetime] NULL ,
>[usernum] [int] NULL ,
>[deleteusernum] [int] NULL ,
>[securityvalue] [int] NULL ,
>[doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>,
>[institution] [int] NULL ,
>[maxdocrev] [int] NULL
>) ON [DBSpace2]
>GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
>[itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
>GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
>[status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
>GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
>[itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
>[DBSpace2i]
>GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
>[itemnum]) ON [DBSpace2]
>GO
>I would have thought it would use one of the existing indexes but it
>just does a table scan.
>I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
>[itemtypenum]) ON [PRIMARY]
>GO
>But that didn't work either.
>Any suggestions?
>Thank you,
>Toni
>*** Sent via Developersdex http://www.codecomments.com ***
|||what is the ORDER By 8?
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Toni" <teibner@.allinasql.com> wrote in message
news:ul%23akXZYHHA.992@.TK2MSFTNGP02.phx.gbl...
>I have a vendor application running on SQL 2000 sp4. I've got a query
> that isn't using any indexes:
> select hsi.itemdata.itemnum, hsi.itemdata.itemname,
> hsi.itemdata.batchnum, hsi.itemdata.status,
> hsi.itemdata.itemtypegroupnum,
> hsi.itemdata.itemtypenum, hsi.itemdata.itrevnum, hsi.itemdata.itemdate,
> hsi.itemdata.datestored, hsi.itemdata.usernum,
> hsi.itemdata.deleteusernum,
> hsi.itemdata.securityvalue, hsi.itemdata.doctracenumber,
> hsi.itemdata.institution, hsi.itemdata.maxdocrev
> from hsi.itemdata
> where hsi.itemdata.status + 0 = 0 and
> ( hsi.itemdata.itemtypenum = 101 or
> hsi.itemdata.itemtypenum = 102 or
> hsi.itemdata.itemtypenum = 103 or
> hsi.itemdata.itemtypenum = 329 )
> order by 8 desc
> The table has 3,105,135 records in test. (There are 22,905,590 in
> production.)
> CREATE TABLE [hsi].[itemdata] (
> [itemnum] [int] NOT NULL ,
> [itemname] [char] (101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [batchnum] [int] NULL ,
> [status] [int] NULL ,
> [itemtypegroupnum] [int] NULL ,
> [itemtypenum] [int] NULL ,
> [itrevnum] [int] NULL ,
> [itemdate] [datetime] NULL ,
> [datestored] [datetime] NULL ,
> [usernum] [int] NULL ,
> [deleteusernum] [int] NULL ,
> [securityvalue] [int] NULL ,
> [doctracenumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [institution] [int] NULL ,
> [maxdocrev] [int] NULL
> ) ON [DBSpace2]
> GO
> CREATE INDEX [itemdata10] ON [hsi].[itemdata]([itemtypenum],
> [itemdate] DESC , [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE INDEX [itemdata13] ON [hsi].[itemdata]([batchnum], [itemnum],
> [status]) WITH FILLFACTOR = 90 ON [DBSpace2i]
> GO
> CREATE UNIQUE INDEX [itemdata9] ON [hsi].[itemdata]([itemnum],
> [itemdate] DESC , [itemtypenum], [status]) WITH FILLFACTOR = 90 ON
> [DBSpace2i]
> GO
> CREATE INDEX [ccitemdata8] ON [hsi].[itemdata]([datestored],
> [itemnum]) ON [DBSpace2]
> GO
> I would have thought it would use one of the existing indexes but it
> just does a table scan.
> I also tried:
> CREATE INDEX [IX_itemdata] ON [hsi].[itemdata]([status],
> [itemtypenum]) ON [PRIMARY]
> GO
> But that didn't work either.
> Any suggestions?
> Thank you,
> Toni
> *** Sent via Developersdex http://www.codecomments.com ***
|||On Thu, 8 Mar 2007 17:03:46 -0000, "Jack Vamvas"
<DEL_TO_REPLY@.del.com> wrote:

>what is the ORDER By 8?
That says to order the eighth column in the SELECT list. My
understanding is that this syntax is deprecated in the SQL standard,
and it certainly has no place in production code, but it is a handy
shortcut when doing quick and dirty SQL.
Roy Harvey
Beacon Falls, CT
|||On Thu, 08 Mar 2007 12:22:58 -0500, Roy Harvey <roy_harvey@.snet.net>
wrote:

>That says to order the eighth column in the SELECT list.
Make that:
That says to order BY the eighth column in the SELECT list.
|||This is code from a vendor application. I don't have any control over
it other than to recommend they make changes. As to the why of some of
the questions, I can answer some of them.
I don't know why the vendor is doing the calculation of
hsi.itemdata.status + 0 = 0, unfortunately.
I guess it's not real selective at all. (There are only 613 different
values in the table in test.) For this specific query, this is what is
in the table:
itemtypenum rows
101 127282
102 100347
103 6901
329 387
I apologize for my lack of knowledge on this. I'm not very experienced
with query tuning.
It sounds like the whole query needs to be rewritten in order to be
efficient and make use of any indexes that might exist on the table. Is
that correct?
I appreciate all the responses!
Thank you so much
Toni
*** Sent via Developersdex http://www.codecomments.com ***
|||Since there are 3.1 million rows in the table, and the query returns
almost 0.3 million, the selectivity on itemtypenum is roughly ten
percent. That really isn't selective enough for an index on
itemtypenum to help this query. Had status been selective, and the
query written without the silly + 0, an index on (status, itemtypenum)
might have been of some use. As it stands I do not see any
oportunities for improvement.
Roy Harvey
Beacon Falls, CT
On Thu, 08 Mar 2007 10:02:41 -0800, Toni <teibner@.allinasql.com>
wrote:

>This is code from a vendor application. I don't have any control over
>it other than to recommend they make changes. As to the why of some of
>the questions, I can answer some of them.
>I don't know why the vendor is doing the calculation of
>hsi.itemdata.status + 0 = 0, unfortunately.
>I guess it's not real selective at all. (There are only 613 different
>values in the table in test.) For this specific query, this is what is
>in the table:
>itemtypenum rows
>101 127282
>102 100347
>103 6901
>329 387
>I apologize for my lack of knowledge on this. I'm not very experienced
>with query tuning.
>It sounds like the whole query needs to be rewritten in order to be
>efficient and make use of any indexes that might exist on the table. Is
>that correct?
>I appreciate all the responses!
>Thank you so much
>Toni
>*** Sent via Developersdex http://www.codecomments.com ***