--------- 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.
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.)
Region (1) ------(OO) Region
TimeZone order number