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?
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)
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
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
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))