Windows Server

Colin Bowern · Guest

Hi,

I'm noticing up to a 60 second latency to close the CS 2002 OLEDB Provider
connection after querying the Profiles resource. The code is running off a
local development server with a local SQL instance. A quick SQL Profiler
trace shows me that the SELECT statement executes right away and then after
the delay an Audit Logout event shows up. I'm concerned that moving this
new code into production will show the same results.

The query itself is relatively simple:

SELECT "EmailAddress" FROM "UserObject" WHERE "EmailAddress"=N'a@b.com'

Thoughts?

Thanks,
Colin

David Messner [MSFT] · Guest

I'm not quite sure the sequence of open, query, close that you're using but
the recommendation is to cache the CSOLEDBProvider connection and reuse it
for the lifetime of your applicaiton - it internally maintains its own
connection pool and is fully free-threaded. Yes this is counter to how you
use other OLEDB and ODBC connections, but this is by far the most efficient
way to use CSOLEDB.

-djm
--
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. © 2005 Microsoft Corporation. All rights
reserved.

--------------------
From: "Colin Bowern" <colin.bowern@nospam.officialcommunity.com>
Subject: CS OLEDB Provider Close Latency
Date: Mon, 1 Aug 2005 17:05:10 -0400
Lines: 19
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-RFC2646: Format=Flowed; Original
Message-ID: <uoaNFztlFHA.1148@TK2MSFTNGP12.phx.gbl>
Newsgroups: microsoft.public.commerceserver.general
NNTP-Posting-Host: mail.officialcommunity.net 207.188.77.211
Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.commerceserver.general:3126
X-Tomcat-NG: microsoft.public.commerceserver.general

Hi,

I'm noticing up to a 60 second latency to close the CS 2002 OLEDB Provider
connection after querying the Profiles resource. The code is running off a
local development server with a local SQL instance. A quick SQL Profiler
trace shows me that the SELECT statement executes right away and then after
the delay an Audit Logout event shows up. I'm concerned that moving this
new code into production will show the same results.

The query itself is relatively simple:

SELECT "EmailAddress" FROM "UserObject" WHERE "EmailAddress"=N'a@b.com'

Thoughts?

Thanks,
Colin

Colin Bowern · Guest

Hi David,

I am using it similar to how I would use other database connections and
closing it right after use. I will make the change to cache the connection
though based on your advice.

Thanks,
Colin

"David Messner [MSFT]" <davidme@online.microsoft.com> wrote in message
news:3rj9IuHmFHA.940@TK2MSFTNGXA01.phx.gbl...

Colin Bowern · Guest

Hi David,

I cached the connection however it appears after doing some tracing with
JetBrains dotTrace that the issue is in the
System.Data.OleDb.OleDbConnection.State property. It takes approximately 60
seconds for it to return a value. Do you have any thoughts to what would
cause CSOLEDB to take 60 seconds to return the connection state?

Thanks,
Colin

"Colin Bowern" <colin.bowern@nospam.officialcommunity.com> wrote in message
news:uJ54IrQmFHA.4000@TK2MSFTNGP12.phx.gbl...

David Messner [MSFT] · Guest

No, we've not seen that issue before. Are you calling the
OleDbConnection.State property directly or is it being called from a
Commerce Server code path? If the latter, can you send the call stack? If
the former, maybe you can find a way to avoid using that property?

Thanks
-djm
--
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. © 2005 Microsoft Corporation. All rights
reserved.

--------------------
From: "Colin Bowern" <colin.bowern@nospam.officialcommunity.com>
Subject: Re: CS OLEDB Provider Close Latency
Date: Thu, 4 Aug 2005 17:21:36 -0400

Hi David,

I cached the connection however it appears after doing some tracing with
JetBrains dotTrace that the issue is in the
System.Data.OleDb.OleDbConnection.State property. It takes approximately
60
seconds for it to return a value. Do you have any thoughts to what would
cause CSOLEDB to take 60 seconds to return the connection state?

Thanks,
Colin

