Update on a primary key

Question

Update on a primary key

Roust_m

SSCoach

Points: 17410
More actions
February 3, 2005 at 6:37 am

#65974
Hi!
I have two tables. I use update of this kind:
update table with (rowlock) set date = date where number = 2
on both tables. (number is a primary key)The time for the first table is about 30mSeconds, while on the second ~ 500-1500 mSeconds. If I run the "slow" update a number of times with less then 1 second interval, it finishes as quick as the fast update. If I wait more then 5 seconds it gets slow again. I updated statistics, recreated all indexes. What can be a problem here?
Thanks.

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

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

rhunt SSCertifiable Points: 5868 More actions · Answer 1

Are the two tables the exact same size? Or, are they significantly different? If they are different, then the different executions times on the query could be expected behavior.

Also, is the slower table being updated more frequently (i.e. changing more in a less amount of time)?

I have no idea what your memory resources look like on that server and how often data is being purged from RAM, however, there is a chance that your immediate re-updates are not going back to the table/indexes for execution because all necessary information still resides in memory.

These are just some shots in the dark...

Good Luck

Ryan

Roust_m SSCoach Points: 17410 More actions · Answer 2

The "fast" table has three times more records, but it does not much matter, because I use a clustered index on PK. The "fast" also has more user activity on it. The server has 32GB memory and I pinned the "slow" table - no difference. The problem is: there is no obvious reasons for "slow" table to be slow.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

What about DRI ?

How many tables reference this PK that you're updating ? Remember, when you change a PKey, SqlServer has to check all tables that reference it to ensure that there will be no Foreign Keys remaining that reference the key you're about to remove in the update.

Roust_m SSCoach Points: 17410 More actions · Answer 4

There are six FK that reference that PK (a dozen on a "fast" table), but I am not updating th PK, but using it as search criteria:

update table with (rowlock)  set date = date where number = 2

number is a PK, date - is not.  date is not referenced by FK as well.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 5

Sorry, got thrown by the thread title.

Without the full DDL of each table, tough to give an answer. What about Triggers & Check Constraints that reference the Date column being updated ?

What does the execution plan look like for each update ? (Paste the SQL into Query Analyser and hit CTRL-L)

Roust_m SSCoach Points: 17410 More actions · Answer 6

No triggers on both tables. The actual updates are:

update SORDER set occur=occur where sorder = 12345 -- fast table

&

update STOCK_INVOICE set occur = occur where stock_invoice = 345 -- slow table

Query plans are the same:

Fast table:

