annoying delay during refresh of aspx web page with ODBC Access query on IIS Windows 2003

[Wolfgang Kaml]

Hello All,

I have been working on this for almost a week now and I haven't anything up
my sleeves anymore that I could test in addition or change....
Since I am not sure, if this is a Windows 2003 Server or ADO or ODBC issue,
I am posting this on all of the three newsgroups.

That's the setup:
Windows 2003 Server with IIS and ASP.NET actiavted
Access 2002 mdb file (and yes, proper rights are set on TMP paths and path,
where mdb file is located)
ASP.Net web server component that will query and update the Access DB in the
functionality of a web counter

The problem is, that after the first call of the web site with the web
counter component, the refresh will take about 8-9 seconds. Doesn't matter
if I close the IE or just hit the refresh.

From what I can observe is, that after the start of the IIS web server no
w3wp.exe process is running. As soon as I open the aspx web site with the
..Net built counter component, a w3wp.exe process is being instantiated and
the calls to the Access DB are made. Since the w3wp.exe process has to
start, the inital display of the web page takes about 1-2 seconds and
everything works fine. I can see a .tmp file being created in the TMP
directory and a .ldb file being created in the path of the .mdb file for a
brief moment. Then they disappear. The w3wp.exe process stays. Then, if I
hit refresh or close and reopen the web site, nothing seems to happen for
8-9 seconds and then the page displays without any errors. The difference
is, that the .tmp file and the .ldb file are also sitting in the
corresponding paths for a while longer, but both disappear again.

Since I have been doing quiet a bit of trouble shooting already, here is
some detailed info as of what happens during the inital call of the web
site, and during subsequent calls:

Output of current second and millisecond of the machine during initial call,
when no w3wp.exe process existed yet:
58:343 objCounterDBConnection = New OdbcConnection
58:359 objCounterDBCommand = New OdbcCommand
58:359 objCounterDBConnection.Open()
58:390 objCounterDbRdr = objCounterDBCommand.ExecuteReader
58:562 objCounterDbRdr.Read
58:562 objCounterDbRdr.Close()
58:578 objCounterDBConnection.Close()
58:625 preparing insert
58:640 objCounterDBConnection.Open()
58:640 objCounterDBCommand.ExecuteNonQuery()
58:640 objCounterDBConnection.Close()
58:687 objCounterDBConnection & objCounterDBCommand.Dispose()
58:687 finished

Output of current second and millisecond of the machine during subsequent
call, when existing w3wp.exe process apparently take the job:
10:359 objCounterDBConnection = New OdbcConnection
10:359 objCounterDBCommand = New OdbcCommand
10:359 objCounterDBConnection.Open()
10:375 objCounterDbRdr = objCounterDBCommand.ExecuteReader
19:140 objCounterDbRdr.Read
19:140 objCounterDbRdr.Close()
19:140 objCounterDBConnection.Close()
19:187 preparing insert
19:187 objCounterDBConnection.Open()
19:187 objCounterDBCommand.ExecuteNonQuery()
19:187 objCounterDBConnection.Close()
19:234 objCounterDBConnection & objCounterDBCommand.Dispose()
19:234 finished

As you can see, during a subsequent call, the ExecuteReader takes close to 9
seconds before it returns the OdbcDataReader object.

WHY????

This is not an update and hence has nothing to do with lazy propagation
issues as far as I can tell what I have seen on different posts.
Also, this problem occurs only on the Windows 2003 Server, not the Windows
2000 Server.
What is the issue here? - the initial w3wp.exe process is being run under
the NETWORK SERVICE account and I have given that account proper permissions
to perform the job. That is being verified in my opinion by the fact, that
the first run of the component works fine.
To me this is an absolute issue of Windows 2003 Server and/or ODBC on
Windows 2003 Server.

Is it ODBC, is it the Windows 2003 server? I have no clue.

Your help on that would be really appreciated and I can post the sources if
necessary if somebody else would like to try that out. I'm almost 99.99%
certain that this is a true Windows 2003 Server or ODBC issue that I am just
about ready to open a call with Microsoft on that.
The source is clean as it could be and I have double checked with quiet a
few examples on MSDN and other Tech articels on that.

Yesterday I had the IIS and application part completly removed from the
server, everything cleaned up and started all over with the install. Exact
same results. No other pocesses are running on that machine at all. It's
clean as it could be. So I am really stumbling on that.

Please help!

Thank you,
Wolfgang

[Cowboy \(Gregory A. Beamer\)]

I have not read everything, but can get you to a point that might help.

1. Get rid of ODBC. It is God-awful slow. Use OleDb instead with a
connection string and avoid the whole DSN and/or ODBC methodology.
2. If you have tons of code in your ASP.NET project, re-architect and move
business code to business layer components. This will reduce heavy JIT
loads.

Another option is "you have outgrown Access." If you database is in the
multiple megs (over 25MB in some cases, more in others), you are ending up
with an inefficient database. Access starts to really puke when it grows,
esp. with web apps.

Note that Windows Server 2003 can reduce overhead, if you have apps in the
proper pools. Since you do not mention multiple apps, this is unlikely the
problem. I mention largely because improper setup of apps can cause Windows
Server 2003 to take its own sweet time.

NOTE: In Whidbey, there is a precompilation option (finally). Unfortunately,
this does not help the immediate problem. I would focus on getting rid of
ODBC and using OleDb and figuring out what code is taking a long time to JIT
compile. Moving some code to business components may reduce load on the app
JITting. This will force some refactoring of code, but it will pay off
rather quickly as the app can JIT business components as needed, rather than
focusing on the entire ASP.NET app.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************
"Wolfgang Kaml" <[e-mail]> wrote in message
news:eE**************@TK2MSFTNGP10.phx.gbl...

