Due to limitations imposed by the Information Management department, my boss would like me to find the most effective way to increase the performance of our existing Dell Precision 670 computers (3.8G, 2M, Xeon Irwindale, 800). I already have 4 gigs of memory though Windows XP (32 bit) only recognized 3 gigs. I would like to increase the performance, specifically with Excel 2007 functions such as large Vlookups with multiple very large Excel sheets (500 Megs+).
Can any provide any data regarding the performance benefits of upgrading to a second processor or a dual-core processor? Will the motherboard on my computer support a dual core processor; if so which one(s)? What is the biggest bang for the buck and where can I purchase it? How much should I expect to pay? Any installation instructions specific to this solution would be greatly appreciated.
http://support.dell.com/su
Service Tag = BVSVJ91
Thanks!
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
You have many options available. For ultimate performance, go to 2 x Dual core Xeon processors and a 64bit OS (Windows XP or Vista or Windows 7 ). Although Office 2007 is ONLY 32 bit you will gain huge speed increases IF your spreadsheet is computationally intensive and/or you run other apps at the same time.
Also dont forget that Microsoft have confirmed that office 2010 will be available in both 32bit and 64 bit versions.
Our organization is on Windows XP and we are trying to conform. If we did
hypothetically upgrade to a 64-bit operating system wouldn't the limitations of Excel 07 still hinder the performance?
http://msdn.mi
M
Although the limit of 2GB is still there, you are running Excel as a 32Bit app on a 64Bit platform so you will almost gaurantee the full amount of memory that excel wants will be real physical memory. Hope that makes sense? :S
For example, you run Excel AND Outlook on a 32Bit version of XP with 3.3GB memory available, your going to be paging/swapping a lot of memory with your spreadsheets.
Run Excel AND Outlook on a 64Bit version WITH lots of RAM, 8GB+, and each of your 32Bit apps will get almost the full 32bit, 3.3GB, memory made available to it.
Hope that helps clear it UP? :D
Neilsr you are 100% correct. These spreadsheets should ideally be in a database and I have converted some of the most time consuming portions to Access but I working with giant spreadsheets designed in Excel before I was around by accountants and analyst that were not proficient in SQL or VBA. They understand Excel and are very comfortable manipulating data using pivot tables, filters and Excel functions. Slowly we are trying to implement Vlookup solutions like the following UDF shown below which is over 100 times faster in my trials. Additionally the lack of a full outer join in the QBE in Access seems to be intimidating. I think the hope is that a faster computer would alleviate some of the time involved in creating reports without having to recreate the process in a new environment.
==========================
Function VLookupSort(SearchArgument
ColumnNo As Long, Optional SortDirection, Optional NotFound)
' Works as Vlookup, exact match (4th argument = FALSE)
' But takes advantage of the fact that a table is sorted
' and thus is much faster
' Also permits table to be sorted descending (Sortdirection -1)
' Optional argument for return value if item not found, defaults to #NA
Dim ItemFound
If IsMissing(SortDirection) Then SortDirection = 1
ItemFound = Application.Match(SearchAr
SortDirection)
If SearchTable(ItemFound, 1) <> SearchArgument Then
If IsMissing(NotFound) Then
VLookupSort = CVErr(xlErrNA)
Else
VLookupSort = NotFound
End If
Else
VLookupSort = _
SearchTable(ItemFound, ColumnNo)
End If
End Function
==========================
Your best improvement is not in upgrading the cpu, since that is an old family Xeon and two of them don't improve Excel, but in improving the Excel worksheets. If you have multiple vlookups to get data from the same row, you can optimize the Excel easily by replacing the VLOOKUPs with one MATCH and an INDEX function for each column that you want. The MATCH function will get you the row, and you never need to search after that, which is expensive if you have thousands of rows.
In order to get a hardware improvement, you would need to upgrade the processor class, RAM and motherboard, since you're still running the old Netburst family (Irwindale). You're probably scoring 1447 with a Xeon 3.73GHz http://www.cpubenchmark.ne
If I have open 2 large Excel workbooks both 500 megs and a Vlookups (Unsorted & Exact Match) takes over 6 hours, where do you think the hardware is bottlenecking? I know using a sorted list the VlookupSort UDF will take less than 10 minutes but is optimizing the spreadsheet really my only realistic option? Would a second processor even make a difference? I know this is a loaded question but roughly how much faster would you estimate an Intel Core i7 with 8 gigs of memory and a 64 bit operating system run a similar Vlookup?
Do you agree with the following findings? Am I missing anything?
Findings:
Dell Precision 670 has very limited potential since it is in the old Netburst family (2.1 Xeon). Additional processor wont significantly improve Excel performance (20%+) and RAM limit already exceeded.
Excel 2007 (32 bit) application limited to 2 gigs of RAM.
http://msdn.microsoft.com/
Ant
Windows XP (32 bit) is limited to 4 gigs of physical RAM (memory) [Organization Standard]
(4 gigs Video Card = 3 gigs) http://www.microsoft.com/w
Recommen
http://msdn.microsoft.com/
Upgrad
Hardware Alternatives:
Convert Excel worksheets to Access DB. Requires learning new Access skill set to maintain and modify.
Use more efficient functions than unsorted vlookups. (Match/Index, Custom UDFs, User Defined Functions). Requires building on existing Excel knowledge.
A 6 hour Excel vlookup is definitely cpu constrained - you're doing multiple searches over large numbers of rows. Even with a processor that is theoretically 10x faster, it would take 36 minutes, which is still a long time.
Replacing multiple VLOOKUPs with MATCH and INDEX and sorting the data would probably be your best improvement, even better than your custom VLookupSort, since that performs a MATCH everytime it is used. If you combine that with an i7 system that has four cores and hyperthreading and a 64-bit OS, you will probably be able to finish in under a minute, judging from the 10-minute time with the optimized function.
Can anyone provide an example of the Excel LOOKUP/MATCH using binary search with sorted data? Ideally this solution will not return the nearest match like a Vlookup binary search.
If these numbers are accurate I could achieve a huge performance improvement from just implementing the MATCH/INDEX solution as Callandor pointed out.
http://www.eggheadcafe.com
VLOOKUP/FALSE solution: 5 seconds.
VBA solution: 0.11 seconds
XLM solution: .055 seconds
MATCH/INDEX solution: .0043 seconds
Thanks everyone for all your help! I greatly appreciate it.
Business Accounts
Answer for Membership
by: ArmyGrooPosted on 2009-11-02 at 12:40:52ID: 25723479
You exceeding the capabilities of your RAM with these searches.
First check the capabilities of your motherboard. I couldn't see from your service tag how many ram slots that system had but check out www.crucial.com and it will give you the total ram possible for the board.
The best solution is to upgrade to a 64-bit version of either xp or W7. Then upgrade to 8Gig of RAM.
If your system will not support greater than 4 gig of ram then build a new system for them to test the capabilities of a new machine.
The below has 8gig of RAM and 64-bit windows 7 for $750
http://www.newegg.com/Prod