[AccessD] Performance issue - QueryDef

Thanks Brett.  It is only the 'Set qdf =' line that is slow, the params and other lines are almost instantaneous.  In Design view, the Q takes less than a second to open, less after close then re-open in Design view.

The Q itself is 3 tables, a 'one' and two 'manys', with RI enforced in the underlying tables.

Anyway, I think it's resolved ... a close db, compact, and re-open has worked wonders <vbg>.

My thanks

Stephen Bond



> -----Original Message-----
> From: Brett Barabash [mailto:BBarabash at TappeConstruction.com]
> Sent: Tuesday, 25 November 2003 4:14 p.m.
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [AccessD] Performance issue - QueryDef
> 
> 
> Steven,
> If this is a fairly complex query, it probably takes the SQL 
> parser a bit of
> time to process it so it can generate a list of parameters.  
> Try opening it
> in design view.  How long does it take?  Then, it has to 
> determine whether
> what the parameters are: controls on a form or otherwise.
> 
> 
> -----Original Message-----
> From: Stephen Bond [mailto:stephen at bondsoftware.co.nz]
> Sent: Monday, November 24, 2003 8:54 PM
> To: accessd at databaseadvisors.com
> Subject: [AccessD] Performance issue - QueryDef
> 
> Line 3 below takes 4 seconds to execute:
>  
>     1.  Dim db as DAO.database, qdf as DAO.QueryDef 
>     2.  set db = Currentdb() 
>     3.  set qdf = db.QueryDefs("qryTransactionEntryFormGenerate")
>         4.  For each prm in qdf.Parameters
>         5.    prm.value = Eval(prm.Name)
>         6.  Next prm
>         7.  qdf.execute
>  
> The query is an Append query, the table being appended to is 
> empty.  The
> execution time is negligible.  Why should line 4 take so long??
> 
> Stephen Bond 
> 
> --------------------------------------------------------------
> ------------------------------------------------------
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom
> they are addressed.
> If you have received this email in error please notify the
> originator of the message. This footer also confirms that this
> email message has been scanned for the presence of computer viruses.
> 
> Any views expressed in this message are those of the individual
> sender, except where the sender specifies and with authority,
> states them to be the views of Tappe Construction Co.
> 
> Scanning of this message and addition of this footer is performed
> by SurfControl E-mail Filter software in conjunction with 
> virus detection software.
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>