Hello All,

I have been working on this for almost a week now and I haven't anything up my sleeves anymore that I could test in addition or change....
Since I am not sure, if this is a Windows 2003 Server or ADO or ODBC issue, I am posting this on all of the three newsgroups.

That's the setup:
Windows 2003 Server with IIS and ASP.NET actiavted
Access 2002 mdb file (and yes, proper rights are set on TMP paths and path, where mdb file is located)
ASP.Net web server component that will query and update the Access DB in the functionality of a web counter

The problem is, that after the first call of the web site with the web
counter component, the refresh will take about 8-9 seconds. Doesn't matter
if I close the IE or just hit the refresh.

From what I can observe is, that after the start of the IIS web server no
w3wp.exe process is running. As soon as I open the aspx web site with the
.Net built counter component, a w3wp.exe process is being instantiated and
the calls to the Access DB are made. Since the w3wp.exe process has to
start, the inital display of the web page takes about 1-2 seconds and
everything works fine. I can see a .tmp file being created in the TMP
directory and a .ldb file being created in the path of the .mdb file for a
brief moment. Then they disappear. The w3wp.exe process stays. Then, if I
hit refresh or close and reopen the web site, nothing seems to happen for
8-9 seconds and then the page displays without any errors. The difference
is, that the .tmp file and the .ldb file are also sitting in the
corresponding paths for a while longer, but both disappear again.

Since I have been doing quiet a bit of trouble shooting already, here is
some detailed info as of what happens during the inital call of the web
site, and during subsequent calls:

Output of current second and millisecond of the machine during initial call, when no w3wp.exe process existed yet:
58:343 objCounterDBConnection = New OdbcConnection
58:359 objCounterDBCommand = New OdbcCommand
58:359 objCounterDBConnection.Open()
58:390 objCounterDbRdr = objCounterDBCommand.ExecuteReader
58:562 objCounterDbRdr.Read
58:562 objCounterDbRdr.Close()
58:578 objCounterDBConnection.Close()
58:625 preparing insert
58:640 objCounterDBConnection.Open()
58:640 objCounterDBCommand.ExecuteNonQuery()
58:640 objCounterDBConnection.Close()
58:687 objCounterDBConnection & objCounterDBCommand.Dispose()
58:687 finished

Output of current second and millisecond of the machine during subsequent
call, when existing w3wp.exe process apparently take the job:
10:359 objCounterDBConnection = New OdbcConnection
10:359 objCounterDBCommand = New OdbcCommand
10:359 objCounterDBConnection.Open()
10:375 objCounterDbRdr = objCounterDBCommand.ExecuteReader
19:140 objCounterDbRdr.Read
19:140 objCounterDbRdr.Close()
19:140 objCounterDBConnection.Close()
19:187 preparing insert
19:187 objCounterDBConnection.Open()
19:187 objCounterDBCommand.ExecuteNonQuery()
19:187 objCounterDBConnection.Close()
19:234 objCounterDBConnection & objCounterDBCommand.Dispose()
19:234 finished

As you can see, during a subsequent call, the ExecuteReader takes close to 9 seconds before it returns the OdbcDataReader object.

WHY????

This is not an update and hence has nothing to do with lazy propagation
issues as far as I can tell what I have seen on different posts.
Also, this problem occurs only on the Windows 2003 Server, not the Windows
2000 Server.
What is the issue here? - the initial w3wp.exe process is being run under
the NETWORK SERVICE account and I have given that account proper permissions to perform the job. That is being verified in my opinion by the fact, that
the first run of the component works fine.
To me this is an absolute issue of Windows 2003 Server and/or ODBC on
Windows 2003 Server.

Is it ODBC, is it the Windows 2003 server? I have no clue.

Your help on that would be really appreciated and I can post the sources if necessary if somebody else would like to try that out. I'm almost 99.99%
certain that this is a true Windows 2003 Server or ODBC issue that I am just about ready to open a call with Microsoft on that.
The source is clean as it could be and I have double checked with quiet a
few examples on MSDN and other Tech articels on that.

Yesterday I had the IIS and application part completly removed from the
server, everything cleaned up and started all over with the install. Exact
same results. No other pocesses are running on that machine at all. It's
clean as it could be. So I am really stumbling on that.

Please help!

Thank you,
Wolfgang

[Wolfgang Kaml]

Hey Cowboy, ;-))

I really do appreciate your feedback.

A few comments:
- The component I have is a most simplistic WebCounter, so there is not much
of an architecture required. I know that this would be a requirement for a
more sophisticated application. My component has only a very few lines of
code and therefore could be an exreme good example for MS to figure out an
eventual ODBC problem on Windows 2003 Server with MS Access.
- Also, the DB is very small and using SQL Server or MSDE would be an
overkill. I know of the limitations of MS Access though and I am far from
reaching that. That's why it has been an even bigger puzzle to my, why
Windows 2003 Server would 'hang' for 8-9 seconds over such an easy issue.

I will follow your suggestion and modify the app to use OleDb and see if it
makes a difference. If it does, then that would be the ultimate proof that
MS has an issue with the Access ODBC driver on Windows 2003 server.

Now, I'd like to know the difference on Odbc versus OleDb and tried
searching MSDN briefly after reading your article. Somebody responded in a
different thread on a newsgroup here, the OldDb sits on top of Odbc, which
after reading the article
http://msdn.microsoft.com/library/de.../html/daag.asp I
know is not true.
So, OleDb is a different way to access a MS Access DB, but are the different
ways explained somewhere? Do you have an article handy somewhere - just in
case? Don't spend time on searching, I can try that too.

I will let you know how things go after tossing Odbc.

