Re: Record source at runtime

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

Thanks again. I didn't realize some of what you said about indexes. I have
more than one query that is lacking the PK field. It probably matters
little with a small number of records, but best practice is best practice.
Also, I didn't know that about the index order. I had known that indexing
is important, but that is the best concise explanation I have heard about
why it is important. Thanks for taking the time to explain. Now there's
one more thing to think about. Funny about how that list keeps growing.

"Arvin Meyer [MVP]" <a@xxxxx> wrote in message
news:%23F08lePDIHA.5856@xxxxxxxxxxxxxxxxxxxxxxx
If you have defined a primary key, always include that in your query. to
speed things up other indices are often added like last name, first name,
etc. When using those indexes, always use them in the order they are
created (check the table index list) Like:

Select PersonID, LastName, FirstName From tblPeople Where LastName =
"Meyer" And FirstName = "Arvin";

Notice, I've used the PK and the index in the order I created them. That's
the way most (and for sure both Access and SQL-Server) use an index to
look for data.

In the case of Access, It see's that the PK isn't in the Where Clause, but
is in the data requested, so it will return the PKs from those rows that
use "Meyer", then it will see that it needs to go back and return the data
requested in the 3 fields from those rows.

SQL-Server does something similar, except since it's running on the
engine, it doesn't need to return the index first, it processes that on
the server and returns the data.

In both cases, had I written the query:

Select PersonID, FirstName, LastName From tblPeople Where FirstName =
"Arvin" And LastName = "Meyer";

It would still look for the index on LastName before it did on FirstName,
taken several milliseconds before it moved to the next index. In the above
case, it wouldn't be noticeable, but it sure would if you were looking for
John Smith in a couple of hundred thousand records.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"BruceM" <bamoob@xxxxxxxxxxxxxxxx> wrote in message
news:e5UnBEPDIHA.5208@xxxxxxxxxxxxxxxxxxxxxxx

"Arvin Meyer [MVP]" <a@xxxxx> wrote in message
news:u4jZERODIHA.5328@xxxxxxxxxxxxxxxxxxxxxxx
"BruceM" <bamoob@xxxxxxxxxxxxxxxx> wrote in message
news:uUri8eBDIHA.5712@xxxxxxxxxxxxxxxxxxxxxxx

Arvin, I see what you mean about something such as a tab control that
contains several subforms. That makes sense to me.
In keeping with the general practice of limiting the size of the
recordset, I suppose it would be best to limit the fields to just those
I need rather than using the whole table as the Record Source.
Probably not a big deal in this case, but if it is a best practice I
may as well do it that way.
Thanks again.

The way Jet works is to go to the server, find the table(s) and pull the
index. If no index is requested, it will pull the entire table of data.
Assuming good design, and therefore correct indexing, once the index is
satisfied, it returns to the server and gets just the row(s) of data
requested. Limiting the fields doesn't matter much unless they are OLE
fields (images, hyperlinks, memos) Those are stored on a different place
in the database and aren't called unless requested.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thanks for the follow-up, and for clarifying that limiting the fields
makes little difference in most cases, but I have to say I'm not sure
just what you mean by "pull the index" and "if no index is requested".





.

Relevant Pages

  • Re: Record source at runtime
    ... If you have defined a primary key, always include that in your query. ... The way Jet works is to go to the server, find the tableand pull the ... it will pull the entire table of data. ... Limiting the fields doesn't matter much unless they are OLE ...
    (microsoft.public.access.formscoding)
  • Re: MailMerge hangs and crashes with Access on Server
    ... Since I am fairly new to working in this environment, I am not sure what you mean by an "Access group" with help to restructuring the query? ... "Peter Jamieson" wrote: ... All I know is that the performance of queries depends to a large extent on whether, for example, a join is performed on the client side, or on the server side, incurring no network traffic and potentially benefitting from caching on the server. ... There are no dialog boxes, and actually, I have now split the database, and have kept the Word templates and the Access frontend locally on my machine, and moved the backend to the server. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Performance Benchmarks?
    ... Are attribute relationships defined properly on the dimensions? ... If you run Profiler against the server, how long is spent in "Query ... I have created aggregations (Partitions tab in cube design). ...
    (microsoft.public.sqlserver.olap)
  • High CPU in client (Excel, OWC, Proclarity, etc.) accessing Analysis Services
    ... the OWC10 with 3 dimensions on the row axis, ... The largest size of any of these 4 dimensions < 360 members. ... Performance Guide to optimize the query, the cube, the server, etc. ...
    (microsoft.public.sqlserver.olap)
  • Re: Performance Benchmarks?
    ... adding memory on the server can help you; more data can be cached on the ... Here is my test query. ... )} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ... I have created aggregations (Partitions tab in cube design). ...
    (microsoft.public.sqlserver.olap)