Query Speed Issues

H

HCAccessUser

I have a reporting database that uses three large .mdb files (approx 2.5 gb
total for the three) which hold lots of tables in each. Every night, I clear
down the tables and refill them. They occasionally grow and need compacting,
but whenever I compact them, I have trouble refilling them the next night and
must rebuild the structure of several tables to get them working again.

I recently wrote a routine to recreate the tables by appending empty
versions into new .mdb files, then adding indexes. The tables appear
identical to the old ones except are smaller, but my reports now run VERY
slowly (a report that should take 15 seconds over the network takes 75
seconds). When I run a report or query on the new tables, it writes
"Calculating" in the status bar for about 35-40 seconds, then begins to run
the query. With the old tables, it skips this step and runs everything faster.

I need to use the smaller tables, but can't for speed reasons. What can I do
to remedy this? What does the message "Calculating" indicate?

B Usher
 
G

Graham R Seach

I'm afraid I don't have the true and exact answers to your questions, but
I'll offer you an explanation I consider likely.

As you probably already know, Access compiles a query the first time it is
run. I imagine it probably does this using specific (table) object ids, kind
of like the SQL Server SCHEMABINDING option. When you run a query after
recreating the tables, the query engine has to find the new object ids and
recompile the query because the object ids are now different. Don't quote
me, but that may be the reason for the "Calculating..." status.

As for the need to rebuild the tables; I wouldn't have thought that'd be
necessary. Do you shutdown Access after compacting? When you say you have
trouble filling them, what errors are you getting? Off the top of my head,
it sounds like a PK issue; most likely to do with AutoNumber.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
H

HCAccessUser

That makes sense. Do you know if there is a way to recompile the queries or
somewhere to look for more info on this?

I compress the .mdb files and exit them. I then try to append data into
them, and a few (maybe 10-15) won't fill. I get a very vague error message
(something with "There is no message for this error". I have to recreate the
table structure to get it to work again. I recreate it by doing a make table
query, then applying necessary indexes. There is no auto number on the tables
-- they are copies of sql server queries and I set up primary key on data
items per the sql tables.

B Usher
 
G

Graham R Seach

As far as I know, the only way to compile a query is to run it.

As far as that vague error message is concerned, I've only experienced it
about twice in 12 years or so, and I'm none the wiser.

How are you getting the data into the tables. If using a query, what's the
SQL for the most offensive table?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
 
H

HCAccessUser

Well, they've been run and run. I tried using Analyser and it speeds it up a
bit, but makes the run time 58 seconds against the 15 it should be.

The append queries are simple. A typical one is:
INSERT INTO stthstud ( student_id, acad_period, register_id, register_group,
u_version, starting_week, withdrawn_week, class_seq_no, total_present,
total_absent, percent_attend, end_week )
SELECT stthstud.student_id, stthstud.acad_period, stthstud.register_id,
stthstud.register_group, stthstud.u_version, stthstud.starting_week,
stthstud.withdrawn_week, stthstud.class_seq_no, stthstud.total_present,
stthstud.total_absent, stthstud.percent_attend, stthstud.end_week
FROM dbo_stthstud;

where dbo_stthstud is on SQL Server and stthstud is a linked Access table.
Once I recreate the structure, they run fine. That's why I think the compact
scrambles some of the table structures. I compact when the mdbs are empty to
save time. I'll test it next week on tables that are full and see if it works
better.

Thanks for the help. I'll do some more research on compiling queries as I
like the idea of being able to make clean mdb files periodically.

B Usher
 
G

Graham R Seach

OK, I'm stumped.

One suggestion I'd make however, is to do the updates using DTS against a
remote server (Access). In fact, why not do the whole thing in SQL Server?
It'd certainly run a whole lot faster!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
 
A

aaron.kempf

MDB is crap and can't handle 25 records without failing; let alone 2.5
gb

i would reccomend using SQL Server for anything that is over 10mb.

-Aaron
 
Top