Thanks,
Wolfgang

"Cowboy (Gregory A. Beamer)" <No************@comcast.netNoSpamM> wrote in
message news:%2****************@tk2msftngp13.phx.gbl...

I have not read everything, but can get you to a point that might help.

1. Get rid of ODBC. It is God-awful slow. Use OleDb instead with a
connection string and avoid the whole DSN and/or ODBC methodology.
2. If you have tons of code in your ASP.NET project, re-architect and move
business code to business layer components. This will reduce heavy JIT
loads.

Another option is "you have outgrown Access." If you database is in the
multiple megs (over 25MB in some cases, more in others), you are ending up
with an inefficient database. Access starts to really puke when it grows,
esp. with web apps.

Note that Windows Server 2003 can reduce overhead, if you have apps in the
proper pools. Since you do not mention multiple apps, this is unlikely the
problem. I mention largely because improper setup of apps can cause Windows Server 2003 to take its own sweet time.

NOTE: In Whidbey, there is a precompilation option (finally). Unfortunately, this does not help the immediate problem. I would focus on getting rid of
ODBC and using OleDb and figuring out what code is taking a long time to JIT compile. Moving some code to business components may reduce load on the app JITting. This will force some refactoring of code, but it will pay off
rather quickly as the app can JIT business components as needed, rather than focusing on the entire ASP.NET app.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************
"Wolfgang Kaml" <[e-mail]> wrote in message
news:eE**************@TK2MSFTNGP10.phx.gbl...

Hello All,

I have been working on this for almost a week now and I haven't anything up

my sleeves anymore that I could test in addition or change....
Since I am not sure, if this is a Windows 2003 Server or ADO or ODBC

issue,

I am posting this on all of the three newsgroups.

That's the setup:
Windows 2003 Server with IIS and ASP.NET actiavted
Access 2002 mdb file (and yes, proper rights are set on TMP paths and

path,

where mdb file is located)
ASP.Net web server component that will query and update the Access DB in

the

functionality of a web counter

The problem is, that after the first call of the web site with the web
counter component, the refresh will take about 8-9 seconds. Doesn't matter if I close the IE or just hit the refresh.

From what I can observe is, that after the start of the IIS web server no w3wp.exe process is running. As soon as I open the aspx web site with the .Net built counter component, a w3wp.exe process is being instantiated and the calls to the Access DB are made. Since the w3wp.exe process has to
start, the inital display of the web page takes about 1-2 seconds and
everything works fine. I can see a .tmp file being created in the TMP
directory and a .ldb file being created in the path of the .mdb file for a brief moment. Then they disappear. The w3wp.exe process stays. Then, if I hit refresh or close and reopen the web site, nothing seems to happen for 8-9 seconds and then the page displays without any errors. The difference is, that the .tmp file and the .ldb file are also sitting in the
corresponding paths for a while longer, but both disappear again.

Since I have been doing quiet a bit of trouble shooting already, here is
some detailed info as of what happens during the inital call of the web
site, and during subsequent calls:

Output of current second and millisecond of the machine during initial

call,

when no w3wp.exe process existed yet:
58:343 objCounterDBConnection = New OdbcConnection
58:359 objCounterDBCommand = New OdbcCommand
58:359 objCounterDBConnection.Open()
58:390 objCounterDbRdr = objCounterDBCommand.ExecuteReader
58:562 objCounterDbRdr.Read
58:562 objCounterDbRdr.Close()
58:578 objCounterDBConnection.Close()
58:625 preparing insert
58:640 objCounterDBConnection.Open()
58:640 objCounterDBCommand.ExecuteNonQuery()
58:640 objCounterDBConnection.Close()
58:687 objCounterDBConnection & objCounterDBCommand.Dispose()
58:687 finished

Output of current second and millisecond of the machine during subsequent call, when existing w3wp.exe process apparently take the job:
10:359 objCounterDBConnection = New OdbcConnection
10:359 objCounterDBCommand = New OdbcCommand
10:359 objCounterDBConnection.Open()
10:375 objCounterDbRdr = objCounterDBCommand.ExecuteReader
19:140 objCounterDbRdr.Read
19:140 objCounterDbRdr.Close()
19:140 objCounterDBConnection.Close()
19:187 preparing insert
19:187 objCounterDBConnection.Open()
19:187 objCounterDBCommand.ExecuteNonQuery()
19:187 objCounterDBConnection.Close()
19:234 objCounterDBConnection & objCounterDBCommand.Dispose()
19:234 finished

As you can see, during a subsequent call, the ExecuteReader takes close to 9

seconds before it returns the OdbcDataReader object.

WHY????

This is not an update and hence has nothing to do with lazy propagation
issues as far as I can tell what I have seen on different posts.
Also, this problem occurs only on the Windows 2003 Server, not the

Windows 2000 Server.
What is the issue here? - the initial w3wp.exe process is being run under the NETWORK SERVICE account and I have given that account proper

permissions

to perform the job. That is being verified in my opinion by the fact, that the first run of the component works fine.
To me this is an absolute issue of Windows 2003 Server and/or ODBC on
Windows 2003 Server.

Is it ODBC, is it the Windows 2003 server? I have no clue.

Your help on that would be really appreciated and I can post the sources

if

necessary if somebody else would like to try that out. I'm almost 99.99%
certain that this is a true Windows 2003 Server or ODBC issue that I am

just

about ready to open a call with Microsoft on that.
The source is clean as it could be and I have double checked with quiet a few examples on MSDN and other Tech articels on that.

Yesterday I had the IIS and application part completly removed from the
server, everything cleaned up and started all over with the install. Exact same results. No other pocesses are running on that machine at all. It's
clean as it could be. So I am really stumbling on that.

