Updated subject: Query failing to execute WAS: RE: [AccessD] Combobox question UPDATE

My 2 cents worth.

1/ Removing the indexes is a sure fire way to slow things down.
2/ If this is running on a JET database then executing the SQL string
directly does not give the JET engine a chance to optimize the query. Better
to plug the SQL into a querydef and the run the query, as you discovered.
How to do that in code?...

Dim Qd as QueryDef
Dim sSQL as String

	sSQL = "UPDATE Contract 	INNER JOIN [Payroll Line Items] ON
Contract.[Contract ID] = [Payroll Line Items].Contract ID] SET [Payroll Line
Items].PolicyID = [EnforceWCID] WHERE [Payroll Line Items].CostType='WC' AND
Contract.EnforceWCID<>0;"

	Set Qd = CurrentDb.QueryDefs("MyTempQuery")
	Qd.SQL = sSQL
	Set Qd = Nothing
	Docmd.OpenQuery "MyTempQuery"

3/ How many records were involved with the other databases?

Lambert

> -----Original Message-----
> From:	Bobby Heid [SMTP:bheid at appdevgrp.com]
> Sent:	Wednesday, June 30, 2004 3:31 PM
> To:	'Access Developers discussion and problem solving'
> Subject:	RE: Updated subject: Query failing to execute WAS: RE:
> [AccessD] Combobox question UPDATE
> 
> I let it run during lunch and it finally did get through the query.  It
> updated about 48000 records.
> 
> I still do not know why it is taking so long.  I even took off almost all
> of
> the indexes of the payroll table and it still took a very long time.
> 
> Any ideas?
> 
> Thanks,
> Bobby
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bobby Heid
> Sent: Wednesday, June 30, 2004 11:41 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Updated subject: Query failing to execute WAS: RE: [AccessD]
> Combobox question
> 
> 
> Sorry about that.  I forgot to change the subject.
> 
> Bobby
> 
> 
> -----Original Message-----
> From: Bobby Heid [mailto:bheid at appdevgrp.com] 
> Sent: Wednesday, June 30, 2004 11:40 AM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [AccessD] Combo box question
> 
> 
> Hi all,
> 
> I have a query that is run during a conversion of a database that appears
> to
> be hanging on this one particular database.
> 
> Here's the query:
> UPDATE Contract 
> 	INNER JOIN [Payroll Line Items] 
> 		ON Contract.[Contract ID] = [Payroll Line Items].[Contract
> ID] 
> 	SET [Payroll Line Items].PolicyID = [EnforceWCID]
> 	WHERE [Payroll Line Items].CostType='WC' 
> 		AND Contract.EnforceWCID<>0;
> 
> This query is placed into a string and executed with: db.execute
> strSQL,dbfailonerror
> 
> There are ~192,000 payroll records and ~3500 contract records.
> 
> If I copy the SQL into the query designer and run it, it only takes about
> 5
> seconds or so to execute.  I have let the version in the code run for over
> 1/2 hour and have to end up killing access.
> 
> Anyone have any ideas as to what may be happening?  I am running AXP sp2.
> The tables are linked.  This code has ran fine on over 100 other
> conversions.
> 
> Thanks,
> Bobby
> 
> -- 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com