pfm.Entity Table - XML

Discussions related to Crystal Reports development.

Moderator: Lisa Ennis

Post Reply
cgriffin
Posts: 22
Joined: Mon Nov 17, 2014 6:14 pm

pfm.Entity Table - XML

Post by cgriffin » Fri Aug 19, 2016 10:47 am

Good morning,

We currently have 89 million records in our pfm.Entity table. We created a custom field for our closed date, as the standard field, if someone was to accidentally set the order back to "In Progress" and then you Close it, it automatically sets the date back to the current date/time. The custom field built for us by SoftPro is an XML field stored in the pfm.Entity table.

We are having the issue of our closed order reports taking an excessive amount of time to run 45+ minutes, especially if we run a range for a year.

Does SoftPro have any suggestions on archiving process for the pfm.Entity table, indexing, or perhaps create a view, or export the data somehow, to access it from a date/time field, oppose to XML?

Any and all suggestions are most welcome and needed!

Please note that I have looked on this forum for other examples of the pfm.Entity and we are joining to the pf.OrderInfo table and not the pfm.Order table.

Thank you,
Colin.

Lisa Ennis
Posts: 77
Joined: Thu Sep 11, 2008 1:57 pm

Re: pfm.Entity Table - XML

Post by Lisa Ennis » Mon Aug 22, 2016 10:48 am

I would suggest trying an indexed view to see if that may be faster for you. Also, if you can limit the records from the pf.OrderInfo table by some means prior to joining to the pfm.Entity table that could help as well. And join on to the pf.OrderInfo table on the rootId's.

Is it an option to use the "FirstClosed" field from the pfm.StatusHistory table? LastClosed in this table could change, but FirstClosed should not.
Lisa Ennis
Report Developer
SoftPro

Post Reply