Please help!

Thank you,
Wolfgang

[Wolfgang Kaml]

Gregory,

THANK YOU so much for the hint. I have thought about using OleDb before, but
wouldn't have given it a try for a long time because I never thought that
Microsoft would have in fact a buggy ODBC setup on Windows 2003 Server.

Everybody using ODBC on Windows 2003 Server with a MS Access DB - and
specially _Microsoft_ - listen up:

With this example, I confirmed the ODBC data connection to MS Access on
Windows 2003 Server to have a bug.

Have a good look at this code example:

Example 1 - using OleDb - takes 1 second on Windows 2003 Server with MS
Access, on _every_ call
'---------------------------------------------------------------------------
------------------------------------
Dim objCounterDBConnection As OleDbConnection
Dim objCounterDbRdr As OleDbDataReader
Dim objCounterDBCommand As OleDbCommand

objCounterDBConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\mydb.mdb")
objCounterDBCommand = New OleDbCommand("select count(*) from [COUNTER] where
CNTPAGE = " & Me.CounterID, objCounterDBConnection)

Try
objCounterDBConnection.Open()
objCounterDbRdr =
objCounterDBCommand.ExecuteReader(CommandBehavior. SingleRow)
While objCounterDbRdr.Read
If Not IsDBNull(objCounterDbRdr.Item(0)) Then strReturnValue =
objCounterDbRdr.Item(0)
End While
objCounterDbRdr.Close()
objCounterDBConnection.Close()
Catch ex As Exception
End Try

objCounterDBCommand.Dispose
objCounterDBConnection.Dispose

Example 2 - using ODBC - takes 1 second on Windows 2003 Server with MS
Access on the 1st call, and 8.9 seconds on every subsequent call.
'---------------------------------------------------------------------------
------------------------------------
Dim objCounterDBConnection As OdbcDbConnection
Dim objCounterDbRdr As OdbcDbDataReader
Dim objCounterDBCommand As OdbcDbCommand

objCounterDBConnection = New OdbcDbConnection("PageTimeout=5;FIL=MS
Access;MaxBufferSize=2048;DSN=MyDSN;UID=admin;Driv erId=25")
objCounterDBCommand = New OdbcDbCommand("select count(*) from [COUNTER]
where CNTPAGE = " & Me.CounterID, objCounterDBConnection)

Try
objCounterDBConnection.Open()
objCounterDbRdr =
objCounterDBCommand.ExecuteReader(CommandBehavior. SingleRow)
While objCounterDbRdr.Read
If Not IsDBNull(objCounterDbRdr.Item(0)) Then strReturnValue =
objCounterDbRdr.Item(0)
End While
objCounterDbRdr.Close()
objCounterDBConnection.Close()
Catch ex As Exception
End Try

objCounterDBCommand.Dispose
objCounterDBConnection.Dispose

'---------------------------------------------------------------------------
------------------------------------

Dear MS folks,
That's what I call a real bug! Please check it and fix it, or drop ODBC
support on Windows 2003 server, because it is a hassle.

Best Regards,
Wolfgang


"Wolfgang Kaml" <[e-mail]> wrote in message
news:eT**************@TK2MSFTNGP10.phx.gbl...

Hey Cowboy, ;-))

I really do appreciate your feedback.

A few comments:
- The component I have is a most simplistic WebCounter, so there is not much of an architecture required. I know that this would be a requirement for a
more sophisticated application. My component has only a very few lines of
code and therefore could be an exreme good example for MS to figure out an
eventual ODBC problem on Windows 2003 Server with MS Access.
- Also, the DB is very small and using SQL Server or MSDE would be an
overkill. I know of the limitations of MS Access though and I am far from
reaching that. That's why it has been an even bigger puzzle to my, why
Windows 2003 Server would 'hang' for 8-9 seconds over such an easy issue.

I will follow your suggestion and modify the app to use OleDb and see if it makes a difference. If it does, then that would be the ultimate proof that
MS has an issue with the Access ODBC driver on Windows 2003 server.

Now, I'd like to know the difference on Odbc versus OleDb and tried
searching MSDN briefly after reading your article. Somebody responded in a
different thread on a newsgroup here, the OldDb sits on top of Odbc, which
after reading the article
http://msdn.microsoft.com/library/de.../html/daag.asp I know is not true.
So, OleDb is a different way to access a MS Access DB, but are the different ways explained somewhere? Do you have an article handy somewhere - just in
case? Don't spend time on searching, I can try that too.

I will let you know how things go after tossing Odbc.

Thanks,
Wolfgang

"Cowboy (Gregory A. Beamer)" <No************@comcast.netNoSpamM> wrote in
message news:%2****************@tk2msftngp13.phx.gbl...

I have not read everything, but can get you to a point that might help.

1. Get rid of ODBC. It is God-awful slow. Use OleDb instead with a
connection string and avoid the whole DSN and/or ODBC methodology.
2. If you have tons of code in your ASP.NET project, re-architect and move

business code to business layer components. This will reduce heavy JIT
loads.

Another option is "you have outgrown Access." If you database is in the
multiple megs (over 25MB in some cases, more in others), you are ending up with an inefficient database. Access starts to really puke when it grows, esp. with web apps.

Note that Windows Server 2003 can reduce overhead, if you have apps in the proper pools. Since you do not mention multiple apps, this is unlikely the problem. I mention largely because improper setup of apps can cause Windows

Server 2003 to take its own sweet time.

NOTE: In Whidbey, there is a precompilation option (finally).

Unfortunately,

this does not help the immediate problem. I would focus on getting rid of ODBC and using OleDb and figuring out what code is taking a long time to

JIT

compile. Moving some code to business components may reduce load on the

app

JITting. This will force some refactoring of code, but it will pay off
rather quickly as the app can JIT business components as needed, rather

than

focusing on the entire ASP.NET app.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************
"Wolfgang Kaml" <[e-mail]> wrote in message
news:eE**************@TK2MSFTNGP10.phx.gbl...

Hello All,

I have been working on this for almost a week now and I haven't anything

up

my sleeves anymore that I could test in addition or change....
Since I am not sure, if this is a Windows 2003 Server or ADO or ODBC

issue,

I am posting this on all of the three newsgroups.

That's the setup:
Windows 2003 Server with IIS and ASP.NET actiavted
Access 2002 mdb file (and yes, proper rights are set on TMP paths and

path,

where mdb file is located)
ASP.Net web server component that will query and update the Access DB

in the

functionality of a web counter

The problem is, that after the first call of the web site with the web
counter component, the refresh will take about 8-9 seconds. Doesn't matter if I close the IE or just hit the refresh.

From what I can observe is, that after the start of the IIS web server no w3wp.exe process is running. As soon as I open the aspx web site with the .Net built counter component, a w3wp.exe process is being instantiated and the calls to the Access DB are made. Since the w3wp.exe process has to
start, the inital display of the web page takes about 1-2 seconds and
everything works fine. I can see a .tmp file being created in the TMP
directory and a .ldb file being created in the path of the .mdb file

for a brief moment. Then they disappear. The w3wp.exe process stays. Then,

if

I hit refresh or close and reopen the web site, nothing seems to happen for 8-9 seconds and then the page displays without any errors. The difference is, that the .tmp file and the .ldb file are also sitting in the
corresponding paths for a while longer, but both disappear again.

Since I have been doing quiet a bit of trouble shooting already, here

is some detailed info as of what happens during the inital call of the web site, and during subsequent calls:

Output of current second and millisecond of the machine during initial call,

when no w3wp.exe process existed yet:
58:343 objCounterDBConnection = New OdbcConnection
58:359 objCounterDBCommand = New OdbcCommand
58:359 objCounterDBConnection.Open()
58:390 objCounterDbRdr = objCounterDBCommand.ExecuteReader
58:562 objCounterDbRdr.Read
58:562 objCounterDbRdr.Close()
58:578 objCounterDBConnection.Close()
58:625 preparing insert
58:640 objCounterDBConnection.Open()
58:640 objCounterDBCommand.ExecuteNonQuery()
58:640 objCounterDBConnection.Close()
58:687 objCounterDBConnection & objCounterDBCommand.Dispose()
58:687 finished

Output of current second and millisecond of the machine during

subsequent call, when existing w3wp.exe process apparently take the job:
10:359 objCounterDBConnection = New OdbcConnection
10:359 objCounterDBCommand = New OdbcCommand
10:359 objCounterDBConnection.Open()
10:375 objCounterDbRdr = objCounterDBCommand.ExecuteReader
19:140 objCounterDbRdr.Read
19:140 objCounterDbRdr.Close()
19:140 objCounterDBConnection.Close()
19:187 preparing insert
19:187 objCounterDBConnection.Open()
19:187 objCounterDBCommand.ExecuteNonQuery()
19:187 objCounterDBConnection.Close()
19:234 objCounterDBConnection & objCounterDBCommand.Dispose()
19:234 finished

As you can see, during a subsequent call, the ExecuteReader takes close to

9

seconds before it returns the OdbcDataReader object.

WHY????

This is not an update and hence has nothing to do with lazy

propagation issues as far as I can tell what I have seen on different posts.
Also, this problem occurs only on the Windows 2003 Server, not the

Windows 2000 Server.
What is the issue here? - the initial w3wp.exe process is being run under the NETWORK SERVICE account and I have given that account proper

permissions

to perform the job. That is being verified in my opinion by the fact, that the first run of the component works fine.
To me this is an absolute issue of Windows 2003 Server and/or ODBC on
Windows 2003 Server.

Is it ODBC, is it the Windows 2003 server? I have no clue.

Your help on that would be really appreciated and I can post the sources if

necessary if somebody else would like to try that out. I'm almost

99.99% certain that this is a true Windows 2003 Server or ODBC issue that I am

just

about ready to open a call with Microsoft on that.
The source is clean as it could be and I have double checked with

quiet a few examples on MSDN and other Tech articels on that.

Yesterday I had the IIS and application part completly removed from

the server, everything cleaned up and started all over with the install.

Exact same results. No other pocesses are running on that machine at all. It's clean as it could be. So I am really stumbling on that.

Please help!

Thank you,
Wolfgang

[Bob Barrows]

Wolfgang Kaml wrote:

Dear MS folks,
That's what I call a real bug! Please check it and fix it, or drop
ODBC
support on Windows 2003 server, because it is a hassle.

ODBC has been on the Deprecated Components list for at least 2, and possibly
3 versions of MDAC
(http://msdn.microsoft.com/library/en...eprecated.asp).
Unfortunately, the word has not made it to the rank and file. Too many books
have been written in the past 10 yrs using the obsolete technology, and
beginners are still reading those books. There's nobody to tell these
readers that the information in their books is obsolete.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Wolfgang Kaml]

3 more interesting things here:

1) I did some more testing on the ODBC vs. OLEDB part and found out, that
without using DSN but referencing the file name directly in the ODBC code,
the ODBC version is just as fast as OLEDB. It's DSN that's causing the
problem.

