1 Reply Latest reply on May 16, 2018 6:02 AM by Roger Palmen

    How can PI OLEDB Enterprise get fast query results from SAP HANA through PI AF Data References?

    isolutions

      PI OLEDB Enterprise performs very poorly when loading data from ODBC (SAP HANA), with either a Table Lookup or a CDR.

      • With a Table Lookup, the performance is unusably slow. In System Explorer's "Time Series Data", it takes 2.2 seconds to load 10 rows, and 3.4 s for 1000 rows. In PI OLEDB, the query times out.
      • Using a CDR is faster in System Explorer (0.6 s for 10 rows, 0.8 s for 1000 rows), but the performance is still unacceptable in PI OLEDB.

       

      While the performance in System Explorer is reasonable, the performance in PI OLEDB is not:

      • If I run a PI SQL Commander query for the table lookup attribute and then the CDR attribute, they both take a long time to run (at least 20 seconds), so I tried reversing the order.
      • If I run a query for the CDR attribute first, it takes about 8 to 12 seconds to return 7 rows. The Table Lookup query times out after 81 s, but it takes about 8 to 12 seconds in subsequent runs (possibly caching).
      • If I double the number of rows, the execution time doubles.

       

      My CDR overrides GetValue, GetValues, RecordedValue, and RecordedValues in AFDataReference. Logging shows that PI System Explorer uses GetValue to get a single value and RecordedValues for the "Time Series Data" dialog, but PI OLEDB makes a call to GetValue per row in the result. Even with the SupportedMethods flags overriden to "GetValues" instead of "GetValue | GetValues", PI OLEDB still seems to call GetValue.

       

      How can we improve the performance? Can we make it use GetValues instead of GetValue?

       

       

      Appendix

      PI SQL Commander Query

      The other query is the same with a column name changed to "\Totals\Steam Injection".

      SELECT eh.Path, eh.Name Element, tir.Time, tir."\Totals\Steam Injection Slow"
      FROM [<redacted>].[Asset].[ElementTemplate] et
      INNER JOIN [<redacted>].[Asset].[Element] e
      ON et.ID = e.ElementTemplateID
      INNER JOIN [<redacted>].[Asset].[ElementHierarchy] eh
      ON e.ID = eh.ElementID
      INNER JOIN [<redacted>].[DataT].[ft_TransposeInterpolateRange_Injector Well] tir
      ON eh.ElementID = tir.ElementID
      WHERE et.Name = N'Injector Well'
      AND tir.StartTime = DATE(N't-7d')
      AND tir.EndTime = DATE(N't')
      AND tir.TimeStep = N'1d'
      AND eh.Path  = '\<redacted>\Offsites\<redacted>\'
      AND eh.Name = 'I01'
      OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)
      

       

      CDR Queries

      GetValue

      SELECT TOP 1 "Production Date" PITimestamp, "Allocated CWE of Steam m3" PIValue FROM <redacted> WHERE "Production Date" <= ? AND "Well Code"='<redacted>' ORDER BY "Production Date" DESC
      

       

      RecordedValues

      SELECT "Production Date" PITimestamp, "Allocated CWE of Steam m3" PIValue FROM <redacted> WHERE ("Production Date" >= ? AND "Production Date" <= ? AND "Well Code"='<redacted>') ORDER BY "Production Date" DESC
      

       

      PI AF Table

      Attribute Config String (Table Lookup)

      SELECT [Allocated CWE of Steam m3] FROM [<name of PI Table>] WHERE [Well Code] = '<redacted>';TC=Production Date
      

       

      Connection string

      SERVERNODE={<redacted>:30015};DRIVER={HDBODBC};UID=<redacted>;PWD=<redacted>;
      

       

      Query

      SELECT 
      "Well Code",
      "Property",
      "Facility Area",
      "Well Name",
      "Well Pad",
      "Well Type",
      "Production Date",
      "Meter On Strm Hours",
      "Allocated Oil Volume m3",
      "Allocated Gas Volume e3m3",
      "Allocated Water Volume m3",
      "Allocated CWE of Steam m3",
      "Brackish Water Volume m3",
      "Measured Water Volume m3",
      "Fresh Water Volume m3",
      "Inj On Strm Hours"
      FROM <redacted> WHERE 
      ("Production Date" > ADD_DAYS(NOW(),-365) and "Production Date" <= ADD_DAYS(NOW(),-1))
      
        • Re: How can PI OLEDB Enterprise get fast query results from SAP HANA through PI AF Data References?
          Roger Palmen

          Looking at the numbers, the source of the problem is getting data from HANA is slow in the first place. And as PI OLEDB is notoriously difficult to tune, that will only increase the load.

           

          First i would check the caching settings. If you do repeated calls that could help a LOT, but it won't help in the refresh.

          Secondly, if i have latency issues to retrieve data from a remote system, i typically resort to using a caching table in SQLserver and read that in PSE. That also removes a dependency from your system so that helps in stability. This looks like daily data, so it can't be that much data, and updating your cache table once every hour is not that big of a deal.