Query performance & SHOWPLAN anomalies

[Grant]

I have a query that performs very well immediately after a compact/
repair, but quickly degrades after the database has been in use
(updating activity) for a short time.

When running quickly, SHOWPLAN output reveals that the Jet optimizer
chooses Rushmore processing on indexes of three columns in the
predicate in order to restrict rows in a table that contains about
200,000 records prior to joining.

After a brief period of system use, which includes updates on the
tables under discussion, the query’s performance slows considerably
and SHOWPLAN reveals that the indexes used previously are no longer
being used, resulting in a scan over most of the 200,000 row table to
resolve the predicate. This causes the query's execution time to
increase from sub-second to 30 seconds; more if the query is running
over a network.

Further, SHOWPLAN output shows statistics about the indexes which
reveals that the number of entries in the indexes do not match the
number of rows in the table. I assume this represents some kind of
degradation, but I do not understand under what circumstances this
occurs. Compact/repair corrects this discrepancy (and the
optimization problem), but it recurs shortly after the application
begins updating the database.

I’m trying to understand why a small amount of updating activity
changes Jet’s optimization so drastically and what I can do to
maintain performance without having to run compact/repair incessantly.

Any observations or suggestions are appreciated. Thanks for your
help!

- Grant

 

[Stefan Hoffmann]

hi Grant,

[..]

It sounds like your indices get corrupted.

I’m trying to understand why a small amount of updating activity
changes Jet’s optimization so drastically and what I can do to
maintain performance without having to run compact/repair incessantly.

Can you spefcify "small amount of updating activity"? I'm sure it is cause.

btw, what kind of application is it?

Multi-user, splitted FE/BE? Does this effect depened on special
machines? What version of MDAC/Jet?

mfG
--> stefan <--

 

[Grant]

Stefan,

hi Grant,

Grant wrote:

 > [..]
It sounds like your indices get corrupted.

I’m trying to understand why a small amount of updating activity
changes Jet’s optimization so drastically and what I can do to
maintain performance without having to run compact/repair incessantly.

Can you spefcify "small amount of updating activity"? I'm sure it is cause.

It's hard to quantify the amount of updating activity. Let's say less
than 100 rows are added to the table in question.

btw, what kind of application is it?

VB 6.0. Typical record keeping application: work orders, etc.

Multi-user, splitted FE/BE? Does this effect depened on special
machines? What version of MDAC/Jet?

Multi-user app w/ .mdb file on a network file server. OLEDB Provider
4.0 for Jet. Not sure about the MDAC version, as this occurs on
multiple machines at different sites.

Any suggestions on how to mitigate index corruption? Thanks for your
help!!

mfG
--> stefan <--

- Grant

 

[Tony Toews [MVP]]

Multi-user app w/ .mdb file on a network file server. OLEDB Provider
4.0 for Jet.

Hmm, I know next to nothing about OLEDB works let alone with VB6 and a
Access MDB. I wonder if this is somehow causing the problem. But
how I don't know.

Any suggestions on how to mitigate index corruption?

Index corruption? What do you mean? Or are you talking about the
problem mentioned in this thread?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Stefan Hoffmann]

hi Grant,

Any suggestions on how to mitigate index corruption? Thanks for your
help!!

Can you reproduce the behaviour when you relocate your .mdb to your
local hard drive?

btw, can you post both plans?


mfG
--> stefan <--

 

[Grant]

hi Grant,


Can you reproduce the behaviour when you relocate your .mdb to your
local hard drive?

btw, can you post both plans?

mfG
--> stefan <--

Stefan,

Here is the query named qryROOpenDailyReminder0:

SELECT U.UnitNumber, U.Category, D.UnitID, D.ID AS DocID, D.SiteID,
D.FormID, D.DateIn, D.Class, D.Type, D.MechanicID, D.VendorID
FROM Document AS D, Unit AS U
WHERE D.UnitID = .[ID] AND D.SiteID=1 AND D.Type='R/O' AND
D.Status='OPEN' AND U.Deleted=False
ORDER BY U.UnitNumber;

Here is plan for qryROOpenDailyReminder0 that ignores indexes on
Document columns Type, Status, and SiteID, running very slowly. This
plan does not begin to be produced until after some amount of updating
on the tables following a compact/repair. Note that the plan index
stats citing "221077 entries" are incorrect as there are actually
222405 rows in the table. It takes over 30 seconds to resolve the
query using this plan.

---------------------------------------------
DATE: 10/29/08
VER: 4.00.9511

NOTE: Currently does not handle subqueries, vt parameters, and
subqueries
NOTE: You may see ERROR messages in these cases