2) I just worked for a while with Crystal Reports and the only way you can
connect to an Access DB is using a DSN. Interesting to watch: Microsoft is
making ODBC obsolete, but making a product, that's using ODBC to connect to
their database 'de-facto' standard and ships it with their own product
(refering to Crystal Reports for VS.NET). A good idea for MS would have been
to just buy that company, because they are changing their name so fast, it's
hard to keep track of them. But my appology, put that on a different note
....

3) - And that's actually the most important _QUESTION_ here:
You are linking to a very good article. I am not sure if I am understanding
everything correct that's being said on that but...
- Is Microsoft now calling their MS Jet OLE DB provider obsolete as well? -
In other words, will they drop supporting MS Access?

Thank you,
Wolfgang
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:%2******************@TK2MSFTNGP12.phx.gbl...

Wolfgang Kaml wrote:

Dear MS folks,
That's what I call a real bug! Please check it and fix it, or drop
ODBC
support on Windows 2003 server, because it is a hassle.

ODBC has been on the Deprecated Components list for at least 2, and

possibly 3 versions of MDAC
(http://msdn.microsoft.com/library/en...eprecated.asp).
Unfortunately, the word has not made it to the rank and file. Too many books have been written in the past 10 yrs using the obsolete technology, and
beginners are still reading those books. There's nobody to tell these
readers that the information in their books is obsolete.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[MSFT]

Hi Wolfgang,

I agree with Cowboy that we may try JET and OLEDB instead. With ODBC, it
will use Access ODBC driver; With OLE DB. it will use JET provider. If
there is something with the ODBC driver, OLEDB may give a better result.
Additionally, what is the version of your Access database? You may perform
a Compact Operation on the database or create a new database instead.
Sometime, a corrupted database also can cause such a problem.

Luke
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Bob Barrows]

