locked
How can I explain the cause of a phenomenon in my query in access? RRS feed

  • Question

  • I have a query in MS acceess that in where clause I have:

    WHERE (((tb_KonzeptFunktionen.Konzept)=[Formulare]![frm_Fahrzeug]![ID])); 

    it takes long time to run but when I delete this where clause or put this Where clause instead of the last, for example :

    WHERE tb_KonzeptFunktionen.Konzept=1; 

    The query runs less than a second.How can I explain the cause of a phenomenon in my query ?

    Tuesday, May 29, 2012 8:48 AM

Answers

  • What happens if you declare the parameter (always a good idea)

    PARAMETERS [Formulare]![frm_Fahrzeug]![ID] LONG;
    SELECT tb_KonzeptFunktionen.Konzept AS KonzeptID,
    etc

    Ken Sheridan, Stafford, England

    • Proposed as answer by Dummy yoyo Friday, June 8, 2012 7:11 AM
    • Marked as answer by Dummy yoyo Friday, June 8, 2012 7:12 AM
    • Unmarked as answer by babak.per Tuesday, June 12, 2012 8:37 AM
    • Unproposed as answer by babak.per Tuesday, June 12, 2012 8:37 AM
    • Marked as answer by babak.per Tuesday, June 12, 2012 9:59 AM
    Tuesday, May 29, 2012 11:04 PM
  • I mean, why without this parameter definition my query is slow but with this parameter definition the query is fast?
    I don't really know.  I can only assume that it's an internal optimization issue.  The point has not previously been raised as far as I'm aware.  While the failure to declare parameters has sometimes been found to be the reason for query returning the wrong rows, I don't recall it having been identified as a cause of poor performance before.  It's interesting to see that this is also a factor.

    Ken Sheridan, Stafford, England

    • Marked as answer by babak.per Friday, June 22, 2012 6:57 AM
    Tuesday, June 19, 2012 11:34 AM

