Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    316
    Thanks
    4
    Thanked 0 Times in 0 Posts

    How much processing power do I need?

    How much processing power do I need to complete the attached spreadsheet quickly?

    There's a workbook attached with 826k+ rows of customer account numbers in column B. In column A I'd like a formula for each of those records that counts the total occurrences of each account number. Some Account numbers will appear in the list multiple times for those customers that have had multiple transactions.

    The file is big, about 7mb, so I've attached a compressed version of 3.5mb. On my work pc I turned Calculation to Manual and then copied the formula in A2 down to row 826638. When I calculated the spreadsheet I went to lunch and it was still calculating several hours later. I aborted the process with ESC. The pc is over four year old, but it's got 16Gb of ram and an Intel Xeon CPU E5503 @ 2.00GHz (running 64-bit XP). I figured a pc with that much ram should have been able to conquer the task.

    I recently bought a pc for home so I thought I should try this task there to see how quickly a new faster processor might work out the results. The new pc runs an Intel i5-4440 CPU @ 3.1GHz with 12Gb of ram (running Win 8.1). But after I copied the formula down the column and hit F9, the pc appeared to be chugging (or perhaps choking) about as fast as my pc at work.

    A web search of CPUs shows that the Xeon E5503 processor has cache sizes (L2 & L3) of 512kb and 4mb, while the 4440 runs caches of 1mb and 6mb. I think both chips have an onboard L1 cache of 256kb. But apparently the cache size isn't enough or maybe that's not the problem.


    So my question for this post is: How fast does your pc run this task? If you can complete this task in under 15 or 30 minutes, I'd really like to know what processor and configuration you are running. If you try run this task, you should first save all other work and close other apps as you may get an "out or resources" notice.
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,157
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Arcturus,

    Currently in process on my Desktop with i7 Haswell (4 cores 8 Threads) and 12 Gb Memory, Win 8.1 Update 1 Pro 64-Bit, Excel 2010 32 Bit. Processing about 2% per minute.
    Last edited by RetiredGeek; 2014-07-23 at 18:47.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,192
    Thanks
    43
    Thanked 226 Times in 210 Posts
    First, you have to amend your formula from
    =COUNTIF($B$2:B826638,B2) to =COUNTIF($B$2:$B$826638,B2)
    or when you copy down you will run out of resources because the upper limit of the range grows as you copy down.

    I limited to checking the first 50,000 numbers over the entire 826638 which took 1 min 25 sec w/ Core I7 OC to 4.2 Ghz at 100% load, 12Gb RAM, Win 7 Pro 64 Bit, Excel 2010 32 bit. It sent my Temps on all cores to 59 degrees with Corsair 100i liquid cooler.

    Congrats, a home brewed stress test benchmarking tool!

  4. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,250
    Thanks
    130
    Thanked 1,148 Times in 1,057 Posts
    Access based solution here: http://windowssecrets.com/forums/sho...ower-do-I-need.

    Takes just a few seconds.
    Rui
    -------
    R4

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,157
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Arcturus,

    Ok the test finally finished w/o error in 2:25 on my machine. Note: I'd made the change to the formula noted by Maud before copying the formula and running the test.
    Looks like Rui's solution is the way to go! HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #6
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,485
    Thanks
    26
    Thanked 171 Times in 167 Posts
    Hi RG/Arcturus

    You don't need to use Access.

    I opened the file, and created a simple pivot table on an empty sheet.
    I used entire Column [B] Account# as my data source.
    I chose Account# as my choice for Row labels, and Count of Account# for my Values.
    The pivot table was created instantly on my Corei7 Win7 laptop i.e. less than 2 seconds.
    I could then use filters to examine the data.
    For example, three accounts had 4 entries, 1 account had 6 entries (120435).

    zeddy

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,072
    Thanks
    12
    Thanked 36 Times in 35 Posts
    Can't argue with <2 seconds in Excel. Out of curiosity I moved the data to an array processing language (called APL) and the same calculation was virtually instantaneous.

Posting Permissions

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