RE: Processing thousands of records

Tech-Archive recommends: Fix windows errors by optimizing your registry

Great!

If you lived near St. Louis, you could take the college course that I teach.
;-)

Allen mentioned one book by Hernandez. To that I'll add "Database Design for
Mere Mortals" also by Michael Hernandez for basic database design and
normalization.

http://safari.oreilly.com/0201752840

As for indexes, every table should have a primary key. Hopefully it is only
one field. Access automatically creates an index for primary key fields.

If your queries join two or more tables, go to the Relationships window and
see if a relationship is defined between the tables involved. Hopefully they
are and Referential Integrity is enables as this will also create an index
with the Foreign Key in the other table. If not, see if you can link the
tables and turn on RI. There's a very good chance that it can not. In that
case mark sure that the linked fields in both tables have indexes.

Also index any field that is part of the Where Clause or Order By.

Warning: Indexes aren't free. They also take up space and can slow down
inserts and updates. Don't go overboard on the indexing. As mentioned before
Access will automatically create an index for Primary Key fields. It will
also create indexes for field with any of the following in any part of the
field names: ID; key; code; num. In fact Access has no problem creating two
or more indexes on the same field. For example if your Primary Key is named
PK_ID, you will have two indexes for the same field which is wasteful.

Now for your query: INTO 1stVisit02

Often inserting records into another table is a sign of problems. Normally
you just want to grab the data from the primary tables as needed. Maybe now
that the query is working faster, you don't need the 1stVisit02 query. Moving
records from one table to another is often a mistake and a sign that the data
isn't normalized properly. An exception is when importing data from other
files and programs.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"Dave" wrote:

I took a shot at this and indexed Item Number, Serial Number and Task Number.

I got a return in 1 minute.

Soooo.... where do I read about fundamental indexing and normalization?

Thanks

Dave

"Jerry Whittle" wrote:

How long is 'forever' in this case?

The first thing that affect performance is how well the database is
designed. Are the tables properly normalized?

Next could be the query itself. There are a few tricks of the trade like
trying an Exists clause instead of an In clause. The flip side is that
sometimes the In will work better than the Exists so you need to try it both
ways. Then there is differences between UNION and UNION ALL queries. The same
goes for DISTINCT versus GROUP BY all the fields.

Then there's indexing. Do you have indexes on the fields where you have
criteria?

Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here. Information on primary keys and relationships
would be a nice touch too. Also any indexes.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Dave" wrote:

I have a query that someone had helped me with and it works great: However
when I run this against 91,000 records the query runs forever.

When running queries against thousands of records, is there a tricks to do
this effectively. Obviously the process and memory limits calculations, but
are there tricks to handling a lot of data?

Thanks

Dave
.

Relevant Pages

  • RE: Processing thousands of records
    ... Jerry Whittle, Microsoft Access MVP ... Access automatically creates an index for primary key fields. ... that the query is working faster, you don't need the 1stVisit02 query. ... where do I read about fundamental indexing and normalization? ...
    (microsoft.public.access.queries)
  • Re: Non-updateable query issue
    ... The issue arises when I create the query for the datasheet view to ... master table primary key. ... I am a big fan of normalization. ... Say Fred deletes record 79 from Table1, ...
    (microsoft.public.access.queries)
  • RE: Processing thousands of records
    ... Access automatically creates an index for primary key fields. ... that the query is working faster, you don't need the 1stVisit02 query. ... Jerry Whittle, Microsoft Access MVP ... where do I read about fundamental indexing and normalization? ...
    (microsoft.public.access.queries)
  • Re: year prefix on primary key
    ... computed field in your query. ... Doug Steele, Microsoft Access MVP ... primary key when a new record is added to a table. ...
    (microsoft.public.access.modulesdaovba)
  • RE: Mailmerge displays primary key rather than text
    ... that value in the query. ... Microsoft Access MVP ... MS Word mailmerge. ... in the mail merge but instead I get "168" which is the primary key for the ...
    (microsoft.public.access.queries)