Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

re: Northwind Mystery

Discussion in 'Information Technology' started by Nugimac, Jul 28, 2006.

  1. Nugimac

    Nugimac
    Expand Collapse
    Guest

    Thanks Albert for explaining the detail of how Access searches for matching
    data to send across the network. Could you explain the same sort of idea for
    the following:
    If I choose a product from a combo and ask Access to open the product form
    at that particular product – docmd.openform with the Where condition.
    I have read different posts on this and am confused about whether in this
    scenario Access will pull all product records across the network and just
    show the records for the product I selected, or will it search the table and
    just grab the records matching the product ID I chose?

    Nugimac
     
  2. Loading...

    Similar Threads Forum Date
    USA How Police Identified Severed Head After 24-Year Mystery Breaking News Mar 29, 2013
    Mystery Of Meaning Of Death In Sikhism Questions and Answers Dec 15, 2012
    Pace Bowling: Indian Mystery, Indian Obsession Sports & Fitness Aug 29, 2011
    Judaism What is the Greatest Mystery? Interfaith Dialogues Jul 18, 2011
    Ganga Sagar: History, Mystery, Legend, Devotion History of Sikhism Oct 3, 2010

  3. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    Dear Nugimac:

    If I may, I'll offer something on this.

    Access MUST bring the essential parts of the DB across the network to do
    this. As Albert was saying, it is very important to have an index on the
    subject column(s). This way, Access can read the INDEX to find which rows
    meet the filter requirements. If indexed on this column, the answer given
    in that index will be a compact list of only those rows that meed the filter
    requirement. This is much less disk reading, and much less network
    bandwidth, than reading through the entire table to find which rows meet the
    filter requirements.

    This is commonly a dramatic difference in performance. In a server
    database, as we also discussed, this does not have to go over the network,
    so if the server is NOT HD bound, then there's not much of a penalty for an
    inefficient search. If, however, the server is HD bound (meaning the
    performance of the server is limited by its need to make hard drive
    accesses) then all users waiting for server response will suffer.

    Tom Ellison


    "Nugimac" <Nugimac@discussions.microsoft.com> wrote in message
    news:8A26FF1F-9AB0-49E9-BEE7-7AD109527DE3@microsoft.com...
    > Thanks Albert for explaining the detail of how Access searches for
    > matching
    > data to send across the network. Could you explain the same sort of idea
    > for
    > the following:
    > If I choose a product from a combo and ask Access to open the product form
    > at that particular product - docmd.openform with the Where condition.
    > I have read different posts on this and am confused about whether in this
    > scenario Access will pull all product records across the network and just
    > show the records for the product I selected, or will it search the table
    > and
    > just grab the records matching the product ID I chose?
    >
    > Nugimac
     
  4. Nugimac

    Nugimac
    Expand Collapse
    Guest

    Hi Tom, thanks for your comments. The issue of indexing is very important in
    Access. I am keen to have someone verify that Access can use the indexed
    ProductID field to grab only those records which match the chosen ProductID
    and pull only those records across the network in a situation with a
    docmd.openform with the where condition. I have read some posts which suggest
    that this may not be the case when using docmd.openform with where condition,
    but its not clear if they had some special situation which I didn't fully
    understand.

    "Tom Ellison" wrote:

    > Dear Nugimac:
    >
    > If I may, I'll offer something on this.
    >
    > Access MUST bring the essential parts of the DB across the network to do
    > this. As Albert was saying, it is very important to have an index on the
    > subject column(s). This way, Access can read the INDEX to find which rows
    > meet the filter requirements. If indexed on this column, the answer given
    > in that index will be a compact list of only those rows that meed the filter
    > requirement. This is much less disk reading, and much less network
    > bandwidth, than reading through the entire table to find which rows meet the
    > filter requirements.
    >
    > This is commonly a dramatic difference in performance. In a server
    > database, as we also discussed, this does not have to go over the network,
    > so if the server is NOT HD bound, then there's not much of a penalty for an
    > inefficient search. If, however, the server is HD bound (meaning the
    > performance of the server is limited by its need to make hard drive
    > accesses) then all users waiting for server response will suffer.
    >
    > Tom Ellison
    >
    >
    > "Nugimac" <Nugimac@discussions.microsoft.com> wrote in message
    > news:8A26FF1F-9AB0-49E9-BEE7-7AD109527DE3@microsoft.com...
    > > Thanks Albert for explaining the detail of how Access searches for
    > > matching
    > > data to send across the network. Could you explain the same sort of idea
    > > for
    > > the following:
    > > If I choose a product from a combo and ask Access to open the product form
    > > at that particular product - docmd.openform with the Where condition.
    > > I have read different posts on this and am confused about whether in this
    > > scenario Access will pull all product records across the network and just
    > > show the records for the product I selected, or will it search the table
    > > and
    > > just grab the records matching the product ID I chose?
    > >
    > > Nugimac

    >
    >
    >
     
  5. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    Dear Nugimac:

    Issues like this are very much on my mind today.

    Now, your question states that you would, "match the chosen ProductID".
    Normally, the ProductID would be a value that could just as well be hidden
    from users. Do you have users entering or selecting a Product ID? Or
    perhaps this is a hidden column in a combo box. You may want to consider
    for a moment that you could easily filter on ProductName just as well.

    Both these columns, ProductID and ProductName, must be unique and should be
    indexed. In Northwind Traders, they are.

    In opening your form with a filter, either should work. I would point out
    that either would work even if there were no index. The indexing makes it
    faster. For the amount of data in the Northwind Traders sample database,
    you may not be able to see the difference. Mine has 77 rows of data in
    Products. That's not going to take any time for a table scan. Probably not
    more than 2 or 3 segments of data anyway, and they're probably all going to
    be read if the search goes through an index. Indeed, the table scan could
    be just a bit faster than the index search (oh, horror, I've committed a sin
    here!) As the index search is going to read the index and then read the
    whole table anyway, in most cases. The CPU time for the search is
    insignificant. What counts is total disk accesses. Unindexed, it just
    could be faster.

    But, this isn't the kind of thing you want to be thinking about when you
    design it. What really counts is what happens when the occurrance of the
    searched value in the target column is fairly "rare". Say the query will
    return only 0.1% of all the rows in the table. Now you have a situation
    where the index will save you reading more than 90% of the segments of the
    data table, for a cost of reading only a few index segments.

    I do not know what the concern is about DoCmd.OpenForm filtered as you
    require, but I'm fairly confident it will use a query to do the job, and
    that it can use the index if it exists. You could put this in a loop,
    opening and closing the form a hundred times, and have it log the date/time
    it starts and ends in the immediate pane (use DoCmd.Print). For ten minutes
    work, you'd know for sure whether it helps and just how much.

    Tom Ellison


    "Nugimac" <Nugimac@discussions.microsoft.com> wrote in message
    news:E5DF2856-F229-46C0-92D7-3FA7FCD0CC02@microsoft.com...
    > Hi Tom, thanks for your comments. The issue of indexing is very important
    > in
    > Access. I am keen to have someone verify that Access can use the indexed
    > ProductID field to grab only those records which match the chosen
    > ProductID
    > and pull only those records across the network in a situation with a
    > docmd.openform with the where condition. I have read some posts which
    > suggest
    > that this may not be the case when using docmd.openform with where
    > condition,
    > but its not clear if they had some special situation which I didn't fully
    > understand.
    >
    > "Tom Ellison" wrote:
    >
    >> Dear Nugimac:
    >>
    >> If I may, I'll offer something on this.
    >>
    >> Access MUST bring the essential parts of the DB across the network to do
    >> this. As Albert was saying, it is very important to have an index on the
    >> subject column(s). This way, Access can read the INDEX to find which
    >> rows
    >> meet the filter requirements. If indexed on this column, the answer
    >> given
    >> in that index will be a compact list of only those rows that meed the
    >> filter
    >> requirement. This is much less disk reading, and much less network
    >> bandwidth, than reading through the entire table to find which rows meet
    >> the
    >> filter requirements.
    >>
    >> This is commonly a dramatic difference in performance. In a server
    >> database, as we also discussed, this does not have to go over the
    >> network,
    >> so if the server is NOT HD bound, then there's not much of a penalty for
    >> an
    >> inefficient search. If, however, the server is HD bound (meaning the
    >> performance of the server is limited by its need to make hard drive
    >> accesses) then all users waiting for server response will suffer.
    >>
    >> Tom Ellison
    >>
    >>
    >> "Nugimac" <Nugimac@discussions.microsoft.com> wrote in message
    >> news:8A26FF1F-9AB0-49E9-BEE7-7AD109527DE3@microsoft.com...
    >> > Thanks Albert for explaining the detail of how Access searches for
    >> > matching
    >> > data to send across the network. Could you explain the same sort of
    >> > idea
    >> > for
    >> > the following:
    >> > If I choose a product from a combo and ask Access to open the product
    >> > form
    >> > at that particular product - docmd.openform with the Where condition.
    >> > I have read different posts on this and am confused about whether in
    >> > this
    >> > scenario Access will pull all product records across the network and
    >> > just
    >> > show the records for the product I selected, or will it search the
    >> > table
    >> > and
    >> > just grab the records matching the product ID I chose?
    >> >
    >> > Nugimac

    >>
    >>
    >>
     
  6. Nugimac

    Nugimac
    Expand Collapse
    Guest

    Thanks Tom, this is helpful. The idea of how it could be tested is a handy
    thing, I hadn't thought about it in this way before. Thanks.
    Nugimac


    "Tom Ellison" wrote:

    > Dear Nugimac:
    >
    > Issues like this are very much on my mind today.
    >
    > Now, your question states that you would, "match the chosen ProductID".
    > Normally, the ProductID would be a value that could just as well be hidden
    > from users. Do you have users entering or selecting a Product ID? Or
    > perhaps this is a hidden column in a combo box. You may want to consider
    > for a moment that you could easily filter on ProductName just as well.
    >
    > Both these columns, ProductID and ProductName, must be unique and should be
    > indexed. In Northwind Traders, they are.
    >
    > In opening your form with a filter, either should work. I would point out
    > that either would work even if there were no index. The indexing makes it
    > faster. For the amount of data in the Northwind Traders sample database,
    > you may not be able to see the difference. Mine has 77 rows of data in
    > Products. That's not going to take any time for a table scan. Probably not
    > more than 2 or 3 segments of data anyway, and they're probably all going to
    > be read if the search goes through an index. Indeed, the table scan could
    > be just a bit faster than the index search (oh, horror, I've committed a sin
    > here!) As the index search is going to read the index and then read the
    > whole table anyway, in most cases. The CPU time for the search is
    > insignificant. What counts is total disk accesses. Unindexed, it just
    > could be faster.
    >
    > But, this isn't the kind of thing you want to be thinking about when you
    > design it. What really counts is what happens when the occurrance of the
    > searched value in the target column is fairly "rare". Say the query will
    > return only 0.1% of all the rows in the table. Now you have a situation
    > where the index will save you reading more than 90% of the segments of the
    > data table, for a cost of reading only a few index segments.
    >
    > I do not know what the concern is about DoCmd.OpenForm filtered as you
    > require, but I'm fairly confident it will use a query to do the job, and
    > that it can use the index if it exists. You could put this in a loop,
    > opening and closing the form a hundred times, and have it log the date/time
    > it starts and ends in the immediate pane (use DoCmd.Print). For ten minutes
    > work, you'd know for sure whether it helps and just how much.
    >
    > Tom Ellison
    >
    >
    > "Nugimac" <Nugimac@discussions.microsoft.com> wrote in message
    > news:E5DF2856-F229-46C0-92D7-3FA7FCD0CC02@microsoft.com...
    > > Hi Tom, thanks for your comments. The issue of indexing is very important
    > > in
    > > Access. I am keen to have someone verify that Access can use the indexed
    > > ProductID field to grab only those records which match the chosen
    > > ProductID
    > > and pull only those records across the network in a situation with a
    > > docmd.openform with the where condition. I have read some posts which
    > > suggest
    > > that this may not be the case when using docmd.openform with where
    > > condition,
    > > but its not clear if they had some special situation which I didn't fully
    > > understand.
    > >
    > > "Tom Ellison" wrote:
    > >
    > >> Dear Nugimac:
    > >>
    > >> If I may, I'll offer something on this.
    > >>
    > >> Access MUST bring the essential parts of the DB across the network to do
    > >> this. As Albert was saying, it is very important to have an index on the
    > >> subject column(s). This way, Access can read the INDEX to find which
    > >> rows
    > >> meet the filter requirements. If indexed on this column, the answer
    > >> given
    > >> in that index will be a compact list of only those rows that meed the
    > >> filter
    > >> requirement. This is much less disk reading, and much less network
    > >> bandwidth, than reading through the entire table to find which rows meet
    > >> the
    > >> filter requirements.
    > >>
    > >> This is commonly a dramatic difference in performance. In a server
    > >> database, as we also discussed, this does not have to go over the
    > >> network,
    > >> so if the server is NOT HD bound, then there's not much of a penalty for
    > >> an
    > >> inefficient search. If, however, the server is HD bound (meaning the
    > >> performance of the server is limited by its need to make hard drive
    > >> accesses) then all users waiting for server response will suffer.
    > >>
    > >> Tom Ellison
    > >>
    > >>
    > >> "Nugimac" <Nugimac@discussions.microsoft.com> wrote in message
    > >> news:8A26FF1F-9AB0-49E9-BEE7-7AD109527DE3@microsoft.com...
    > >> > Thanks Albert for explaining the detail of how Access searches for
    > >> > matching
    > >> > data to send across the network. Could you explain the same sort of
    > >> > idea
    > >> > for
    > >> > the following:
    > >> > If I choose a product from a combo and ask Access to open the product
    > >> > form
    > >> > at that particular product - docmd.openform with the Where condition.
    > >> > I have read different posts on this and am confused about whether in
    > >> > this
    > >> > scenario Access will pull all product records across the network and
    > >> > just
    > >> > show the records for the product I selected, or will it search the
    > >> > table
    > >> > and
    > >> > just grab the records matching the product ID I chose?
    > >> >
    > >> > Nugimac
    > >>
    > >>
    > >>

    >
    >
    >
     
  7. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    > Hi Tom, thanks for your comments. The issue of indexing is very important
    > in
    > Access. I am keen to have someone verify that Access can use the indexed
    > ProductID field to grab only those records which match the chosen
    > ProductID
    > and pull only those records across the network in a situation with a
    > docmd.openform with the where condition.


    Yes, if the column has a index, and you filter to 1 record, or however many
    productid matches..that is what the transfer involves.

    A full table scan does not occur, and the whole table is NOT transferred
    here..

    As Tom mentioned, part of the index will travel down the wire. Those indexes
    are themselves balanced b-trees, and a traversed *very* efficient. Typically
    about 5 leaves in the b-tree will get a match for 1 records out of a
    million. And, often, several of those leaves will be in one disk frame. So,
    at most a few disk frames will be needed to pluck a unique id out of 1
    million records...

    So, if you have a unique customer number, then use the "where" clause

    docmd.opeform "frmCiustomers",,,"CustomerID = 43252"

    The above will only transfer the one customer record...(with a bit of
    overhead..but not much).

    So,...no full table transfer occurs...even when the form is bound to the
    table. Just use a where clause, and that form should load the record instant
    if the table has 1 reocrd...or 500,000 reocrds.....

    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     
  8. Nugimac

    Nugimac
    Expand Collapse
    Guest

    Thanks Albert for explaining this is a way that I can understand. It is great
    to have this confirmed. By the way, my son is studying software engineering
    at uni and he has shown me diagrams of leaves and trees so your comments are
    perfectly appropriate.

    "Albert D.Kallal" wrote:

    > > Hi Tom, thanks for your comments. The issue of indexing is very important
    > > in
    > > Access. I am keen to have someone verify that Access can use the indexed
    > > ProductID field to grab only those records which match the chosen
    > > ProductID
    > > and pull only those records across the network in a situation with a
    > > docmd.openform with the where condition.

    >
    > Yes, if the column has a index, and you filter to 1 record, or however many
    > productid matches..that is what the transfer involves.
    >
    > A full table scan does not occur, and the whole table is NOT transferred
    > here..
    >
    > As Tom mentioned, part of the index will travel down the wire. Those indexes
    > are themselves balanced b-trees, and a traversed *very* efficient. Typically
    > about 5 leaves in the b-tree will get a match for 1 records out of a
    > million. And, often, several of those leaves will be in one disk frame. So,
    > at most a few disk frames will be needed to pluck a unique id out of 1
    > million records...
    >
    > So, if you have a unique customer number, then use the "where" clause
    >
    > docmd.opeform "frmCiustomers",,,"CustomerID = 43252"
    >
    > The above will only transfer the one customer record...(with a bit of
    > overhead..but not much).
    >
    > So,...no full table transfer occurs...even when the form is bound to the
    > table. Just use a where clause, and that form should load the record instant
    > if the table has 1 reocrd...or 500,000 reocrds.....
    >
    > --
    > Albert D. Kallal (Access MVP)
    > Edmonton, Alberta Canada
    > pleaseNOOSpamKallal@msn.com
    > http://www.members.shaw.ca/AlbertKallal
    >
    >
    >
     
  9. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    Dear Albert:

    I see you've really caught on.

    About the B-Tree: I believe Access uses a 4K segment for each node. Given,
    say, a 20 byte natural key value and 8 bytes for a pointer, 4096 / 28 = 146
    entries per node. Using a 70% fill, about 100 rows. 100 rows times 5
    levels of tree is 100 million rows. For a million rows, I'd expect 4
    levels. That's just 4 disk accesses to traverse the index, plus one for the
    table.

    Compare this with an autonumber key of 8 bytes, plus the same 8 bytes
    overhead (pointer to the next node or the data). 4096 / 16 = 256 entries
    per node. 180 entries at 70% fill. Probably 3 levels of B-Tree for a
    million rows. The performance boost is modest. Plus, if you don't put the
    natural key value from the FK table in the dependent table, then you lose
    part of the benefits to performance having the data clustered/compacted in
    natural key order.

    Add to this the fact that the surrogate key / autonumber increases the size
    of the table and requires an additional index. What do you have? Very
    close to flat performance difference for SELECT queries. Natural keys
    faster for inserts, deletes, and close to a draw for updates.

    This is not what the surrogate key advocates will consistently say. They
    have convinced themselves that a 20 byte key is 2.5 times as long as an 8
    byte key, so it must be 2.5 times as fast. Hardly ever true.

    Well, now I see I'm putting words in the mouths of the opposition. Well,
    I've heard this from many of them, repeatedly. It's not true, but they've
    convinced themselves.

    Expect a "lookup" performance enhancement from autonumber keys during SELECT
    queries that JOIN to the FK table of about 15% as an average. However, the
    JOIN must be made even when the only column needed is part of the natural
    key. If the tables were related on the natural key instead, that value
    would be there already, and no lookup JOIN needed. This happens frequently
    enough.

    But then, the database must cascade update to allow the inevitable, if rare,
    changes to a natural key value in the FK table. In my designs I have
    measured this. There is one such natural key field updated every 3-6
    working days. It takes about 2 seconds to perform, rarely up to 6 or so.
    Nothing to speak of. This is one of the BIG objections to natural key
    relationships, and it's almost nothing to speak of.

    The real problem: Microsoft hasn't implemented cascading updates so they
    really work in complex situations you're bound to encounter. The
    work-around is a bit time consuming (and I've only ever done it for MSDE).
    I've had to do it a few dozen times. More about this later, if anyone's
    interested.

    Tom Ellison


    "Albert D.Kallal" <PleaseNOOOsPAMmkallal@msn.com> wrote in message
    news:uEJz$5hdGHA.1656@TK2MSFTNGP02.phx.gbl...
    >> Hi Tom, thanks for your comments. The issue of indexing is very important
    >> in
    >> Access. I am keen to have someone verify that Access can use the indexed
    >> ProductID field to grab only those records which match the chosen
    >> ProductID
    >> and pull only those records across the network in a situation with a
    >> docmd.openform with the where condition.

    >
    > Yes, if the column has a index, and you filter to 1 record, or however
    > many productid matches..that is what the transfer involves.
    >
    > A full table scan does not occur, and the whole table is NOT transferred
    > here..
    >
    > As Tom mentioned, part of the index will travel down the wire. Those
    > indexes are themselves balanced b-trees, and a traversed *very* efficient.
    > Typically about 5 leaves in the b-tree will get a match for 1 records out
    > of a million. And, often, several of those leaves will be in one disk
    > frame. So, at most a few disk frames will be needed to pluck a unique id
    > out of 1 million records...
    >
    > So, if you have a unique customer number, then use the "where" clause
    >
    > docmd.opeform "frmCiustomers",,,"CustomerID = 43252"
    >
    > The above will only transfer the one customer record...(with a bit of
    > overhead..but not much).
    >
    > So,...no full table transfer occurs...even when the form is bound to the
    > table. Just use a where clause, and that form should load the record
    > instant if the table has 1 reocrd...or 500,000 reocrds.....
    >
    > --
    > Albert D. Kallal (Access MVP)
    > Edmonton, Alberta Canada
    > pleaseNOOSpamKallal@msn.com
    > http://www.members.shaw.ca/AlbertKallal
    >
    >
     
  10. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    Dear Albert:

    I haven't even mentioned other topics where autonumber relationships cause
    great difficulty:

    - merge replication (using random autonumbers helps prevent conflicts, but
    destroys your idea of having any advantage to an autonumber PK)

    Not that there isn't conflict resolution using natural keys, also. But if
    two separate databases have an account for John Jackson, there's at least a
    good possibility this is the same guy in both. If both have customerID 113,
    there's almost no chance they are the same person. And, you have a lot of
    fix up to do first.

    I'll add more as I have time.

    Tom Ellison


    "Albert D.Kallal" <PleaseNOOOsPAMmkallal@msn.com> wrote in message
    news:uEJz$5hdGHA.1656@TK2MSFTNGP02.phx.gbl...
    >> Hi Tom, thanks for your comments. The issue of indexing is very important
    >> in
    >> Access. I am keen to have someone verify that Access can use the indexed
    >> ProductID field to grab only those records which match the chosen
    >> ProductID
    >> and pull only those records across the network in a situation with a
    >> docmd.openform with the where condition.

    >
    > Yes, if the column has a index, and you filter to 1 record, or however
    > many productid matches..that is what the transfer involves.
    >
    > A full table scan does not occur, and the whole table is NOT transferred
    > here..
    >
    > As Tom mentioned, part of the index will travel down the wire. Those
    > indexes are themselves balanced b-trees, and a traversed *very* efficient.
    > Typically about 5 leaves in the b-tree will get a match for 1 records out
    > of a million. And, often, several of those leaves will be in one disk
    > frame. So, at most a few disk frames will be needed to pluck a unique id
    > out of 1 million records...
    >
    > So, if you have a unique customer number, then use the "where" clause
    >
    > docmd.opeform "frmCiustomers",,,"CustomerID = 43252"
    >
    > The above will only transfer the one customer record...(with a bit of
    > overhead..but not much).
    >
    > So,...no full table transfer occurs...even when the form is bound to the
    > table. Just use a where clause, and that form should load the record
    > instant if the table has 1 reocrd...or 500,000 reocrds.....
    >
    > --
    > Albert D. Kallal (Access MVP)
    > Edmonton, Alberta Canada
    > pleaseNOOSpamKallal@msn.com
    > http://www.members.shaw.ca/AlbertKallal
    >
    >
     
  11. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Tom,

    > Compare this with an autonumber key of 8 bytes, plus the same 8 bytes
    > overhead (pointer to the next node or the data).


    When you showed your example for the autonumber, were you counting the
    foreign key + the autonumber primary key when you stated 8 bytes? The reason
    I ask is that an autonumber is a long integer, which take 4 bytes, not 8
    bytes.



    Tom Wickerath, Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________


    "Tom Ellison" wrote:

    > Dear Albert:
    >
    > I see you've really caught on.
    >
    > About the B-Tree: I believe Access uses a 4K segment for each node. Given,
    > say, a 20 byte natural key value and 8 bytes for a pointer, 4096 / 28 = 146
    > entries per node. Using a 70% fill, about 100 rows. 100 rows times 5
    > levels of tree is 100 million rows. For a million rows, I'd expect 4
    > levels. That's just 4 disk accesses to traverse the index, plus one for the
    > table.
    >
    > Compare this with an autonumber key of 8 bytes, plus the same 8 bytes
    > overhead (pointer to the next node or the data). 4096 / 16 = 256 entries
    > per node. 180 entries at 70% fill. Probably 3 levels of B-Tree for a
    > million rows. The performance boost is modest. Plus, if you don't put the
    > natural key value from the FK table in the dependent table, then you lose
    > part of the benefits to performance having the data clustered/compacted in
    > natural key order.
    >
    > Add to this the fact that the surrogate key / autonumber increases the size
    > of the table and requires an additional index. What do you have? Very
    > close to flat performance difference for SELECT queries. Natural keys
    > faster for inserts, deletes, and close to a draw for updates.
    >
    > This is not what the surrogate key advocates will consistently say. They
    > have convinced themselves that a 20 byte key is 2.5 times as long as an 8
    > byte key, so it must be 2.5 times as fast. Hardly ever true.
    >
    > Well, now I see I'm putting words in the mouths of the opposition. Well,
    > I've heard this from many of them, repeatedly. It's not true, but they've
    > convinced themselves.
    >
    > Expect a "lookup" performance enhancement from autonumber keys during SELECT
    > queries that JOIN to the FK table of about 15% as an average. However, the
    > JOIN must be made even when the only column needed is part of the natural
    > key. If the tables were related on the natural key instead, that value
    > would be there already, and no lookup JOIN needed. This happens frequently
    > enough.
    >
    > But then, the database must cascade update to allow the inevitable, if rare,
    > changes to a natural key value in the FK table. In my designs I have
    > measured this. There is one such natural key field updated every 3-6
    > working days. It takes about 2 seconds to perform, rarely up to 6 or so.
    > Nothing to speak of. This is one of the BIG objections to natural key
    > relationships, and it's almost nothing to speak of.
    >
    > The real problem: Microsoft hasn't implemented cascading updates so they
    > really work in complex situations you're bound to encounter. The
    > work-around is a bit time consuming (and I've only ever done it for MSDE).
    > I've had to do it a few dozen times. More about this later, if anyone's
    > interested.
    >
    > Tom Ellison
    >
    >
    > "Albert D.Kallal" <PleaseNOOOsPAMmkallal@msn.com> wrote in message
    > news:uEJz$5hdGHA.1656@TK2MSFTNGP02.phx.gbl...
    > >> Hi Tom, thanks for your comments. The issue of indexing is very important
    > >> in
    > >> Access. I am keen to have someone verify that Access can use the indexed
    > >> ProductID field to grab only those records which match the chosen
    > >> ProductID
    > >> and pull only those records across the network in a situation with a
    > >> docmd.openform with the where condition.

    > >
    > > Yes, if the column has a index, and you filter to 1 record, or however
    > > many productid matches..that is what the transfer involves.
    > >
    > > A full table scan does not occur, and the whole table is NOT transferred
    > > here..
    > >
    > > As Tom mentioned, part of the index will travel down the wire. Those
    > > indexes are themselves balanced b-trees, and a traversed *very* efficient.
    > > Typically about 5 leaves in the b-tree will get a match for 1 records out
    > > of a million. And, often, several of those leaves will be in one disk
    > > frame. So, at most a few disk frames will be needed to pluck a unique id
    > > out of 1 million records...
    > >
    > > So, if you have a unique customer number, then use the "where" clause
    > >
    > > docmd.opeform "frmCiustomers",,,"CustomerID = 43252"
    > >
    > > The above will only transfer the one customer record...(with a bit of
    > > overhead..but not much).
    > >
    > > So,...no full table transfer occurs...even when the form is bound to the
    > > table. Just use a where clause, and that form should load the record
    > > instant if the table has 1 reocrd...or 500,000 reocrds.....
    > >
    > > --
    > > Albert D. Kallal (Access MVP)
    > > Edmonton, Alberta Canada
    > > pleaseNOOSpamKallal@msn.com
    > > http://www.members.shaw.ca/AlbertKallal
    > >
    > >

    >
    >
    >
     
  12. Tom Ellison

    Tom Ellison
    Expand Collapse
    Guest

    Dear Tom:

    You're right. So, 4096 / 12 = 341 entries per node, times 70% fill is 238.
    The index for a million rows just might fit in 3 levels, barely. Still, the
    natural key index, which has more levels at a 5:3 ratio, is not that much
    slower, and there are some performance advantages to it. I'm going to cover
    that when I publish.

    Thanks!

    Tom Ellison


    "Tom Wickerath" <AOS168b AT comcast DOT net> wrote in message
    news:0E1394E3-C94A-4280-8706-188E52C209A5@microsoft.com...
    > Tom,
    >
    >> Compare this with an autonumber key of 8 bytes, plus the same 8 bytes
    >> overhead (pointer to the next node or the data).

    >
    > When you showed your example for the autonumber, were you counting the
    > foreign key + the autonumber primary key when you stated 8 bytes? The
    > reason
    > I ask is that an autonumber is a long integer, which take 4 bytes, not 8
    > bytes.
    >
    >
    >
    > Tom Wickerath, Microsoft Access MVP
    >
    > http://www.access.qbuilt.com/html/expert_contributors.html
    > http://www.access.qbuilt.com/html/search.html
    > __________________________________________
    >
    >
    > "Tom Ellison" wrote:
    >
    >> Dear Albert:
    >>
    >> I see you've really caught on.
    >>
    >> About the B-Tree: I believe Access uses a 4K segment for each node.
    >> Given,
    >> say, a 20 byte natural key value and 8 bytes for a pointer, 4096 / 28 =
    >> 146
    >> entries per node. Using a 70% fill, about 100 rows. 100 rows times 5
    >> levels of tree is 100 million rows. For a million rows, I'd expect 4
    >> levels. That's just 4 disk accesses to traverse the index, plus one for
    >> the
    >> table.
    >>
    >> Compare this with an autonumber key of 8 bytes, plus the same 8 bytes
    >> overhead (pointer to the next node or the data). 4096 / 16 = 256 entries
    >> per node. 180 entries at 70% fill. Probably 3 levels of B-Tree for a
    >> million rows. The performance boost is modest. Plus, if you don't put
    >> the
    >> natural key value from the FK table in the dependent table, then you lose
    >> part of the benefits to performance having the data clustered/compacted
    >> in
    >> natural key order.
    >>
    >> Add to this the fact that the surrogate key / autonumber increases the
    >> size
    >> of the table and requires an additional index. What do you have? Very
    >> close to flat performance difference for SELECT queries. Natural keys
    >> faster for inserts, deletes, and close to a draw for updates.
    >>
    >> This is not what the surrogate key advocates will consistently say. They
    >> have convinced themselves that a 20 byte key is 2.5 times as long as an 8
    >> byte key, so it must be 2.5 times as fast. Hardly ever true.
    >>
    >> Well, now I see I'm putting words in the mouths of the opposition. Well,
    >> I've heard this from many of them, repeatedly. It's not true, but
    >> they've
    >> convinced themselves.
    >>
    >> Expect a "lookup" performance enhancement from autonumber keys during
    >> SELECT
    >> queries that JOIN to the FK table of about 15% as an average. However,
    >> the
    >> JOIN must be made even when the only column needed is part of the natural
    >> key. If the tables were related on the natural key instead, that value
    >> would be there already, and no lookup JOIN needed. This happens
    >> frequently
    >> enough.
    >>
    >> But then, the database must cascade update to allow the inevitable, if
    >> rare,
    >> changes to a natural key value in the FK table. In my designs I have
    >> measured this. There is one such natural key field updated every 3-6
    >> working days. It takes about 2 seconds to perform, rarely up to 6 or so.
    >> Nothing to speak of. This is one of the BIG objections to natural key
    >> relationships, and it's almost nothing to speak of.
    >>
    >> The real problem: Microsoft hasn't implemented cascading updates so they
    >> really work in complex situations you're bound to encounter. The
    >> work-around is a bit time consuming (and I've only ever done it for
    >> MSDE).
    >> I've had to do it a few dozen times. More about this later, if anyone's
    >> interested.
    >>
    >> Tom Ellison
    >>
    >>
    >> "Albert D.Kallal" <PleaseNOOOsPAMmkallal@msn.com> wrote in message
    >> news:uEJz$5hdGHA.1656@TK2MSFTNGP02.phx.gbl...
    >> >> Hi Tom, thanks for your comments. The issue of indexing is very
    >> >> important
    >> >> in
    >> >> Access. I am keen to have someone verify that Access can use the
    >> >> indexed
    >> >> ProductID field to grab only those records which match the chosen
    >> >> ProductID
    >> >> and pull only those records across the network in a situation with a
    >> >> docmd.openform with the where condition.
    >> >
    >> > Yes, if the column has a index, and you filter to 1 record, or however
    >> > many productid matches..that is what the transfer involves.
    >> >
    >> > A full table scan does not occur, and the whole table is NOT
    >> > transferred
    >> > here..
    >> >
    >> > As Tom mentioned, part of the index will travel down the wire. Those
    >> > indexes are themselves balanced b-trees, and a traversed *very*
    >> > efficient.
    >> > Typically about 5 leaves in the b-tree will get a match for 1 records
    >> > out
    >> > of a million. And, often, several of those leaves will be in one disk
    >> > frame. So, at most a few disk frames will be needed to pluck a unique
    >> > id
    >> > out of 1 million records...
    >> >
    >> > So, if you have a unique customer number, then use the "where" clause
    >> >
    >> > docmd.opeform "frmCiustomers",,,"CustomerID = 43252"
    >> >
    >> > The above will only transfer the one customer record...(with a bit of
    >> > overhead..but not much).
    >> >
    >> > So,...no full table transfer occurs...even when the form is bound to
    >> > the
    >> > table. Just use a where clause, and that form should load the record
    >> > instant if the table has 1 reocrd...or 500,000 reocrds.....
    >> >
    >> > --
    >> > Albert D. Kallal (Access MVP)
    >> > Edmonton, Alberta Canada
    >> > pleaseNOOSpamKallal@msn.com
    >> > http://www.members.shaw.ca/AlbertKallal
    >> >
    >> >

    >>
    >>
    >>
     

Share This Page