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]

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/default.asp?url=/library/en-us/dnbda/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

 

[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;DriverId=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

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/default.asp?url=/library/en-us/dnbda/html/daag.asp I

 

[Bob Barrows]

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-us/ado270/htm/ado_deprecated.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

 

[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]

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

 

[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!
**********************************************************************

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;DriverId=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

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/default.asp?url=/library/en-us/dnbda/html/daag.asp I

 

[Cowboy \(Gregory A. Beamer\)]

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!
**********************************************************************

 

[Scot Rose [MSFT]]

- Is Microsoft now calling their MS Jet OLE DB provider obsolete as well? -

In other words, will they drop supporting MS Access?

Actually Jet support in MDAC was dropped as of version 2.6. There are newer components that have been released in subsequent
Service packs for Jet, But in the long run, Yes, Jet is being obsoleted. It will probably take some time considering the large number of
developers that use it but going forward with new development work it is suggested that developers use the MSDE (Note that recent
versions of Access also use MSDE and at some point Jet may no longer be supported in Access)


Scot Rose, MCSD
Microsoft Visual Basic Developer Support
Email : (e-mail address removed) <Remove word online. from address>

This posting is provided “AS IS”, with no warranties, and confers no rights.

Get Secure!
http://www.microsoft.com/security
http://www.microsoft.com/protect


--------------------

 

[Wolfgang Kaml]

Thanks for that clarification. Sounds like MS Access has evolved to be a
convenient front end for MSDE. As of what version of MS Access will there be
MSDE sitting behind the scene?

Wolfgang

Actually Jet support in MDAC was dropped as of version 2.6. There are

newer components that have been released in subsequent

Service packs for Jet, But in the long run, Yes, Jet is being obsoleted.

It will probably take some time considering the large number of

developers that use it but going forward with new development work it is

suggested that developers use the MSDE (Note that recent

versions of Access also use MSDE and at some point Jet may no longer be supported in Access)


Scot Rose, MCSD
Microsoft Visual Basic Developer Support
Email : (e-mail address removed) <Remove word online. from address>

This posting is provided "AS IS", with no warranties, and confers no rights.

Get Secure!
http://www.microsoft.com/security
http://www.microsoft.com/protect


--------------------

@TK2MSFTNGP10.phx.gbl> <[email protected]>

annoying delay during refresh of aspx web page with ODBC Access