Query performance #12

Activity

1. JulianAtEnEx commented on May 31, 2023

   

   JulianAtEnEx

   on May 31, 2023

   Contributor

   More actions

   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.