none
Accessing 3 million rows of table RRS feed

  • Question

  • Hi All,

    I have a question on accessing a table having around 3 million rows (preferably from Excel 2007) 

    This table is a result of ETL extracted data and want now to enable user to use this data. Therefore, I have proposed a solution to them to access it from excel 2007. So i am trying to generate pivot table which is pointing to this table on our production server

    Since this table is having approximately 3 million of data rows , the excel pivot table is taking a long time to "read" data rows. 

    Can i continue with this solution or can you suggest me some other practical solution so that user can access this data ?

    Thanks 

    Friday, October 3, 2014 5:51 AM

Answers

All replies

  • What takes time? Reading the data such into Excel, or constructing the pivot table in Excel?

    It is possible to write queries to pivot the data in SQL Server. But since a query returns a table, and a table has a well-defined set of columns, you can not construct a view with dynamic columns, which I suspect is what you want. You can do this with dynamic SQL in a stored procedure, but that code is not always simple to maintain.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, October 3, 2014 8:01 AM
  • Typically SSAS OLAP cube provides fast access from Excel pivot table.

    Office: "Get data from Analysis Services   

    Analysis Services provides dimensional data that is well-suited for data exploration in PivotTables and Power View reports. "

    LINK:  https://support.office.com/en-US/Article/Get-data-from-Analysis-Services-ba86270b-5cc2-4bb9-a21d-8bafc20f0cd3?ui=en-US&rs=en-US&ad=US




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014






    Friday, October 3, 2014 8:06 AM
    Moderator
  • You shouldn't allow users to access 3 million rows - this can adversely affect the performance. Instead create a view and SELECT TOP few records (say 1,000 or 10,000) from this table. To view the data, users can add their own filters (WHERE clause) and fetch no more than 10,000 records matching their criteria, which should satisfy all their queries. Just grant access to this view to the users

    Satish Kartan www.sqlfood.com


    Friday, October 3, 2014 10:18 AM
  • You shouldn't allow users to access 3 million rows - this can adversely affect the performance. Instead create a view and SELECT TOP few records (say 1,000 or 10,000) from this table. To view the data, users can add their own filters (WHERE clause) and fetch no more than 10,000 records matching their criteria, which should satisfy all their queries. Just grant access to this view to the users

    Satish Kartan www.sqlfood.com


    For example, the view can be defined as below and users have to specify the WHERE clause (select colA, colB from uv_V1 where colA = 'blah')

    create view uv_V1 as 

    select top 10000 colA, colB from TableName



    Satish Kartan www.sqlfood.com

    Friday, October 3, 2014 10:24 AM
  • Depending on whether user wants to do flexible or static analysis you can create a SSAS cube or a SSRS report which can pull required data for the users out of these 3 million rows based on parameters/slicers they apply. That would mean it will only retrieve the required data from the table based on parameter values user chooses

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by UltraDev Friday, October 3, 2014 12:33 PM
    Friday, October 3, 2014 10:46 AM
  • agreed with Visakh

    Issue resolved but i had to develop SSAS cube (and no workaround i found for browsing from excel 2007 as data volume is huge)

    Thanks

    • Marked as answer by UltraDev Friday, October 3, 2014 12:34 PM
    Friday, October 3, 2014 12:34 PM