"Colin Bowern" <colin.bowern@nospam.officialcommunity.com> wrote in message
news:uJ54IrQmFHA.4000@TK2MSFTNGP12.phx.gbl...

Colin Bowern · Guest

Hi David,

Here's the call stack:

100.00 % Root - 60295 ms
99.92 % Thread #1419920 - 60248 ms
99.92 % ProcessRequest - 60248 ms - 1 call -
System.Web.Hosting.ISAPIRuntime.ProcessRequest(int, int32)
99.92 % ProcessRequest - 60248 ms - 1 call -
System.Web.HttpRuntime.ProcessRequest(HttpWorkerRequest)
99.92 % ProcessRequestInternal - 60248 ms - 1 call -
System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest)
99.92 % BeginProcessRequest - 60247 ms - 1 call -
System.Web.HttpApplication.BeginProcessRequest(HttpContext, AsyncCallback,
Object) (from System.Web.IHttpAsyncHandler)
99.91 % ResumeSteps - 60238 ms - 1 call -
System.Web.HttpApplication.ResumeSteps(Exception)
99.90 % ExecuteStep - 60237 ms - 31 calls -
System.Web.HttpApplication.ExecuteStep(IExecutionStep, bool &)
99.90 % Execute - 60235 ms - 1 call -
System.Web.HttpApplication.CallHandlerExecutionStep.Execute() (from
System.Web.HttpApplication+IExecutionStep)
99.90 % ProcessRequest - 60235 ms - 1 call -
System.Web.UI.Page.ProcessRequest(HttpContext)
99.90 % ProcessRequest - 60235 ms - 1 call -
System.Web.UI.Page.ProcessRequest()
99.90 % ProcessRequestMain - 60233 ms - 1
all - System.Web.UI.Page.ProcessRequestMain()
99.86 % RaisePostBackEvent - 60213 ms - 1
all - System.Web.UI.Page.RaisePostBackEvent(NameValueCollection)
99.86 % RaisePostBackEvent - 60213 ms - 1
call - System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler,
String)
99.86 % RaisePostBackEvent - 60213 ms - 1
call - System.Web.UI.WebControls.Button.RaisePostBackEvent(String) (from
System.Web.UI.IPostBackEventHandler)
99.86 % OnClick - 60213 ms - 1 call -
PeterBlum.VAM.Button.OnClick(EventArgs)
99.86 % OnClick - 60213 ms - 1 call -
System.Web.UI.WebControls.Button.OnClick(EventArgs)
99.86 % Submit_Click - 60213 ms - 1
call - OfficialCommunity.Store.Pages.SignIn.Submit_Click(Object,
EventArgs)
99.86 % Authenticate - 60213 ms -
1 call -
OfficialCommunity.CommerceServer.Users.Authentication.CSAuthProvider.Authenticate(String,
String, bool)
99.86 % Exists - 60212 ms - 1
call - OfficialCommunity.CommerceServer.Users.UserProfile.Exists(String)
99.86 % ProfileExists - 60212
s - 1 call -
OfficialCommunity.CommerceServer.Users.ProfileBase.ProfileExists(String,
String, String)
99.86 %
ExecuteProfileResourceReader - 60212 ms - 1 call -
OfficialCommunity.CommerceServer.DatabaseManager.ExecuteProfileResourceReader(String)
99.62 % get_State - 60066
s - 1 call - System.Data.OleDb.OleDbConnection.get_State()

Below is the code for the procedure that is calling the State variable. I
would like to use the State variable for code safety so I'm hoping this
problem can be diagnosed and resolved:

