Index for column with a lot of Null
-
----------------------------------
quote: Creating a composite index will not work in my situation. This is becuase those three columns are in the Where clause of three different SPs.
--------------------------------------
I still don't see why you wouldn't use a composite index. As long as the first column is referenced in the query then the index will be used.
For example, a composite index on AKey, BKey, CKey.
query
Where Akey is not null and Akey = @Akey
Where Akey is not null and Bkey = @Bkey
Where Akey is not null and Ckey = @Ckey
Signature is NULL
-
quote:
I still don't see why you wouldn't use a composite index. As long as the first column is referenced in the query then the index will be used.Calvin,
See tikus' earlier explanation on this. Seems that he does not use the three values in similar queries, so the composite index may not be an appropriate choice (no matter how much we think it might help...)
quote:
...highly transactional table...Tikus, Please post the schema of the table in question. There are a couple of suggestions I would have depending on the design of the table. And, no, I'm not suggesting a redesign of the table; I realize you are constrained to the existing design...
Jay
-
CREATE TABLE [dbo].[CheckQueue] (
[CheckQueueKey] [int] IDENTITY (1, 1) NOT NULL ,
[AppUser] [int] NULL ,
[CmdCodeKey] [smallint] NOT NULL ,
[CustomerKey] [int] NULL ,
[RequestDate] [datetime] NOT NULL ,
[ResponseDate] [datetime] NULL ,
[CheckQueueCodeKey] [smallint] NULL ,
[WebCustomerKey] [int] NULL ,
[ProductKey] [smallint] NULL ,
[PIN] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SerialNbr] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PinKey] [int] NULL ,
[IvrCustomerKey] [int] NULL ,
[TablePK] [int] NULL
) ON [PRIMARY]
GO
CREATE INDEX [IX_CheckQueueWeb] ON [dbo].[CheckQueue]([WebCustomerKey]) WITH FILLFACTOR = 96 ON [PRIMARY]
GO
CREATE INDEX [IX_CheckQueueCust] ON [dbo].[CheckQueue]([CustomerKey]) WITH FILLFACTOR = 95 ON [PRIMARY]
GO
CREATE INDEX [IX_CheckQueueIvr] ON [dbo].[CheckQueue]([IvrCustomerKey]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
CREATE INDEX [CheckQueue9] ON [dbo].[CheckQueue]([ResponseDate], [CmdCodeKey]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[CheckQueue] WITH NOCHECK ADD
CONSTRAINT [PK_CheckQueue] PRIMARY KEY CLUSTERED
(
[CheckQueue]
) WITH FILLFACTOR = 0 ON [PRIMARY]
Edited by - tikus on 08/29/2003 10:46:30 AM
-
tikus,
I was hoping that one of the more hardware-knowledgeable guys would have popped in to this thread by now, but alas, I guess not. I don't think that you are doing anything particularly wrong, given your existing schema can't change. I would say it might be worth experimenting with a test server and perhaps separating the highly utilized indexes onto separate disks...but, from what you've posted, there isn't a whole lot that I can see that is glaringly obvious without knowing the exact configuration of your db. If I were you, I'd try testing different variations of the indexes and fillfactor on a test server to find optimum conditions. If this is a "put-out-this-fire-quickly" kind of situation, I don't see any immediate errors you could fix. Sorry,
Jay
-
This is just a thought, but do you really want the clustered index on the identity column? I know this is standard, but a lot of times there's a better column to be clustering on.
Signature is NULL
-
Yes, Calvin, I read about identity column and Clustered index. Since I am new to this database, I would like to observe more before I start changing things.
And, Jay, I did reindex the indexes on my test database that is located on another server. Guess what, it turned out to be perfectly defragmented 🙁
Well, thank you very much to all of you for your help!!
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply