Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2008
    Posts
    2

    Unanswered: Access vs. TEMP Directory

    I am running Access 2003 on a WinXP (version 2002 service pack 2) machine. It has two drives installed -- one a large, slow system drive and the other a very high speed, smaller flash disk. Every night I use the Access db to process very large numbers of small queries to translate data from one form to another. The JET engine uses the Windows/TEMP directory to process these queries in.

    I would like to force access to use a temp directory of my choosing on the high speed drive to speed up the process and cut down on disk i/o. I tried changing the User environment temp directory to the high speed drive but Access is still using the system TEMP directory on the low speed drive. I don't want to move the Windows/Temp folder to the high speed drive because I don't want to fill that drive with other stuff Windows jams in there.

    How can I force Access to use the a temp directory on the high speed drive and leave the windows/temp folder on the system drive?

    OR

    How can I automatically keep the Windows/TEMP folder clean if I can't do the above and have to put the Windows/TEMP folder on the high speed drive?

    Thanks!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by accessfoole
    very large numbers of small queries to translate data from one form to another.
    One form to another?
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    suspect its one formAT rather than one Access Form......

    Be wary of using flash drives, unless its one of the very latest flash drves you may find you get disk errors very soon. Current freely available (cheap) flash drives have a limited life, its somewhere around 100..200,000 write cycles per byte before the flash memory starts to degrade.

    I suppose you could create a temporary / intermediate db on the flash drive, or copy the source data there, do your processing in that db on the flash drive and then do your final process to copy dtaa to the target db.

    whether you will see a significant performance increase I don't know. I think you would get a better performance hit by revisiting your queries, making sure you are only doing what you have to do in each query, strip out any problem terms, make sure you use the optimum where & join syntax. If you have very large numbers of queries there may well be a few performance hogs ther, there may also be some repitition in the queries, by carefull re-design and working on the timing of the queries you may be able to strip out redundant cycles.

    try to avoid using vba functions in the queries, try to avoid user written functions, make sure you don't use any SQL aggregate functions (unless you really really need to)


    but first off I'd want to get a handle on what was using the resources, probably time, I'd want to run a timer on each query to see how long its taking.

  4. #4
    Join Date
    Jan 2008
    Posts
    2
    Yes, by form I meant format. I've been down the query optimization path already....

    The Flash drive is not your normal one but a special one used in military aircraft and very high performance servers (http://www.bitmicro.com/products_edi...a_35_u320.php). I'm not worried about life on that drive.

    Your third paragraph is exactly on the mark. I move all the source files and the access db to the flash drive so I can do all the processing there. Problem is ACCESS uses the TEMP directory on the slow drive to actually do the queries.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    UIm just wondering if its a setting somewhere in the registry. whether thats a settign for Office or JET, I don't know. One oddity I did spot is that I couldn't find any relevant registry entries for Access.

    another option may be to start access using a user profile rather then the registry setting using the /profile command line switch. I suspect the latter may be a smarter approach, although how you define an appropriate profile beats me

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    what <drive>:\<wherever>\temp ?

    is something wrong deeper in the mix: i don't expect to see *any* stuff going to disk unless i put it there or it is off to swap-file because i ran out of RAM

    you may not be at liberty to explain the real situation to the world at large, but please conjure up an example situation on hardware built in the past 5 years that has Access paging off to it's own choice of temp.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Be wary of using flash drives, unless its one of the very latest flash drves you may find you get disk errors very soon. Current freely available (cheap) flash drives have a limited life, its somewhere around 100..200,000 write cycles per byte before the flash memory starts to degrade.
    That's news to me! Thanks. How can you tell if it's a "very latest" flash drive and do the "very latest" ones have the same problem but just more write cycles?

    I know, this is offtopic :P
    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

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    First generation flash drives have a very specific life, in some ways its a bit like CD RW devices, theres only so many times you can turn an individual bit on and off. It applies to all devices uses flash memory, ranging from USB pen drives all the way up to CF cards, and devices using the same chip technology. Whether the OP's chosen drive is a new generation that has got round this problem I don't know, I can well imagine the aero industry takes a slightly different view on cost, replacements and life expectantcy. But I was informed very recently that flash disks are good for a limited number of cycles (around the 100..200k mark), but are not reccommended for general hardware use. I was particularly interested in the low power consumption of flash drives.

    They are a great way of storing information that is going to be modified rarely, they are not appropriate for system drives where there may well be significant amounts of IO running in background, say the pagefile, or a temporary filestore.

    its my understanding that as more is known about flash technologies that the number of read write cycles is dramatically extended (still limited), but these drives are still very, very expensive.

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Thanks

    I'll be fine then, I only use my flash drive to transport databases to client sites.
    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

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by StarTrekker
    Thanks

    I'll be fine then, I only use my flash drive to transport databases to client sites.
    USB sticks are in my view a great means of transferring data, much quicker than writing to CD or downloading..... unless of course you have the truculent customer(s) from hell who are still using ME/98 or NT (they do still exist), so no USB there then.....

    But then again for that sort of application you are never going to hit the 100K per bit usage that seems to be the shortfall... I reckon I will have lost, crushed or had the device nicked long before then.

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agreed... I would have upgraded mine long before then
    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

Posting Permissions

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