Re: SQL TOP 50,000 Help

On Fri, 9 Feb 2007 12:51:01 -0800, Klatuu
<Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

I like your hashing algorithm, it is going in my "bag of tricks", but unless
I am too thick to catch your point, I don't see how it applies to this
particular question.

A lot of the time, people use AutoNumber as their primary key, and the
Mod function works well enough with that. However, there are times
when it isn't easy to use it, e.g. when multiple columns are involved
in the primary key or unique index, when the PK is textual, or when
the column used to generate the hash number contains many equal
values. This is similar to the problems which arise with TOP (as you
pointed out).

To work around that problem, it sometimes helps to store a random
number in an extra column and run the hash (or Mod) function over that
to ensure equal distribution of values. The random number should be an
integer if you want use Mod because that function is only meaningful
for integers. The integer range should then be 0 to Count(<table
rows>)-1. Of course, there might be a few duplicate numbers generated,
but for this purpose only the statistical distribution is meaningful.

Also, if the table doesn't already contain a column of type
AutoNumber, one can be added. Access will automatically populate it
for existing records, and it is very quick. There is also a way to
generate a sequential number using Recordset.AbsolutePosition, but
that is usually way too slow.

In general, it is often better (i.e. more precise, but not necessarily
faster) to open a recordset in code and iterate over N rows than to
use TOP, perhaps creating a temporary table from the result set and
running additional queries on that. That way, the duplicate values
don't really matter.

--
Bob Hairgrove
NoSpamPlease@xxxxxxxx
.

Relevant Pages

  • Re: Allen Brownes function: "Duplicate record (and related child record)" -- my modified ver
    ... Duplicate the main form record and related records in the subform. ... Dim strSQL As String ... Dim lngOldID As Long '*** Primary key value of the orginial record. ... - "TrackingNumber" is primary key ...
    (microsoft.public.access.formscoding)
  • Re: Allen Brownes Copying forms/Subforms
    ... Is OrderID the primary key? ... Allen Browne - Microsoft MVP. ... perhaps the duplicate is on another field where you specified a "No ...
    (microsoft.public.access.modulesdaovba)
  • RE: Duplicate record with child fields
    ... with the sub table tblWesternBlotWorksheetsub ... record and trying to put it in the primary key of the new record. ... to copy duplicate records with child fields. ... GelNumber is the primary Key in the main table with a 1:N relationship ...
    (microsoft.public.access.forms)
  • Re: Duplicate the record in form and subform
    ... Have changed the code as below but got an error message "Error#3061, ... Dim strSql As String 'SQL statement. ... Dim lngID As String 'Primary key value of the new record. ... 'Make sure there is a record to duplicate. ...
    (microsoft.public.access.formscoding)
  • Re: storing byte values
    ... constraint which includes the column with the byte values? ... RAW column in a table (don't confuse RAW with LONG RAW, ... won't work unless you use large block size and a composite primary key ... and then hash the data using SHA-1 ...
    (comp.databases.oracle.misc)