locked
how to disable multithreading in Access 2010 RRS feed

  • Question

  • Hi,

    I run time consuming vba codes in Access 2010, and since my PC has 4 cells and 8 threads, the maximum processor rate when I execute the vba code in Access is around 13% (so 1/8 of the total).

    I imagine that if I disabled multithreading, I will double my calculation capacity up to 25% (so using a processor cell at 100%).

    I have seen on internet that Excel has a built option to enable or disable multithreading, but I did not see this option on Access 2010. Do you know how I could disable multithreading without modifying BIOS options? (I have a Toshiba laptop, and I did not see this option on the BIOS).

    Thank you very much in advance,

    Josu

    Monday, October 12, 2015 10:08 AM

Answers

  • While it is true that you can only get 100% of one thread, the problem is Access in general is NOT CPU bound.

    So even on a say a duel core i3 (with 4 threads), it true that you only get 25% max. And if you could turn off threading, you WOULD get 50% (so fair point!).

    However, such a setting will likely not help anyway?

    Why?

    Because even on a crappy i3, and at 25% that is STILL a rate of about 100 MILLION VBA instructions per second!

    Again - about a 100 million VBA instructions per second! (this is astounding by the way!)

    Now, I cannot imagine you are executing ANYTHING CLOSE to 100 million VBA instructions!

    However while 100 million VBA commands per second is typical on a modern computer, it is NOT the speed of your CPU or the speed of VBA instructions that is at issue.

    When you execute a VBA command or anything that reads data, you are NOW waiting for the disk drive, the memory or MOST often your network speed that limits this issue. So a super tiny % of the speed issue is VBA speed - it is everything else - and the everything else don't go faster with more CPU!

    You have to mention how many commands you are executing, but Access has NOT been CPU bound for about 10 years now. (it is network, memory and disk drive limited).

    As a result, throwing faster or more CPU to Access will in general not help the speed of your data processing.

    You have to mention what operations are running slow, but you can execute about 100 million VBA instructions per second even on ONE thread of a modern CPU – of course if that VBA instruction is reading data, or reaching out side of Access? Then no matter how fast the CPU is, it will be waiting for the network or the disk drive or something else!

    Using a data macro to launch a form, or a VBA command - the VBA command is executed several million times faster then the macro - but BOTH wait for the memory, and screen to plot - as a result you not see the difference in execting a command that takes 1/1000th of a second or 1/100000000 of a second, since they THEN both wait 2 seconds for the form to load from memory and pull data (both of which don't run faster with more CPU). So out of the 2 seconds wait, the 1/1000th of a second, or 1/100000000 of a second was CPU issue - you not see any difference.

    It is a RARE day that I see any Access performance problem solved by more CPU – in fact I cannot think of anything that was slow in Access that ever been fixed by more CPU in about the last 10 years now.

    Fix the code, or the query or whatever it is that is running slow.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    • Marked as answer by jooosu Tuesday, October 13, 2015 12:02 PM
    Monday, October 12, 2015 6:54 PM
  • I don't think anything would change if you disabled hyperthreading. You can probably disable it in the computer's bios, but the only thing that would change is the reported percentages, not the actual cpu cycles available to your application. I believe Access is single-threaded, so everything is running on one thread. When hyperthreading is enabled, Windows sees your cpu as capable of executing 8 threads simultaneously even though it only has 4 physical cpu cores. One thread (which is one processor core) is fully busy with Access work, so Windows reports that as 1 busy/8 available = 12.5% max utilization. If you disable hyperthreading so Windows only sees 4 simultaneously available threads, one core will still be fully utilized by Access, but Windows would now report 25% busy. The number of processor cycles available to Access has not changed, just the Windows reporting.

    Hyperthreading lets one core appear to process 2 threads simultaneously by overlapping some operations, but if one thread is fully utilizing the core, and nothing else is competing for resources, then hyperthreading enabled or disabled is essentially no difference. Only one thread will be running on that core and that thread will fully utilize that core. There may be some small inefficiency from having hyperthreading enabled, but I don't think you could notice the difference in an Access application.


    Paul

    Monday, October 12, 2015 10:26 PM

All replies

  • I don't follow your logic, but I'm not aware of any Access option to control its use of processor threads.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, October 12, 2015 2:24 PM
  • While it is true that you can only get 100% of one thread, the problem is Access in general is NOT CPU bound.

    So even on a say a duel core i3 (with 4 threads), it true that you only get 25% max. And if you could turn off threading, you WOULD get 50% (so fair point!).

    However, such a setting will likely not help anyway?

    Why?

    Because even on a crappy i3, and at 25% that is STILL a rate of about 100 MILLION VBA instructions per second!

    Again - about a 100 million VBA instructions per second! (this is astounding by the way!)

    Now, I cannot imagine you are executing ANYTHING CLOSE to 100 million VBA instructions!

    However while 100 million VBA commands per second is typical on a modern computer, it is NOT the speed of your CPU or the speed of VBA instructions that is at issue.

    When you execute a VBA command or anything that reads data, you are NOW waiting for the disk drive, the memory or MOST often your network speed that limits this issue. So a super tiny % of the speed issue is VBA speed - it is everything else - and the everything else don't go faster with more CPU!

    You have to mention how many commands you are executing, but Access has NOT been CPU bound for about 10 years now. (it is network, memory and disk drive limited).

    As a result, throwing faster or more CPU to Access will in general not help the speed of your data processing.

    You have to mention what operations are running slow, but you can execute about 100 million VBA instructions per second even on ONE thread of a modern CPU – of course if that VBA instruction is reading data, or reaching out side of Access? Then no matter how fast the CPU is, it will be waiting for the network or the disk drive or something else!

    Using a data macro to launch a form, or a VBA command - the VBA command is executed several million times faster then the macro - but BOTH wait for the memory, and screen to plot - as a result you not see the difference in execting a command that takes 1/1000th of a second or 1/100000000 of a second, since they THEN both wait 2 seconds for the form to load from memory and pull data (both of which don't run faster with more CPU). So out of the 2 seconds wait, the 1/1000th of a second, or 1/100000000 of a second was CPU issue - you not see any difference.

    It is a RARE day that I see any Access performance problem solved by more CPU – in fact I cannot think of anything that was slow in Access that ever been fixed by more CPU in about the last 10 years now.

    Fix the code, or the query or whatever it is that is running slow.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    • Marked as answer by jooosu Tuesday, October 13, 2015 12:02 PM
    Monday, October 12, 2015 6:54 PM
  • I don't think anything would change if you disabled hyperthreading. You can probably disable it in the computer's bios, but the only thing that would change is the reported percentages, not the actual cpu cycles available to your application. I believe Access is single-threaded, so everything is running on one thread. When hyperthreading is enabled, Windows sees your cpu as capable of executing 8 threads simultaneously even though it only has 4 physical cpu cores. One thread (which is one processor core) is fully busy with Access work, so Windows reports that as 1 busy/8 available = 12.5% max utilization. If you disable hyperthreading so Windows only sees 4 simultaneously available threads, one core will still be fully utilized by Access, but Windows would now report 25% busy. The number of processor cycles available to Access has not changed, just the Windows reporting.

    Hyperthreading lets one core appear to process 2 threads simultaneously by overlapping some operations, but if one thread is fully utilizing the core, and nothing else is competing for resources, then hyperthreading enabled or disabled is essentially no difference. Only one thread will be running on that core and that thread will fully utilize that core. There may be some small inefficiency from having hyperthreading enabled, but I don't think you could notice the difference in an Access application.


    Paul

    Monday, October 12, 2015 10:26 PM
  • Excellent and Indeed Paul!

    I had thought that how things work (and had read so in the past).

    In my example, I saying I get 25% max - this is NOT true! And keeping simple, imagine I had one core with two hyper threads (that each appear as a separate CPU), and you run one thread, the  you get 100% of the CPU - the task manager will only show 50% utilization when in fact you get 100% of the CPU. So NO limits exist - even if it had 10 hyper threads and I only run one thread - task manager will show 10% when in fact I am using 100% of the CPU.

    Of course you WELL note that SOME overlap can occur - so the reverse is not true!

    If I launch two copies of Access and run the SAME code, I will achieve some parallelization EVEN if I only have one REAL core - this is because hyper threading DOES gain use of "some" overlapping as you note above. That overlap in some cases is amazing, and one can see gains in some software of up to 40% - and this occurs for nearly the SAME silcon cost of one CPU - so HT still an amazing feature.

    I do much appreciate your follow up - you are correct that the "low" CPU is a artifact of the task manager, and if other threads are not busy, the you get full use of ONE core.

    And regardless when in the context of Access - more CPU these days will not fix 99% of software. Removal of dlookups, dcounts() in sql etc. will.

    Regards,

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canad

    Tuesday, October 13, 2015 1:28 AM
  • Hi,

    Thank you both for your clear and very detailed answers. Now, I understand the issue much better. I guess that I will need to improve the speed in another way, changing the queries that were executed, etc.

    It is a pleasure to find people like you that help other less experienced access users like me.

    Thanks again!

    Josu

    Tuesday, October 13, 2015 12:06 PM