How to FORCE the INDEX in update statment?

Question

Post reply

How to FORCE the INDEX in update statment?

Ezhilan

Ten Centuries

Points: 1319
More actions
August 2, 2007 at 12:42 am

#118063

Hi,
I have created one index on the table FSA_EXPORT_TEMP, I think it is not taking the Index while updating it.
Index Name is 'sp_ind'
How can I force the Index in the following Update statement.
--------------
update FSA_EXPORT_TEMP WITH (ROWLOCK)
set trading_partner = 'XXX'
where reporting_date = @reporting_date
and fsa_le = @fsa_le
and transfer_le like '%%X'
and sp_id =@sp_id
-------------
Regards,
E Ezhilan

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

update FSA_EXPORT_TEMP WITH (ROWLOCK, INDEX(<index name here&gt)

set trading_partner = 'XXX'

where reporting_date = @reporting_date

and fsa_le = @fsa_le

and transfer_le like '%%X'

and sp_id =@sp_id

N 56°04'39.16"
E 12°55'05.25"

Ezhilan Ten Centuries Points: 1319 More actions · Answer 2

While trying..I got this error..

Server: Msg 1069, Level 15, State 1, Procedure proc_fsa_export_usg_valuation, Line 329

Index hints are only allowed in a FROM clause.

please help...

Regards,

E Ezhilan

Vladan SSC-Insane Points: 22072 More actions · Answer 3

Try to do what the error message suggests - add FROM clause:

update FSA_EXPORT_TEMP

set trading_partner = 'XXX'

FROM FSA_EXPORT_TEMP WITH (INDEX(index_name))

where reporting_date = @reporting_date

and fsa_le = @fsa_le

and transfer_le like '%%X'

and sp_id =@sp_id

I'm not sure it will work, but I think it should.

philcart SSC-Forever Points: 47795 More actions · Answer 4

Rather than forcing the statement to use an index that is not appropriate. Can you post the index definition and we might be able to see why it's not being used?

--------------------
Colt 45 - the original point and click interface

Ezhilan Ten Centuries Points: 1319 More actions · Answer 5

Herewith sending the structure...

---------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FSA_EXPORT_TEMP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[FSA_EXPORT_TEMP]

GO

CREATE TABLE [dbo].[FSA_EXPORT_TEMP] (

[sp_id] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,

[reporting_date] [datetime] NOT NULL ,

[fsa_le] [char] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[account_fsa] [char] (7) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[account_id] [int] NOT NULL ,

[roll_forward] [char] (5) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[dr_cr] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[security] [char] (8) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[sub_portfolio] [char] (6) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[isin] [char] (12) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[glg] [char] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[valuation] [char] (4) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[fsa_view] [char] (4) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[source] [char] (4) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[currency] [char] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[division] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[mu] [char] (5) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[amount_usg_base] [numeric](25, 3) NOT NULL ,

[amount_usg] [numeric](25, 3) NOT NULL ,

[amount_chg_base] [numeric](25, 3) NOT NULL ,

[amount_chg] [numeric](25, 3) NOT NULL ,

[amount_loc_base] [numeric](25, 3) NOT NULL ,

[amount_loc] [numeric](25, 3) NOT NULL ,

[bp] [char] (4) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[security_lending] [char] (4) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[invcat] [char] (7) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[restricted_asset] [char] (4) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[industry_fsa] [char] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[country_fsa] [char] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[issuer_fsa] [char] (5) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[maturity_year] [char] (8) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[callable] [char] (4) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[rating_fsa] [char] (2) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[transfer_le] [char] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[user_id] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[lpb] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[primseg] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[account_gl] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,

[currency_rep] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,

[currency_func] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,

[amount_usg_func] [numeric](25, 3) NULL ,

[roll_forward_gfa] [char] (5) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,

[trading_partner] [char] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NULL

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[FSA_EXPORT_TEMP] WITH NOCHECK ADD

CONSTRAINT [PK_FSA_EXPORT_3] PRIMARY KEY CLUSTERED

(

[id],

[reporting_date],

[account_fsa],

[account_id],

[roll_forward],

[dr_cr],

[security],

[sub_portfolio],

[valuation],

[fsa_view],

[currency],

[restricted_asset],

[transfer_le],

[lpb],

[primseg],

[account_gl]

) ON [PRIMARY]

GO

CREATE INDEX [fsa_exp_ind] ON [dbo].[FSA_EXPORT_TEMP]([sp_id]) WITH FILLFACTOR = 100 ON [PRIMARY]

GO

philcart SSC-Forever Points: 47795 More actions · Answer 6

Ok, am I missing something here ... you have a 16 field primary key?? Bearing in mind that the primary key identifies a unique record, surely the id column (which is defined as an identity) should be enough.

Which index are you incorrectly trying to force the query to use? Neither of them are appropriate for the update statement that you've provided.

Try an index that covers your search criteria and you might find you don't need to force it to use an appropriate index.

--------------------
Colt 45 - the original point and click interface

steve smith-401573 SSChampion Points: 10889 More actions · Answer 7

OK. Maybe I'm misinterpreting the original mission here: The UPDATE statement is (copied from above):

--------------

update FSA_EXPORT_TEMP WITH (ROWLOCK)

set trading_partner = 'XXX'

where reporting_date = @reporting_date

and fsa_le = @fsa_le

and transfer_le like '%%X'

and sp_id =@sp_id

-------------

This update statement sets trading_partner = 'XXX'. Yet trading_partner is not in the index. WHY should this index be forced?

What are you really trying to accomplish here? I don't see how anything you are doing here is affecting the index.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 8

For the code example given, there is absolutely no reason to force an index... it does nothing for the code.

And, I agree, 16 column PK???? CLUSTERED??? So much for speedy Inserts and Updates

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)