Company File Size over 200MB

Thomas_7040795
Thomas_7040795 Member Posts: 54 ✭✭
edited May 2020 in Accounts Hosted
Our company file size is over 200 MB and contains data for more than 15 years. Working with this file is getting a bit slow and I am wondering whether we should do some kind of compression, cutoff or clean up.

I have checked this topic in the Reckon Community, but I find contradicting comments on whether or not to do a 'company file clean up'.

What is the right thing to do? 

Comments

  • John Peppas
    John Peppas Accredited Partner Posts: 8 Accredited Partner Accredited Partner
    edited May 2020
    Hi

    Have you done a full data verification and also data rebuild?

    This may improve the speed.

    I have clients with files a lot bigger than yours and they have no issues.

    A file cleanup/archive will not make much difference

    Feel free to email  at jpeppas@qldbc.com.au or phone 0418 771 971 though I will not be able to answer until after 5pm today

    John Peppas
    Reckon Accredited Partner
  • gazza73
    gazza73 Accredited Partner Posts: 802 Accredited Partner Accredited Partner
    edited May 2020
    There's a process that you can perform, in strict sequence,  that can help with large database files as follows:    It is important to do each step - don't skip any like I once used to!!


    a) confirm a stable ENVIRONMENT exists with O/S and LAN/WAN
    b)  Make a QBB with a verify, so we can safely delete the TLG file, and permit a new TLG to grow from scratch again
    c)  Make  QBM (highly compressed, but no loss of any data whatsoever)
    d)  Restore the QBM to a fresh QBW for future production use
    e)  AND THEN resort all lists to default
    f)  AND THEN do a rebuild
    g)  Go live with a much faster responding Customer Centre..... 

    Also,  whilst running Reckon,  check that you have sufficient RAM (memory) physically in the server (hosting machine) as well as the local workstation that is running the client end PROGRAM.

    This has helped us with a file that is now 1.3GB   (that's 1345MB in fact) in size with over 85,000 'names'   per the CTL-1 product information statistics page.  Also, when checking that STATS page, refer to the mid-left side where if shows DB FileFragments,  and compare the BEFORE versus AFTER  stats.  Having <100 fragments would be terrific!


    Gaz

    PS:  Many thanks to Tony Tran (Reckon Support)
    for the explanation of this a couple of years back....



    Gary Pope
    m: 0408994799
    e:  gaz@alchester.com.au
    An Accredited Partner- Consultant (VIC. Aust)
    http://www.alchester.com.au/reckon-ac...
    “Working with Accountants/Bookkeepers PPs/APs, as an
         independent IT Professional and retired FCPA Accountant”
  • Thomas_7040795
    Thomas_7040795 Member Posts: 54 ✭✭
    edited July 2017

    Thanks John,

    Greatly appreciate your recommendation and will proceed accordingly.

  • Thomas_7040795
    Thomas_7040795 Member Posts: 54 ✭✭
    edited July 2017

    Thanks Gary,

    Is there somewhere a more detailed step by step instruction of this rather complex process?


  • gazza73
    gazza73 Accredited Partner Posts: 802 Accredited Partner Accredited Partner
    edited July 2017
    Thomas.

    I don't like suggesting critical things too much like this - I'd suggest if you are not comfortable, that you ensure you try this all on a spare FOLDER with a 'copy' of the original QBW file,  and give that 'copy' a really unique different name to play with.  I then FILE--> Open that unusual file, and go to the COMPANY INFO menu to change the internal  company name to something strange like:  "play_Copy_of_my_Company PTY LTD" .

    Doing that,  ensure you' don't get confused and open/play with this test copy file in real daily production!   

    Practice and test everything on  a play-around copy that is clearly, separate from all other files on your system.

    I repeat - better to be safe than sorry if unsure of things.

    1.  create a play around folder on your system say:  C: \play_with_RECKON
    2. Use CTL-1 to see where your REAL database (company file :xxxx.QBW) is
    3.  Use a simple  2-STEP WINDOWS Copy, and then Paste  (do not move or Drag !!!!!)  of the QBW file from its real location ,to this new 'play' folder
    4.  Now be sure you are looking with Windows  Explorer, at the 
    play folder,  and rename the QBW file to say:  'play_with_my_COMPANY.QBW'
    5.  Now run Reckon and do a FILE -->  OPEN  of that 'play' file located in the 'play folder'.
    6.  DO a CTL-1    to ensure that the STATS page is showing that you have the 'play' file open,  and not your original real one.
    7.  Now go to the COMPANY menu, and the INFORMATION sub-menu and give this company a 'pretend-like' name,  such as:  "play_COPY_of_my_Company PTY LTD".  Why?  If you accidentally just run RECKON tomorrow and forget which company file is real versus play,  it will be really obvious because the silly play-around name will appear on the top of your screen, and on every invoice or report - and then you'll realise you just have to CLOSE that company and FILE-->  OPEN the original one like you were using prior to this exercise.

    --------------------------       -------------------------
    OK:  all that's been done so far,  is to protect you from yourself, whilst you have a play around with the entire procedure we suggested.
    --------------------------       --------------------------

    From here on , let's do all the steps in the 'play' folder with the 'play file'

    This process is in many USER manuals and HELP screen - we're simply backing up your file, so we can then restore it,  and then re sequence (re sort)  various lists to be in the best performing structure for access by multiple users.

    All we really want to do,  is to make sure you have a special QBB (backup version) of your QBW file.  With a QBB up your sleeve, you can eventually throw away the huge TLG file - but let's not worry about that - just make a QBB backup.


    You should be doing this every day
    regardless of the question raised here today.



    BACKUPS are imperative with critical data.  There are two formats available, QBB and QBM,  and I like to make BOTH when you also want to consider a performance cleanup, which ideally means disposing of your huge fallback transaction log file (the TLG file).  But never delete such a TLG file unless you have an alternative fallback  QBB file that contains known, VERIFIED, full data and history.
    (subject for another day!)

     FILE -->  SAVE a COPY or BACKUP --->
    (the first choice if "Backup Copy" which makes the QBB file)
    (the second choice is the Portable Company file which makes the QBM variant of backup).  Be sure to tick the VERIFY option! 

    Do BOTH, one at a time.  

    For this exercise, because you are simply playing around,  you can just backup LOCALLY.  

    When asked about "Save Backup Copy:Options",  use the browse button to locate that 'play' folder' as the place to park the QBB (or QBM) file you're about to make, (from the 'play'  QBW file you currently have open already).  

    Say NEXT and use the default "Save Right Now"  option (Don't go messing up other backup schedules you have - just do  quick one time backup for this whole exercise).  

    Click FINISH  and enjoy a break for a few minutes whilst your large QBW file gets sent out to the QBB (or QBM file).

    OK.  Once done,  remember to do it all again for the QBM way of things.

    ----------------------------          -----------------------------

    Your  'play' folder should now still have the fat old large under-performing QBW file (unaffected by all this, other than you've stolen its entire contents and backed them into two styles of backup files:  a QBB and a QBM)

    Be aware that Microsoft has a terrible habit of wanting people to HIDE the three (or sometimes four) character EXTENSIONS (file types) after the DOT in a file name.  IE:  fred.doc  or fred.xls  or fred.qbw    are three types of file.  That magic DOT separates the TYPE of file it is.  DOC is typically a word processing file,  XLS a spreadsheet, and QBW a Reckon Accounts Company Database file.   If you have your Windows system set to HIDE all known 'extensions' you'd simply see 'fred' .... but which one is which?   Say, on a Windows  7  Explorer screen if you are not seeing the DOT and the TYPE of file it is,  then on a windows explorer session, go to menu: TOOLS  --> FOLDER OPTIONS;  then to the VIEW TAB,  and untick the option that is called:  "Hide extensions for known file types".    Click APPLY as well as immediately clicking on "Apply to Folders" near the top of that screen.  

    Then all those  DOT types will be visible!   Much easier to see what's what!

    Don't worry too much about above.  Windows doesn't know about QBB or QBM so those two would be obvious anyway, and the other one is the QBW file you well knew in the first place.

    -----------------------------        ---------------------------


    Now it is time to reverse the QBM version of the above step.  We are in effect, going through a process as f you had a major catastrophe and lost your vital QBW file, and needed to get it back from the QBM file.

    SO,  do a restore from QBM  to a fresh new QBW

    In RECKON,  log out of the session because you are still using the 'play'  QBW file up till this point. 

    Restore the QBM backup just made above,  over the top of the 'play' QBW as if you were wanting to destroy that QBW and make it afresh using such a nice QBM backup file.

    Start running RECKON accounts from the startm but DO NOT select a company like usual.  Instead there are three buttons at the base of the 'No Company Open" screen.  The bottom middle one is:  Open or RESTORE an existing Company. Click that option.    You will then be presented with three choices, the last one is what we want:  "Restore a portable file (.QBM).

    **REMEMBER**
    keep playing about within the 'play' folder, using the 'play' files here.  So BROWE when asked, to the 'play' folder' and pick the freshly made xxxxx.QBM file just made above,  as the file you wish to USE FOR RESTORING FROM.



    Then you'll be asked where to put such a restoration -  again pick the 'play' folder' as the storage place,  and then make up a nice new name for the 'restored_play_company.QBW "  as a play around type name.

    ---------------------------         ------------------------------
    Off you go to FINISH that for a while, and you should end up with a better shaped QBW file with that newly selected name.

    ----------------------------        -------------------------------


    You're now ready for those last two steps we mentioned.......
    e)  AND THEN resort all lists to default
    f)  AND THEN do a rebuild


    For (e)  you need to fire up RECKON accounts, but this time tell it to FILE --> OPEN  that newly restored file say:   "restored_play_company.QBW ".  

    And in that session,  be sure to RE SORT (ie sort into a better sequence)  every list there is in the system.  Don't skip any - there are LOTS.  Some may only contain a few entries, like the Tax Code List - but do it anyway.   For each list in the main LIST menu,  choose the next one (Chart Of Accounts is first).....  Then at the base of the 'list' window refer to the button called (say)  "ACCOUNT" (or whatever the list name relates to)  and choose the bottom sub-menu option called:  Re-sort list.

    Now for the NEXT type of list in the system.   IE:  Menu:  LIST   -->  ITEM list
    and at the bottom will be a button called "Item".  Click that and select the bottom sub-menu choice called:  re-sort list.

    Next list......   and so forth.

    The trickiest ones to miss are the  9 odd sub-lists of Customer & Supplier Profile Lists.  All up I think there are about  17 to do.

    ------------------------     -----------------------------------


    Now it was the ABOVE process that everyone seems to miss when tidying up QBW files to perform better.   Unless all those LISTS are re-sorted properly,  then the next step just leaves you back where you started form,  an internally UNTIDY database that is slow for multiple users to utilise.

    So, onto the last step
    f)  AND THEN do a rebuild

    For this, choose menu:  FILE -->   UTILITIES  -->  REBUILD DATA.

    We've been doing all of this on a play-around set of files, but Reckon doesn't realise that.  So it will require you to go through a precautionary process of making yet another BACKUP.   Just let it do that,  but like earlier on in these note,  BROWSE to the 'play' area and give the files a name that you'll realise is now a backup made obviously some minutes later on than the first QBB ones you made.    It's just the way the process needs to run, to be safe.  Because after that interim backup step it does here,  the system will then reshuffle things all internally,  to take advantage of 

    i)  the nice compressed nature of how you got to this point via the restoration step

    ii)  re index and sequence things according to the nicely ordered  (re-sort) step you did.

    ------------------------------          -----------------------------

    When done,  you should then be in a position to do some TESTING of the performance of you're revitalised DATABASE (QBW file).   You're not to use this 'play' file for real - it is just a confidence booster, and a way for you to compare how long it takes to run some long report,  or worst case,  have say the Customer Centre open with outstanding balances perpetually being viewed/recalculated, whilst some other user is trying to run other reports.   Compare the difference and see if your 'performance' issue is being addressed.

    -------------------------------         --------------------------------


    If you feel you've achieved the performance improvement desired,  then it is time to get serious about doing all these steps for real,  on the live QBW file -  but only if you have secure off-line copies of all your QBW files, and with QBB and QBM files set to one side.



    I've laboured the point with the above explanation, because you asked for detailed instructions - so apologies if some points are over-explained.  But there can be serious confusion if users are not IT-savvy in terms of FOLDER locations and FILE names and understanding the difference between QBW, QBB QBM and TLG files and which ones a re live or not.  SO if not confident,  don't try this without the help of an Accredited Partner's assistance.  Plenty of us to choose from nearby to you.

    Check for your closest at the links  demonstrated on this map of AUST at:
    http://www.alchester.com.au/reckon-accredited-partners-enhance-business-solutions/



    You owe me a beer!

    Cheers


    Gaz




    Gary Pope
    m: 0408994799
    e:  gaz@alchester.com.au
    An Accredited Partner- Consultant (VIC. Aust)
    http://www.alchester.com.au/reckon-ac...
    “Working with Accountants/Bookkeepers PPs/APs, as an
         independent IT Professional and retired FCPA Accountant”?
  • Thomas_7040795
    Thomas_7040795 Member Posts: 54 ✭✭
    edited July 2017

    Gary,

    Thank you very much indeed. I hope you have this written up some where so that you only need to do a 'copy - paste' ... :-)

    I certainly owe you a beer for that. I will write to your personal email about where to get it.

    Thanks

    Thomas

  • Thomas_7040795
    Thomas_7040795 Member Posts: 54 ✭✭
    edited July 2017

    Thanks Kevin,

    Really appreciate your feedback and I am happy to try.

    Thomas 


  • gazza73
    gazza73 Accredited Partner Posts: 802 Accredited Partner Accredited Partner
    edited July 2017
    Heh!  We're all square - it's only a saying.  We'll share on in the next life, so to speak.   Cheers,  Gaz