referential integrity makes my query run 30 times longer.

--------- summary of problem ----------------

I have a very simple snapshot query which was taking 30 seconds to run. I made it run much faster by dropping referential integrity.
But, that was an ugly solution because I usually prefer referential integrity rules.

For instance, in this application I was using referential integrity to insure that every sales order had a valid Region.  Dropping referential integrity was fairly safe because users choose the Region from a drop down list, but I don't like losing the additional protection of a referential integrity database rule.

I will live with my ugly solution, but I wonder if anybody has some comments about this performance issue?  

Of course, the standard anwer will probably be "switch to SQL Server", but that it is going to have to wait for a long time.


--------- details of problem  ----------------


RegionTable a record for each of our 4 company regions, North, South, East and West.   OrderTable has a records for every order with about 100,000 records.


The original query was very complicated, but the following simplification still takes 30 seconds to run.

    SELECT OrderTable.CustomerNumber
    FROM RegionTable INNER JOIN OrderTable ON RegionTable.region = OrderTable.[region]
    WHERE (((OrderTable.CustomerNumber)=222222) AND ((OrderTable.[Product Year])=2009) AND ((OrderTable.DateRun)>#1/5/2010#));

The two tables are in a Relationship with Join Type of 1 and "Enforce Referential Integrity" turned on.

When I turn off Referential Intergity,  the query runs in 1 second.

The purpose of the relationship was to insure that every OrderTable record had a legitimate Region.   I can understand why that relationship might make and append query run more slowly, but it does not make sense that a Snapshot query would run more slowly.


Relationships between Table1 and table2 are displayed follows: (the OO is my shorthand for the 'infinity' symbol sideways 8.)

RegionTable                OrderTable
Region  (1) ------(OO) Region    
TimeZone                  order number
                                DateRun
                                total sale
                                CustomerNumber
LVL 5
rberkeConsultantAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Also, can you upload a scaled down version of BE (with fewer records) and this query - with RI set up as you originally had it - the case where it takes 30 seconds; ie ... the original table/case, but with many fewer records.  C&R (to shrink) and Zip (to shrink) ...

?

mx
0
 
Rey Obrero (Capricorn1)Commented:
see if this make any difference


SELECT OrderTable.CustomerNumber
    FROM OrderTable INNER JOIN RegionTable ON RegionTable.region = OrderTable.[region]
    WHERE (((OrderTable.CustomerNumber)=222222) AND ((OrderTable.[Product Year])=2009) AND ((OrderTable.DateRun)>#1/5/2010#));

also, have you created any index in your table?
0
 
rberkeConsultantAuthor Commented:
Swapping the order of the tables did not improve performance. (I would have been shocked if it did)

RegionTable has Region as primary key.  OrderTable has OrderNumber-DateRun as primary key   and Region as secondary key.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
rberkeConsultantAuthor Commented:
Another interesting point --  if the database is moved from the server to a local drive, everything runs very fast.
 
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
"if the database is moved from the server to a local drive, everything runs very fast"

Then this would be network-related. Check hardware connections, uplug/replug cables, check NICs error logs, etc etc.
0
 
Rey Obrero (Capricorn1)Commented:
ask  your IT to monitor the network connection.
0
 
rberkeConsultantAuthor Commented:
I am the IT manager, and the programmer asked me.  Both of us are stumped.

The problem occurs on all clients (6 computers), so it is not a port issue.  
Removing the referential integrity solves the problem on all computers.

No events on client computer in any of the logs.

On server there a couple of "Success Audit" events but those are information only.

The problem occurs even if client is plugged directly into the server, so it is not a switch issue.

A compact and repair does not help.

All other Access applications run fine.  

Server is 6 months old and was purchased with plenty of power for future needs.  Cpu utilization typically runs below 3%.






0
 
Rey Obrero (Capricorn1)Commented:
create a blank db in a local machine,
link to the tables in the db that is on the server

create the same query in the local machine

test the query
0
 
rberkeConsultantAuthor Commented:
The problem originated in a system with backend database on server and front end database on the clients with dozens of tables/queries/forms/reports/modules.  

Over the last week, I simplified and isolated the problem by combining the back end with front end, then stripped it down. I now have a single database with no links and only 3 components:  2 tables and 1 query.  Since I suspected database corruption, I then recreated that database from scratch by copying the structure, then running an append query to create the data.

This is the simplest system that demonstrates the problem.

I then followed your instructions which essentially asked me return to a backend/frontend model.
When the frontend is on local machine an backend is on server,  things still run slow.
When both data bases are on the local machine things run fast.
   
0
 
Rey Obrero (Capricorn1)Commented:
what os is running on the server?
0
 
rberkeConsultantAuthor Commented:
sbs 2003 sp2
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<When the frontend is on local machine an backend is on server,  things still run slow.
When both data bases are on the local machine things run fast. >

This will always be the case. Access is a "file server" type of database, which basically means it's just a text file. All processing is done by the frontend program, so when the FE requests a query Access must pull all the tables across the wire, perform whatever is needed, and then present the data. Obviously if the tables are on the same disc as the FE that would be much quicker since disc read/writes are much faster than processes over the wire.

Compare this to a server-type database, which includes a query parser. Progarms request information and the server processes that info returns a formed dataset to the caller. Access/Jet/ACE don't do this. The only exception to this is if your query includes all indexed fields. In that case, the FE can use only the index tables (which are smaller) and as such performance can be better.

In an Access setup the power of the workstation is more important than the power of the server.

Here's a webpage that may help. The most relevant would be the SMB issue, in my opinion (turn OFF Small Message Blocking):
http://www.granite.ab.ca/access/performancefaq.htm

Also, as Cap said earlier, indexing is very important.
0
 
rberkeConsultantAuthor Commented:
LSMConsulting


The only one I could not try is the SMB issue, because it requires a reboot. I will try that tomorrow evening.

One of the tips mentioned opening a related recordset at login and leaving it open. That is very similar to a "solution" which I came up with in http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_25860761.html   It sort of works, the 30 second query happens immediately at login, then goes away for the rest of the session.  But it is almost as ugly as dropping the relationship.  

No luck on all the others. I went though the whole granite.ab.ca list (except, since my test database only has 1 query and two tables, I skipped everthing related to inserts/deletes/development gui/ forms/reports/shared access and other unrelated issue.)
Most of the remaining tips had been implemented long ago (for instance I turned off name tracking about 7 years ago, and periodically review it to make sure it does not get reset accidentally.)  

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"I can understand why that relationship might make and append query run more slowly,"
Why? On the contrary.  If they are set up correctly, it should run faster ... which is one of the benefits of having relationships with RI enforced.  Something else is going on.

What happens if you make this a dynaset query?

Is Region the Primary Key of the Region table ?

mx

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<opening a related recordset at login and leaving it open ... But it is almost as ugly as dropping the relationship.  >

This is a common technique when dealing with file-based data. The overhead to maintain a connection is nothing compared to the overhead of opening one. There is no call for this on a server-type database, but with file-based data it's important. If it fixes your issue, then it's the solution to your problem.

I don't necessarily agree with mx re: "one of the benefits of having relationships with RI enforced". Indexing tends to make data inserts slower and data reads faster, as a general rule, so there is some balance that must go on. Too much indexing can significantly slow your application, as can too little. You should also periodically revisit your indexing to insure that it's optimized. Unlike many other platforms, Access little in the way of assistance for this, so it's up to you to determine the best indexing plan.



0
 
rberkeConsultantAuthor Commented:
mx:
query was originally dynaset and was also slow.  In fact, I only changed it to snapshot in an attempt to make it go faster.
RegionTable has Region as primary key.  OrderTable has OrderNumber-DateRun as primary key   and Region as secondary key.

Also, you ask why I thought a relationship might make an append query run more slowly.
Of course, I was assuming the tables were already indexed properly.
Without a relationship, an append into OrderTable is very straightforward. MS Access just appends the records to the end of the database then updates the OrderTable indexes.
With a relationship with referential integrity, appends to OrderTable must first be checked against the related tables which requires extra I/Os and should therefore go slower.
 
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"I don't necessarily agree with mx re: "one of the benefits of having relationships with RI enforced". Indexing tends to make data inserts slower and data reads faster, as a general rule, so there is some balance that must go on"

"MS Access just appends the records to the end of the database then updates the OrderTable indexes."

But ... the SQL you posted at the top is a SELECT query, so no indexes are being updated.  Am I missing something?

mx
0
 
rberkeConsultantAuthor Commented:
mx:  The data is slightly proprietary. Can I email it to you?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Sorry, the violates EE rules.  You can only upload here so any expert can participate ...

Did you see my question here @ http:#a31289767 ?

mx
0
 
rberkeConsultantAuthor Commented:
Mx:  No, you are not missing something.  The slow query is Select and Snapshot  so it should not be affected by RI.  Nonetheless, RI still makes things go slower.  That is the whole point of this post  !!!!   Why the heck is RI hurting things ?


LSM:  I agree that too many indexes can clearly hurt update performance.  

The OrderTable in this particular database started off with 8 secondary indexes.  But, I eventually cut that number by 60%, and the problem still continued.


Also, I have seen cases where too many indexes even hurt INQUIRY performance.  I was never quite sure why, but dropping a few indexes actually helped. One theory was that MS Access accidentally used the "wrong" index and ended up taking longer to get at the data. But, I never proved it.
0
 
rberkeConsultantAuthor Commented:
Removing proprietary data will take me a bit of work.  If I put in the effort do you have an SBS 2003 server network to test it on?  Or at least a Server 2003 network?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
But you keep talking about 'update performance', which is not the issue here.

There is no argument that indexes are a double edge sword. I just see how that has anything to do with the issue here?

Well, if you can't upload ... don't really know what else to suggest.  I still say something else is going on.

mx
0
 
Leigh PurvisDatabase DeveloperCommented:
>> One theory was that MS Access accidentally used the "wrong" index and ended up taking longer to get at the data.
That's absolutely reasonable, despite the fact (and inevitable note from me) that MS Access has no database engine at all and so couldn't possibly decide. :-p
We're talking about Jet/ACE naturally.
(Equally statistics changing may cause poorer choices than were being made previously. A C&R can even result in poorer performance in some reports we here).
And this is also a possible issue with the query here.
By adding referential integrity, you're creating a new index. You say these fields are indexed?
Were they already indexed beforehand?
Consider removing that pre-existing index (so that in the table designer in Access the FK field appears to have no index upon it), then adding RI to the relationship. (Do a C&R of course to get back to an even keel).
The performance is still exactly the same?
FWIW I would imagine a snapshot isn't the way to go. Assuming you are selecting the PK of the "detail" table involved then dynaset should usually be a better first choice.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"By adding referential integrity, you're creating a new index. You say these fields are indexed?
Were they already indexed beforehand?"

Exactly, which is why I made the statement about RI.  I tried to find the discussion the Microsoft Jet Database Engine Programmers Guide last night, but I just couldn't find it in the time I had.  However, the FMS white paper (portion) is sort of similar:

http://www.fmsinc.com/tpapers/faster/

"To make queries run faster, you should have indexes on all fields in the query that join, restrict, or sort the data. Whenever possible, link on Primary Key fields instead of other fields. Indexes are most critical on tables with large numbers of records, so you may not see a difference on small tables. You also don't need to add secondary indexes on fields that are part of referential integrity."

mx

0
 
rberkeConsultantAuthor Commented:
Removed OrderTable's Pre-existing index and compacted.  Did not help.    The RegionTable's only had one index which was the primary index, so I did not remove that.

I have trimmed the database down so that a zip file is only 1 meg.  I will remove proprietary data and post the results soon.
 
0
 
rberkeConsultantAuthor Commented:
The update performance is a tangent which is not productive. I only mentioned it briefly in my first post, and all subsequent mentions have simply been my responses to questions by the devoted followers of this thread.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Remember to post the mdb that is represents the original issue ... with whatever indexes, etc ... in other words not the one that you just 'Removed OrderTable's Pre-existing index and compacted.'  

So that we can compare avocados to avocados.

mx
0
 
rberkeConsultantAuthor Commented:
Yeah, I a pretty well tuned in as to what people would like.

But, the bad news is that I am running out of time.

I did some mass changes replacing customer name address etc with all Xxxxs.  I changed too much.  Now the query is consistently slow with or without RI.

For what its worth, the production system is working better now that RI has been turned off (just between those two tables, another dozen RI relations remain unaffected.

I may play with this some more next week.

I will still try dropping the SMB and rebooting, and hope to do that tonight.

rberke
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
" I changed too much.  Now the query is consistently slow with or without RI."
Well ... that's ok ... if you still want to upload.  However, I will have to add a load of records the mess with.

mx
0
 
rberkeConsultantAuthor Commented:
No--the record count has not changed, just the content of the records.  Something very weird is happening and it will have to wait until next week.

By the way, I made those SMB that channges suggested by LSM's link and rebooted.  They did not seem to help.  I returned them to their previous values.

0
 
rberkeConsultantAuthor Commented:
I am closing this problem.  my "solution" was to remove referential integrity from a relation,
AND to put a dummy query into my database open routine.  

I wish I had more time to document details, but I have wasted too much time on this crud already.

For anybody who wants to see some of my false starts, here is a summary of the related problems.

When query is first opened it takes 30 seconds. Afterwards it is very fast.
   http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_25860761.html

How to reoganize all tables sequencing them by primary key?
    http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_25877487.html

referential integrity makes my query run 30 times longer.
    http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_25959786.html
0
 
rberkeConsultantAuthor Commented:
I feel bad abandoning this after having taken so much time for the contributing experts.

Unfortuantely I can't justify spending more time when my workarounds have eliminated the problem.

Thanks for everybody's help

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"Author Comments:
I feel bad abandoning this after having taken so much time for the contributing experts.

Unfortuantely I can't justify spending more time when my workarounds have eliminated the problem.

Thanks for everybody's help"

You are welcome.

mx
0
 
rberkeConsultantAuthor Commented:
Minor change:

The login form still stays open and invisible throughout the entire session.

But, originally it made things run fast by having the open event issue a Docmd.openquery "myQuery", acviewnormal, acreadonly.

But, since the open query was visible, it  would occasional appear to a user when other forms got closed.

To avoid the query showing, we changed the open event to issue a set rs = db.openrecordset("myQuery",dbopendynaset)  instead of the openquery.

The new code works just as well as the first solution. I tested it with both referential integrity both on and off, and it improves performance in both situations.  

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.