Edward Zuris
edzedz at comcast.net
Wed Feb 1 10:05:25 CST 2012
Well the compiled option under debug is staying grayed out.
I looked at the production site with a years worth of data
and I see almost the same pattern.
After a month the update times start to drop. It is like
Access 2003 32-bit is teaching itself to do a faster job
with the queries and the .movenext and .moveprevious for
the DAO VBA code.
I didn't see this before as the development system was
using Office 2000. It seems Access 2003 does a better
job learn how to speed things up.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Edward Zuris
Sent: Tuesday, January 31, 2012 9:02 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Compression slows down MDB
Thanks Jim,
I'll check out the compiled option staying grayed
out after a compile.
But I am able to recreate the issue, over and over
by just doing (C&R), and the pattern returns.
Will be doing to more testing then get back to you.
Ed-
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Monday, January 30, 2012 10:55 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Compression slows down MDB
Ed,
Were talking about two different things here:
1. SQL statement and execution and the costing plans used to execute
them.
2. VBA Code compilation
With what you are doing (#2), what I would do before the cutting code
out is do:
? Application.IsCompiled
In the debug window or check the menu and see of the compiled option is
grayed out. It may be that something in your app is causing the
application to become decompiled. This would slow down all the VBA code
that executes.
What I was talking about (#1), is triggered by a compact and repair and
has nothing to do with VBA code. If your not doing a C&R when you mess
with the code, then your problem is with code in some way, not the SQL
statements.
If you do a C&R when you do the code thing, then the code manipulation
may just be a red herring and the change in performance might be due to
what I was talking about.
Jim.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Edward Zuris
Sent: Monday, January 30, 2012 10:31 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Compression slows down MDB
Hi Jim,
I think you are on to something.
The application in question is to help manage the internal tasks with
in several busy Hotels.
After processing two and half days, (2.5), worth of data
I would take a chunk of code like:
+------------------------------------------------------------+
' *****************************************************
' Open Form @Clone Recordset
'
Set rScreen = [Forms]![ft069JMaidUpd]![SubForm2].Form.RecordsetClone
lMaidCount = 0
On Error Resume Next
rScreen.MoveLast
lMaidCount = rScreen.RecordCount
rScreen.MoveFirst
On Error GoTo 0
+------------------------------------------------------------+
Cut it out into a Temp notepad file, do a debug compile and save the
MDB. Then paste the code back in to where it goes, repeat the debug
compile and MDB save.
Then the processing times would improve from 13-14 seconds per guest
room, down to 2-3 seconds per guest room.
Why would doing that kind of compile cause such improvement ?
I started to record the times each routine was taking and storing the
results in a table.
+------------------------------------------------------------+
' *******************************************************
' *******************************************************
' This is where the @Big update for @tt051 tables are called.
' Update all the buckets and special stuff.
'
dcboAssignedDate = Timer
Call Forms("ft069JMaidUpd").cboAssignedDate_Click
dcboAssignedDate = Timer - dcboAssignedDate
+------------------------------------------------------------+
Furthermore, there are many SQL statements, plus lots of VBA .MoveNext,
.MovePrevious, .Edit and .Update commands.
Complex three way joins, and lots of record delegating and record
inserting going on. Thus at times tables are empty or filled with lots
of space, chr$(32)'s.
+------------------------------------------------------------+
' *****************************************************
' Out with the old data
'
s1SQL = ""
s1SQL = s1SQL & " DELETE "
s1SQL = s1SQL & " tt051bToDoRpt.zRowID "
s1SQL = s1SQL & " FROM "
s1SQL = s1SQL & " tt051bToDoRpt "
s1SQL = s1SQL & " WHERE "
s1SQL = s1SQL & " tt051bToDoRpt.zHotel = "
s1SQL = s1SQL & " " & Chr$(34) & gscboHotelSite & Chr$(34) & " "
s1SQL = s1SQL & " ; "
DoCmd.SetWarnings False
DoCmd.RunSQL s1SQL, False
DoCmd.SetWarnings True
' *****************************************************
' Transfer Housekeeping data into tt051bToDoRpt
'
s1SQL = ""
s1SQL = s1SQL & " INSERT INTO "
s1SQL = s1SQL & " tt051bToDoRpt "
s1SQL = s1SQL & " ( "
s1SQL = s1SQL & " Department, zRoomTask, Task_Description, "
s1SQL = s1SQL & " SpanishText, Priority, "
s1SQL = s1SQL & " Assigned, WorkDate, zLoadDate, zHotel,
HouseKeepKey, Housekeeper, "
s1SQL = s1SQL & " RmType, Ppl, RmStatus, Condition, StayOCC,
Service, "
s1SQL = s1SQL & " zIndicator, zOrder, zFlag, Active, DateAdded, "
s1SQL = s1SQL & " DateModified, RoomNumID, TaskShortDesc, zType,
ToDoListKey, MaidReassign ) "
s1SQL = s1SQL & " SELECT "
s1SQL = s1SQL & " tt051aToDoRpt.Department, tt051aToDoRpt.zRoomTask,
"
s1SQL = s1SQL & " tt051aToDoRpt.Task_Description, "
s1SQL = s1SQL & " tt051aToDoRpt.SpanishText, "
s1SQL = s1SQL & " tt051aToDoRpt.Priority, tt051aToDoRpt.Assigned,
tt051aToDoRpt.WorkDate, "
s1SQL = s1SQL & " tt051aToDoRpt.zLoadDate, "
s1SQL = s1SQL & " tt051aToDoRpt.zHotel, tt051aToDoRpt.HouseKeepKey,
"
s1SQL = s1SQL & " tt051aToDoRpt.Housekeeper, tt051aToDoRpt.RmType, "
s1SQL = s1SQL & " tt051aToDoRpt.Ppl, tt051aToDoRpt.RmStatus, "
s1SQL = s1SQL & " tt051aToDoRpt.Condition, tt051aToDoRpt.StayOCC, "
s1SQL = s1SQL & " tt051aToDoRpt.Service, 7 AS zInd,
tt051aToDoRpt.zOrder, "
s1SQL = s1SQL & " tt051aToDoRpt.zFlag, tt051aToDoRpt.Active, "
s1SQL = s1SQL & " tt051aToDoRpt.DateAdded,
tt051aToDoRpt.DateModified, "
s1SQL = s1SQL & " tt051aToDoRpt.RoomNumID,
tt051aToDoRpt.TaskShortDesc, "
s1SQL = s1SQL & " tt051aToDoRpt.zType, tt051aToDoRpt.ToDoListKey,
tt051aToDoRpt.MaidReassign "
s1SQL = s1SQL & " FROM "
s1SQL = s1SQL & " tt051aToDoRpt "
s1SQL = s1SQL & " WHERE "
s1SQL = s1SQL & " tt051aToDoRpt.zHotel = "
s1SQL = s1SQL & " " & Chr$(34) & gscboHotelSite & Chr$(34) & " "
s1SQL = s1SQL & " And "
s1SQL = s1SQL & " tt051aToDoRpt.zIndicator = 2 "
s1SQL = s1SQL & " ORDER BY "
s1SQL = s1SQL & " tt051aToDoRpt.Department, "
s1SQL = s1SQL & " tt051aToDoRpt.zRoomTask "
s1SQL = s1SQL & " ; "
DoCmd.SetWarnings False
DoCmd.RunSQL s1SQL, False
DoCmd.SetWarnings True
+------------------------------------------------------------+
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Saturday, January 28, 2012 6:21 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Compression slows down MDB
William,
<<How would a query parser know whether a table is "full or not"?>>
As determined by looking at the table statistics.
I have a DB here somewhere that is an example of this. It had a three
table join which would be cause the wrong execution plan to be chosen if
one of the tables, which was a large one, was full of records or empty.
Using JET show plan, you could clearly see that the query parser used
two different plans of execution based on the table being full or empty.
One was efficient, one was not. As a result, the query went from <2 sec
execution to more then 30 seconds.
The table in question was filled/emptied on a regular basis and
sometimes when a C&R done, happened to be empty. Took forever to track
down why sometimes it would work well and other times not.
<<Why would access throw away valuable info such as the most efficient
way to plan out and run a query.>>
Because a DB changes over time and it doesn't track statistics like
that. JET is pretty simplistic. The assumption was made that after a
C&R, the DB is its most efficient form and that most tables are
relatively static in size.
After a C&R, pages have been reorganized, indexes re-balanced, table
statistics updated, etc. So every query plan at that point is
invalidated. First execution after that generates a new plan, which is
then saved with the query and used until the next C&R or you modify the
query and save it.
<<And what does "recosted" mean?>>
Any and all SQL query parsers given a specific statement "cost" it,
which means that it tries as several different ways of executing it and
then chooses the plan that is the most efficient or "costs" the least.
It does this based on the SQL statement, indexes available and type of
indexes (ie. having a unique index vs a non-unique one allows for
different types of index-merge operations), and the number of records in
the tables involved.
In the case I mentioned above the parser decided that with the table
empty, it cost less to sort the main table and then perform other
operations. However with the table full, sorting the table was a very
expensive operation and should have been done on the result set as a
finial step, which was only a couple of records.
JET doesn't expose this costing, but SQL server does and you can view
the costing plan for any SQL statement. It will show you each and every
step it performs (index joins, sorts, table scan, etc) and the cost to
executing the overall statement.
SQL even allows you to override the parser costing by using plan hints
to force the parser to weight certain plans more. JET does not have
this feature. If it did, the problem above could have been avoided even
with the table empty.
We also lost one of the old performance tricks that you could use with
JET; using a SQL statement instead of a saved query. In the past, SQL
statements were always costed at every execution. So if you had a very
dynamic table, you would just use SQL statements with it rather then
saved queries.
However many developers started using SQL everywhere and started
having performance problems as a result (because a query plan was being
done every time).
Rather then document the difference and force people to use saved
queries, Microsoft decided to create a temp query (a saved query) for
any SQL statement it found.
These start with a tilde (~) and you can see them in the querydefs
collection.
Jim.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William
Benson
Sent: Friday, January 27, 2012 10:28 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Compression slows down MDB
Jim I had to admit I could not make any sense out of what you explained.
How would a query parser know whether a table is "full or not"?
Trust me I am not debating you it just doesn't get thru my thick head
what you are saying.
Why would access throw away valuable info such as the most efficient way
to plan out and run a query.
And what does "recosted" mean?
Sorry I am such an ignoramus, if you prefer to reply with a link that I
can read up on this I would appreciate.
This seems to indicate it is a bad idea to compact on close of a
database. On Jan 27, 2012 3:18 PM, "Jim Dettman"
<jimdettman at verizon.net> wrote:
> Ed,
>
> I'm not sure this explains this, but part of the compact and repair
> process is to reset the table statistics. Those are used by the query
> parser to "cost" different ways of doing a query. Also part of the
> repair and compact process is that each query is flagged to be
> re-costed. So first time it's run, you get a new costing plan
> calculated (rather then using
the
> save one).
>
> Sometimes on rare occasions, the query parser on queries involving
> large tables will choose an inefficient plan depending on whether the
> table is full or not.
>
> But with all that said, you query plan would remain inefficient even
> if the database grew until it was re-costed again, at which point it
> would be back up to speed.
>
> So besides the compact and repair, what else are you doing within the
> DB in terms of records? Why does it go from 850MB down to 170? Are
> you
doing
> a large amount of inserting/deleting?
>
> Jim.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Edward
> Zuris
> Sent: Friday, January 27, 2012 12:11 PM
> To: accessd at databaseadvisors.com
> Subject: [AccessD] Compression slows down MDB
>
>
> Looking at MSFT's web site I get the impression
> that repair and compression of your MDB file is
> a good thing.
>
> However. . .
>
> Has anyone had the experience of doing a compress
> and your MsAccess application slowed way down ?
>
> At 170 megabytes in size, it takes 31 minutes to do
> a days worth of updates. At 850 megabytes it takes
> just 8 minutes.
>
> BTW, this happens on W2K 32bit Pro with Office 2000,
> and Win-7 64bit Pro, using Access 2003 32bit, using
> access 2000 file structures.
>
> The application didn't change that behavior when
> converting every thing over to Access 2003 file
> structures.
>
> Any ideas ?
>
> Thanks.
>
> Sincerely,
> Ed Zuris.
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com