large table update causes timeout expired

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)

large table update causes timeout expired

Author

Topic

viktors
Starting Member

7 Posts

Posted - 2008-12-22 : 07:54:22

Hi!

I have a table with 1,000,000 rows (MS SQL 2005).

The problem is that when I excetute a simple update procedure:

CREATE PROCEDURE dbo.UpdateProcedure
AS
UPDATE TestData
SET TestData.Field = 'value'
RETURN

I get "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." error message after some 30 seconds. I am executing this procedure form VisualWebDeveloper's procedure create/view screen.

Can anybody suggest the SQL syntax to avoid the timeout. I tried to put
SET LOCK_TIMEOUT -1 and SET LOCK_TIMEOUT 1000000
in front of UPDATE but that didn't change anything.

Also when I was creating this 1m row table with procedure:

CREATE PROCEDURE dbo.StoredProcedure1
AS
DECLARE @Counter INT
SET @Counter = 0
WHILE @Counter < 1000000
BEGIN
SET @Counter = @Counter + 1
INSERT INTO TestData(Field) VALUES ('test')
END
RETURN

it gave me the same error, though some 60,000 rows were inserted during each execution of the procedure...

At the same time,
CREATE PROCEDURE dbo.UpdateProcedure
AS
UPDATE TestData
SET TestData.Field = TestData.OtherField
RETURN
always seems to go through...

I would appreciate any suggestions,

viktors

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-12-22 : 08:30:59

That's not a SQL error. A timeout is the client application's response to a query taking longer than it's timeout is set to. The default timeout in ADO/ADO.NET is 30 sec. If you want to change that, you'll have to change the client app.

CREATE PROCEDURE dbo.StoredProcedure1
AS
DECLARE @Counter INT
SET @Counter = 0
WHILE @Counter < 1000000
BEGIN
SET @Counter = @Counter + 1
INSERT INTO TestData(Field) VALUES ('test')
END
RETURN

That's a really, really slow way of creating a million row table. Why do you need to create 1000000 identical rows from a client app, and why use that method?

--
Gail Shaw
SQL Server MVP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 08:42:29

The same question goes regarding to the UPDATE thingy.
There is no WHERE clause so all records are updated to same value.

E 12°55'05.63"
N 56°04'39.26"

viktors
Starting Member

7 Posts

Posted - 2008-12-22 : 10:06:53

I needed to create a large table (with 1m rows) to test connected data access vs disconnected (in memory) data access in ASP.NET 2.0 - speed and memory usage.

The scenario is to select a number of rows from a large table. Certainly, when I issue SELECT statement with WHERE clause (and table has non-unique indices in place) it executes fast. Disconnected data access with DataTable (and DataView) is faster though but that's not related to the timeout issue.

I just get this timeout error when try to manipulate my test table. Maybe, if that's not a SQL error, I need to change some parameters in the application. I came across such parameters as connection timeout and command timeout.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-22 : 10:15:30

This should onyl take a second or two to complete.

CREATE PROCEDURE dbo.StoredProcedure1 
AS

INSERT		TestData
		(
			Field
		)
SELECT		TOP 1000000
		'Test'
FROM		master..spt_values AS v1
INNER JOIN	master..spt_values AS v2 ON v2.type = 'p'
WHERE		v1.type = 'P'

E 12°55'05.63"
N 56°04'39.26"

viktors
Starting Member

7 Posts

Posted - 2008-12-22 : 11:58:42

Peso, thanks for your code!

Indeed it added 1m rows to my test table and, though it took a little bit more time on my pc, it didn't time out:)

As far as I got this, you used system table master..spt_values joined with itself to create a large table, then selected top 1m rows from it (value 'Test') and inserted that into TestData table. I saw your post on the use of master..spt_values table http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102936 :)

viktors
Starting Member

7 Posts

Posted - 2008-12-23 : 06:50:10

Also, running my stored procedures from the app code instead of create/modify screen and setting the commandtimeout property long enough solved all timeout issues.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources