Re: ADO - Bad when it is time to Optimize


"Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom> wrote in message
news:OHo7vi0YHHA.4988@xxxxxxxxxxxxxxxxxxxxxxx
We have a situation where SQL server gets slow in processing requests
due to some contention issues and at that point I can see the User
Connections shooting up.

You ought to bear in mind that SQL Server is such that you can only have
_ONE_ active Server-sided, ForwardOnly cursor per-connection.
You cannot have 2 or more for the same connections. You can have multiple
types of other cursors. Just not 2 active Server-sided, ForwardOnly
cursors.
It is a SQL Server limitation.
And I beleive, but not checked it out, that ADO will duplicate a
connection
just to give you your 2nd Server-sided, ForwardOnly cursor.

I use Server-sided, ForwardOnly cursor a lot, but I tend to use them
serially so I dont run into this limitation.

Stephen Howe


A good point, as it illustrates one of the many ways it is to miss-use
connections and not even realize it. Another common mistake is to create a
disconnected recordset from a Server-Side cursor, or temporary stored
procedure.

It is rare that one doesn't find opportunities within the code whenever you
run into what appears to be 'connection pool' problem.

-ralph


.

Relevant Pages

  • Re: Bidirectional cursor navigation
    ... It has async operations (including async connections), ... has server-side AND client-side cursors, ... >> the wonders of the web implemented connected architectures. ... >> Server can support thousands of users (we ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SBS 2003 IIS BASED SERVICES FAIL INTERMITTENTLY
    ... If I read your post correctly, you have a switch where the SBS ... Run DHCP server on your SBS, and set all client machine nics to dynamic. ... Once you have your nics configured, run the Connect to the Internet wizard, ... QUESTION1 - what is REFUSING CONNECTIONS? ...
    (microsoft.public.windows.server.sbs)
  • RE: Remote access problem
    ... CEICW setting RWW is OK. ... I clicked "Connect to server desktops" and got the screen with all the ... Remote connections ... > Internet Connection wizard' to configure the server networking settings? ...
    (microsoft.public.windows.server.sbs)
  • Re: Reducing load for LAMP app?
    ... MySQL: as much as possible, he keeps query results in RAM, but ... His hoster says that Apache server is under significant load. ... Using apc is pretty much transparent, but memcached will require modifying your database abstraction layer using the memcached functions. ... With persistent connections, you must have the maximum number of connections *ever* required allocated *all of the time* - even if no one is using your server. ...
    (comp.lang.php)
  • Re: SQL clients dropping connections on WAN
    ... I can transfer files over the connections, ... Server, even see the SQL 2000 server. ... The only change made when we did the DSL switch ... The forth server is actually on it's own subnet in the DMZ of the firewall ...
    (microsoft.public.sqlserver.clients)