public static OleDbDataReader ExecuteProfileResourceReader(string
sqlCommand)
{
ArgumentValidation.CheckForEmptyString(sqlCommand, "sqlCommand");

OleDbConnection dbConnection =
CacheManager.CacheLookup(RuntimeCacheKey.CSOLEDBProvider) as
OleDbConnection;

if((dbConnection == null)||(dbConnection.State ==
ConnectionState.Closed))
{
// Clean up the connection string for use by the OleDbConnection
object.
string dbConnectionString =
CommerceServerConfiguration.GetConfig().CommerceProviderConnectionString;
StringBuilder dbConnectionStringBuilder = new
StringBuilder(dbConnectionString);

if(!Regex.IsMatch(dbConnectionString, "provider=",
RegexOptions.IgnoreCase))
dbConnectionStringBuilder.Insert(0, "Provider=Commerce.DSO;");

if(!Regex.IsMatch(dbConnectionString, "data source=",
RegexOptions.IgnoreCase))
dbConnectionStringBuilder.Replace("url=", "Data Source=");

// The MS recommendation is "to cache the CSOLEDBProvider connection
// and reuse it for the lifetime of the applicaiton - it internally
// maintains its own connection pool and is fully free-threaded.
// This is counter to how you use other OLEDB and ODBC connections,
// but this is by far the most efficient way to use CSOLEDB." -
David Messner [MSFT]
dbConnection = new
OleDbConnection(dbConnectionStringBuilder.ToString());
dbConnection.Open();

CacheManager.CacheInsert(RuntimeCacheKey.CSOLEDBProvider,
dbConnection, true);
}

// Setup command execution
OleDbCommand dbCommand = new OleDbCommand(sqlCommand, dbConnection);
dbCommand.CommandType = CommandType.Text;

// Return the executed command
return dbCommand.ExecuteReader( CommandBehavior.Default );
}

I can reproduce the error with a fairly high degree of consistency. I'll be
out of the office until August 15th, so I'll make sure to check back then.

Thanks,
Colin

"David Messner [MSFT]" <davidme@online.microsoft.com> wrote in message
news:h%23WU4mdmFHA.3472@TK2MSFTNGXA01.phx.gbl...

Colin Bowern · Guest

Hi David,

Any thoughts on what may be causing the latency?

Thanks,
Colin

"Colin Bowern" <colin.bowern@nospam.officialcommunity.com> wrote in message
news:uFWfxigmFHA.3144@TK2MSFTNGP12.phx.gbl...

David Messner [MSFT] · Guest

Good news, we've been able to reproduce the issue.

It occurs when you use OleDbCommand.ExecuteReader AND you don’t call
OleDbDataReader.Dispose on the object returned. Our recommendation is that
you always manually dispose the OleDbDataReader objects returned by his
helper method.

Hope this helps
-djm

--------------------
From: "Colin Bowern" <colin.bowern@nospam.officialcommunity.com>
Subject: Re: CS OLEDB Provider Close Latency
Date: Mon, 15 Aug 2005 08:58:42 -0400

Hi David,

Any thoughts on what may be causing the latency?

Thanks,
Colin

"Colin Bowern" <colin.bowern@nospam.officialcommunity.com> wrote in message
news:uFWfxigmFHA.3144@TK2MSFTNGP12.phx.gbl...

Colin Bowern · Guest

Hi David,

That's great news, thanks for the update! Should I also stick with not
closing the CSOLEDB connection as well then as per your original
recommendation in addition to disposing of the OleDbDataReader?

Thanks,
Colin

"David Messner [MSFT]" <davidme@online.microsoft.com> wrote in message
news:LWeL%23XsoFHA.944@TK2MSFTNGXA01.phx.gbl...

David Messner [MSFT] · Guest

Yes, definitely - you want to share one CSOLEDB connection for the entire
application; it creates its own connection pool, very expensive.

-djm
--------------------
From: "Colin Bowern" <colin.bowern@nospam.officialcommunity.com>
Subject: Re: CS OLEDB Provider Close Latency
Date: Thu, 18 Aug 2005 17:37:33 -0400

Hi David,

That's great news, thanks for the update! Should I also stick with not
closing the CSOLEDB connection as well then as per your original
recommendation in addition to disposing of the OleDbDataReader?

Thanks,
Colin

"David Messner [MSFT]" <davidme@online.microsoft.com> wrote in message
news:LWeL%23XsoFHA.944@TK2MSFTNGXA01.phx.gbl...

	Windows Server Forum Index -> Commerce Server	All times are GMT
Page 1 of 1