Query (with vba) fast as "select qry" 100x slower as "make table

[tektripper]

I have a query that makes use of many vba functions (but very basic). It uses 3 tables that are all indexed on the linked fields. If I run this query as select query it takes a few seconds before the datasheet appears. If I would like to view all records (ie move to last row) it may take a minute or two (the output is 10-15k rows.

When I convert this query to a make table/append query it takes an eternity to run.... In fact I am sitting here wondering if it will ever finish :-(. It would be quicker if I copied the output from the select query and pasted it into another table.

The database links to our data warehouse and blows up quickly (which is why it is compacted/repaired daily) but i can't understand why such a difference between the two query types (we are tallking a factor of x 100 or x 1000).

Driving me insane.

PS: My computer is a WIN NT P111 850 with 256 mb ram.

 

[KenReay]

Hi

In a Make TAble query you are as it says, making a table, this means that Access must insert n rows (in your case approx 15,000 rows) so this will take longer tahn the select query, which just has to read the rows.

True there will be the creation of temporay tables etc to allow Jet to extract and assemble the data you asked for, but this will be more or less the same in both examples, the overhead is in creating the table, and adding the rows.

You say the database 'links to your data warehouse', does this imply the Access mdb is just a front end with the data held (say) in SQL server?

If so you should gain some speed by making your query a stored procedure on the SQL Server, and executing it via Access, in this way the query will run (more quickly) on the server, and will return (over the lan) only the rows selected.

Hope this helps Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[tektripper]

Thanks for your response.

But just to let you know... our Datawarehouse is on a massive oracle database. I don't have the ability to create stored procedueres and if i did I believe I might lose the abaility to use my vba functions (i'm not sure of this).

I do use make table queries quite often and find them only marginally slower than select queries for the reasons you outlined. I have however, found a wrok around for my problem using dao in code. I basically just open the select query as a recordset and loop through the records adding them to my table using .AddNew. This takes a few minutes as opposed to the 3-4 hours the make-table took.

I still believe the is some sort of bug in the optimization algorithm that causes my example to take way too long.

ANyways thanks for your response, to all of you who may have experienced similar problems the code work around is sometimes a viable one!

Cheers and have a WICKED christmas