--- qryROOpenDailyReminder0 ---

- Inputs to Query -
Table 'Document'
Database 'C:\TEMP\AA IR4236\Dossier_Cust.mdb'
Using index 'FK_Document_Unit'
Having Indexes:
FK_Document_Unit 221077 entries, 390 pages, 479 values
which has 1 column, fixed
FK_Document_Trip 221077 entries, 139 pages, 1 value
which has 1 column, fixed
FK_Document_StationID 221077 entries, 227 pages, 13 values
which has 1 column, fixed
FK_Document_State 221077 entries, 139 pages, 3 values
which has 1 column, fixed
FK_Document_Site 221077 entries, 198 pages, 16 values
which has 1 column, fixed
FK_Document_ParentDoc 221077 entries, 139 pages, 1 value
which has 1 column, fixed
FK_Document_ModifiedUser 221077 entries, 155 pages, 6 values
which has 1 column, fixed
FK_Document_Mechanic 221077 entries, 164 pages, 49 values
which has 1 column, fixed
FK_Document_FuelImport 221077 entries, 151 pages, 6 values
which has 1 column, fixed
FK_Document_Cost 221077 entries, 165 pages, 711 values
which has 1 column, fixed
FK_Document_Class 221077 entries, 168 pages, 6 values
which has 1 column, fixed
FK_Document_BuiltUser 221077 entries, 153 pages, 6 values
which has 1 column, fixed
FK_Document_BillingMethod 221077 entries, 139 pages, 1 value
which has 1 column, fixed
Table 'Unit'
Database 'C:\TEMP\AA IR4236\Dossier_Cust.mdb'
- End inputs to Query -

01) Restrict rows of table Unit
using rushmore
for expression "U.Deleted=0"
02) Inner Join result of '01)' to table 'Document'
using index 'Document!FK_Document_Unit'
join expression "U.ID=D.UnitID"
then test expression "D.Status='OPEN' And (D.Type='R/O' And
D.SiteID=1)"
03) Sort result of '02)'


The following is the query plan that results immediately after a
compact/repair on the same database. Note the number of index entries
now corresponds with the number of rows in table Document (222405).
In addition, the optimizer is now using Rushomre to resolve predicates
on indexed Document columns Status, Type, and SiteID. This query plan
resolves in less than one second.

---------------------------------------------
DATE: 10/29/08
VER: 4.00.9511

NOTE: Currently does not handle subqueries, vt parameters, and
subqueries
NOTE: You may see ERROR messages in these cases


--- temp query ---

- Inputs to Query -
Table 'Document'
Database 'C:\TEMP\AA IR4236\Dossier_CR.mdb'
Using index 'PK_Document'
Having Indexes:
PK_Document 222405 entries, 373 pages, 222405 values
which has 1 column, fixed, unique, primary-key, no-nulls
IX_DocumentType 222405 entries, 177 pages, 6 values
which has 1 column, fixed
IX_DocumentStatus 222405 entries, 162 pages, 3 values
which has 1 column, fixed
IX_DocumentFormID 222405 entries, 255 pages, 38692 values
which has 1 column, fixed
FK_Document_Vendor 222405 entries, 142 pages, 92 values
which has 1 column, fixed
FK_Document_Unit 222405 entries, 264 pages, 479 values
which has 1 column, fixed
FK_Document_Trip 222405 entries, 137 pages, 1 value
which has 1 column, fixed
FK_Document_StationID 222405 entries, 216 pages, 13 values
which has 1 column, fixed
FK_Document_State 222405 entries, 138 pages, 3 values
which has 1 column, fixed
FK_Document_Site 222405 entries, 195 pages, 16 values
which has 1 column, fixed
FK_Document_ParentDoc 222405 entries, 137 pages, 1 value
which has 1 column, fixed
FK_Document_ModifiedUser 222405 entries, 147 pages, 6 values
which has 1 column, fixed
FK_Document_Mechanic 222405 entries, 155 pages, 49 values
which has 1 column, fixed
FK_Document_FuelImport 222405 entries, 143 pages, 6 values
which has 1 column, fixed
FK_Document_Cost 222405 entries, 161 pages, 711 values
which has 1 column, fixed
FK_Document_Class 222405 entries, 165 pages, 6 values
which has 1 column, fixed
FK_Document_BuiltUser 222405 entries, 147 pages, 6 values
which has 1 column, fixed
FK_Document_BillingMethod 222405 entries, 137 pages, 1 value
which has 1 column, fixed
- End inputs to Query -

01) Scan table 'Document'
Database 'C:\TEMP\AA IR4236\Dossier_CR.mdb'
Using index 'PK_Document'

