Multiple Updates at once?

I have an application that, upon exiting, has to update upwards of 300+
records in a SQL Server database.
There are issues with timeouts due to network traffic and server load.
I was wondering if there was a better procedure or method of transferring a
large group of simple sql tasks to the server that could perhaps be done in
one complete action. this may alleviate the timeout issue.
Thank you for your help.I take it you aren't making a single call to a stored proc? Or the 300
records can't be updated with a single UPDATE statement?
Bryce|||300 rows isn't that many to update - provided you don't do it as 300
separate UPDATE statements. Are you using stored procedures to perform the
updates? Where does the data come from?
David Portas
SQL Server MVP
--|||I am not familiar with stored procedures. How would I make a stored
procedure that would change multiple records in a database based on
different criteria, ie replace UPDATE queries like these ones.
UPDATE Movies SET MovieClassNo = 1 WHERE (MovieID = 'A1241234')
UPDATE Movies SET MovieClassNo = 2 WHERE (MovieID = 'F19892346')
UPDATE Movies SET MovieClassNo = 3 WHERE (MovieID = 'L34982423')
UPDATE Movies SET MovieClassNo = 1 WHERE (MovieID = 'Q98645949')
I have looked in the BOL, and I jsut get kinda lost when I do.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IY6dnSoM4vCqM63fRVn-oA@.giganews.com...
> 300 rows isn't that many to update - provided you don't do it as 300
> separate UPDATE statements. Are you using stored procedures to perform the
> updates? Where does the data come from?
> --
> David Portas
> SQL Server MVP
> --
>|||Atley wrote:
> I am not familiar with stored procedures. How would I make a stored
> procedure that would change multiple records in a database based on
> different criteria, ie replace UPDATE queries like these ones.
> UPDATE Movies SET MovieClassNo = 1 WHERE (MovieID = 'A1241234')
> UPDATE Movies SET MovieClassNo = 2 WHERE (MovieID = 'F19892346')
> UPDATE Movies SET MovieClassNo = 3 WHERE (MovieID = 'L34982423')
> UPDATE Movies SET MovieClassNo = 1 WHERE (MovieID = 'Q98645949')
>
Are those movieID values hard-coded or are they parameters that need to
be passed into the procedure? What abou the MoveiClassNo? If they are
parameters and you have 300 updates to run, then I'd want to know what
this process is and why you need to update 300 rows in the Movies table
when the application terminates. Passing 300 (or possible 600)
parameters to a procedure is not ideal. In that case, it would probably
be easier to call the procedure 300 times, once for each movieID.
But I'd still like to understand better the reason for the updates and
why there are so many at application termination.
David Gugick
Imceda Software
www.imceda.com|||In principle you can do something like this:
UPDATE Movies
SET movieclassno =
CASE movieid
WHEN 'A1241234' THEN 1
WHEN 'F19892346' THEN 2
WHEN 'L34982423' THEN 3
WHEN 'Q98645949' THEN 1
END
WHERE movieid IN
('A1241234',
'F19892346',
'L34982423',
'Q98645949')
but it's better to use stored procedure parameters to pass those values
rather than construct the SQL dynamically at the client. The question
is, why are you batching up all 300 updates to execute them in one go?
David Portas
SQL Server MVP
--|||These are Movies that are being transfered from one location to another in a
bulk transfer
So the movies are being scanned in, and then the location ID has to be
changed in one fell sweep.
The parameters are not known until the waybill is made and the movies are
scanned in.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110536761.241514.182540@.g14g2000cwa.googlegroups.com...
> In principle you can do something like this:
> UPDATE Movies
> SET movieclassno =
> CASE movieid
> WHEN 'A1241234' THEN 1
> WHEN 'F19892346' THEN 2
> WHEN 'L34982423' THEN 3
> WHEN 'Q98645949' THEN 1
> END
> WHERE movieid IN
> ('A1241234',
> 'F19892346',
> 'L34982423',
> 'Q98645949')
> but it's better to use stored procedure parameters to pass those values
> rather than construct the SQL dynamically at the client. The question
> is, why are you batching up all 300 updates to execute them in one go?
> --
> David Portas
> SQL Server MVP
> --
>|||These are Movies that are being transfered from one location to another in a
bulk transfer
So the movies are being scanned in, and then the location ID has to be
changed in one fell sweep.
The parameters are not known until the waybill is made and the movies are
scanned in.
This has to be done before the application is closed or the waybill is
printed, or the waybill is closed.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:uAWkxjdJFHA.3960@.TK2MSFTNGP09.phx.gbl...
> Atley wrote:
> Are those movieID values hard-coded or are they parameters that need to
> be passed into the procedure? What abou the MoveiClassNo? If they are
> parameters and you have 300 updates to run, then I'd want to know what
> this process is and why you need to update 300 rows in the Movies table
> when the application terminates. Passing 300 (or possible 600)
> parameters to a procedure is not ideal. In that case, it would probably
> be easier to call the procedure 300 times, once for each movieID.
> But I'd still like to understand better the reason for the updates and
> why there are so many at application termination.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Atley wrote:
> These are Movies that are being transfered from one location to
> another in a bulk transfer
> So the movies are being scanned in, and then the location ID has to be
> changed in one fell sweep.
> The parameters are not known until the waybill is made and the movies
> are scanned in.
> This has to be done before the application is closed or the waybill is
> printed, or the waybill is closed.
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:uAWkxjdJFHA.3960@.TK2MSFTNGP09.phx.gbl...
I would try writing a stored procedure to do the update and calling it
300 times from the application. It should happen very quickly. Pass the
MovieClassNo and MoveID to the procedure. This option keeps all the
intelligence in the database.
Before I would consider anything else, I would try that.
David Gugick
Imceda Software
www.imceda.com|||CREATE TABLE WayBills
(movieid CHAR (8) NOT NULL PRIMARY KEY,
movie_class_nbr INTEGER NOT NULL
CHECK (movie_class_nbr BETWEEN 1 AND 4));
Then use the collected data:
UPDATE Movies
SET movie_class_nbr
= (SELECT movie_class_nbr
FROM WayBills AS W1
WHERE W1.movie_id = Movies.movie-id)
WHERE EXISTS (SELECT *
FROM WayBills AS W1
WHERE W1.movie_id = Movies.movie_id);