CREATE TABLE [SORDER] (

[sorder] [int] NOT NULL ,

[spay_term] [int] NOT NULL ,

[sprice_type] [varchar] (12) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[discount_type] [smallint] NULL ,

[pay_draft_prc] [smallmoney] NULL ,

[prepayment] [money] NULL ,

[pay_discount] [smallmoney] NULL ,

[pl_discount] [smallmoney] NULL ,

[bill_name] [varchar] (80) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[bill_postal_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[bill_region] [varchar] (80) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[bill_city] [varchar] (40) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[bill_street] [varchar] (255) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[bill_country] [varchar] (40) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[bill_fax] [varchar] (20) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[bill_phone] [varchar] (20) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[bill_contact] [varchar] (30) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[pay_guar_date] [datetime] NULL ,

[pay_draft_date] [datetime] NULL ,

[discount_add] [smallmoney] NULL ,

[consolidator] [int] NULL ,

[consolidate] [bit] NOT NULL ,

[exp_ship_date] [datetime] NULL ,

[req_ship_date] [datetime] NULL ,

[shipto_number] [int] NULL ,

[ship_name] [varchar] (80) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[ship_postal_code] [varchar] (12) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[ship_region] [varchar] (80) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[ship_city] [varchar] (40) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[ship_street] [varchar] (255) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[ship_country] [varchar] (40) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[ship_phone] [varchar] (20) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[ship_fax] [varchar] (20) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[ship_contact] [varchar] (30) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[tr_agent] [int] NULL ,

[sdeliv_type] [tinyint] NULL ,

[delivery_price] [money] NULL ,

[ORDNUMBER] [char] (22) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[occur] [datetime] NULL ,

[changer] [int] NULL ,

[state] [char] (1) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[customer] [int] NOT NULL ,

[sales_person] [int] NULL ,

[sorder_status] [tinyint] NOT NULL CONSTRAINT [DF__SORDER__sorder_s__0E28D206] DEFAULT (0),

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

[good_location] [char] (10) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[sale_channel] [char] (10) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[order_date] [datetime] NULL ,

[complete_date] [datetime] NULL ,

[on_hold] [bit] NOT NULL CONSTRAINT [DF__SORDER__on_hold__0F1CF63F] DEFAULT (0),

[commission_prc] [smallmoney] NULL CONSTRAINT [DF__SORDER__commissi__10111A78] DEFAULT (0),

[is_demo] [bit] NOT NULL CONSTRAINT [DF__SORDER__is_demo__11053EB1] DEFAULT (0),

[org_total_cost] [money] NOT NULL CONSTRAINT [DF__SORDER__org_tota__11F962EA] DEFAULT (0.0),

[org_total_ddp_cost] [money] NOT NULL CONSTRAINT [DF__SORDER__org_tota__12ED8723] DEFAULT (0.0),

[org_total_wo_com] [money] NOT NULL CONSTRAINT [DF__SORDER__org_tota__13E1AB5C] DEFAULT (0.0),

[org_total] [money] NOT NULL CONSTRAINT [DF__SORDER__org_tota__14D5CF95] DEFAULT (0.0),

[total_cost] [money] NOT NULL CONSTRAINT [DF__SORDER__total_co__15C9F3CE] DEFAULT (0.0),

[total_ddp_cost] [money] NOT NULL CONSTRAINT [DF__SORDER__total_dd__16BE1807] DEFAULT (0.0),

[total_wo_com] [money] NOT NULL CONSTRAINT [DF__SORDER__total_wo__17B23C40] DEFAULT (0.0),

[total] [money] NOT NULL CONSTRAINT [DF__SORDER__total__18A66079] DEFAULT (0.0),

[pl_diff] [tinyint] NULL ,

[creation_date] [datetime] NULL ,

[weight] [decimal](15, 4) NULL ,

[reference] [varchar] (22) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[sorder_type] [char] (3) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[priority] [int] NULL ,

[insurance_price] [money] NULL ,

[money_amount] [money] NULL ,

[cur_weight] [decimal](15, 4) NULL ,

[sorder_source] [char] (2) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[comment] [varchar] (80) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[que_amount] [money] NULL ,

[so_to_pay] [money] NULL ,

[org_total_SC] [money] NULL ,

[total_SC] [money] NULL ,

[money_amount_SC] [money] NULL ,

[operation] [tinyint] NULL ,

[int] NULL ,

[deactivate_date] [datetime] NULL ,

[autodelete_date] [datetime] NULL ,

[amount_MD] [money] NULL ,

[amount_MD_prev] [money] NULL ,

[amount_MD_desired] [money] NULL ,

[print_specification] [tinyint] NULL ,

[market_discount_prc] [smallmoney] NULL ,

[services_prc] [smallmoney] NULL ,

[special_markup_prc] [smallmoney] NULL ,

[bagged] [tinyint] NULL ,

[start_assembling_date] [datetime] NULL ,

[delivery_price_calc_type] [int] NULL ,

[partner_discount_prc] [smallmoney] NULL ,

[first_paid_date] [datetime] NULL ,

[second_testing] [bit] NULL ,

CONSTRAINT [PK__SORDER__74994623] PRIMARY KEY CLUSTERED

(

[sorder]

&nbsp ON [PRIMARY] ,

CONSTRAINT [FK__SORDER__consolid__729BEF18] FOREIGN KEY

(

[consolidator]

&nbsp REFERENCES [SORDER] (

[sorder]

&nbsp,

CONSTRAINT [FK__SORDER__currency__73901351] FOREIGN KEY

(

[currency]

&nbsp REFERENCES [CURRENCY] (

[currency]

&nbsp,

CONSTRAINT [FK__SORDER__customer__7484378A] FOREIGN KEY

(

[customer]

&nbsp REFERENCES [CUSTOMER] (

[customer]

&nbsp,

CONSTRAINT [FK__SORDER__discount__75785BC3] FOREIGN KEY

(

[discount_type]

&nbsp REFERENCES [DISCOUNT_TYPE] (

[discount_type]

&nbsp,

CONSTRAINT [FK__SORDER__good_loc__766C7FFC] FOREIGN KEY

(

[good_location]

&nbsp REFERENCES [GOOD_LOCATION] (

[good_location]

&nbsp,

CONSTRAINT [FK__SORDER__sale_cha__7760A435] FOREIGN KEY

(

[sale_channel]

&nbsp REFERENCES [SALE_CHANNEL] (

[sale_channel]

&nbsp,

CONSTRAINT [FK__SORDER__sales_pe__7854C86E] FOREIGN KEY

(

[sales_person]

&nbsp REFERENCES [SALES_PERSON] (

[sales_person]

&nbsp,

CONSTRAINT [FK__SORDER__sdeliv_t__7948ECA7] FOREIGN KEY

(

[sdeliv_type]

&nbsp REFERENCES [SDELIV_TYPE] (

[sdeliv_type]

&nbsp,

CONSTRAINT [FK__SORDER__sorder_s__7A3D10E0] FOREIGN KEY

(

[sorder_source]

&nbsp REFERENCES [SORDER_SOURCE] (

[sorder_source]

&nbsp,

CONSTRAINT [FK__SORDER__sorder_s__7B313519] FOREIGN KEY

(

[sorder_status]

&nbsp REFERENCES [SORDER_STATUS] (

[sorder_status]

&nbsp,

CONSTRAINT [FK__SORDER__sorder_t__7C255952] FOREIGN KEY

(

[sorder_type]

&nbsp REFERENCES [SORDER_TYPE] (

[sorder_type]

&nbsp,

CONSTRAINT [FK__SORDER__spay_ter__7D197D8B] FOREIGN KEY

(

[spay_term]

&nbsp REFERENCES [SPAY_TERM] (

[spay_term]

&nbsp,

CONSTRAINT [FK__SORDER__sprice_t__7E0DA1C4] FOREIGN KEY

(

[sprice_type]

&nbsp REFERENCES [SPRICE_TYPE] (

[sprice_type]

&nbsp,

CONSTRAINT [FK_SORDER_TR_AGENT] FOREIGN KEY

(

[tr_agent]

&nbsp REFERENCES [TR_AGENT] (

[tr_agent]

&nbsp

) ON [PRIMARY]

GO

Slow table:

CREATE TABLE [STOCK_INVOICE] (

[stock_invoice] [int] NOT NULL ,

[occur] [datetime] NULL CONSTRAINT [DF__STOCK_INV__occur__0F382DC6] DEFAULT (getdate()),

[changer] [sysname] NULL CONSTRAINT [DF__STOCK_INV__chang__102C51FF] DEFAULT (suser_sname()),

[psinvoice] [int] NOT NULL ,

[inv_trans] [int] NULL ,

[varchar] (22) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[creation_date] [datetime] NULL ,

[invoice_date] [datetime] NULL ,

[complect_date] [datetime] NULL ,

[delivery_date] [datetime] NULL ,

[breakingUp_date] [datetime] NULL ,

[pack_list_print_count] [int] NULL ,

[pack_list_verified] [bit] NULL ,

[online_date] [datetime] NULL ,

[stock_invoice_status] [int] NULL ,

[ready_to_date] [datetime] NULL ,

[give_out_date] [datetime] NULL ,

[stg_warehouse] [int] NULL ,

[ban_count] [int] NULL ,

[task_queue] [int] NULL ,

[allow_edit_PL] [bit] NULL ,

[weight] [float] NULL ,

[volume] [float] NULL ,

[customer] [int] NULL ,

[tr_agent] [int] NULL ,

[sdeliv_type] [tinyint] NULL ,

[mobile_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[customer_name] [varchar] (80) COLLATE SQL_Latin1_General_CP1251_CS_AS NULL ,

[places_count] [int] NULL ,

[ready_to_ordering_date] [datetime] NULL ,

[warranty] [bit] NULL ,

[lot_count] [int] NULL ,

[ban_free_date] [datetime] NULL ,

[consolidation_ready_date] [datetime] NULL ,

CONSTRAINT [PK__STOCK_INVOICE__0E44098D] PRIMARY KEY CLUSTERED

(

[stock_invoice]

&nbsp ON [PRIMARY] ,

CONSTRAINT [FK__STOCK_INV__inv_t__11207638] FOREIGN KEY

(

[inv_trans]

&nbsp REFERENCES [INV_TRANS] (

[inv_trans]

&nbsp,

CONSTRAINT [FK__STOCK_INV__stg_w__05CEBF1D] FOREIGN KEY

(

[stg_warehouse]

&nbsp REFERENCES [STG_WAREHOUSE] (

[stg_warehouse]

&nbsp,

CONSTRAINT [FK__STOCK_INV__task___394E6323] FOREIGN KEY

(

[task_queue]

&nbsp REFERENCES [TASK_QUEUE] (

[task_queue]

&nbsp

) ON [PRIMARY]

GO

PW-201837 SSC-Insane Points: 20805 More actions · Answer 7

Hmm, only remaining thing I can think of then, is that the faster one is taking place as an "in place" update.

Sql Server can process an Update in 2 ways, either as a delete of the existing row + an insert of the new row state, or as a single update to the existing row.

An "in place" update can be significantly faster. Not sure, but I think you need a log analyser to determine exactly what is happening (eg Lumigent Log Explorer)

Try searching the MS Sql Server newsgroups on keywords "in place update", you'll find newsgroup threads with responses from some of the authors of well regarded SQL resource books.