Wolfgang Kaml wrote:

2) I just worked for a while with Crystal Reports and the only way
you can connect to an Access DB is using a DSN. Interesting to watch:
Microsoft is making ODBC obsolete, but making a product, that's using
ODBC to connect to their database 'de-facto' standard and ships it
with their own product (refering to Crystal Reports for VS.NET). A
good idea for MS would have been to just buy that company, because
they are changing their name so fast, it's hard to keep track of
them.

You don't need to use a DSN to make a connection from a Crystal Report.

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[MSFT]

Hi Wolfgang,

Thank you for using the community.

Regarding the questions:

1. With DSN name, it will use MSDASQL provider to access the DSN. It seems
to be the root cause for the problem.
2. Crystal report can use many resource as data source of a report. For
example, ODBC, ADO, OLEDB and even the DataSet in ADO.NET
3. Access and JET is fully support normally. Recenetly the newest JET 4.0
service pack is released.

Hope this help,

Luke
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[MSFT]

Hi Wolfgang,

I found something really confused. Did you work with ODBC .NET Managed
Provider? For more information about ODBC .NET Managed Provider, you may
refer to:

HOW TO: Use the ODBC .NET Managed Provider in Visual Basic .NET and
Connection Strings
http://support.microsoft.com/default...;EN-US;Q310985

With

ODBC .NET Managed Provider, we define the connection and Command like:

Dim objCounterDBConnection As OdbcConnection
Dim objCounterDbRdr As OdbcDataReader
Dim objCounterDBCommand As OdbcCommand

However, in your code:

Dim objCounterDBConnection As OdbcDbConnection
Dim objCounterDbRdr As OdbcDbDataReader
Dim objCounterDBCommand As OdbcDbCommand
Did you have "Imports System.Data.Odbc" in your code?

By the way, I have tested your code with ODBC .NET Managed Provider, I
haven't found same problem as you descripted. The access database is an
Access 97 database and I use Microsoft Access Driver to build the system
DSN. Therefore, I think it is too early to tell it is a bug.

Luke
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

[Cowboy \(Gregory A. Beamer\)]

ODBC is an old technology. It is still around, as some database vendors are
still using it. It is the LCD approach to database access and is extremely
generic. Unfortunately, when you try to be one size fits all it turns out to
be "not really fitting anyone at all."

Microsoft is not interested in supporting ODBC any more, and would have
likely dumped it long ago, if not for a few database vendors who refuse to
create OleDb providers. I do agree that you have likely found a bug in the
ODBC, but the fact that fewer and fewer people use it means fewer resources
to iron out all the loose ends.

Glad you got resolution.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************
"Wolfgang Kaml" <[e-mail]> wrote in message
news:un**************@TK2MSFTNGP10.phx.gbl...

Gregory,

THANK YOU so much for the hint. I have thought about using OleDb before, but wouldn't have given it a try for a long time because I never thought that
Microsoft would have in fact a buggy ODBC setup on Windows 2003 Server.

Everybody using ODBC on Windows 2003 Server with a MS Access DB - and
specially _Microsoft_ - listen up:

With this example, I confirmed the ODBC data connection to MS Access on
Windows 2003 Server to have a bug.

Have a good look at this code example:

Example 1 - using OleDb - takes 1 second on Windows 2003 Server with MS
Access, on _every_ call
'--------------------------------------------------------------------------- ------------------------------------
Dim objCounterDBConnection As OleDbConnection
Dim objCounterDbRdr As OleDbDataReader
Dim objCounterDBCommand As OleDbCommand

objCounterDBConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\mydb.mdb") objCounterDBCommand = New OleDbCommand("select count(*) from [COUNTER] where CNTPAGE = " & Me.CounterID, objCounterDBConnection)

Try
objCounterDBConnection.Open()
objCounterDbRdr =
objCounterDBCommand.ExecuteReader(CommandBehavior. SingleRow)
While objCounterDbRdr.Read
If Not IsDBNull(objCounterDbRdr.Item(0)) Then strReturnValue =
objCounterDbRdr.Item(0)
End While
objCounterDbRdr.Close()
objCounterDBConnection.Close()
Catch ex As Exception
End Try

