Edward Zuris
edzedz at comcast.net
Mon Jan 30 09:31:19 CST 2012
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