Insert Query Speed

[cable]

I've got the following query:

INSERT INTO selFeesSum ( FEE_TYPE, FEE_HOW_PAID, FEE_AMOUNT )
SELECT srcFees.FEE_TYPE, srcFees.FEE_HOW_PAID, srcFees.FEE_AMOUNT
FROM selAccounts INNER JOIN srcFees ON (selAccounts.ACCOUNT_NO = srcFees.ACCOUNT_NO) AND (selAccounts.SUBACC_NO = srcFees.SUBACC_NO);

If I run the query via currentdb.execute I it's taking around 2 secs:
SQL:09/03/2005 13:20:44
09/03/2005 13:20:46
If I use the use the docmd.openquery it takes 20 secs:
QRY:09/03/2005 13:20:46
09/03/2005 13:21:06

I thought saved 'compiled' queries were supposed to be quicker?

 

[cable]

oh I didn't know that the querydef method would make such a difference, I'll try it.
tbh I've never really understood what querydef's gives you...except if your building a changing query for a report.

 

[cable]

When a querydef is saved, Jet processes it and determines how to execute it. It examines the existing indexes and the relative table sizes and figures out exactly how to go about obtaining your requested results. It then stores the "plan" for use when the query is executed. With dynamic SQL (everything that isn't saved as a querydef), that plan needs to be calculated EVERY time the query is run. It doesn't take a lot of time but it is unnecessary if you use stored querydefs. A bigger problem is that it contributes to database bloat. Apparently it takes quite a lot of workspace to come up with a plan and that space is not recovered until the db is compacted. So, if you execute a dynamic SQL statement 100 times each day, the plan is calculated 100 times each day and that discarded workspace accumultes until the db is compacted. If you are using a querydef, the plan is calculated and saved when the querydef is saved. It is then available for use when the query is run.

Ok, that makes sense! But is using a querydef the same as creating it in the grid, and then same as using docmd.openquery?