Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2007
    Location
    Washington D.C.
    Posts
    24

    Unanswered: How to slow down your database to the point where it is almost useless

    ...not that you would want to do such a thing.

    Rather, I have finally--after about 5 days--figured out what was bringing my database to an unmanageable crawl.

    I initially wanted to post my question here (about 4 days ago) but I didn't want to post it until I was positive that I had exhaused all possible approaches... and every day when I got ready to post I managed to think of something else to try.

    If you are too impatient to read through the monologue, just skip to the end... to get to the solution

    The Background:
    - I have a rather large (to me at least) database that I use at work. It is completely home-grown so every once in a while (like now) I discover new things that perplex me for days. Then, once I figure it out, I wonder why I didn’t figure it out sooner… Anyway, moving along…

    - The backend is comprised of 4 main tables, based upon data that I receive from 4 separate billing sources (it’s the government… trust me its complicated). All of the tables have between 700,000 – 900,000 records each.

    - Everything was moving along swimmingly until…. The office switches over to Office 2007. I notice that my performance is slower, but I chalk this up to simply more overhead with the new system… who knows… But then, when it comes time for me to run a report for my boss, everything comes to a screeching halt. It literally takes over 5 minutes for one report to run (normally takes a few seconds)… I am perplexed, but happy at least that the data was still there… thus begins my quest…

    - Day 1: Examine the queries/reports… I go through all of my queries to make sure that something didn’t happen during the conversion. Nothing. I look at all my reports… although there are some nifty new features (mostly fluff!) in the report designer, I don’t notice anything out of the ordinary…

    - Day 2: Stuck in a staff meeting most of the morning… spend most of the time in the staff meeting thinking about why my database is not working… After I get back to my cube, I copy my backend database off of the server onto my local machine...re-link the tables…. Thinking that perhaps it is a server problem and not something with my design… No such luck…

    - Day 3: With no other solutions coming to me, I start to manipulate the tables directly… (I know, that’s never a good idea, but I was at a loss… and had the “live” copy of the db on the server in case I goobered things up too much). And then I isolated the problem finally… it was the TABLE… 3 of the 4 tables opened lightning fast… but the 4th one….. took 3 minutes just to OPEN… Made no sense…. After careful examination, I noticed that when I tried to open the table, I got a “Running query” message in the lower right hand corner of the screen… WTF? Query? I’m opening a table… Now I was stuck again…

    - Day 4: Went into the table design and got rid of all of my indexes… I know, it shouldn’t affect the opening of the table, just updates… but again, I’m desperate. As you probably all figured out, it did not work. So now I look at the actual table structure… look at the data types, etc. Nothing wrong there. I read about “Unicode compresson”…. I figure, hey, maybe I’ll get rid of that since I have the text fields set to “Yes”… That doesn’t work either. Not only doesn’t it work, but I can’t even do it… When I try to change that setting to “no”, MS Access tells me I don’t have enough memory… whatever… Still trying to figure out in the back of my head why MS Access is telling me that I'm running a query when I'm simply trying to open a table. I certainly didn't ASK for a query to be run...

    - Day 5: I’m ready to just create a new table, and copy all of my data into it with some sort of massive append query… But wanted to look one more time at the tables. (one of the other tables has 800,000 records and opens fine… 100,000 more records shouldn’t make a difference)

    Then… SUCCESS…. After looking at the table properties, I notice something different about this table… on the property sheet… the “Order by” property is populated… on my “date_created” field. And the “Order By On Load” property is set to “Yes”…. So in a nutshell, every time I was opening the table, I was RE-SORTING the table….

    No wonder the report was slow! All the reports… all the queries... all the forms… everything that was dependent upon that table of 900,000 records was being crippled by the simple fact that I or someone else…somewhere along the line…decided it would be a good idea to sort the table every flipping time that it was opened….

    And there you have it folks…. If you made it this far, thanks for listening to me vent about my own internal database flaw….

    The moral of the story… and possibly even worth adding to pkstormy’s other thread on database performance… Never EVER EVER let MS Access do your sorting in the table itself… don’t use the Order By property…. Save all that stuff for the queries, otherwise you might just end up accidentally slowing down your database.

    Thanks for listening! And if nothing else, I hope you at least got a chuckle

    Dean

  2. #2
    Join Date
    Nov 2008
    Posts
    4
    It is funny, how much time can somebody spend to solve a problem, trying every possibility and the solution at the end is very simple! It has happened to me many times. But I don't understand why the problem appeared after you moved to access 2007, you hadn't noticed it before?

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I never even knew about this. It is terrifying they even have this as an option. If it wasn't so buried this would be a massive Access WTF??? and be in here:
    http://www.mvps.org/access/tencommandments.htm
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Oct 2007
    Location
    Washington D.C.
    Posts
    24
    Quote Originally Posted by SocratesF
    It is funny, how much time can somebody spend to solve a problem, trying every possibility and the solution at the end is very simple! It has happened to me many times. But I don't understand why the problem appeared after you moved to access 2007, you hadn't noticed it before?
    Socrates,

    I really hadn't noticed... I think Office 2007 was just a red herring though. I really do think that somewhere along the line I went in there and modified the setting, but I'm really not sure what I was thinking at the time, or when I did it (or why it took me 5 days to remember doing it!) Perhaps one of my co-workers, but I can't be sure.

    Dean

  5. #5
    Join Date
    Oct 2007
    Location
    Washington D.C.
    Posts
    24
    Quote Originally Posted by pootle flump
    I never even knew about this. It is terrifying they even have this as an option. If it wasn't so buried this would be a massive Access WTF??? and be in here:
    http://www.mvps.org/access/tencommandments.htm
    Exactly what I was thinking! Why would that be an option, and why would someone (like myself) be so foolish as to utilize it.

    You wouldn't happen to still have version 2003 would you? I'm curious to see if that option even existed pre-2007. (I reluctantly switched over to 2007 after my office did, and I made the other bonehead mistake of installing 2007 over top of 2003, as opposed to installing it elsewhere so as to leave both versions on my system)

    Dean

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, I think it's amazing that it's there too... but I think that of many things in the table design area... like Input Mask, Lookups and Format. I guess I am lucky that I have never used such things and I don't have colleagues who can make such changes to table design. If they did, I would also be wondering WTF is going on.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes - it is in 2003.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and it is too easy to do!

    as coder, you occasionally need to jump into a table to see what is going on: sort the table; quit the table; and you are prompted to save
    ...saying YES generates this behaviour.

    meanwhile - has anyone found code to read (and preferrably write) this particular property ?

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by izyrider
    meanwhile - has anyone found code to read (and preferrably write) this particular property ?
    Oh yes - at least 10 seconds ago

    Code:
    Sub randomStuff()
    
        Dim t As DAO.TableDef
        Dim p As DAO.Property
        Dim db As DAO.Database
        
        Set db = Application.CurrentDb
            
        Set t = db.TableDefs("t1")
            
        For Each p In t.Properties
        
            Debug.Print p.Name
            
        Next p
        
        Debug.Print
        
        Debug.Print t.Properties("OrderByOnLoad").Value
        Debug.Print t.Properties("OrderByOn").Value
        Debug.Print t.Properties("OrderBy").Value
        
    End Sub
    Code:
    randomStuff
    Name
    Updatable
    DateCreated
    LastUpdated
    Connect
    Attributes
    SourceTableName
    RecordCount
    ValidationRule
    ValidationText
    ConflictTable
    ReplicaFilter
    Orientation
    OrderByOn
    NameMap
    DefaultView
    GUID
    DisplayViewsOnSharePointSite
    TotalsRow
    FilterOnLoad
    OrderByOnLoad
    HideNewField
    OrderBy
    
    True
    False
    date_text
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Oct 2007
    Location
    Washington D.C.
    Posts
    24
    That's good stuff... Had no idea that you could get to that in VBA...

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    bravo Poots - i couldn't find it last night.

    izy
    Last edited by izyrider; 11-19-08 at 13:44.
    currently using SS 2008R2

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Mar 2005
    Posts
    5

    sudden and intermittent slowdown

    My client is running a db written in access 2003 now running on 2007. Has run for years without problems. They did an item price increase a week ago. This has been done at least yearly for each of their many customers for ten years or so. There have been no program changes in several years. When they ran the increase, all data from the order details table curiously disappeared. This table isn't touched by the update query which applies a percentage increase to the items table. Being swamped at the time they soldiered forward, entering new orders rather than call me to troubleshoot, deciding they could do without the lost order details. Person in charge checked invoice totals, etc and decided nothing else was askew. Anyway, 4 days later the data entry person calls to say the computer had slowed to a crawl immediately after the errant price increase and she hadn't had time to call me in the meantime. I begin the zip and send to my home computer to troubleshoot--she calls back to say it was suddenly running up to speed (so I didn't spend my weekend on it). Today, two days later she calls to say the speedup only lasted half an hour. They have had their outside network guru check things out -- everything else is running normally -- so the assumption is that it is Access at fault. The dang thing works normally on my downloaded copy on my machine.

    Help! I don't even have a clue where to start! Thanks.

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    An interesting thing to do in such a case is to examine the Query Plan of Access. To do so you must create or change the value of JetShowPlan into the Registry:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Debu g
    (can be Jet\3.0\ or Jet\3.5\, depending of the version of Access). This forces Access to create a file (SHOWPLAN.OUT) where detailed information about what Access actually does when a Query is executed can be found (and opening a linked table makes Access query the server). This is a text file and it is comparable to the Execution Plan of a SQL Server.
    See:Customize Jet database settings (MDB) - Access - Office.com
    Use Microsoft Jet's ShowPlan to write more efficient queries | TechRepublic
    Have a nice day!

  15. #15
    Join Date
    Mar 2005
    Posts
    5

    another related? problem

    I appreciate Sinndho's suggestion, but I'm not nearly good enough to attempt that or to know what it was telling me! As I previously reported, the mdb works just fine on my standalone computer. Needing to keep doing something, I split the db. Certain reports now won't work--there is one (and only one) table that if it is in the BE, any report based on any query referencing it give error: the Microsoft Jet database engine could not find the object ". I can import it back into FE and all works fine. This is not the table that was cleared during the price increase.

Posting Permissions

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