Poor Peformance from 2K Version

[Andy]

I have a Version 97 Access database split with Front End on local machine and
Back End on the Network - the way it should be.

I have written a piece of code which reads data from 2 tables in the Back
End database (across the network), manipulates the data and then formats it
into report. There is a fair amount of processing so the report takes about 6
seconds to be displayed - I am happy with that.

However, for the problem. If I convert just the Front End to Access 2K (it
remains linked to the same Access 97 Back End database) and then open the
converted 2K Front End from Access 2002 (Office XP as some Users may have
this version on their machines when it is delivered) then exactly the same
routine above takes 90 secs to run. That is 15 times slower - quite
unacceptable.

I have run this on my test Newtork with no other Users so the Network is
stable (and quiet) and the same timings occur every time (6 secs (97)
compared to 90 secs (2K)).

Can anyone begin to suggest why the 2K Front End should perform so badly
compared with the native 97 Version?

Thanks.

 

[Klatuu]

97 and 2K are different formats. The extra time is the 2K front end is
having to convert the data on the fly to use it. I would suggest converting
the Back End to 2K.

 

[Andy]

Thanks but I can't really do that. There is a mixed community using this
application - some have Access 97 and some have Access 2K so the only
solution (that I know of) is to have the shared Back End as Ver 97. I could
not convert the Back End to 2K because the people with the 97 Front Ends
would not be able to read the data at all.

When you say that 2K has to convert the data - why and how? 97 and 2K both
use Microsoft Jet DBMS Engine. It's not like I am moving between SQL Server
and Access which I agree is really different and could cause Performance
problems.

 

[Klatuu]

If there were no difference in the format then 97 front end would have no
problem using 2K back end, now, would it? The structure of the mdb is
different between the versions.

 

[Albert D.Kallal]

97 and 2K are different formats. The extra time is the 2K front end is
having to convert the data on the fly to use it. I would suggest
converting
the Back End to 2K.

It does not convert the data. JET 4 is able to use the old format. Further,
we have near unlimited processing on a modern machine. The slowness is still
going to be network and other issues, as converting data on the fly would be
near instant anyway if JET had to convert the data....

Two things to check:

1) a2000 and later use a separate IDE for the code. The object model was
changed, and as a result
any sql that uses VBA functions in the sql can run SIGNIFICANTLY
slower. The usual work
around is to remove the VBA expressions from the query..and use them
in the report direclty.
Another approach is to simply try and avoid using functions in the
sql.

2) a2000 and later is more aggressive in terms of dealing with the
locking file. So, try the same code
but ALWAYS FORCE a persistent connection. That means your front end
opens a table from the
back end. (you do this in your startup code long BEFORE you try and
run the report). Try this
idea and post back. This is usually the #1 fix for this problem.

After you check the above two..and post back with your results (it helps..as
then other can learn this too!!!).

The next thing to check is things like track name auto correct, but really,
try the front end as a mde, and see
if that works...

The definitive list of things to check in a2000 and later is here
http://www.granite.ab.ca/access/performancefaq.htm

Go through ALL of the items in the list..

However, the FIRST thing to try is the persistent connection trick...it
might even result in BETTER performance then what you had with a97....

 

[Andy]

Thanks Albert - I have now looked at your related FAQ and articles and will
certainly try the suggestions in your post - especially the persistent
connection at #2.

b.t.w. The Track name AutoCorrect info is set to off and I have tried mde
format.

Your suggestion at #1 about avoiding sql that uses VBA functions - can you
please give some examples? The code in my orginal post contains only 2 sql
statements and 1 DLookup listed below and I don't think VBA is involved in
these? :-

a. SQLcmd = "Select * from tblStaff Where tblStaff.TML = 0 ORDER by
tblStaff.SName"
Set rs1 = db1.OpenRecordset(SQLcmd)

b. SQLcmd = "Select * from tblAL where" & _
" (tblAL.Start between " & SDA & " and " & EDA & _
") OR (tblAL.Finish between " & SDA & " and " & EDA &
")" & _
" OR (tblAL.Start < " & SDA & " AND tblAL.Finish > " &
EDA & ")"

Set rs3 = db1.OpenRecordset(SQLcmd)

c. BH = DLookup("[BHID]", BHTable, "[BHDay] = #" & Format FloatingDate,
"mm\/dd\/yyyy") & "#")

Thanks again and I will post any improvements that I can achieve.

I did try one thing suggested by Klatuu - I converted the BE to 2K so that I
had FE as 2K with BE as 2K and the code did speed up 15 times faster (to 6
seconds) as fast as having FE 97 with BE 97. But, as I said, with a mixed
community of FE versions, the common BE (on the network) has to be 97 as this
is where the shared data is held.

 

[Andy]

Albert - I tried the persistent connection method but unfortnately the code
is still taking 90 seconds to run (same as before) with 2K FE and 97 BE.

Could you please expand on your #1 suggestion :- any sql that uses VBA
functions in the sql can run SIGNIFICANTLY slower.

I would like to explore this further and see if this is causing the problem.

Thanks again.

 

[Albert D.Kallal]

Albert - I tried the persistent connection method but unfortnately the
code
is still taking 90 seconds to run (same as before) with 2K FE and 97 BE.

I would try running the code on another machine...no network involved...

Perhaps some virus software is interfering here. The time drop you see is
rather large, and somting else is wrong here...just don't know what yet...

The persistent connection should help performance. (you sure you done that
right?).

Any indexes changed here?

Could you please expand on your #1 suggestion :- any sql that uses VBA
functions in the sql can run SIGNIFICANTLY slower.

The above refers to using a custom built function as a expression in the
sql. Your example sql had no such expressions, and thus this suggestion will
not help you. So, if you declare a function as public, you can use that in
sql.

select FirstName, LastName, MyCustomFunction[InvoiceDate] from tblCustomers

The above is an example of sql that calls a function. However, I don't think
we are taking 15 times slower here even when you do use the above.
(but, this was one example of something that did run slower in a2000
as compared to a97.

However, with a factor of 15 times larger here..this is something else at
play...

I have to think there is some network issue, virus scan software issue......

Somting is not right here...

You need to test on a clean machine.

 

[Andy]

Thanks Albert.

These tests were on a home network with nobody else using any machines at
the time.

I have introduced changes to my application as described in your articles
and I am sure that these are sensible and will add Performance benefits.

I have delivered these to the community at work and the run times seem to be
acceptable both for 97 and 2K Users so, thanks again for your excellent
advice.

Andy.

Albert - I tried the persistent connection method but unfortnately the
code
is still taking 90 seconds to run (same as before) with 2K FE and 97 BE.

I would try running the code on another machine...no network involved...

Perhaps some virus software is interfering here. The time drop you see is
rather large, and somting else is wrong here...just don't know what yet...

The persistent connection should help performance. (you sure you done that
right?).

Any indexes changed here?

Could you please expand on your #1 suggestion :- any sql that uses VBA
functions in the sql can run SIGNIFICANTLY slower.

The above refers to using a custom built function as a expression in the
sql. Your example sql had no such expressions, and thus this suggestion will
not help you. So, if you declare a function as public, you can use that in
sql.

select FirstName, LastName, MyCustomFunction[InvoiceDate] from tblCustomers

The above is an example of sql that calls a function. However, I don't think
we are taking 15 times slower here even when you do use the above.
(but, this was one example of something that did run slower in a2000
as compared to a97.

However, with a factor of 15 times larger here..this is something else at
play...

I have to think there is some network issue, virus scan software issue......

Somting is not right here...

You need to test on a clean machine.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[SAC]

You might check and see if the Track Name Auto correct info is checked under
Options, General. If so uncheck it.