--- temp query ---

- Inputs to Query -
Table 'Unit'
Database 'C:\TEMP\AA IR4236\Dossier_CR.mdb'
Using index 'PK_Unit'
Having Indexes:
PK_Unit 487 entries, 1 page, 487 values
which has 1 column, fixed, unique, primary-key, no-nulls
IX_UnitSerialNumber 395 entries, 4 pages, 390 values
which has 1 column, fixed, nulls ignored
IX_UnitLicensePlate 322 entries, 3 pages, 244 values
which has 1 column, fixed, nulls ignored
IX_Unit_UnitNumber_Category 487 entries, 5 pages, 487 values
which has 2 columns, fixed, unique
IX_Unit_Deleted 487 entries, 1 page, 2 values
which has 1 column, fixed
IX_MfgYear 359 entries, 1 page, 43 values
which has 1 column, fixed, nulls ignored
IX_delete_date 487 entries, 1 page, 53 values
which has 1 column, fixed
FK_Unit_SyscoCategory 487 entries, 1 page, 1 value
which has 1 column, fixed
FK_Unit_Site 487 entries, 1 page, 15 values
which has 1 column, fixed
FK_Unit_FuelType 487 entries, 3 pages, 6 values
which has 1 column, fixed
FK_Unit_Customer 487 entries, 1 page, 1 value
which has 1 column, fixed
FK_Unit_Condition 487 entries, 3 pages, 11 values
which has 1 column, fixed
FK_Unit_Category 487 entries, 4 pages, 80 values
which has 1 column, fixed
- End inputs to Query -

01) Scan table 'Unit'
Database 'C:\TEMP\AA IR4236\Dossier_CR.mdb'
Using index 'PK_Unit'

--- qryROOpenDailyReminder0 ---

- Inputs to Query -
Table 'Document'
Database 'C:\TEMP\AA IR4236\Dossier_CR.mdb'
Table 'Unit'
Database 'C:\TEMP\AA IR4236\Dossier_CR.mdb'
- End inputs to Query -

01) Restrict rows of table Unit
using rushmore
for expression "U.Deleted=0"
02) Restrict rows of table Document
using rushmore
for expression "((D.SiteID=1) AND (D.Type='R/O')) AND
(D.Status='OPEN')"
03) Sort result of '02)'
04) Inner Join result of '01)' to result of '03)'
using temporary index
join expression "U.ID=D.UnitID"
05) Sort result of '04)'


Any assistance appreciated. Thanks!!

- Grant

 

[Stefan Hoffmann]

hi Grant,

I would rewrite the statement using the JOIN syntax, but this should not
affect the performance.

WHERE D.UnitID = .[ID]
AND D.SiteID=1
AND D.Type='R/O'
AND D.Status='OPEN'
AND U.Deleted=False

Have you tried a combined index on Document(SiteID, Type, Status)?


mfG
--> stefan <--

 

[Grant]

hi Grant,

I would rewrite the statement using the JOIN syntax, but this should not
affect the performance.

WHERE D.UnitID = .[ID]
AND D.SiteID=1
AND D.Type='R/O'
AND D.Status='OPEN'
AND U.Deleted=False

Have you tried a combined index on Document(SiteID, Type, Status)?

mfG
--> stefan <--

JOIN syntax makes no difference. I have not tried altering the schema/
indexes yet. Still trying to understand why this behavior occurs.

- Grant

 

[david]

Interesting, but no solution. Do you get the same behaviour
when testing locally with one user? You say multiple servers
-- is it all one company with a common platform, or is multiple
different server versions?

I think I'd look at transactions and flushing the Jet cache. I've
never actually looked at the index numbers in ShowPlan to
see if my indexes are getting updated correctly.

(david)





Stefan,

hi Grant,

[..]

It sounds like your indices get corrupted.

I’m trying to understand why a small amount of updating activity
changes Jet’s optimization so drastically and what I can do to
maintain performance without having to run compact/repair incessantly.

Can you spefcify "small amount of updating activity"? I'm sure it is
cause.

It's hard to quantify the amount of updating activity. Let's say less
than 100 rows are added to the table in question.

btw, what kind of application is it?

VB 6.0. Typical record keeping application: work orders, etc.

Multi-user, splitted FE/BE? Does this effect depened on special
machines? What version of MDAC/Jet?

Multi-user app w/ .mdb file on a network file server. OLEDB Provider
4.0 for Jet. Not sure about the MDAC version, as this occurs on
multiple machines at different sites.

Any suggestions on how to mitigate index corruption? Thanks for your
help!!

mfG
--> stefan <--

- Grant