PDA

View Full Version : [SOLVED] Refreshing takes too long: SQL combined multi sheets in 1 sheet.



nawaf
09-07-2018, 01:34 PM
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/PowerPivot-Identical-Excel-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$]

Aflatoon
09-13-2018, 06:17 AM
Have you tried using Power Query instead to retrieve the data to your model?

nawaf
09-13-2018, 06:44 AM
would that be fast? if so, no I didn't try, dunno how. is there a site that can teach me?