-1

I'm trying to get a better idea of how to better optimize my Power Query code. Is there a way to profile queries and determine where they are spending their time?

So far all I've been able to come up with is to break the query apart into separate queries that I can then connect to tables in Excel time using the below VBA code. The problem is, it is quite laborious to break the queries down and track down performance issues. Especially since the act of breaking them down may change how they perform.

'MicroTimer from http://stackoverflow.com/a/7116928/2250183
Public Function TestQuery(connectionName As String) As Double
    Dim startTime As Double
    Dim connection As OLEDBConnection
    Set connection = ThisWorkbook.Connections(connectionName).OLEDBConnection

    connection.BackgroundQuery = False
    startTime = MicroTimer()
    connection.Refresh
    TestQuery = MicroTimer() - startTime
    connection.BackgroundQuery = True
End Function
Community
  • 1
  • 1
AndASM
  • 9,458
  • 1
  • 21
  • 33
  • Have you tried [profiler](http://www.nullskull.com/a/1602/profiling-and-optimizing-vba.aspx)? The images are down in the official site, but, I saved the site before that, [check this link](http://postimg.org/gallery/30nktztwq/) to get the images – Sgdva Jun 22 '16 at 18:54
  • @Sgdva that profiles VBA code? I'm looking to profile Power Query code. While I'm using VBA do to so, profiling the VBA code itself doesn't help me. That'd be like profiling the profiler. – AndASM Jun 22 '16 at 18:56
  • I see, yeah, it profiles VBA Coding, I misunderstood that you were trying to profile the routines for connections in your code, to look which ones were the slower, my bad. – Sgdva Jun 22 '16 at 19:00
  • What / who is hosting the data you are querying? If you're connecting to an SQL Server then you should use the profiler there: https://www.mssqltips.com/sqlservertutorial/272/profiler-and-server-side-traces/ I am using this all the time for my queries. – Ralph Jun 22 '16 at 19:38
  • 1
    @Ralph I'm using power query to transform flat files into usable data for exploratory analysis. I may ask for help on the specifics of optimizing those queries separately. But here I'm asking about techniques to help figure it out myself. To try and answer the next question: I'm working in a mildly tech-phobic corporate environment. I'm only allowed to use the MS Office suite, no MS Access, and they are afraid of VBA. For some reason Power Query is acceptable. Insane corporate politics. (I can use VBA myself, just not in anything sustained or shared.) – AndASM Jun 22 '16 at 19:59
  • I've never understood why asking how to profile a programming language so a developer can figure out how to improve performance got voted into oblivion. Oh SO, so friendly and helpful. – AndASM May 30 '18 at 23:59

1 Answers1

2

Power Query has a Tracing option which writes detailed info out to trace files. Navigate to Options / Diagnostics to turn it on.

A few people have blogged about using Power Query (what else?) to interpret and analyse those files:

http://excelando.co.il/en/analyzing-power-query-performance-source-large-files/

https://blog.crossjoin.co.uk/2014/12/11/reading-the-power-query-trace-filewith-power-query/

Mike Honey
  • 14,523
  • 1
  • 24
  • 40