objCounterDBCommand.Dispose
objCounterDBConnection.Dispose

Example 2 - using ODBC - takes 1 second on Windows 2003 Server with MS
Access on the 1st call, and 8.9 seconds on every subsequent call.
'--------------------------------------------------------------------------- ------------------------------------
Dim objCounterDBConnection As OdbcDbConnection
Dim objCounterDbRdr As OdbcDbDataReader
Dim objCounterDBCommand As OdbcDbCommand

objCounterDBConnection = New OdbcDbConnection("PageTimeout=5;FIL=MS
Access;MaxBufferSize=2048;DSN=MyDSN;UID=admin;Driv erId=25")
objCounterDBCommand = New OdbcDbCommand("select count(*) from [COUNTER]
where CNTPAGE = " & Me.CounterID, objCounterDBConnection)

Try
objCounterDBConnection.Open()
objCounterDbRdr =
objCounterDBCommand.ExecuteReader(CommandBehavior. SingleRow)
While objCounterDbRdr.Read
If Not IsDBNull(objCounterDbRdr.Item(0)) Then strReturnValue =
objCounterDbRdr.Item(0)
End While
objCounterDbRdr.Close()
objCounterDBConnection.Close()
Catch ex As Exception
End Try

objCounterDBCommand.Dispose
objCounterDBConnection.Dispose

'--------------------------------------------------------------------------- ------------------------------------

Dear MS folks,
That's what I call a real bug! Please check it and fix it, or drop ODBC
support on Windows 2003 server, because it is a hassle.

Best Regards,
Wolfgang


"Wolfgang Kaml" <[e-mail]> wrote in message
news:eT**************@TK2MSFTNGP10.phx.gbl...

Hey Cowboy, ;-))

I really do appreciate your feedback.

A few comments:
- The component I have is a most simplistic WebCounter, so there is not much

of an architecture required. I know that this would be a requirement for a

more sophisticated application. My component has only a very few lines of code and therefore could be an exreme good example for MS to figure out an eventual ODBC problem on Windows 2003 Server with MS Access.
- Also, the DB is very small and using SQL Server or MSDE would be an
overkill. I know of the limitations of MS Access though and I am far from reaching that. That's why it has been an even bigger puzzle to my, why
Windows 2003 Server would 'hang' for 8-9 seconds over such an easy issue.
I will follow your suggestion and modify the app to use OleDb and see if

it

makes a difference. If it does, then that would be the ultimate proof that MS has an issue with the Access ODBC driver on Windows 2003 server.

Now, I'd like to know the difference on Odbc versus OleDb and tried
searching MSDN briefly after reading your article. Somebody responded in a different thread on a newsgroup here, the OldDb sits on top of Odbc, which after reading the article

http://msdn.microsoft.com/library/de.../html/daag.asp I

know is not true.
So, OleDb is a different way to access a MS Access DB, but are the

different

ways explained somewhere? Do you have an article handy somewhere - just in case? Don't spend time on searching, I can try that too.

I will let you know how things go after tossing Odbc.

Thanks,
Wolfgang

"Cowboy (Gregory A. Beamer)" <No************@comcast.netNoSpamM> wrote in message news:%2****************@tk2msftngp13.phx.gbl...

I have not read everything, but can get you to a point that might help.
1. Get rid of ODBC. It is God-awful slow. Use OleDb instead with a
connection string and avoid the whole DSN and/or ODBC methodology.
2. If you have tons of code in your ASP.NET project, re-architect and move business code to business layer components. This will reduce heavy JIT
loads.

Another option is "you have outgrown Access." If you database is in the multiple megs (over 25MB in some cases, more in others), you are ending up

with an inefficient database. Access starts to really puke when it grows, esp. with web apps.

Note that Windows Server 2003 can reduce overhead, if you have apps in the proper pools. Since you do not mention multiple apps, this is unlikely the problem. I mention largely because improper setup of apps can cause Windows

Server 2003 to take its own sweet time.

NOTE: In Whidbey, there is a precompilation option (finally).

Unfortunately,

this does not help the immediate problem. I would focus on getting rid of ODBC and using OleDb and figuring out what code is taking a long time

to JIT

compile. Moving some code to business components may reduce load on

the app

JITting. This will force some refactoring of code, but it will pay off
rather quickly as the app can JIT business components as needed,

rather than

focusing on the entire ASP.NET app.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************
"Wolfgang Kaml" <[e-mail]> wrote in message
news:eE**************@TK2MSFTNGP10.phx.gbl...
> Hello All,
>
> I have been working on this for almost a week now and I haven't anything up
> my sleeves anymore that I could test in addition or change....
> Since I am not sure, if this is a Windows 2003 Server or ADO or ODBC
issue,
> I am posting this on all of the three newsgroups.
>
> That's the setup:
> Windows 2003 Server with IIS and ASP.NET actiavted
> Access 2002 mdb file (and yes, proper rights are set on TMP paths

and path,
> where mdb file is located)
> ASP.Net web server component that will query and update the Access DB in the
> functionality of a web counter
>
> The problem is, that after the first call of the web site with the

web > counter component, the refresh will take about 8-9 seconds. Doesn't matter

> if I close the IE or just hit the refresh.
>
> From what I can observe is, that after the start of the IIS web server no

> w3wp.exe process is running. As soon as I open the aspx web site

with the

> .Net built counter component, a w3wp.exe process is being

instantiated and

> the calls to the Access DB are made. Since the w3wp.exe process has

to > start, the inital display of the web page takes about 1-2 seconds and > everything works fine. I can see a .tmp file being created in the TMP > directory and a .ldb file being created in the path of the .mdb file