All replies

  • Hi babak.per,

    I think it's because you use the localised name for Forms so the engine have to translate Formulare to Forms before it can execute the query but that's it's just a conjecture of mine.

    HTH Paolo

    Tuesday, May 29, 2012 8:56 AM
  • Don't know specifically but are you always sure you have the form, [frm_Fahrzeug] open at the time when this query is run?

    You say the query takes a long time to run. Does it always finish running? When it finishes running, does it always give the correct answer? (I can think of scenarios where you might get the results you describe, depending on the answers to these questions.)

    Can you tell us where you use these queries? Do you run them from VBA code? Or do you have queries like this in the RecordSource of certain Forms, or, in certain saved Queries?

    One potential way to work around this problem would be to write the query using some VBA code. You might read the value of [Formulare]![frm_Fahrzeug]![ID] using VBA code, verify that everything is OK with this value and that it is available (no error conditions); and then construct an SQL query based on that validated value...

    "WHERE tb_KonzeptFunktionen.Konzept=" & [Formulare]![frm_Fahrzeug]![ID] & ";"

    Something like this. I notice that your query contains lots of brackets... This shouldn't cause any problems for simple queries as this appears to be, but I've noticed cases where subtle changes in bracketing within SQL queries (that should theoretically make no difference) can cause major problems for Access (so that Access is no longer able to run the query at all). This happens sometimes even in cases where Access has automatically added the brackets for you! So take out some brackets from the real query, and see if that helps too.


    Matthew Slyman M.A. (Camb.)

    Tuesday, May 29, 2012 9:04 AM
  • Thank you so much Mathew

    Does it always finish running? When it finishes running, does it always give the correct answer? (I can think of scenarios where you might get the results you describe, depending on the answers to these questions.)

    I 'm not sure if I understand your question corectly but yes it always finish running and give me back corect answer 

    Can you tell us where you use these queries? Do you run them from VBA code? Or do you have queries like this in the RecordSource of certain Forms, or, in certain saved Queries?

    I use this code in a query and not in VBA code its a part of a query that you can see this query here

    I deleted the brackets but no change

    Tuesday, May 29, 2012 9:32 AM
  • I think this might come down to poor optimisation of the query by MS Access. Based on past experience, combined with what you are telling us here; I have a suspicion that MS Access performs the JOIN first and THEN applies this troublesome WHERE condition in cases where the WHERE condition is NOT a simple constant; or alternatively, in cases where the WHERE condition contains simple constants, Access performs a trivial optimisation internally prior to actually running the query by applying the WHERE condition (or those parts of the WHERE condition that are admissible to this optimisation) prior to the JOIN.

    You might be able to optimise the query yourself by:

    1. Making a subquery based on tb_KonzeptFunktionen, applying this WHERE condition to that, and then JOINing on this subquery. OR
    2. Converting this WHERE condition into a JOIN condition (you can do this even though the WHERE condition we are discussing does not actually relate the two tables together. Beware of SQL Server ODBC compatibility issues that may be caused by this work-around approach - if you are using SQL Server via ODBC, you can work around this further problem by using explicit type conversion functions CInt, CLng, CStr etc., on both sides of each expression.)

    Let me know if this doesn't solve your problem.


    Matthew Slyman M.A. (Camb.)

    • Marked as answer by Dummy yoyo Friday, June 8, 2012 7:10 AM
    • Unmarked as answer by babak.per Tuesday, June 12, 2012 8:37 AM
    Tuesday, May 29, 2012 4:21 PM
  • Note @Microsoft: If this turns out to be the problem, please make sure that in future versions of MS Access, Access should treat Form Control parameters within Queries as though they were literal constants, for query optimization purposes. [Pending confirmation on this point...]

    Matthew Slyman M.A. (Camb.)

    Tuesday, May 29, 2012 4:30 PM
  • Had a look at your original post to view the whole query but it doesn't clarify if you are using this in VBA or as the record source for a form.  Either way have you tried assigning the value of [Formulare]![frm_Fahrzeug]![ID] to a variable then using that.  For example;

    Dim lValue As Long
    
    lValue = [Formulare]![frm_Fahrzeug]![ID]
    
    me.recordSource = "SELECT tb_KonzeptFunktionen.Konzept AS KonzeptID, tb_KonzeptFunktionen.Funktion,
        tb_KonzeptFunktionen.Version, 
        qryFunktionen_Übersicht.ID,
        qryFunktionen_Übersicht.Fehlerpfad_Kommentar AS Kommentar, 
        qryFunktionen_Übersicht.Fehlerpfadname, 
        qryFunktionen_Übersicht.Fehlerpfad_CDT, 
        qryFunktionen_Übersicht.Fehlerpfad_Kommentar, 
        qryFunktionen_Übersicht.symptombasiert, 
        qryFunktionen_Übersicht.Beschreibung_vorhanden, 
        qryFunktionen_Übersicht.Max_Pfad, 
        qryFunktionen_Übersicht.Max_Info, 
        qryFunktionen_Übersicht.Max_Status, 
        qryFunktionen_Übersicht.Max_Strategie, 
        qryFunktionen_Übersicht.Max_Prüfplan, 
        qryFunktionen_Übersicht.Min_Pfad, 
        qryFunktionen_Übersicht.Min_Info, 
        qryFunktionen_Übersicht.Min_Status, 
        qryFunktionen_Übersicht.Min_Strategie, 
        qryFunktionen_Übersicht.Min_Prüfplan, 
        qryFunktionen_Übersicht.Sig_Pfad, 
        qryFunktionen_Übersicht.Sig_Info, 
        qryFunktionen_Übersicht.Sig_Status,
        qryFunktionen_Übersicht.Sig_Strategie, 
        qryFunktionen_Übersicht.Sig_Prüfplan, 
        qryFunktionen_Übersicht.Plaus_Pfad, 
        qryFunktionen_Übersicht.Plaus_Info, 
        qryFunktionen_Übersicht.Plaus_Status, 
        qryFunktionen_Übersicht.Plaus_Strategie, 
        qryFunktionen_Übersicht.Plaus_Prüfplan, 
        qryFunktionen_Übersicht.Beschreibung_allgemein, 
        qryFunktionen_Übersicht.Funktionsname        
    FROM tb_KonzeptFunktionen RIGHT JOIN qryFunktionen_Übersicht 
        ON tb_KonzeptFunktionen.Funktion = qryFunktionen_Übersicht.Funktionsname
    WHERE (((tb_KonzeptFunktionen.Konzept)=" & lValue & "))"

    • Marked as answer by Dummy yoyo Friday, June 8, 2012 7:12 AM
    • Unmarked as answer by babak.per Tuesday, June 12, 2012 8:36 AM
    Tuesday, May 29, 2012 10:43 PM
  • What happens if you declare the parameter (always a good idea)

    PARAMETERS [Formulare]![frm_Fahrzeug]![ID] LONG;
    SELECT tb_KonzeptFunktionen.Konzept AS KonzeptID,
    etc

    Ken Sheridan, Stafford, England

    • Proposed as answer by Dummy yoyo Friday, June 8, 2012 7:11 AM
    • Marked as answer by Dummy yoyo Friday, June 8, 2012 7:12 AM
    • Unmarked as answer by babak.per Tuesday, June 12, 2012 8:37 AM
    • Unproposed as answer by babak.per Tuesday, June 12, 2012 8:37 AM
    • Marked as answer by babak.per Tuesday, June 12, 2012 9:59 AM
    Tuesday, May 29, 2012 11:04 PM
  • Hi babak,

    Welcome to the MSDN forum!

    I temporarily marked the replies as answers and you can unmark them if they provide no help.

    Thanks for your understanding and have a nice day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    Friday, June 8, 2012 7:12 AM
  • Thank you ken your idee was bereliant !

    but How can I interpret this?

    Tuesday, June 12, 2012 10:04 AM
  • but How can I interpret this?

    Sorry, I don't understand what you mean?

    Ken Sheridan, Stafford, England

    Tuesday, June 12, 2012 10:45 AM
  • I mean, why without this parameter definition my query is slow but with this parameter definition the query is fast?
    Tuesday, June 19, 2012 10:45 AM
  • I mean, why without this parameter definition my query is slow but with this parameter definition the query is fast?
    I don't really know.  I can only assume that it's an internal optimization issue.  The point has not previously been raised as far as I'm aware.  While the failure to declare parameters has sometimes been found to be the reason for query returning the wrong rows, I don't recall it having been identified as a cause of poor performance before.  It's interesting to see that this is also a factor.

    Ken Sheridan, Stafford, England

    • Marked as answer by babak.per Friday, June 22, 2012 6:57 AM
    Tuesday, June 19, 2012 11:34 AM
  • I would like to say: if I have index on the necessary fields, that works fine but without indexing is again very slow.

    indexing  and Parameter Definition work fine in my situation

    Friday, June 22, 2012 7:48 AM