Re: How to benchmark Access queries?

From: E Harris (redunzl3_at_hotmail.com)
Date: 05/17/04 

Date: 17 May 2004 16:47:16 -0700

"Jeff Boyce" <JeffBoyce_IF@msn.com-DISCARD_HYPHEN_TO_END> wrote in message news:<O9HyZp0OEHA.3452@TK2MSFTNGP10.phx.gbl>...
> Some thoughts...
>
> Access is a file-server application ... can you migrate your back-end/data
> to a more robust engine (e.g. SQL Server)?
>
> Are the tables sufficiently indexed? I.e., have you indexed every field
> that's used for criteria and/or join?

Jeff,

Thanks for being the lone responder. I had thought thought surely
this would get more comments, being a forum specifically for Access
queries.

But I'll take the advice of making sure all the fields involved in
joins are indexed.

>
> Given the myriad of ways folks can design data structures (and queries), I'm
> not familiar with benchmarks, per se. I have found situations, however,
> when I can get faster results by "chaining" a series of queries, one after
> the other, rather than trying to do it all at once in a more complex query.

Actually, I have a chain of 3 queries right now that is not performing
as well as a chain of 2 and a table join (both obviously retrieving
the same data). In the chain of 3, the first query filters all data
according to one set of parameters. In the chain of 2, this filtering
is done by means of a table join in the final query.

> That works in Access, but may not in Cold Fusion, unless you have a way to
> build/reference intermediate recordsets.

You can, and that's what I was wondering-- is it better to offload
query-work to Access or let the ColdFusion scripts have their own SQL
to do it, hitting only Access tables (as opposed to Access queries)?

Thanks again, though, Jeff. It apparently is a bit of an artform and
I will just have to try some experimentation to see what exactly will
yield faster results.

Relevant Pages

  • Re: Index Seek (or) Index Scan in Execution Plan
    ... your queries do run better when you use them. ... Run your query with and without the hint, ... SQL Server MVP ... >>>> It is preferable to not use index hints, ...
    (microsoft.public.sqlserver.server)
  • Re: Index Seek (or) Index Scan in Execution Plan
    ... your queries do run better when you use them. ... Run your query with and without the hint, ... SQL Server MVP ... >>>> It is preferable to not use index hints, ...
    (microsoft.public.sqlserver.programming)
  • Re: Ongoing purging of active records causes deadlocks
    ... For a query like: ... plan for some of our queries and it looks like it sorts first, ... What is a possible alternative, though, is that the purge first performs: ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ...
    (comp.databases.ms-sqlserver)
  • Re: Looking for a professional SQL programmer for a small job
    ... Pro SQL Server 2000 Database Design - ... I have two queries to build and while I know my way around SQL, ... >> One is a seach query that pull rental properties from a database based on ...
    (microsoft.public.sqlserver.programming)
  • Re: Index Seek (or) Index Scan in Execution Plan
    ... The 2 queries in your list are not the same. ... This is called selectivity - If a very small percentage of rows in the table ... will be returned then the query is very selective. ... Wayne Snyder, MCDBA, SQL Server MVP ...
    (microsoft.public.sqlserver.server)