Update big table - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Update big table

Author

Topic

Spyhabs
Starting Member

2 Posts

Posted - 2010-05-05 : 12:44:38

I'd like to know how to update a big tables. I need to update for example 1000 rows in a table of 200000 entries.

I do it for each update : update table set status='PENDING' where id=X and uid = Y.

I use C# to send this SQL request to the server for each row to update. It takes > 10 minutes to update all rows.

Is there a way to improve an update of multiple rows in a table?

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-05 : 13:32:25

Yes, you can write a single UPDATE stetement to update all records at once.
How do you determine the records to be updated? Is it from another table?

X002548
Not Just a Number

15586 Posts

Posted - 2010-05-05 : 13:42:27

In other words...where do x and y come from?

And you should do this as a store procedure

That's just ny opinion

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2010-05-05 : 13:58:42

I would probably pass two lists (e.g. "1,2,3,4,5" style) to a stored procedure:

List1 would the the IDs, and List2 would be the UIDs (in the same order), and then I would split both lists and join then together to get a set of values of ID and UID and join that to the main table.

But that's all probably considered to be "fairly advanced"

so may be a bridge-too-far for you at present?

It would basically look something like this where IDs are 10,11,12,13,14 and UIDs are 20,21,22,23,24


DECLARE @List_ID varchar(8000),
        @List_UID varchar(8000) 

SELECT @List_ID= '10,11,12,13,14',
       @List_UID '20,21,22,23,24'