for

a

> brief moment. Then they disappear. The w3wp.exe process stays. Then,

if

I

> hit refresh or close and reopen the web site, nothing seems to happen for

> 8-9 seconds and then the page displays without any errors. The

difference

> is, that the .tmp file and the .ldb file are also sitting in the
> corresponding paths for a while longer, but both disappear again.
>
> Since I have been doing quiet a bit of trouble shooting already,

here is > some detailed info as of what happens during the inital call of the web > site, and during subsequent calls:
>
> Output of current second and millisecond of the machine during

initial call,
> when no w3wp.exe process existed yet:
> 58:343 objCounterDBConnection = New OdbcConnection
> 58:359 objCounterDBCommand = New OdbcCommand
> 58:359 objCounterDBConnection.Open()
> 58:390 objCounterDbRdr = objCounterDBCommand.ExecuteReader
> 58:562 objCounterDbRdr.Read
> 58:562 objCounterDbRdr.Close()
> 58:578 objCounterDBConnection.Close()
> 58:625 preparing insert
> 58:640 objCounterDBConnection.Open()
> 58:640 objCounterDBCommand.ExecuteNonQuery()
> 58:640 objCounterDBConnection.Close()
> 58:687 objCounterDBConnection & objCounterDBCommand.Dispose()
> 58:687 finished
>
> Output of current second and millisecond of the machine during

subsequent

> call, when existing w3wp.exe process apparently take the job:
> 10:359 objCounterDBConnection = New OdbcConnection
> 10:359 objCounterDBCommand = New OdbcCommand
> 10:359 objCounterDBConnection.Open()
> 10:375 objCounterDbRdr = objCounterDBCommand.ExecuteReader
> 19:140 objCounterDbRdr.Read
> 19:140 objCounterDbRdr.Close()
> 19:140 objCounterDBConnection.Close()
> 19:187 preparing insert
> 19:187 objCounterDBConnection.Open()
> 19:187 objCounterDBCommand.ExecuteNonQuery()
> 19:187 objCounterDBConnection.Close()
> 19:234 objCounterDBConnection & objCounterDBCommand.Dispose()
> 19:234 finished
>
> As you can see, during a subsequent call, the ExecuteReader takes

close

to

9
> seconds before it returns the OdbcDataReader object.
>
> WHY????
>
> This is not an update and hence has nothing to do with lazy

propagation > issues as far as I can tell what I have seen on different posts.
> Also, this problem occurs only on the Windows 2003 Server, not the

Windows

> 2000 Server.
> What is the issue here? - the initial w3wp.exe process is being run

under

> the NETWORK SERVICE account and I have given that account proper
permissions
> to perform the job. That is being verified in my opinion by the fact, that

> the first run of the component works fine.
> To me this is an absolute issue of Windows 2003 Server and/or ODBC

on > Windows 2003 Server.
>
> Is it ODBC, is it the Windows 2003 server? I have no clue.
>
> Your help on that would be really appreciated and I can post the

sources if
> necessary if somebody else would like to try that out. I'm almost 99.99% > certain that this is a true Windows 2003 Server or ODBC issue that I am just
> about ready to open a call with Microsoft on that.
> The source is clean as it could be and I have double checked with quiet

a

> few examples on MSDN and other Tech articels on that.
>
> Yesterday I had the IIS and application part completly removed from

the > server, everything cleaned up and started all over with the install.

Exact

> same results. No other pocesses are running on that machine at all. It's > clean as it could be. So I am really stumbling on that.
>
> Please help!
>
> Thank you,
> Wolfgang
>
>

[Cowboy \(Gregory A. Beamer\)]

"Wolfgang Kaml" <[e-mail]> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...

3 more interesting things here:

1) I did some more testing on the ODBC vs. OLEDB part and found out, that
without using DSN but referencing the file name directly in the ODBC code,
the ODBC version is just as fast as OLEDB. It's DSN that's causing the
problem.
I think if you stressed it, you would still find that the ODBC lags a bit,
esp. over time. But, it should not be that slow. The DSN method was really
pushed back in 1997, when many non-programmers were introduced to web apps
with ASP 1.0 (Visual InterDev). It was a royal mess, IMO.
2) I just worked for a while with Crystal Reports and the only way you can
connect to an Access DB is using a DSN. Interesting to watch: Microsoft is
making ODBC obsolete, but making a product, that's using ODBC to connect to their database 'de-facto' standard and ships it with their own product
(refering to Crystal Reports for VS.NET). A good idea for MS would have been to just buy that company, because they are changing their name so fast, it's hard to keep track of them. But my appology, put that on a different note
This is changing. AFAIK, Crystal has moved to OleDb since version 9, but I
am not 100% sure on this. Crystal suggests using a RDBMS, like SQL Server or
Oracle, instead of Access, regardless.

The Crystal Reports for .NET was a marketing ploy for Crystal. It uses
version 8 and only allows 5 report connections. The thought process is a
person will develop and then buy more licenses (or upgrade to Crystal 10)
when they need them.
3) - And that's actually the most important _QUESTION_ here:
You are linking to a very good article. I am not sure if I am understanding everything correct that's being said on that but...
- Is Microsoft now calling their MS Jet OLE DB provider obsolete as well? - In other words, will they drop supporting MS Access?

No, but it sounds like it. In Whidbey, you will see that Microsoft creates a
new method to access Access databases, which is a native provider, similar
to the SQL Server native provider. They are not going to continue to expand
the functionality of OleDb in Access, so it is better to move to the Access
native provider when you move to Whidbey.

It is going to take a bit of time to make the Jet OleDb provider obsolete,
but it will happen.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************** ********************
Think Outside the Box!
************************************************** ********************