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.developersdex.com ***> where hsi.itemdata.status + 0 = 0
Never do calculation on the column side if you can avoid. Why the + 0? wouldn't it be the same as:
where hsi.itemdata.status = 0
Now, whether an index on that columns can be used depends on the selectivity for the condition.
Also, if you have an index on the itemtypenum column, it might be used (4 times because of the ORs),
but again, whether or not it is used depends on the selectivity.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"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.developersdex.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.developersdex.com ***|||what is the ORDER By 8?
--
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=uk/">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.developersdex.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.|||Toni,
it is not a good practice to manipulate columns in the "where" clause. SQL
Server will not try to use statistics for those columnas in case they exists.
> hsi.itemdata.status + 0 = 0
Compare the execution plans between that statement and one using:
...
where
hsi.itemdata.status = 0 and
( hsi.itemdata.itemtypenum = 101 or
hsi.itemdata.itemtypenum = 102 or
hsi.itemdata.itemtypenum = 103 or
hsi.itemdata.itemtypenum = 329 )
...
Also check fragmentation (dbcc showcontig) in that table and, if possible,
create a clustered index. It has some rows for a heap.
AMB
"Toni" 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.developersdex.com ***
>|||Jack Vamvas,
> what is the ORDER By 8?
Order by the 8th column in the resultset. Not a good practice to use on
production code.
AMB
"Jack Vamvas" wrote:
> what is the ORDER By 8?
> --
> Jack Vamvas
> ___________________________________
> The latest IT jobs - www.ITjobfeed.com
> <a href="http://links.10026.com/?link=uk/">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.developersdex.com ***
>
>