Results 1 to 13 of 13
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Upate Query Optimaztion (2003 SP 3)

    Perhaps you can schedule the update query to be run before or after regular office hours, or during the weekend?

  2. #2
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Upate Query Optimaztion (2003 SP 3)

    Hans,

    Thanks. I have considered it, but they don't leave their pc's on, the server doesn't have Microsoft Access and in the event of a failure, I would want to know right away. If I were to break the update query up into 10 more queries, effectively only updating 10% of the records at a time, would it be less likely to fail, then trying to run the entire recordset through at once?

    Thanks again for taking time to share.

    Ken

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Upate Query Optimaztion (2003 SP 3)

    I'm afraid I don't know. You might try it though.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Update Query Optimization (2003 SP 3)

    Edited by HansV to correct typos in subject

    I am having a problem running an old update query against data on a server. Monthly I get a file with 12000+ records. The 12000+ are compared to a permanent table of 42,000+ records. If there is a match on a single indexed 15 character text field, then it updates 30 fields in the permanent table from the smaller table. Apparently in years past both tables were significantly smaller, and ran without incident. However, the tables have grown significantly, and the file server is not the greatest. It was taking as long as twenty minutes to run the query as recently as April. In May and June something happened and the backend database with the permanent table got corrupted. I had to get everyone out of the database, and when I opened the backend, Microsoft Access said the database needed to be repaired. Once repaired everyone was able to get back in without incident, though I didn't rerun the offending query.

    At end of day, when everyone had left, I copied it to local disk, then executed the query, then put it back up on the server. What alternatives exist to update the permanent table without getting everyone out, and without running the risk of corruption?

    Thanks in advance for your ideas and solutions.

    Ken

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Update Query Optimization (2003 SP 3)

    Are you sure that field is still indexed on the server table? And is there a PK on the table? I've seen indexes get lost when databases get corrupted.

    Also, what about this file that contains the 12000+ records? How are you using it? That is, are you just linking to it or importing? If you import to a table in Access, you can make sure that field is indexed.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Update Query Optimization (2003 SP 3)

    From what you've described, it seems like the process shouldn't take as long as it does. If that 12000+ table doesn't actually have a designated PK, I'd make that text field the PK since you said it was indexed with No Duplicates. Access likes to find a PK.

    Since this common Text field is not the PK of the master table. however, I'd be curious to see how many records are actually being updated. Once you've added all monthly records to your 12000+ table, I'd just do a select query joining this table with the master table to see what the record count is.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Update Query Optimization (2003 SP 3)

    Mark,

    Thanks for the follow up. Yes, the field on the server table is still indexed, and there is a primary key, though the field the two tables have in common are not the primary key, but a text field common to both. The 12000+ is also indexed on the same field, indexed with no duplicatess. We get a text file monthly with the records, we delete alll records in the Access 12000+ table and then append the latest file. The mdb file is compacted and repaired each month prior to importing the new records.

    Does this help?

    Thanks,

    Ken

  8. #8
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Update Query Optimization (2003 SP 3)

    Mark,

    thanks again for following up. Approximately 80% (10,000-10,300 of the 12000+) match and are updated in the server table. I am making the change to the 12,000+ table making the matching field a primary key. I'll rerun and compare results.

    Thanks!

    Ken

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Update Query Optimization (2003 SP 3)

    You didn't say how that text field in master table was indexed, so I was wondering if there might be duplicates, such that your update would be for > the # of records in the monthly table.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Update Query Optimization (2003 SP 3)

    Mark,
    Great question. I should have included that in my answer. Although the one on the server is not a primary key, it is indexed and no duplicates are allowed. One thing is different in the properties of the fields is that the incoming 12k does not allow zero length, and there are about 500 legacy records that are zero length in the table on the server.

    Thanks again for your help and ideas!

    Ken

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Update Query Optimization (2003 SP 3)

    How is that text field indexed with No Duplicates when you have 500 records with zero-length strings?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #12
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Update Query Optimization (2003 SP 3)

    Mark,

    That makes sense. Below are the properties of the field, including the index. P_txtID Text 15
    AllowZeroLength: True
    Attributes: Variable Length
    Caption: UniqueID
    CollatingOrder: General
    ColumnHidden: False
    ColumnOrder: Default
    ColumnWidth: 2925
    DataUpdatable: False
    Description: ID number
    DisplayControl: Text Box
    GUID: {guid {7FA21158-8FE7-458F-9728-0FE6F951DDCD}}
    IMEMode: 0
    IMESentenceMode: 3
    OrdinalPosition: 1
    Required: False
    SourceField: P_txtID
    SourceTable: tblOnline
    UnicodeCompression: False


    P_txtID 1
    Clustered: False
    DistinctCount: 38512
    Foreign: False
    IgnoreNulls: False
    Name: P_txtID
    Primary: False
    Required: False
    Unique: True
    Fields:

    P_txtID Ascending

    This is one of those inherited projects, and I can make changes to the original database tables, but have been hesitant to do so. Would forcing a unique number into the blank ones improve performance?

    Thanks again Mark,

    Ken

  13. #13
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Update Query Optimization (2003 SP 3)

    I'm guessing that even though the ZLS property is true, the value in those 500 records is Null.

    I don't see how changing this field would improve performance. They are, I assume, the same field size?

    Assuming everything is as it should be between the tables (both tables have PK, both connecting fields are indexed), then I don't know why your performance should be so bad.

    At this point, I'd look at the hardware. How much memory does your workstation (and the server) have. How much free disk space is on the drive? Have you defragged it lately?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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