Ecommerce account is logged in for a long time on SQL Server

I'm monitoring our SQL Server over the weekend to see what is happening with the Ecomm login account that allows nurses to renew licenses online. Using the standard trace template which has 2 events, "Audit Login" and "Audit Logout", I find that the avera
ge over the weekend for anything over 10 minutes is 70 minutes!
SELECT AVG(Duration / 60000) AS AverageOfMinutesLoggedIn
FROM AllNightStandardProfileForEcomm
WHERE (Duration / 60000 > 10)
Scratching my head here. Does that mean that the webserver (ecomm account using java and MS JDBC driver) logs in and stays logged in until the nurse is done? Is this done at the start of a sesssion? Does this make sense? In ADO connections you login, do t
he dirty deed, and then log out. Over and over. I'm thinking that the web people wrote the code to login at session start and logout at session end. Is that correct? I'm thinking no.
It does take a while for a nurse to renew her license(s) because they have to put in all sorts of continuing education credits. One nurse took 265 minutes! And her session was logged in to the database the whole time.
Any ideas, insites?
John
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
jdnospam@.hevanet.com wrote:

> I'm monitoring our SQL Server over the weekend to see what is happening
> with the Ecomm login account that allows nurses to renew licenses online.
> Using the standard trace template which has 2 events, "Audit Login" and
>"Audit Logout", I find that the average over the weekend for anything over 10 minutes is 70 minutes!
> SELECT AVG(Duration / 60000) AS AverageOfMinutesLoggedIn
> FROM AllNightStandardProfileForEcomm
> WHERE (Duration / 60000 > 10)
> Scratching my head here. Does that mean that the webserver (ecomm account
>using java and MS JDBC driver) logs in and stays logged in until the nurse
>is done? Is this done at the start of a sesssion? Does this make sense? In
> ADO connections you login, do the dirty deed, and then log out. Over and
>over. I'm thinking that the web people wrote the code to login at session
>start and logout at session end. Is that correct? I'm thinking no.
> It does take a while for a nurse to renew her license(s) because they have
>to put in all sorts of continuing education credits. One nurse took 265 minutes!
> And her session was logged in to the database the whole time.
> Any ideas, insites?
Hi. If the JDBC client is middleware, it may be pooling connections, which is a good thing.
However, if it's a simple client, then it may keep a connection for as long as it needs,
and if it's really poorly designed, it may start a transaction to do what the nurse wants,
and then hold the connection open and do updates as the nurse does, locking data till (s)he
finishes... Maybe the application could get a connection at the start to verify the DBMS
is up, then close it and collect all the data while *not* connected to the DBMS. Then,
when it's ready to submit, re-open the connection and do the update is one quick move.
Joe Weinstein at BEA
> John
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||jdnospam@.hevanet.com wrote:

> I'm monitoring our SQL Server over the weekend to see what is happening
> with the Ecomm login account that allows nurses to renew licenses online.
> Using the standard trace template which has 2 events, "Audit Login" and
>"Audit Logout", I find that the average over the weekend for anything over 10 minutes is 70 minutes!
> SELECT AVG(Duration / 60000) AS AverageOfMinutesLoggedIn
> FROM AllNightStandardProfileForEcomm
> WHERE (Duration / 60000 > 10)
> Scratching my head here. Does that mean that the webserver (ecomm account
>using java and MS JDBC driver) logs in and stays logged in until the nurse
>is done? Is this done at the start of a sesssion? Does this make sense? In
> ADO connections you login, do the dirty deed, and then log out. Over and
>over. I'm thinking that the web people wrote the code to login at session
>start and logout at session end. Is that correct? I'm thinking no.
> It does take a while for a nurse to renew her license(s) because they have
>to put in all sorts of continuing education credits. One nurse took 265 minutes!
> And her session was logged in to the database the whole time.
> Any ideas, insites?
Hi. If the JDBC client is middleware, it may be pooling connections, which is a good thing.
However, if it's a simple client, then it may keep a connection for as long as it needs,
and if it's really poorly designed, it may start a transaction to do what the nurse wants,
and then hold the connection open and do updates as the nurse does, locking data till (s)he
finishes... Maybe the application could get a connection at the start to verify the DBMS
is up, then close it and collect all the data while *not* connected to the DBMS. Then,
when it's ready to submit, re-open the connection and do the update is one quick move.
Joe Weinstein at BEA
> John
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Very good observation. I think the operative words here are:
"collect all the data while *not* connected to the DBMS".
I shudder mentioning "connection pooling" to them. NO MORE FANCY STUFF!!!
Get in, get the money, get out.
Anything else you want to comment on I would be all ears. I know they are holding everything in sessions variables, but I'm thinking they should put it all in a de-normalized holding table. Once the payment goes through, then Bang! the database gets up da
ted and we have an extra place to look to troubleshoot. Anything in a session dies when the session dies.
One other question, is the session starting when they open the browser or when they successfully login with their ID/whatever. Off topic but hey.
Cheers,
John Davis
OSBN
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||jdnospam@.hevanet.com wrote:

> Very good observation. I think the operative words here are:
> "collect all the data while *not* connected to the DBMS".
> I shudder mentioning "connection pooling" to them. NO MORE FANCY STUFF!!!
> Get in, get the money, get out.
Good luck.

> Anything else you want to comment on I would be all ears. I know they
>are holding everything in sessions variables, but I'm thinking they
>should put it all in a de-normalized holding table. Once the payment
> goes through, then Bang! the database gets up dated and we have an
>extra place to look to troubleshoot. Anything in a session dies when the session dies.
> One other question, is the session starting when they open the browser or
> when they successfully login with their ID/whatever. Off topic but hey.
> Cheers,
> John Davis
> OSBN
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...