locked
importing data into excel RRS feed

  • Question

  • Does anyone have a bench mark of how many records per second excel should be able to pull from sql.  I see these videos where people are pulling millions of records in a matter of seconds.  I'm using excel 2010 with powerpivot and sql 2005.  

    i get like 20,000 records a second

    i'm guessing that in the videos they are using sql 2008 r2 , have there been enchancements that allow for faster transfer?


    Analyst
    • Moved by Harry Zhu Friday, February 26, 2010 6:36 AM relating to powerpivot (From:Visual C# General)
    Wednesday, February 24, 2010 8:30 PM

Answers

  • Robin,

    Yes, in the videos you will see usage of SQL 2008 and not SQL 2005.  I believe on benchmarking it would be very hard to specifically gauge the exact time for the records. It will depend on things like the following:

    - Client specs (memory, processor, etc)
    - Server specs (processing speed, memory, etc)
    - What is the proximity of data in relation to the client pulling it in.  For example, is it a standalone box with data versus a 3 server jump to the data.

    For example, it is recommended that the client for Excel has the following prerequisites, part of which memory is a big one: (http://www.powerpivot.com/download.aspx):
      • Requires Microsoft Office Professional Plus 2010 Beta
      • PowerPivot for Excel supports 32-bit or 64-bit machines
      • PowerPivot requires a minimum of 1 GB of RAM (2 GB or more recommended)
        The amount of memory needed will depend on the PowerPivot solution you are designing
      • Requires Windows XP with SP3, Windows Vista with SP1 or Windows 7
      • If you are running a version of Windows other than Windows 7, you will need to download and install the .NET Framework 3.5 SP1

    For this type of question, it tough to give a specific benchmark without knowing more of the environment you have created.  If you have more details on the toplogy we can look a bit further as to expectations?

    I would recommend you also check out the recommendations on TechNet here: http://technet.microsoft.com/en-us/library/ee210650(SQL.105).aspx 

    Thanks,
    ~Meghann [MSFT]

    Friday, February 26, 2010 7:05 PM

All replies

  • Does anyone have a bench mark of how many records per second excel should be able to pull from sql.  I see these videos where people are pulling millions of records in a matter of seconds.  I'm using excel 2010 with powerpivot and sql 2005.  

    i get like 20,000 records a second

    i'm guessing that in the videos they are using sql 2008 r2 , have there been enchancements that allow for faster transfer?


    Analyst
    http://social.msdn.microsoft.com/Search/en-US/?query=importing%20data%20into%20excel&rq=meta:Search.MSForums.ForumID(24becc9b-b984-47b2-a748-a62e38c0066f)+site:microsoft.com&rn=Visual+C%23+General+Forum
    Just Be Humble Malange!
    Wednesday, February 24, 2010 10:28 PM
  • Robin,

    Yes, in the videos you will see usage of SQL 2008 and not SQL 2005.  I believe on benchmarking it would be very hard to specifically gauge the exact time for the records. It will depend on things like the following:

    - Client specs (memory, processor, etc)
    - Server specs (processing speed, memory, etc)
    - What is the proximity of data in relation to the client pulling it in.  For example, is it a standalone box with data versus a 3 server jump to the data.

    For example, it is recommended that the client for Excel has the following prerequisites, part of which memory is a big one: (http://www.powerpivot.com/download.aspx):
      • Requires Microsoft Office Professional Plus 2010 Beta
      • PowerPivot for Excel supports 32-bit or 64-bit machines
      • PowerPivot requires a minimum of 1 GB of RAM (2 GB or more recommended)
        The amount of memory needed will depend on the PowerPivot solution you are designing
      • Requires Windows XP with SP3, Windows Vista with SP1 or Windows 7
      • If you are running a version of Windows other than Windows 7, you will need to download and install the .NET Framework 3.5 SP1

    For this type of question, it tough to give a specific benchmark without knowing more of the environment you have created.  If you have more details on the toplogy we can look a bit further as to expectations?

    I would recommend you also check out the recommendations on TechNet here: http://technet.microsoft.com/en-us/library/ee210650(SQL.105).aspx 

    Thanks,
    ~Meghann [MSFT]

    Friday, February 26, 2010 7:05 PM