-- (The above is just to simulate the data coming from the C# application)

UPDATE U
SET status='PENDING'
FROM MyTable AS U
    JOIN dbo.MySplitFunction(@List_ID) AS L_ID
        ON L_ID.Value = U.id
    JOIN dbo.MySplitFunction(@List_UID) AS L_UID
        ON L_UID.Value = U.uid
       AND L_UID.Item = L_ID.Item    -- Keep in Sync with the L_ID list

MySplitFunction is something you would have to create - plenty of examples of such things on SQLTeam.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-05 : 14:20:38

quote:
Originally posted by Kristen

I would probably pass two lists (e.g. "1,2,3,4,5" style) to a stored procedure:

If the OP has SQL 2008 then I would avoid strings and pass a table valued parameter to the stored procedure. But, since we have little to no information, I would not hazard a guess on how to implement a solution.

Spyhabs
Starting Member

2 Posts

Posted - 2010-05-05 : 14:39:42

I like your solution Kristen, I will try tonight.

Thanks

quote:
Originally posted by Kristen

I would probably pass two lists (e.g. "1,2,3,4,5" style) to a stored procedure:

List1 would the the IDs, and List2 would be the UIDs (in the same order), and then I would split both lists and join then together to get a set of values of ID and UID and join that to the main table.

But that's all probably considered to be "fairly advanced" so may be a bridge-too-far for you at present?

It would basically look something like this where IDs are 10,11,12,13,14 and UIDs are 20,21,22,23,24

DECLARE @List_ID varchar(8000),
@List_UID varchar(8000)

SELECT @List_ID= '10,11,12,13,14',
@List_UID '20,21,22,23,24'

-- (The above is just to simulate the data coming from the C# application)

UPDATE U
SET status='PENDING'
FROM MyTable AS U
JOIN dbo.MySplitFunction(@List_ID) AS L_ID
ON L_ID.Value = U.id
JOIN dbo.MySplitFunction(@List_UID) AS L_UID
ON L_UID.Value = U.uid
AND L_UID.Item = L_ID.Item -- Keep in Sync with the L_ID list

MySplitFunction is something you would have to create - plenty of examples of such things on SQLTeam.

Kristen
Test

22859 Posts

Posted - 2010-05-05 : 15:22:19

quote:
Originally posted by Lamprey

If the OP has SQL 2008 then I would avoid strings and pass a table valued parameter to the stored procedure. But, since we have little to no information, I would not hazard a guess on how to implement a solution.

Indeed. There are a variety of flavours for passing such data - even XML if-you-must!

But I think the first thing is to experience the improvement of a set-based solution, rather than passing rows to be updated from the C# application one-by-one

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-05 : 15:47:03

quote:
Originally posted by Kristen

I would probably pass two lists (e.g. "1,2,3,4,5" style) to a stored procedure:

List1 would the the IDs, and List2 would be the UIDs (in the same order), and then I would split both lists and join then together to get a set of values of ID and UID and join that to the main table.

But that's all probably considered to be "fairly advanced" so may be a bridge-too-far for you at present?

It would basically look something like this where IDs are 10,11,12,13,14 and UIDs are 20,21,22,23,24

DECLARE @List_ID varchar(8000),
@List_UID varchar(8000)

SELECT @List_ID= '10,11,12,13,14',
@List_UID '20,21,22,23,24'

-- (The above is just to simulate the data coming from the C# application)

UPDATE U
SET status='PENDING'
FROM MyTable AS U
JOIN dbo.MySplitFunction(@List_ID) AS L_ID
ON L_ID.Value = U.id
JOIN dbo.MySplitFunction(@List_UID) AS L_UID
ON L_UID.Value = U.uid
AND L_UID.Item = L_ID.Item -- Keep in Sync with the L_ID list

MySplitFunction is something you would have to create - plenty of examples of such things on SQLTeam.

If performance is a major concern, you'd be better off using a split function that takes both strings, and returns a table with columns. That would simplify the join. Something like this:

CREATE FUNCTION dbo.Split2 (@List1 VARCHAR(8000), @List2 VARCHAR(8000))
RETURNS @rv TABLE (Value1 INT, Value2 INT)
AS
BEGIN
DECLARE @Pos1 INT SET @Pos1 = -1
DECLARE @Pos2 INT SET @Pos2 = -1
DECLARE @LastPos1 INT SET @LastPos1 = 0
DECLARE @LastPos2 INT SET @LastPos2 = 0
DECLARE @Value1 INT
DECLARE @Value2 INT

	SET @List1 = REPLACE(@List1, ' ', '') + ','
	SET @List2 = REPLACE(@List2, ' ', '') + ','

	SET @Pos1 = CHARINDEX(',', @List1, @Pos1 + 1)
	WHILE @Pos1 != 0
	BEGIN	
		SET @Pos2 = CHARINDEX(',', @List2, @Pos2 + 1)
		IF @Pos2 != 0
		BEGIN
			SET @Value1 = CAST(SUBSTRING(@List1, @LastPos1, @Pos1 - @LastPos1) AS INT)
			SET @Value2 = CAST(SUBSTRING(@List2, @LastPos2, @Pos2 - @LastPos2) AS INT)
			SET @LastPos1 = @Pos1 + 1
			SET @LastPos2 = @Pos2 + 1
			SET @Pos1 = CHARINDEX(',', @List1, @Pos1 + 1)
		END
		ELSE
			RETURN
		
		INSERT INTO @RV SELECT @Value1, @Value2
	END
	RETURN 
END
GO

Then your update statement would look something like this:

UPDATE U
SET status='PENDING'
FROM MyTable AS U
    JOIN dbo.Split2(@List_ID, @List_UID) AS L
        ON L.Value1 = U.id
        AND L.Value2 = U.uid

Also, you'll get better performance if you put an index on (id, uid).

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Kristen
Test

22859 Posts

Posted - 2010-05-06 : 02:14:49

Never thought to use a single function to split two lists. We do have two-list scenarios quite often (a bunch of checkboxes on the page with IDs and Values, only some of which are selected ...) so I might look into that

However, I expect there is more performance to be gained from the black art of choosing the optimal SPLIT function than the difference between a two-list-splitter and a JOIN ... but I'm just guessing ... I could run a test I suppose ...

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-06 : 03:59:59

It would probably depend on the length of the lists as well..

You could improve the above split function with the following change:

--	SET @List1 = REPLACE(@List1, ' ', '') + ','
--	SET @List2 = REPLACE(@List2, ' ', '') + ','
	SET @List1 = @List1 + ','
	SET @List2 = @List2 + ','

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-05-06 : 07:34:58

I vote for table valued parameters. They are awesome for this kind of stuff...no nasty split-functions

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Kristen
Test

22859 Posts

Posted - 2010-05-06 : 13:52:06

I'm not convinced that, even now, it is easy to pass multiple-values from the App to SQL Server - but I'll be extremely happy to see an example that proves me wrong

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-05-07 : 03:17:51

It depends what you define as "easy" but I implemented a TVP (table valued parameter) in roughly 5 minutes in a .net-project I was a part of recently.

-> http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-07 : 05:36:48

quote:
Originally posted by Lumbago

It depends what you define as "easy" but I implemented a TVP (table valued parameter) in roughly 5 minutes in a .net-project I was a part of recently.

-> http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Looks pretty easy to me. :)

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources