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);

Multiple simultaneous osql network connections fail

I am attempting to make 1000 simultaneous osql connections from one Windows
2000 server with sp4 to another Windows 2000 sever with sp4 that is hosting
a SQL 2000 server database with sp3a, but I reach an upper limit of about
30 - 40 connections.
Is osql limited to the number of simultaneous connections it can open to
another server? The database server isn't limited, so it must be the
client. If so, can that be increased?There isn't an OSQL limitation for connection requests other than the
resources on the box. There's something else going on if you can only make
30 to 40 connections. What happens when you try to make the 41st
connection? What error does the client receive?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||This is the error I get for the jobs that failed after, in this case,
submitting 50 osql jobs simultaneously. 42 succeeded and 8 did not.
Application popup: osql.exe - Application Error : The application failed to
initialize properly (0xc0000142). Click on OK to terminate the application.
event id 26
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:kMxpahY0DHA.3564@.cpmsftngxa07.phx.gbl...

quote:

---

> There isn't an OSQL limitation for connection requests other than the
> resources on the box. There's something else going on if you can only

---

make

quote:

---

> 30 to 40 connections. What happens when you try to make the 41st
> connection? What error does the client receive?
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>

---

|||SQL Server uses a non-interactive desktop heap. The non-interactive desktop
heap
may run out of memory if a lot of processes are created. When there is not
sufficient memory to satisfy an
allocation request, an error is returned and the user is notified that the
computer is low on memory. Some programs may not handle the
failure, and sometimes there may not be sufficient memory to create the
error
message dialog box. When there is not sufficient memory to create the error
message dialog box, the requested operation may fail without generating an
error message.
WARNING: If you use Registry Editor incorrectly, you may cause serious
problems that may require you to reinstall your operating system. Microsoft
cannot guarantee that you can solve problems that result from using
Registry
Editor incorrectly. Use Registry Editor at your own risk.
To work around this problem, follow these steps:
1. At a command prompt, type "REGEDT32.EXE" to start Registry Editor.
2. In Registry Editor, locate the following registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl
Set\Control\Session
Manager\SubSystems
3. In the right pane of Registry Editor, click "Windows".
4. On the "Edit" menu, click "Modify".
5. In the "Edit String" dialog box, locate the "SharedSection"
parameter string in the "Value
data" text box, and then specify an increased value for the
"SharedSection" parameter.
Note The SharedSection parameter specifies the system and desktop by using
the following
format, where <<xxxx>> defines the maximum size of the system-wide heap
(in
kilobytes), <<yyyy>> defines the size of the per desktop heap, and
<<zzzz>> is the size of the desktop heap for each desktop that is
associated with a non-interactive Windows station:
SharedSection=<<xxxx>>,<<yyyy>>,<<zzzz>>
6. Click "OK".
Increasing the non-interactive desktop heap by 256K or 512K
typically provides sufficient memory to resolve the problem.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Does Windows Server 2003 still have the 48 MB limitation in
system-wide space for the heaps?
David Martin
kevmc@.online.microsoft.com (Kevin McDonnell [MSFT]) wrote in message news:<WfRfCD90DHA.2256@.cpmsftngxa07
.phx.gbl>...

quote:

---

> SQL Server uses a non-interactive desktop heap. The non-interactive deskto
p
> heap
> may run out of memory if a lot of processes are created. When there is not
> sufficient memory to satisfy an
> allocation request, an error is returned and the user is notified that the
> computer is low on memory. Some programs may not handle the
> failure, and sometimes there may not be sufficient memory to create the
> error
> message dialog box. When there is not sufficient memory to create the erro
r
> message dialog box, the requested operation may fail without generating an
> error message.
>
> WARNING: If you use Registry Editor incorrectly, you may cause serious
> problems that may require you to reinstall your operating system. Microso
ft
> cannot guarantee that you can solve problems that result from using
> Registry
> Editor incorrectly. Use Registry Editor at your own risk.
>
> To work around this problem, follow these steps:
> 1. At a command prompt, type "REGEDT32.EXE" to start Registry Editor.
> 2. In Registry Editor, locate the following registry key:
> HKEY_LOCAL_MACHINE\SYSTEM\CurrentControl
Set\Control\Session
> Manager\SubSystems
> 3. In the right pane of Registry Editor, click "Windows".
> 4. On the "Edit" menu, click "Modify".
> 5. In the "Edit String" dialog box, locate the "SharedSection"
> parameter string in the "Value
> data" text box, and then specify an increased value for the
> "SharedSection" parameter.
> Note The SharedSection parameter specifies the system and desktop by using
> the following
> format, where <<xxxx>> defines the maximum size of the system-wide hea
p
> (in
> kilobytes), <<yyyy>> defines the size of the per desktop heap, and
> <<zzzz>> is the size of the desktop heap for each desktop that is
> associated with a non-interactive Windows station:
> SharedSection=<<xxxx>>,<<yyyy>>,<<zzzz>>
> 6. Click "OK".
>
> Increasing the non-interactive desktop heap by 256K or 512K
> typically provides sufficient memory to resolve the problem.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.

---

|||The Windows 2003 default setting is:
SharedSection=1024,3072,512
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||That did the trick.
Thanks!
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:d9bDOnU1DHA.3564@.cpmsftngxa07.phx.gbl...

quote:

---

> The Windows 2003 default setting is:
> SharedSection=1024,3072,512
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>

---

|||I'm having a problem that I think is related to this non-interactive
desktop heap thing. In Windows 2003, I'm using ASP.NET to start a
large number of processes (about 250 processes that use very little CPU
and about 2KB per process). After the first 150 or so are started, I
get the "The application failed to initialize properly (0xc0000142)"
error. The processes show up in Windows Task Manager but don't
function properly. The ASP.NET application runs in its own application
pool that runs under the System account. Also, after attempting to
launch this large number of processes from an ASP.NET page, all other
ASP.NET pages running under the same application pool randomly get a
"Compiler Error 128". I've tried changing the SharedSection key to
1024,3072,1024 which should be more than sufficient if this is the
problem. Any suggestions?
Kevin McDonnell [MSFT] wrote:
> *The Windows 2003 default setting is:
> SharedSection=1024,3072,512
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no
> rights. *
DavidGrampa
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message237589.html