Query performance

[csebe12]

Hi,

I have been doing some quick performance analysis using PLEXOS 8.3 on:

• Query
• QueryToList

I extracted hourly load data on 29 regions across a single year (producing 253344 rows).

My results for running the query and converting the result into a pandas dataframe are the following:

Method	Time
Query	20.4 s ± 466 ms
QueryToList	46.4 s ± 266 ms

I would like to understand why you changed to using QueryToList and what results you got for its performance.

[JulianAtEnEx]

I can offer some insight into this, though a proper explanation of your results might require examination of your code.

The change from Query to QueryToList is based on some testing I performed with a large solution. The Query() function uses a Recordset which is necessary for COM and Excel users. But a Recordset is slower in general. In my tests, isolating just the duration of Query() vs QueryToList(), I recorded times of 3:00 minutes for Query() and 1:57 for QueryToList().

The next measurement involved is the time to import the output of each of those calls into a Pandas dataframe. To load a Resultset into a dataframe using the code that existed prior to this change, my test recorded 15:18 minutes. To load the output of QueryToList() took 13:46.

One reason that loading the output of QueryToList() into a dataframe may be slower than loading a Resultset is the call to GetProperty(). In the underlying .Net API this function has an overload, one taking a string argument, the other taking an integer argument. It seems that Pythonnet is slow at determining which overload to use. But Pythonnet has provided a way to explicitly tell it which overload to use. So in my recent commit I have added the Overloads[String] syntax to the call to GetProperty(). Without this you do find a significantly slower import of QueryToList() output into a dataframe.