d
discovered that the update statement takes forever - I stopped it after 2
minutes. This is the update statement: UPDATE tbltelemarketing SET download
=
'2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
column. When I do an execution plan on the query, it doesn't use the index o
n
the id column. It does a clustered index scan using an index on several
columns. The table keeps growing so I guess at some point, it stopped using
the ID index.
Is there a way to force it to use the ID index? Any other ideas on how I can
speed up the query. The table has about 11 million rows. Here is the schema
and indexes.
CREATE TABLE [dbo].[tbltelemarketing] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[pest_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[rnwl_num] [nvarchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[term_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[update_date] [smalldatetime] NULL ,
[prop_mkey2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[id_mail] [int] NULL ,
[bill_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[bill_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[bill_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[bill_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[bill_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[bill_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[prop_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[prop_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[prop_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[prop_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[prop_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[webpin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[brmid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[drop_date] [smalldatetime] NULL ,
[hh_score2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[offer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[version_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[bill_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[prop_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[pest_cncl] [smalldatetime] NULL ,
[term_ctype] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[term_cncl] [smalldatetime] NULL ,
[promo_code] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[branch_bad] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[download] [smalldatetime] NULL ,
[recd_date] [smalldatetime] NULL ,
[mo_wk] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[recd_week] [int] NULL ,
[live] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[delete_cd] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[source_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[rpt_promo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[telematch_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
[propzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[billzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
[prev_mail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[branch] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[lead_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[id_lns] [int] NULL ,
[branch_reassign] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
[active_area_code] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
[reassign_date] [datetime] NULL ,
[company_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sic_attr] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[prop_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[coml_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[approved] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[delete_date] [smalldatetime] NULL ,
[update_cd] [int] NULL ,
[update_date_1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_tbltelemarketing] ON
[dbo].[tbltelemarketing]([delete_cd], [live], [promo_cod
e], [drop_date]) ON
[PRIMARY]
GO
CREATE INDEX [IX_tbltelemarketing_1] ON [dbo].[tbltelemarketing
]([live],
[promo_code]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbltelemarketing_2] ON [dbo].[tbltelemarketing
]([branch],
[promo_code], [drop_date], [delete_cd], [live], [prop_zi
p5], [prop_zip4],
[hh_score2]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbltelemarketing_3] ON
[dbo].[tbltelemarketing]([delete_cd], [rpt_promo], [prom
o_code]) ON [PRIMARY]
GO
CREATE INDEX [IX_tbltelemarketing_4] ON [dbo].[tbltelemarketing
]([branch],
[delete_cd], [live], [promo_code]) ON [PRIMARY]
GO
CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemarketi
ng]([ID])
ON [PRIMARY]
GO
Thanks,
--
Dan D.1) Are your statistics on the table are current
2) Why i don't see the unique keywork on ID index and other schema question
if ID is identity why can't it be primary key with non-clustered index
"Dan D." wrote:
> We have a stored procedure that stopped working. I put it in the debugger
and
> discovered that the update statement takes forever - I stopped it after 2
> minutes. This is the update statement: UPDATE tbltelemarketing SET downloa
d =
> '2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
> column. When I do an execution plan on the query, it doesn't use the index
on
> the id column. It does a clustered index scan using an index on several
> columns. The table keeps growing so I guess at some point, it stopped usin
g
> the ID index.
> Is there a way to force it to use the ID index? Any other ideas on how I c
an
> speed up the query. The table has about 11 million rows. Here is the schem
a
> and indexes.
> CREATE TABLE [dbo].[tbltelemarketing] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [pest_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [rnwl_num] [nvarchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [term_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [update_date] [smalldatetime] NULL ,
> [prop_mkey2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [id_mail] [int] NULL ,
> [bill_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [bill_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [bill_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [bill_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [bill_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [bill_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [prop_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [prop_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [prop_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [prop_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [prop_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [webpin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [brmid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [drop_date] [smalldatetime] NULL ,
> [hh_score2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [offer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [version_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [bill_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [prop_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [pest_cncl] [smalldatetime] NULL ,
> [term_ctype] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [term_cncl] [smalldatetime] NULL ,
> [promo_code] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [branch_bad] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [download] [smalldatetime] NULL ,
> [recd_date] [smalldatetime] NULL ,
> [mo_wk] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [recd_week] [int] NULL ,
> [live] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [delete_cd] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [source_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [rpt_promo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [telematch_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_C
I_AS NULL ,
> [propzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [billzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [prev_mail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [branch] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [lead_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [id_lns] [int] NULL ,
> [branch_reassign] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_C
I_AS NULL ,
> [active_area_code] [varchar] (1) COLLATE SQL_Latin1_General_CP1_C
I_AS NULL ,
> [reassign_date] [datetime] NULL ,
> [company_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
> [title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [sic_attr] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [prop_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [coml_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [approved] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
> [delete_date] [smalldatetime] NULL ,
> [update_cd] [int] NULL ,
> [update_date_1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_
AS NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_tbltelemarketing] ON
> [dbo].[tbltelemarketing]([delete_cd], [live], [promo_c
ode], [drop_date]) ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_1] ON [dbo].[tbltelemarket
ing]([live],
> [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_2] ON [dbo].[tbltelemarket
ing]([branch],
> [promo_code], [drop_date], [delete_cd], [live], [prop_
zip5], [prop_zip4],
> [hh_score2]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_3] ON
> [dbo].[tbltelemarketing]([delete_cd], [rpt_promo], [pr
omo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_4] ON [dbo].[tbltelemarket
ing]([branch],
> [delete_cd], [live], [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemark
eting]([ID])
> ON [PRIMARY]
> GO
> Thanks,
> --
> Dan D.|||You can use an index hint when you want to force SQL Server to use an index.
You can search Books Online (within the SQL Server program group) for INDEX
HINT. The general usage is:
.
FROM tbltelemarketing WITH (INDEX (nci_tbltelemarketing_ID)) WHERE ID =
10617138
I assume that ID is the primary key on the table...yet you don't define it
as a primary key constraint. Perhaps you should define a primary key on the
table.
Keith
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:2A769066-CBE1-4391-8900-02E825BE30EA@.microsoft.com...
> We have a stored procedure that stopped working. I put it in the debugger
and
> discovered that the update statement takes forever - I stopped it after 2
> minutes. This is the update statement: UPDATE tbltelemarketing SET
download =
> '2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
> column. When I do an execution plan on the query, it doesn't use the index
on
> the id column. It does a clustered index scan using an index on several
> columns. The table keeps growing so I guess at some point, it stopped
using
> the ID index.
> Is there a way to force it to use the ID index? Any other ideas on how I
can
> speed up the query. The table has about 11 million rows. Here is the
schema
> and indexes.
> CREATE TABLE [dbo].[tbltelemarketing] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [pest_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [rnwl_num] [nvarchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [term_num] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [update_date] [smalldatetime] NULL ,
> [prop_mkey2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [id_mail] [int] NULL ,
> [bill_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [bill_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [bill_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [bill_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [bill_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [bill_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [prop_addr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [prop_city] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [prop_state] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [prop_zip5] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [prop_zip4] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [webpin] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [brmid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [drop_date] [smalldatetime] NULL ,
> [hh_score2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [offer] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [version_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [bill_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [prop_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [pest_cncl] [smalldatetime] NULL ,
> [term_ctype] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [term_cncl] [smalldatetime] NULL ,
> [promo_code] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [branch_bad] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [download] [smalldatetime] NULL ,
> [recd_date] [smalldatetime] NULL ,
> [mo_wk] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [recd_week] [int] NULL ,
> [live] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [delete_cd] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [source_cd] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [rpt_promo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [telematch_phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [propzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [billzpaddr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
> [prev_mail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [branch] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [lead_type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [id_lns] [int] NULL ,
> [branch_reassign] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [active_area_code] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [reassign_date] [datetime] NULL ,
> [company_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [sic_attr] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
> [prop_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [coml_flag] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
> [approved] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [delete_date] [smalldatetime] NULL ,
> [update_cd] [int] NULL ,
> [update_date_1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL
> ) ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_tbltelemarketing] ON
> [dbo].[tbltelemarketing]([delete_cd], [live], [promo_code], &#
91;drop_date])
ON
> [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_1] ON [dbo].[tbltelemarket
ing]([live],
> [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_2] ON
[dbo].[tbltelemarketing]([branch],
> [promo_code], [drop_date], [delete_cd], [live], [prop_
zip5], [prop_zip4],
> [hh_score2]) ON [PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_3] ON
> [dbo].[tbltelemarketing]([delete_cd], [rpt_promo], [promo_code
]) ON
[PRIMARY]
> GO
> CREATE INDEX [IX_tbltelemarketing_4] ON
[dbo].[tbltelemarketing]([branch],
> [delete_cd], [live], [promo_code]) ON [PRIMARY]
> GO
> CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemark
eting]([ID])
> ON [PRIMARY]
> GO
> Thanks,
> --
> Dan D.|||Dan,
The current index on ID is non-unique. If the ID column is in fact the
identifying column, then make sure you create a unique index on it. Or
even better, to create a Primary Key constraint on it (which will
automatically create a unique index on it).
HTH,
Gert-Jan
Dan D. wrote:
> We have a stored procedure that stopped working. I put it in the debugger
and
> discovered that the update statement takes forever - I stopped it after 2
> minutes. This is the update statement: UPDATE tbltelemarketing SET downloa
d =
> '2004-10-09 09:51:00' WHERE ID = 10617138. I had a unique index on the ID
> column. When I do an execution plan on the query, it doesn't use the index
on
> the id column. It does a clustered index scan using an index on several
> columns. The table keeps growing so I guess at some point, it stopped usin
g
> the ID index.
> Is there a way to force it to use the ID index? Any other ideas on how I c
an
> speed up the query. The table has about 11 million rows. Here is the schem
a
> and indexes.
> CREATE TABLE [dbo].[tbltelemarketing] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
<snip>
> CREATE INDEX [nci_tbltelemarketing_ID] ON [dbo].[tbltelemark
eting]([ID])
> ON [PRIMARY]
> GO
> Thanks,
> --
> Dan D.|||I did update the statistics. You don't see the "unique" keyword because I wa
s
trying different indexes with different names and the one you see was the
last one I tried. At one time it was primary with non-clustered index.
"harvinder" wrote:
[vbcol=seagreen]
> 1) Are your statistics on the table are current
> 2) Why i don't see the unique keywork on ID index and other schema questio
n
> if ID is identity why can't it be primary key with non-clustered index
>
> "Dan D." wrote:
>|||It was a primary index at one time. When it stopped working I changed it in
the process of testing.
"Gert-Jan Strik" wrote:
> Dan,
> The current index on ID is non-unique. If the ID column is in fact the
> identifying column, then make sure you create a unique index on it. Or
> even better, to create a Primary Key constraint on it (which will
> automatically create a unique index on it).
> HTH,
> Gert-Jan
>
> Dan D. wrote:
> <snip>
>|||Problem solved. I was looking at the development server but querying the
production server. There was no index on production. Now that there is, it
works. Wonders never cease.
Thanks to all for your help.
"harvinder" wrote:
[vbcol=seagreen]
> 1) Are your statistics on the table are current
> 2) Why i don't see the unique keywork on ID index and other schema questio
n
> if ID is identity why can't it be primary key with non-clustered index
>
> "Dan D." wrote:
>sql
No comments:
Post a Comment