update take too long on some records.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

update take too long on some records.

Author

Topic

shedoks
Starting Member

10 Posts

Posted - 2008-03-31 : 10:30:09

Hi all,

I have confusing problem here. My table which have about 150.000 rows have problem with updates on 15 rows.

It's access application which connect to sql server 2005 database. Access gets error:
"ODBC --update on linked table 'MyTable2 failed"

If i try to update on sql it's take couple minutes to update.
Evrything else on that table is ok.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-31 : 23:33:05

Did you check execution plan? Possible to post your update statement?

shedoks
Starting Member

10 Posts

Posted - 2008-04-01 : 05:21:54

Execution plan is the same as with "Working" query.
Update statment is simple

update mytable
set mytable.column=1
where mytable.id=10000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-01 : 05:22:56

Try to link the table and/or update query as "passthrough".

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

shedoks
Starting Member

10 Posts

Posted - 2008-04-01 : 06:48:31

I made a copy of this record.
The result is the same data with new id. I've updated referenced tables and everything is ok now.
But when i try to add new record with the old id, it take's a two minutes.

SET IDENTITY_INSERT mytable ON
insert into mytable (id, name)
values (1744, 'test')
SET IDENTITY_INSERT mytable OFF

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-01 : 07:04:50

Is the ID column part of a clustered index?

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

shedoks
Starting Member

10 Posts

Posted - 2008-04-01 : 15:32:14

yes it is.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-01 : 15:40:00

How many records do have a value greater than 1744?
How many records do have a value less than 1744?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-01 : 15:44:41

Meanwhile, read about clustered indexes and what happens when you insert a new record.

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

shedoks
Starting Member

10 Posts

Posted - 2008-04-01 : 16:03:48

quote:
Originally posted by Peso
How many records do have a value greater than 1744?
How many records do have a value less than 1744?
E 12°55'05.25"
N 56°04'39.16"

This will have to wait till tomorow :)
greater about 155000 records
less about 1700
it's happening between 1735 and 1745

quote:
Originally posted by Peso

Meanwhile, read about clustered indexes and what happens when you insert a new record.

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

This start to happen before i insert any record, but no matter what i'll read about it :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-01 : 16:37:21

It's vital you understand the underlying mechanisms when inserting a record in a table containing a clustered index.
SQL Server needs to reorganize all records so that they are physically stored according to the clustered index (the is the easy explanation, the truth is not this easy).

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

shedoks
Starting Member

10 Posts

Posted - 2008-04-01 : 18:36:21

but why just in that range ?

the initial problem was update in this table on other fields with id in that range :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources