December 21, 2001 at 5:40 am
Hello all. (I'm not sure that my message is convinient for this forum, but i don't know where else should i post it 🙁 )
One of developers gave me this problem: he wrote 2 updates on similar
tables. If UPDATE is changed on SELECT, everything is ok, both runs quick (3
sec together). But when executing UPDATES strange things started.
Can u explain:
1. First UPDATE runs VERY slow (more than 30 min), second is ok. Why? (they
are quite similar)
2. why do they have quite different execution plans?
First
UPDATE Map SET n_offers = c.n_offers
FROM Map m, cat_stat c --WITH (INDEX=pk_cat_stat)
WHERE c.path = m.path and c.log_num='20011219_0351'
Second
UPDATE Map SET mapped_to = a.category_id
FROM Map, all_categories_log a
WHERE a.path = Map.path AND a.log_num ='20011219_0351'
That's SQL 7 SP3 on WinNT 4.0, SP6
Number of records on tables
Map 33870
cat_stat 822284
all_categories_log 1718574
Here's script of tables:
CREATE TABLE [dbo].[Map] (
[id] [int] NULL ,
[Value] [real] NOT NULL ,
[path] [nvarchar] (255) NOT NULL ,
[datasource] [nvarchar] (50) NOT NULL ,
[modification] [bit] NOT NULL ,
[check] [bit] NOT NULL ,
[creation_time] [datetime] NULL ,
[last_seen_time] [datetime] NULL ,
[last_offers_time] [datetime] NULL ,
[_sel] [bit] NOT NULL ,
[mapped_to] [int] NULL ,
[n_offers] [int] NULL ,
[no_search] [bit] NOT NULL ,
[comment] [nvarchar] (128) NULL ,
[user_modification] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[all_categories_log] (
[log_num] [char] (13) NOT NULL ,
[path] [nvarchar] (255) NOT NULL ,
[id] [int] NULL ,
[parent_id] [int] NULL ,
[category_id] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[cat_stat] (
[log_num] [char] (13) NOT NULL ,
[path] [nvarchar] (255) NOT NULL ,
[ds_name] [varchar] (50) NOT NULL ,
[category_id] [int] NOT NULL ,
[n_offers] [int] NOT NULL ,
[ds_path] [varchar] (255) NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[cat_stat] WITH NOCHECK ADD
CONSTRAINT [PK_cat_stat] PRIMARY KEY CLUSTERED
( [log_num], [path] ) ON [PRIMARY]
ALTER TABLE [dbo].[Map] WITH NOCHECK ADD
CONSTRAINT [DF_Map_Value] DEFAULT (0.7) FOR [Value],
CONSTRAINT [DF_Map_modification] DEFAULT (0) FOR [modification],
CONSTRAINT [DF_Map_no_tests] DEFAULT (1) FOR [check],
CONSTRAINT [DF_Map__sel] DEFAULT (0) FOR [_sel],
CONSTRAINT [DF_Map_not_used] DEFAULT (0) FOR [no_search],
CONSTRAINT [PK_Map] PRIMARY KEY NONCLUSTERED ( [path] ) ON [PRIMARY]
ALTER TABLE [dbo].[all_categories_log] WITH NOCHECK ADD
CONSTRAINT [PK_all_categories_log] PRIMARY KEY NONCLUSTERED
( [log_num], [path] ) ON [PRIMARY]
CREATE INDEX [IX_datasource] ON [dbo].[Map]([datasource]) ON [PRIMARY]
CREATE INDEX [IX_id] ON [dbo].[Map]([id]) ON [PRIMARY]
CREATE INDEX [IX_MapedTo] ON [dbo].[Map]([mapped_to]) ON [PRIMARY]
CREATE INDEX [IX_path] ON [dbo].[all_categories_log]([path]) ON [PRIMARY]
CREATE INDEX [IX_all_categories_log] ON
[dbo].[all_categories_log]([log_num]) ON [PRIMARY]
CREATE INDEX [IX_cat_stat_path] ON [dbo].[cat_stat]([path]) ON [PRIMARY]
CREATE UNIQUE INDEX [IX_log_num_and_path] ON [dbo].[cat_stat]([log_num],
[path], [ds_name]) ON [PRIMARY]
CREATE INDEX [IX_cat_stat_log_num] ON [dbo].[cat_stat]([log_num]) ON
[PRIMARY]
ALTER TABLE [dbo].[Map] ADD
CONSTRAINT [FK_Map_Categories] FOREIGN KEY
( [id] ) REFERENCES [dbo].[Categories] ( [id] ),
CONSTRAINT [FK_Map_datasources] FOREIGN KEY
( [datasource] ) REFERENCES [dbo].[datasources] ( [datasource] )
That's "SET SHOWPLAN text rather than a large binary file".
UPDATE Map SET n_offers = c.n_offers
FROM Map m, cat_stat c
WHERE c.path =m.path and c.log_num='20011219_0351'
(1 row(s) affected)
StmtText
----------------------------------------------------------------------------
-------------------------------------------------------------------
|--Table Update(OBJECT:([Tovar].[dbo].[Map]),
SET:([Map].[n_offers]=[c].[n_offers]))
|--Top(ROWCOUNT est 0)
|--Merge Join(Inner Join, MERGE:([m].[path])=([c].[path]),
RESIDUAL:([m].[path]=[c].[path]))
|--Index Scan(OBJECT:([Tovar].[dbo].[Map].[PK_Map] AS [m]),
ORDERED)
|--Clustered Index
Seek(OBJECT:([Tovar].[dbo].[cat_stat].[PK_cat_stat] AS [c]),
SEEK:([c].[log_num]='20011219_0351') ORDERED)
(5 row(s) affected)
StmtText
----------------------------------------------------------------------------
------------------------------------------------------------
UPDATE Map SET mapped_to = a.category_id
FROM Map, all_categories_log a
WHERE a.path = Map.path AND a.log_num ='20011219_0351'
(1 row(s) affected)
StmtText
----------------------------------------------------------------------------
----------------------------------------------------------------------------
|--Filter(WHERE:([Act1005]<>3))
|--Index Update(OBJECT:([Tovar].[dbo].[Map].[IX_MapedTo]),
SET:([IdxBmk1006]=[Bmk1004], [mapped_to1007]=[Map].[mapped_to]))
|--Sort(ORDER BY:([Map].[mapped_to] ASC, [Bmk1004] ASC,
[Act1005] ASC))
|--Split
|--Table Update(OBJECT:([Tovar].[dbo].[Map]),
SET:([Map].[mapped_to]=[a].[category_id]))
|--Top(ROWCOUNT est 0)
|--Parallelism(Gather Streams)
|--Hash Match(Inner Join,
HASH:([a].[path])=([Map].[path]), RESIDUAL:([Map].[path]=[a].[path]))
|--Parallelism(Repartition
Streams, PARTITION COLUMNS:([a].[path]))
| |--Table
Scan(OBJECT:([Tovar].[dbo].[all_categories_log] AS [a]),
WHERE:([a].[log_num]='20011219_0351'))
|--Parallelism(Distribute Streams,
PARTITION COLUMNS:([Map].[path]))
|--Table
Scan(OBJECT:([Tovar].[dbo].[Map]))
--
With great respect and high hopes
December 21, 2001 at 8:25 am
In the first query the plan is doing a clustered index seek (as per the index hint). Very optimal. The second query is doing a table scan. Very Slow. Put the same hint in the second query, and it should run better.
Tom
December 21, 2001 at 8:52 am
quote:
In the first query the plan is doing a clustered index seek (as per the index hint). Very optimal. The second query is doing a table scan. Very Slow. Put the same hint in the second query, and it should run better.Tom
Tom, the problem is that FIRST query runs slow. Second is OK
December 21, 2001 at 1:30 pm
I didn't really look at the ddl so, this is an off the cuff reply however, did you rebuild the index as part of the troubleshooting or is it being rebuilt regularly as part of a "maintenance plan"?
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply