Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors