I know I'm asking a bit much from Access but the data is not (yet) lying on a db server. The problem I have is Access choses the wrong index in my query. I have a join and 2 criterias, both criterias and the joined fields happen to be indexed. Now performance is greatly influenced by the order the criterias are processed. Normally Access choses to limit on indexed fields first but doesn't seem to analyze which criteria on multiple indexed fields should be executed first and in my case it seems to chose the wrong one resulting in poor performance. Actually I wouldn't really need that particular index in that query as only a bunch of records are returned due to the other criteria but others really need it. The question is if there's ANY way to tell access NOT to use an index or customize the execution plan? It's not tragic as the db will be lying on a db server soon anyway but the current performance is really poor (guess it's about factor 20 slower as with correct execution plan) and I'm tired of explaining why this function is slow (it used to be done with looping through a recordset and I transformed it into a query in foresight to the db change).
How so? It's really not a complex query. If I take out one of the 2 parameters it results in lets say 10 records. Execution time is roughly 2 seconds. Now the 2nd parameter usually gets out about 3 records of these. Now if I take the 2nd parameter back in execution time raises to like 20secs. If the execution plan would be correct it rarely would cause in higher execution time.
this is the query:
FROM [bestellte Artikel] INNER JOIN [Artikel]
ON [bestellte Artikel].[Artikel-Nr] = Artikel.[Artikel-Nr]
WHERE [bestellte Artikel].[Bestellung-Nr]= <ID inserted via code>
AND [bestellte Artikel].[Restmenge] > 0;
I tried your suggestion with the subquery (if I understood it correctly). It's really not simpler that way but it should reflect the desired execution plan. Seems like Access is "too good" optimizing queries though. The results are exactly the same:
FROM [SELECT blablabla
FROM [bestellte Artikel] INNER JOIN Artikel ON [bestellte Artikel].[Artikel-Nr] = Artikel.[Artikel-Nr]
WHERE [bestellte Artikel].[Bestellung-Nr]=<ID inserted via code>]. AS subq
p.s.: don't blame me on the fieldnames, it's wasn't my design in the first place
It is indexed in both tables,
So are the fields of the criterias
As I said it's a problem of Access chosing the wrong execution plan.
edit: let me make it clear once again:
The field for the join is indexed on both sides. Both criteria fields are indexed while the index of the field"Restmenge" should NOT be used for performance reasons (or if then in the right order). It looks like when using the join (for whatever reason only there) it FIRST procecesses the WHERE Restmenge > 0 and THEN WHERE [Bestellung-Nr] = blabla. While this is of course retrieve correct results it is dramatically slower than if the criterias would be processed just the other way round as there are a lot of records matching Restmenge > 0 in the table while there are only a few matching each Bestellung-Nr. You could say to remove the index on the Restmenge field but it is needed for other queries.