RE: Processing thousands of records

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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: bypassing some of the parameters in a parameter query
    ... >> Well, WHERE does work in design view, but having a lot of ANDs and ORs ... what is simple and direct logic in SQL View ... >> query to design view and see if it makes sense to you. ... >> Tom Ellison ...
    (microsoft.public.access.queries)
  • Re: Future of ADPs
    ... To get there from the main window of SQL EM, click on Tools, then SQL Query ... Regardless of how I feel about it as a design tool, ...
    (microsoft.public.access.adp.sqlserver)
  • RE: Designing Query - Help Please ASAP
    ... The changes made to the SQL of the query result from switching into design ... SELECT [tblProject Staffing Resources].ProjectID, [tblProject Staffing ...
    (microsoft.public.access.queries)
  • Re: Expression Left in query giving syntax error
    ... trying to change focus from the field of the design grid. ... Next I tried coding the whole SQL expression including the Left function ... directly in the SQL window. ... The query is made of only one single table - so no ambiguities possible. ...
    (comp.databases.ms-access)
  • Re: Is this join valid?
    ... > complex set of tables and queries, but I've boiled down the behavior ... you describe a problem with the Design View that really should not ... affect what happens when running a query from ASP. ... > the SQL view after creating the query in Design View. ...
    (microsoft.public.inetserver.asp.db)