Wolfgang
Thu Jan 22 17:48:22 CST 2004
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
"Wolfgang Kaml" <ms@no-spam.kaml.com> wrote in message
news:eT7LKdT4DHA.1868@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/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
>
>
>
> "Cowboy (Gregory A. Beamer)" <NoSpamMgbworld@comcast.netNoSpamM> wrote in
> message news:%23xWcv6S4DHA.2428@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" <ms@no-spam.kaml.com> wrote in message
> > news:eEpBTwR4DHA.2304@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
> > >
> > >
> >
> >
>
>