RE: Clustered Index - Parameterized queries

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Avdhut Kavdikar (avdhut_kavdikar_at_hotmail.com)
Date: 07/22/04 

Date: Thu, 22 Jul 2004 03:31:01 -0700

Hi, Pankaj

Thanks for the reponse for my query on the news group. I am listing down the
problem and the required information.

PROBLEM :
We have a clustered index on 4 fields in a table. When a parameterized query
is fired from the client, which has all the four index fields, in order, in
its where clause, a clustered index seek is being made with only one of the
fields (which incidentaly is an INTEGER field) . As a result , the query is
taking lot of time. The index tuning wizard is suggesting an additional
non-clustered index on the INTEGER field. And when the non-clustered index
is created the result drastically improves. A relevant information is the
parameretized query is fired through sp_ExecuteSql. The question is why is
it not using all the fields in the index though the values which are being
supplied and how it is creating an additional non-clustered index on one of
the fields helping the performance because anyway it has to go through the
clustered index pages after finding the matching clustered index keys
through the non-clustered index search. When the exact values are provided,
instead of the parameters the clustered index is used and the response is
also extremely fast. The index tuning wizard is also not suggesting any
additional index in this case. Is there any different startegy used
iternally for parameterized queries with respect to index usage.

Current Structure:
CREATE TABLE [dbo].[W42_TREATMENT_PLAN] (
        [W42_ACCESS_CD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
        [W42_MEMB_CD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
        [W42_ACCT_NO_SEQ] [int] NOT NULL ,
        [W42_TREAT_NUM] [smallint] NOT NULL ,
        [W42_DATE_ESTAB] [smalldatetime] NOT NULL ,
        [W42_EXPIRE_DATE] [smalldatetime] NULL ,
        [W42_PRVDR_CD] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
        [W42_VISIT_STTS] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
        [W42_PLACE_CD] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
        [W42_REEMB_MAX] [int] NULL ,
        [W42_REF_NUM] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [W42_APPT_DATE] [smalldatetime] NULL ,
        [W42_DATE_CHANGED] [smalldatetime] NULL ,
        [W42_CHANGED_BY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
        [W42_SERV_CD] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
        [W42_LINE_NUM] [int] NULL ,
        [W42_COLLECTOR] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
        [W42_TIME_DURATION] [int] NULL ,
        [W42_CCARE_DATE] [smalldatetime] NULL ,
        [W42_STATE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [W42_CCARE_MONTHS] [smallint] NULL ,
        [W42_CCARE_CD] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
        [W42_TIMES_PRINTED] [smallint] NULL ,
        [W42_LAST_PRINTED_DATE] [smalldatetime] NULL ,
        [W42_TIME_CHANGED] [smalldatetime] NULL ,
        [W42_TIME_ESTAB] [smalldatetime] NULL ,
        [W42_COMMENTS] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
        [W42_ACCT_TYP_CD] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
        [W42_REC_TIMESTAMP] [timestamp] NOT NULL ,
        [W42_SEARCH_KEY] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
        [W42_APPT_TIME] [smalldatetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[W42_TREATMENT_PLAN] WITH NOCHECK ADD
         PRIMARY KEY CLUSTERED
        ( [W42_ACCESS_CD],[W42_ACCT_NO_SEQ],[W42_MEMB_CD],[W42_TREAT_NUM])
ON [PRIMARY]
GO

 CREATE INDEX [W42_2] ON [dbo].[W42_TREATMENT_PLAN]([W42_ACCT_NO_SEQ]) WITH
FILLFACTOR = 75 ON [PRIMARY]
GO

ALTER TABLE [dbo].[W42_TREATMENT_PLAN] ADD
         FOREIGN KEY
        ( [W42_ACCT_TYP_CD] ) REFERENCES [dbo].[W15_ACCT_TYPES]
([W15_ACCT_TYP_CD]),
         FOREIGN KEY
        ([W42_ACCESS_CD], [W42_ACCT_NO_SEQ],[W42_MEMB_CD] ) REFERENCES
[dbo].[W22_PATIENT_ACCT] (
                [W22_ACCESS_CD], [W22_ACCT_NO_SEQ],[W22_MEMB_CD]),
        CONSTRAINT [FK_W42_TREATMENT_PLAN_1] FOREIGN KEY
        ([W42_ACCESS_CD], [W42_PLACE_CD]) REFERENCES
[dbo].[W52_LOCATIONS] ([W52_ACCESS_CD], [W52_PLACE_CD])
GO

I think this can give you enough information on the problem. If you require
some more information pl. let me know as I am preety stuck up on this issue
to find the solution for the same.

Pankay, If you can share me with your email adddress, it would be very helpful

Regards,
Avdhut Kavdikar
---------------------------------------------------
   Microsoft Solutions Group,
   Satyam Computer Services Limited
      23/24, Chamiers Road, Teynampet,
       Chennai - 600018.
       Tel : +91 44 24314500 Extn: 7635
      Mobile : +91 9840567765
---------------------------------------------------

Quantcast