slow query - is it design or size?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good morning,

I have a database whose main table contains close to 600K records. One of
the daily reports in the db now takes an hour to run, and we are all about to
pull our hair out in frustration. The report is the culmination of 10 select
queries (one builds on to another so the final report is based on one last
query).

Do you think the problem lies in having so many records or having so many
queries building on top of one another?

TIA for any insight! :)
 
Thank you Roger. I was afraid of that. I am primarily self-taught over
about 6 years, and although I am proud of the functionality I've been able to
build, I'm sure the guts of the db, the design, is most likely embarrassingly
inefficient and convoluted.

Can you recommend a good resource for learning more about proper indexing?
I have an old Access manual and the "Access Bible". Thank you!
 
Jenny

I'll echo Roger's observations, and add a couple...

Is your data located on a "back-end", rather than being in the same .mdb
file as your forms and queries?

Is your data stored in Access/JET, in SQL-Server, as text files, as Excel
files, or ...?

How quickly does your first (in the chain) query run? The second? The nth?
Can you spot a place where everything slows down?

Is your last query built on the next-to-last, or does it collect together
ALL of the other queries into one result?

(and do check on the indexing ... any field in any query that is used to
Join, Sort, or Select may benefit from adding an index in the underlying
table(s). Of course, adding indexes will slow down the data entry aspects.
Indexing may be part art, part science...)

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Jeff - thank you for your response. I've added the answers to your questions
at the end of each question! :)
 
I'll take one step back farther than Roger and Jeff. Convoluted/complicated
queries along with bad performance is often a sign that the data isn't
properly normalized. In other words, it might take a complete redesign of the
database to get acceptable performance.

I highly recommend getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez.
 
Hi Jerry - thank you for the recommendation. I like the title much more than
something like "The Idiot's Guide...." - not quite as insulting! :) I will
get my hands on that book.

In the interim, I changed my [enter date] prompt into the 1st query, rather
than the last, and it sped up the whole thing to about 10 seconds. Not the
most intellectual way to fix the issue, but at least it's functional again.
 
**I don't know much about indexing at all, so I will have to research this aspect**
Click to expand...

<g> You're going to be startled and delighted, I'm guessing!

Each field used for Joining, Sorting, or Searching should be indexed. This
will probably make a dramatic improvement in performance, with no additional
changes to your queries.

John W. Vinson [MVP]
 
In the interim, I changed my [enter date] prompt into the 1st query, rather
than the last, and it sped up the whole thing to about 10 seconds. Not the
most intellectual way to fix the issue, but at least it's functional again.
Click to expand...

I blush to confess how often I've done something similar...

yep, filter the records as early as possible. No point in carrying 600,000
records through ten processing steps... and then throw away 599,400 of them in
the last step!

John W. Vinson [MVP]
 
Back
Top