Index Usage - 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 2005 Forums

Transact-SQL (2005)

Index Usage

Author

Topic

dataczar
Starting Member

18 Posts

Posted - 2009-04-09 : 00:20:57

I have the following table:
CREATE TABLE [dbo].[POLICIES](
[policy_id] [int] NOT NULL,
[row_begin_date] [datetime] NOT NULL,
[row_end_date] [datetime] NOT NULL CONSTRAINT [DF_POLICIES_row_end_date] DEFAULT ('9999-12-31'),
[policy_number] [varchar](25) NOT NULL,
[effective_date] [datetime] NULL,
[termination_date] [datetime] NULL,
[customer_id] [int] NULL,
[company_id] [int] NOT NULL,
[product_id] [int] NOT NULL,
[policy_form_id] [int] NULL,
[renewability_code] [int] NULL,
[base_policy_type_code] [varchar](4) NOT NULL,
[audit_id] [int] NOT NULL,
CONSTRAINT [PK_POLICIES] PRIMARY KEY CLUSTERED
(
[policy_id] ASC,
[row_begin_date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

When I run the following query on the table it is doing a Clustered Index Scan and I am wondering if there is anything I can do about it.

select *
from policies
where getdate() between row_begin_date and row_end_date

Any help on this would be appreciated. This query comes back quickly, but when this table is joined to other tables that are using row_begin and row_end dates then they are all performing a Clustered Index Scan, which is effecting performance.

asgast
Posting Yak Master

149 Posts

Posted - 2009-04-09 : 03:29:29

As I understand you already have index on row_begin_date and row_end_date, correct?

Then try changing your where condition.

dataczar
Starting Member

18 Posts

Posted - 2009-04-09 : 07:11:08

I only have a clustered index on policy_id and row_begin_date. There is not an index on row_end_date.

asgast
Posting Yak Master

149 Posts

Posted - 2009-04-09 : 10:16:33

then add it also remove function from where

change it to

DECLARE @date

SET @date = GETDATE()

....
WHERE @date between row_begin_date and row_end_date

try not to use non deterministic functions in where

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-09 : 12:20:00

is ALLOW_PAGE_LOCKS = ON a good thing to do?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

dataczar
Starting Member

18 Posts

Posted - 2009-04-09 : 12:47:47

If I were to replace getdate() with '2009-04-09' the result would be the same thing. I had just put getdate() in the example.

I also did a test with row_end_date added to the clustered Index and it produced the same result of an index scan.

asgast
Posting Yak Master

149 Posts

Posted - 2009-04-10 : 03:53:46

ALLOW_PAGE_LOCKS = ON is a default thing

asgast
Posting Yak Master

149 Posts

Posted - 2009-04-10 : 04:32:49

can i see some sample data to try and see how it will work?

also ou can try to optimize the join instead of indexes

xpandre
Posting Yak Master

212 Posts

Posted - 2009-04-10 : 05:07:43

you can try reversing the order of of the index created.
[row_begin_date] ASC,
[policy_id] ASC

But this is assuming policy_id would be used alone sparingly in your application, as a query as where policy_id = anything would go for a scan rather than a seek!

dataczar
Starting Member

18 Posts

Posted - 2009-04-10 : 11:14:28

Here is some sampe data:
BEGIN TRANSACTION;
INSERT INTO [dbo].[POLICIES]([policy_id], [row_begin_date], [row_end_date], [policy_number], [effective_date], [termination_date], [customer_id], [company_id], [product_id], [policy_form_id], [renewability_code], [base_policy_type_code], [audit_id])
SELECT 7739, '20080604 00:00:00.000', '99991231 00:00:00.000', N'0C41665', '19890818 00:00:00.000', NULL, 5113, 2, 19, 4148, 3, N'3358', 1647 UNION ALL
SELECT 7740, '19000101 00:00:00.000', '20080603 00:00:00.000', N'0C41669', '19890819 00:00:00.000', NULL, 1773, 2, 19, 4172, NULL, N'3358', 254 UNION ALL
SELECT 7740, '20080604 00:00:00.000', '99991231 00:00:00.000', N'0C41669', '19890819 00:00:00.000', NULL, 1773, 2, 19, 4172, 3, N'3358', 1647 UNION ALL
SELECT 7741, '19000101 00:00:00.000', '20080603 00:00:00.000', N'0C41682', '19890811 00:00:00.000', '20040301 00:00:00.000', 135649, 2, 19, 4200, NULL, N'3358', 254 UNION ALL
SELECT 7741, '20080604 00:00:00.000', '99991231 00:00:00.000', N'0C41682', '19890811 00:00:00.000', '20040301 00:00:00.000', 135649, 2, 19, 4200, 3, N'3358', 1647 UNION ALL
SELECT 7742, '19000101 00:00:00.000', '20080603 00:00:00.000', N'0C41689', '19890811 00:00:00.000', '20030901 00:00:00.000', 110456, 2, 19, 4200, NULL, N'3358', 254 UNION ALL
SELECT 7742, '20080604 00:00:00.000', '99991231 00:00:00.000', N'0C41689', '19890811 00:00:00.000', '20030901 00:00:00.000', 110456, 2, 19, 4200, 3, N'3358', 1647 UNION ALL
SELECT 7743, '19000101 00:00:00.000', '20080603 00:00:00.000', N'0C41690', '19890818 00:00:00.000', '20021101 00:00:00.000', 48556, 2, 19, 4200, NULL, N'3358', 254 UNION ALL
SELECT 7743, '20080604 00:00:00.000', '99991231 00:00:00.000', N'0C41690', '19890818 00:00:00.000', '20021101 00:00:00.000', 48556, 2, 19, 4200, 3, N'3358', 1647 UNION ALL
SELECT 7744, '19000101 00:00:00.000', '20080603 00:00:00.000', N'0C41715', '19890818 00:00:00.000', '20000626 00:00:00.000', 52837, 2, 19, 4170, NULL, N'3358', 254
COMMIT;
RAISERROR (N'[dbo].[POLICIES]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources