Results 1 to 7 of 7

Thread: Index usage

  1. #1
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228

    Unanswered: Index usage

    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).

    Thanks for your time

    Michael

  2. #2
    Join Date
    Nov 2001
    Posts
    336
    Did you try to simplify your query using subqueries?
    It may speed up the whole process.

    Igor

  3. #3
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    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:

    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>
    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:

    SELECT *
    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
    WHERE subq.Restmenge>0;

    p.s.: don't blame me on the fieldnames, it's wasn't my design in the first place

  4. #4
    Join Date
    Nov 2001
    Posts
    336
    Try

    Query1:

    SELECT * From [bestellte Artikel] WHERE [bestellte Artikel].[Bestellung-Nr]= <ID inserted via code> AND [bestellte Artikel].[Restmenge] > 0;

    Query2:

    SELECT blablabla
    FROM Query1 INNER JOIN [Artikel]
    ON [bestellte Artikel].[Artikel-Nr] = Artikel.[Artikel-Nr];

    Igor

  5. #5
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    mhh, the first query is fast, the second one is slow again then again, if I join bestellte Artikel directly there it's fast again... confusing... guess I gotta live with it for the time.

  6. #6
    Join Date
    Nov 2001
    Posts
    336
    Did you try to create an index on Artikel-Nr field?

  7. #7
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    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.
    Last edited by Apel; 05-24-02 at 02:48.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •