Cost performance of unoptimized property vs optimized with DataType modification
Hello dear Community,
I'm using Pega 7.3/7.4 and I have a question considering property stored in DataBase.
the context : I need to get a property through a report. This property references the base 64 code to display an pdf and is natively unoptimized. The thing is that this property can contain a very long string (like a length of 270,000 characters). But if I optimize this property, the DataType in DataBase will be in VARCHAR(32).
So here are my few questions :
- Is it possible to optimize a property and then change its Datatype to something able to contain all the information ?
- If it is possible, does changing the datatype deoptimize my property ?
- Finally, what what will be more expensive in term of perfomance : an unoptimized property in the BLOB or an optimized property but with a modifier DataType ?
***Edited by Moderator Marissa to update platform capability tags****
Couple of points:
1. Never expose .pyAttachStream attribute: .pyAttachStream property is supposed to hold the attachment content (in base64 encoding). Once you attach a file to a case, PRPC converts attachment content into string (Base64 enconded) and store that in pyAttachStream property. As attached file size can easily be in MBs, so you can imagine what the max length of this property can be (certainly not 256 or 1024 char :-) ).
The reason in OOTB pc_data_workattach table this property is part of BLOB, as BLOB column can support up to 2 GB content.
Given that you have exposed this property as a separate column, your report def is (in all possibility) returning only a part of attachment content as no way you can expose that much information (varchar 2 in oracle support upto 4000 bytes). You can verify that by looking at the content/length of that property on clipboard page of report result and on corresponding records instance (open that instance of Data-WorkAttach-File instance separately - using Obj-Open).
In other words, your design to expose that property as a column to get the attachment content will not work as PRPC is not retrieving the full content.
2. You can expose that property as CLOB column, as another member suggested, and also do this change in DSS before you can include that attribute in select clause of your report def. While that might work (Honest, I never tried this kind of setup), but your current design would be too much resource-intensive which will impact both your DB server and network traffic between PRPC server and DB server.
Set the below DSS to true.
Owning ruleset: Pega-RULESPurpose: reporting/retrieveFullClobContent
3. If possible, do NOT expose that property, rather switch to a design that would not require that attribute to be exposed.
Keep up to date on this post and subscribe to comments
- Unable to perform property optimization: The containing class is mapped to pr_other.
- UN-optimize for filtering
- Report definition with unoptimized property returns a different property value
- How to find the details db cache size, shared pool size, pga_aggregate_target, sga_max_size, optimizer_index_cost_adj, memory_target
- How to add unoptimized property in Report definition query tab