Non Clustered Index Problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Non Clustered Index Problems

Author

Topic

darinh
Yak Posting Veteran

58 Posts

Posted - 2006-08-25 : 01:45:10

Hi,

I have a table with 12 million rows of data that we use for data mining. It is structured like this


CREATE TABLE [dbo].[NodeBDataTable] (
	[ID] [bigint] NOT NULL ,
	[NodeBID] [int] NOT NULL ,
	[AddDate] [datetime] NOT NULL 
) ON [PRIMARY]
GO


CREATE 
  INDEX [IX_AddDate] ON [dbo].[NodeBDataTable] ([AddDate])
WITH
    FILLFACTOR = 90
) ON [PRIMARY]
GO

This is the only Index on the AddDate column

I have only shown the relevant columns and Indexes of Interest here but there are lots more

If I run the following query on it


Declare @Startdate datetime
Declare @EndDate datetime

set @startdate = getdate() - 5
set @enddate = getdate() - 4


/**Query A **/
select min(ID) from NodeBDataTable where adddate between getdate() - 5 and  getdate() - 4 

/** Query B */
select min(ID) from NodeBDataTable where adddate between @startdate and  @enddate

Query A does a Non-clustered Index seek and takes a fraction of a second, but Query B does a table scan and takes 3 minutes. That is disappointing as queries of Type A are fairly useless to us. Query A working as expected would seem to prove that the Index is OK, has statistics etc.

As far as I can tell, both queries are exactly the same, so should behave the same. Can anyone see what I have missed?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-25 : 02:13:21

How do Query C perform?

set @startdate = getdate()
set @enddate = @startdate

/** Query C */
select min(ID) from NodeBDataTable where adddate between @startdate -5 and @enddate - 4

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-26 : 17:12:24

Have you considered a clustered index on ID? That would make the index covering and probably solve the problem.
Another option is to include ID in the AddDate index.

Could try an index hint or dynamic sql?

Other than that it's a matter of trying various types of query to see what works.

An in clause with a subquery getting the distinct dates.
Get the dates into a temp table then join to it.

I'm going through a similar thing with a climt of mine and trying to get the developers to notice when they are table scanning.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-08-26 : 19:02:16

Create stored procedure with @startDate and @endDate as input parameters. It should work fine. When you run query with local variables, query analyzer doesn't know its values at compilation time so it doesn't use statistics. This is the reason for poor execution plan choice. When stored procedure execution plan is built, procedure parameter values are known, so I expect the non-clustered index to be used.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources