Custom Fields and Performance

[SBarnes]

We have a customer where the number of custom fields on inventory is getting quite large due to required functionality with external systems and data that needs to be stored against each product.

My concern although they have not complained yet is how will this effect performance given they already have 60 odd custom fields on inventory, particularly on save for instance I would assume given the custom values is a collection does it save asynchronously and only those values that have changed?

[Mike.Sheen]

particularly on save for instance I would assume given the custom values is a collection does it save asynchronously and only those values that have changed?

No - custom field values collections do not save asynchronously, but they do only save modified values.

Unless you were consistently modifying pretty much all of those 60 custom field values, I doubt asynchronously saving the custom field values would bring much benefit - unless there was considerable latency between the SQL Server and the client.

It would be pretty easy to measure by logging a profiler trace to table and then performing some analysis on the logged results. One trap with logging profiler traces to file or table is the duration will be in microseconds - whereas when just viewing in the profiler application duration is in milliseconds.

[pricerc]

Jiwa is one of two ERP packages that consume my working life.

The other product has been around since the 70's and was originally written in COBOL on some UNIX flavour. So it has some baggage. And until the latest major release, that included support for data in ISAM instead of SQL.

They used to store all their custom data in one big data table: From memory, the columns were something like: FormCode char(6), FieldCode char(6), KeyValue char(100), DateValue datetime, NumericValue decimal(18,6), AlphaValue char(255).

There were all sorts of things wrong with that, especially performance, and we used to do anything we could to discourage people from using them.

But a few years ago, with the introduction of their previous major release, they came up with a new style of custom data, where each business object that accepts custom forms can create a custom SQL table, with actual SQL columns for each custom field. We no longer hesitate to put in custom data on these systems. They decided to us a special character to identify the custom form fields, which was a bit sucky (always have to quote the table name in SQL), but they work well.

That release had a system-wide setting for which style of custom form you were using: 'old' or 'new', with a one-way 'migration' tool.

Jiwa's current system is already pretty good, in that the custom field schema is well defined, and available for most business objects. And well encapsulated in a generic 'custom data' library.

But y'all would gain a fair bit of performance if you could figure out how to change those schemas into columns in a table instead of having to 'unpivot' the data for consumption outside of Jiwa.

I understand that having plugin-specific custom data (which is cool) would complicate this, but I wouldn't have thought too badly - you could assign an abbreviation to each plugin, and prefix column names with that.

[SBarnes]

Hi Ryan,

Having dealt with another ERP and how it deals with custom field in that it simply adds the fields to the main table but prefixes them with U_, and then has a series of supporting metadata tables such as valid value and links to other tables I can tell you comes with a separate set of issues.

I don't think unfortunately any system is able to foresee what the data need is going to be needed and handle it efficiently in a generic way and on the whole I think Jiwa's system is pretty good, the issue of the data being held in a sperate table on the whole is not an issue as you can usually create views to get the data back into one row although this does add some development overhead but the one I would like to see change is 255 character limit as it means you can't use custom fields for any sort of longer text fields or say for something like an image but possibly doing this could create some other issues.

Usually for anything outside the 255 character I have had to go and create a separate table which in terms of the cost to a customer compared to the ease of a custom field often rules it out, what would be really good but may be not economically viable for Jiwa was if there was a way to have a custom field that linked to a separate table either for the look up of values or for the actual data even if this mean you have to provide some sort of mechanism such as stored procedures to read and write to the table that followed some sort of convention.

[pricerc]

simply adds the fields to the main table but prefixes them with U_

Yeah, I wouldn't be a fan of that.

The system I'm talking about has separate tables, like Jiwa's, just it adds custom fields as columns, instead of having one generic column.

Administration of it would be a bit trickier than the current system, but using them is much easier. And the performance is MUCH better.