cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nick_Steele
Frequent Visitor

Re-use intermediate #temp table between PQ SQL Server queries

Hi

Thanks for taking the time to read this. I suspect that I want is not possible, but it doesn’t hurt to ask.

 

The issue:

I have a collection of related SQL queries which are all structured in a similar way. They all effectively follow the pattern:

  1. Select some stuff into #temp table
  2. Combine temp with some other tables and Select records OR filter some other views using the keys from #temp (an inner join)

Most of the overhead for each query sits in the first step (10mins vs 30secs for the second).

Each SQL query is saved as a separate Power Query query and the result set for each is then loaded to the data model (Power Pivot). It seems that each PQ query opens its own connection to the SQL database which leads to painfully slow refresh times (as the 10min cost of creating #temp is incurred each time).

Unfortunately, I do not have write permissions on the db so turning #temp into a permanent view/table is not an option.

 

What I’d like to do:

It would be super if you PQ gurus could help me figure out how to either:

  • Reuse the same connection object – this would prevent #temp from passing out of scope each time the connection is opened/closed.
  • Return multiple result sets from a single PQ query which can then be split into separate tables before being loaded to the data model.

Alternatively

Connection objects can be explicitly managed in VBA (with ADO) but I have not figured out a way to use the result set to overwrite an existing table in the data model (note I need it to be an update which preserves model relationships). Is this possible?

 

Any help would be greatly appreciated.

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Power Query queries can reference each other. So you can recreate your SQL logic in Power Query.  Throw in a Table.Buffer or two, and manipulate the "Enable Load"  setting, and you have maximum control.

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

Power Query queries can reference each other. So you can recreate your SQL logic in Power Query.  Throw in a Table.Buffer or two, and manipulate the "Enable Load"  setting, and you have maximum control.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors