Consulting

Results 1 to 3 of 3

Thread: Refreshing takes too long: SQL combined multi sheets in 1 sheet.

  1. #1

    Refreshing takes too long: SQL combined multi sheets in 1 sheet.


    Hello friends, hope allis well!



    The below code uses aSQL code to combine multi sheets (workbooks) into one sheet. This is happening on PowerPivot.
    I got the idea from https://www.contextures.com/PowerPiv...cel-Files.html

    Yes, there are manyrows (5 million) & 17 columns; and refreshing is taking a lotof time.
    What can I do speed up the refreshing the data of the powerpivot?

    btw my computer has good specs:

    Windows 8.1 Pro

    Installed memory (RAM)14GB

    System Type: 64- bit OS,x64-based processor



    Please help, and tell me howcan I speed up the refreshing process.



    Thanks a lot in advance!





    SELECT [HEAD$].*   FROM [HEAD$]
    
    UNION ALL 
    
    SELECT * FROM`F:\DATABASES\AvgClaimCo\17-18.xlsb`.[JAN-APR$]
    
    UNION ALL 
    SELECT * FROM`F:\DATABASES\AvgClaimCo\17-18.xlsb`.[MAY-AUG$]
    
    UNION ALL 
    SELECT * FROM`F:\DATABASES\AvgClaimCo\17-18.xlsb`.[SEP-DEC$]
    
    UNION ALL 
    SELECT * FROM`F:\DATABASES\AvgClaimCo\16.xlsb`.[JAN-MAR$]
    
    UNION ALL 
    SELECT * FROM`F:\DATABASES\AvgClaimCo\16.xlsb`.[APR-SEP$]
    
    UNION ALL 
    SELECT * FROM`F:\DATABASES\AvgClaimCo\16.xlsb`.[OCT-DEC$]
    
    UNION ALL 
    SELECT * FROM`F:\DATABASES\AvgClaimCo\15.xlsb`.[Jan-Mar$]
    
    UNION ALL 
    SELECT * FROM`F:\DATABASES\AvgClaimCo\15.xlsb`.[Apr-Jun$]
    
    UNION ALL 
    SELECT * FROM `F:\DATABASES\AvgClaimCo\15.xlsb`.[Jul-Sep$]
    
    UNION ALL 
    SELECT * FROM`F:\DATABASES\AvgClaimCo\15.xlsb`.[Oct-Dec$]

  2. #2
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,779
    Location
    Have you tried using Power Query instead to retrieve the data to your model?
    Be as you wish to seem

  3. #3
    would that be fast? if so, no I didn't try, dunno how. is there a site that can teach me?

Posting Permissions

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