RE: Processing thousands of records

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

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: Please help me evolve this concept
    ... complex SQL SELECT statements including UNION, OUTER JOINS, GROUP BY, ... It sure will save you a lot of time and coding if all you need is to query ... I could modify this in code and qActorSelect stays 'dynamically ... creating a union query based on qActorSelect as opposed to ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: adding 2 fields including null entries
    ... First I'll go through the method that will not use the crosstabs. ... we'll define the select query for the PSS Names: ... Note that you could paste the sql from either of the queries above in the ... Next, we want to combine these results, so we use a union statement. ...
    (microsoft.public.access.queries)
  • RE: number of columns doesnt match bug in Access
    ... The naming of the fields using reserved words could be an source of problems ... Also you use as an alias in the SQL. ... as they must in a Union Join. ... I have a union query which regularily generates the "number of columns ...
    (microsoft.public.access.queries)
  • RE: number of columns doesnt match bug in Access
    ... Also you use as an alias in the SQL. ... as they must in a Union Join. ... I have a union query which regularily generates the "number of columns ... I use the VBA code On Error resume to get it to work - ...
    (microsoft.public.access.queries)
  • RE: report / Query
    ... CreditAgency field to store the CreditAgency ID number from a CreditAgency ... Then I would create a union query, which is simply two independent queries ... created both queries, switch to SQL View in one of them and ...
    (microsoft.public.access.gettingstarted)