solve insert/update timeout error

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

SQL Server Administration (2005)

solve insert/update timeout error

Author

Topic

chih
Posting Yak Master

154 Posts

Posted - 2008-08-11 : 03:33:31

Hi everyone,

here is a problem I encounter. I have 2 tables SAMPLE and TARGET

The relationship is
1. After we generate samples and insert to SAMPLE, data will be inserted from table SAMPLE to TARGET.
2. data in table TARGET will be updated when members response.

The problem is timeout error occured when data insert and update at the same time.

Normally we insert more than 200,000 rows to table TARGET.

Is there any way to solve it? I am thinking to create one extra table to store member respone so TARGET will not be affected during data inserting. But is this a good practise? Or can I use any hint to solve this problem?

Thank you in advance.

hitesh6221
Starting Member

28 Posts

Posted - 2008-08-11 : 07:52:45

Can U able to send script of this connection and to table script so I can try @ my environment??

Regards,
Hitesh Soni

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-11 : 08:54:58

You must be having locking issues.

chih
Posting Yak Master

154 Posts

Posted - 2008-08-11 : 19:45:54

just simple insert/update statements
e.g.
transaction 1:
insert TARGET
select * from SAMPLE

transaction 2:
update TARGET
set sataus=1
where memberId='.....'

I think it is the locking issue. any suggestion? Thank you

quote:
Originally posted by hitesh6221

Can U able to send script of this connection and to table script so I can try @ my environment??

Regards,
Hitesh Soni

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-11 : 22:16:48

why inserting and update at same time?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-11 : 22:40:36

Does table target have clustered index? Can you post schema of those two tables? May able to reduce blocking if you append new data at the end of target table.

chih
Posting Yak Master

154 Posts

Posted - 2008-08-12 : 02:06:24

yes, table Target has clustered index.
Attributes:
1. Sample
jobId
memberId

2. Target
memberId - primary key
status
url

The reason why insert and update at same time becasue
- at backend we do sampling and insert it to table Target
- at front end is our website. Once Member click the url, it updates column status in table Target
We cannot predict when member will click so we don't know how to avoid locking.

Thank you

quote:
Originally posted by rmiao

Does table target have clustered index? Can you post schema of those two tables? May able to reduce blocking if you append new data at the end of target table.

hitesh6221
Starting Member

28 Posts

Posted - 2008-08-12 : 05:12:45

Can U please check rights on both tables
i.e. one single user has rights on both table and should be same
just check

Regards,
Hitesh Soni

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-08-12 : 08:21:59

quote:
Originally posted by hitesh6221

Can U please check rights on both tables
i.e. one single user has rights on both table and should be same
just check

Regards,
Hitesh Soni

What has rights to do with this issue?

bhuvnesh.dogra
Starting Member

22 Posts

Posted - 2008-08-13 : 06:04:45

1. if possible then insert the data into batch form ...

2.i think after insert ,you do the updation so you can use temp table in between them.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 06:56:43

Post your code.

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

bhuvnesh.dogra
Starting Member

22 Posts

Posted - 2008-08-13 : 07:31:17

1.select * into #temp from SAMPLE
2.insert into TARGET select * from #temp
---------------88888888888888----------------------
one more thing use nolock hint with insertion and update if possible

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-13 : 07:51:09

1a) SELECT top 0 * into #temp from sample
1b) insert #temp select * from sample

2a) insert target select * from #temp

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

chih
Posting Yak Master

154 Posts

Posted - 2008-08-14 : 18:54:38

The locking issue happens on table TARGET not table SAMPLE.
due to insert/update table TARGET at the same time. so even I use temp table, it doesn't solve locking in table TARGET.

Thank you

quote:
Originally posted by Peso

1a) SELECT top 0 * into #temp from sample
1b) insert #temp select * from sample

2a) insert target select * from #temp

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

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-14 : 19:33:29

This isn't exactly the same issue as you have, but there are some tips down the bottom which might work for you: http://sql-server-performance.com/Community/forums/p/27687/149410.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources