Processor, RAM, or SSD?

Hello,

I am a "developer" that writes exclusively VBA scripts and applications. Usually, the application is very fast. However, when I utilize "Update" commands that are a combination of opening/closing files, transfering data, manipulating data, the occasional SQL query, and a smorgasbord of random VBA Scripts. Almost always, the code is in MS Excel.

I understand that if my code is inefficient, it will slow performance. I understand that if my SQL Query is inefficient or the database from which I am pulling it is not indexed properly, it will slow performance.

Other than that, what kind of PC improvements would cut down on the run-time / increase performence? An SSD? A faster processor? More RAM? I didn't see much improvement from having 16 GB of RAM.

Thank You!
EnigmaMatterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
1. Are you running on 32-bit or 64-bit Windows?
2. What version of Excel are you using?
3. Have you measured where you are spending the time in your application?
4. Are you doing any string concatenation inside a loop?
5. Are you invoking a ReDim statement inside a loop?

=============
General advice:
* Work in memory, when possible.
* Work with local HD/SDD, when possible.  I copied temporary working files to each user's PC and processed them there in one application with really poor performance.  The client's NAS is awful for many reasons, including performance
* Measure your application to know where you're spending your time
* Measure the PC system while your application is running
* Measure the network while your application is running
* Work with the DBA to measure the database while your application is running
0
EnigmaMatterAuthor Commented:
1.) 64-Bit WIndows
2.) Excel 2013
3.) No.
4.) No.
5.) No.

GENERALLY, based on the question I asked, what would give the best improvement.
0
aikimarkCommented:
Are you running a 32-bit version of Excel or 64-bit version of Excel?  You might compare performance between 32-bit and 64-bit Excel versions.

You are running under any particular 32-bit emulation? (if 32-bit Excel)

My recommendation is to measure your performance.

Let us know where your application is spending the bulk of its time.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

EnigmaMatterAuthor Commented:
I appreciate your answer, but that really isn't what I am looking for.

All I want to know is whether or not an SSD or a faster processor will improve VBA script executions IN GENERAL.
0
aikimarkCommented:
The "general" answer is NO.

It all depends on what is causing your application to run slow (sub-optimally)

* more memory won't help an application that isn't memory constrained
* SSD won't help if you aren't HDD I/O bound, although it will help speed up the Excel start time and workbook open time
* neither a faster processor nor an SSD will help if the network is the problem
* neither a faster processor nor an SSD will help if the SQL Server database is the problem
* a faster processor only helps up to a point if you have code problems or are compute-constrained.
0
JustInCaseCommented:
My point of view on this subject.

The only two things that help with SQL server are processor and fast storage (SSD) :) in tre case of big data you also need as much memory as you can get.

Generally similar to what aikimark already wrote
SSD will help with many read/write - So your programs will start faster, faster write (database read write) etc
CPU will help to faster perform debugging BUT it is not irrelevant is it single thread app or multi-thread, you need to choose processor according to that
Memory will help you if you have huge projects - and programs are x64 based, for small once you won't even note some big difference if you add memory.

If you debug and it takes 5 minutes to try code you just wrote - and you find error and let's say you do that 20 times - It is 100 minutes that you wait computer to finish, so you can continue your work. If you reduce debugging time to 3 minutes you have 40 minutes to do more work, to finish earlier, or to take longer break - whatever is your preference :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.