Is Reading from blob deprecated?
Does pega deprecate reading data from blob in report definitions? If yes, then why does pega provide PR_READ_FROM_STREAM, PR_READ_DECIMAL_FROM_STREAM and PR_READ_INT_FROM_STREAM functions in the first place?
***Updated by moderator: Lochan to update platform capability***
Accepted Solution
Pegasystems Inc.
US
As Lawrence says the second query is more just a general performance problem and you need to look at generating explain plans and adding the appropriate indexes to get that to perform better.
The UDF one you are probably not going to be able to get to perform any better. If this is a report that you are going to run during off hours or not very often you might be fine with the fact that it takes 20 minutes to execute. But if that is something that is run often, and during peak times, that query is going to cause the entire system to be slow. Plus depending on the database and the size of the blobs involved can lead to memory issues on the database server.
Pegasystems Inc.
FR
Hello,
I think you can still read data from BLOB on report definition. You might not be able to use a non exposed column on criteria but reading from BLOB should still be possible I think.
Pegasystems Inc.
FR
Well yes and no. I wouldn't say "violate best practices", it isn't ideal, you will surely get a warning for this. This might significant for performance of your report especially after few years in Production.
In an ideal world you will always report on exposed columns, never on BLOB.
Pegasystems Inc.
US
Using the UDFs are best in development when you are still trying to determine what properties you need in reports and want to expose. Use of UDFs in production is not recommended and should be limited to only reports that run occasionally or during non-peak hours as there can be a significant performance issue using UDFs.
UnitedHealthcare India PVT Ltd
IN
I have one report which takes around 20 min with data from blob and atleast 1 hour(the query is still running while I'm writing this comment) when I make some alternate joins to fetch the same data. This is the observation from dev. So, as I see it, blob might take time but join is taking even more time to fetch the data.
PEG
GB
I think this may be an instance of a performance problem. But I can't see why it would be slower in the long run*
* = If you've just optimized a column you'll need to verify that it's been populated in the database.
Also, if the purpose of optimizing the column is you need to use it for joins or in the search criteria, then (depending on the data involved) it's often beneficial to add the new column to an index (either singularly or part of a compound index).
Post expose, I'd be inclined to run the report with, say, tracer enabled (and with DB Query event being monitored). Then examine the SQL and use a native SQL tool to try to determine how it's executing (Explain Plan).
This may help to determine if further indexes will be beneficial.
Accepted Solution
Pegasystems Inc.
US
As Lawrence says the second query is more just a general performance problem and you need to look at generating explain plans and adding the appropriate indexes to get that to perform better.
The UDF one you are probably not going to be able to get to perform any better. If this is a report that you are going to run during off hours or not very often you might be fine with the fact that it takes 20 minutes to execute. But if that is something that is run often, and during peak times, that query is going to cause the entire system to be slow. Plus depending on the database and the size of the blobs involved can lead to memory issues on the database server.