Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2003
    Posts
    107

    Unanswered: Improving speed for sorting

    someone in our office has created an application (asp.net with ms access) that provides reports.
    each report provides a list of duplicate records in the database. The field that's being checking for duplicates on is not a pk (obviously..) or indexed.
    The developer is doing a record by record compare,.
    The report takes forever to load.
    Suggestions were:
    1. store the results as an application level var. so that the first person to run the report takes the hit.
    2. if you store all records in a disconnected recordset and then use the recordset to check for duplicates...

    Any suggestions would be appreciated.
    Last edited by dotolee; 03-22-06 at 19:24.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    do you know what is duplicated - is it exactly the same row but with a differne tprimary key (ie the coluns are identical) or are they similar

    if they are identical then run a query on the common columns doing a count may be quuicker at indentifying the erroneous records

    eg select count(mycolumn) as No Rows, mycolumn from mytable order by mycolumn
    you nmay want to alter the query to filter out those with only one row (you may need to use a sub query).

    having got a query which identifies the problem roiws, you could conceivably delete the erronoeous rows
    first off identify your reqcord to keep, update that primary key into any child rows, then delete any rows with the identical list but not required primary key

    if they are similar then you have a problem, that will probably only be capable of being done manually.

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2003
    Posts
    107

    not identical rows.

    only a few columns in the rows are duplicated.
    eg) serial number, or purchase date.
    If doing it manually is the only way, do you have any comments on the type of recordset to use .... ??

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if the serial numbers are duplicated then do a query to identify such items - theres an access wizard that will do that for you.

    if the worst comes to the worst then you can get a handle on how serious the problem is ie identify the number of duplicated rows.

    I would defiantley suggest that you make sure the serial number column is indexed.

    You could then use the output of that query as an input to antoher query and delete all duplicated values.
    -depends on how familiar you or your colleague are with manipulating data using SQL. it can be done but its complicated and time consuming, not something I'd reccomend for a noobie. don't get me wrong it just copmplicated and prone to error - the problem is that you don't know when you may have committed ther error.

    what ever you do do make sure to take a couple of backups before you do anything, work on a copy not the live data.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2003
    Posts
    107

    access wizard

    can you tell me more about the wizard?

    This application is designed to show the end users where the duplicate values are... and then in their own good time ... they'll decide to correct the duplications. They may or may not...
    There are 2 or three different reports that show duplication on various fields.
    Last edited by dotolee; 03-22-06 at 19:25.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    SELECT dupField, Count(dupField)
    FROM yourTable
    GROUP BY dupField
    HAVING Count(dupField) > 1

    That's assuming you need a "birds eye" view of all duplicates in a given table. If your users are working through records one by one, you could apply a filter on a disconnected recordset and check for a row count.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Feb 2003
    Posts
    107

    Sweet!!!!

    Thanks everyone for your help!
    Last edited by dotolee; 03-22-06 at 19:23.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •