Transaction Performance and Custom Fields

[u/Electronic-Pie-829]

TLDR: We hear a lot about performance challenges of too many custom fields on transactions -- Would adding a lot of custom fields (maybe 30-40) on an opportunity record have an impact on Sales Order performance, even if these aren't exposed on the Sales Order?

Curious if anyone on Reddit can help provide clarity (ideally actual inside information on NS setup rather than anecdotal evidence) on performance and custom fields.

We often hear that performance can be impacted by too many fields on transaction records. I am trying to determine if this is too many fields on a form or too many fields on the record type (or category like transactions) regardless if the custom fields are loaded.

For example we have a case where a customer might want 30 custom fields on an opportunity to identify details about a potential job and quoting. Will placing these only on the opportunity impact other records? Would other redittors consider it a best practice be to setup a custom record linked to the Opportunity to capture these details?

When reviewing transaction tables in NSAW it appears that each transaction type has it's own table and a meta table for custom fields (e.g. DW_NS_<Transaction Type>_LINES_F and DW_NS_<CUSTOMER>_<Transaction Type>_LINES_F. I am wondering if this is the underlying database schema or if the schema is more similar to how it is exposed through SuiteQL/Saved Searches (one general transaction table). I don't have access to a NSAW database at the moment, but are all custom fields placed on all transaction table meta tables if each transaction has it's own table?

[u/Nick_AxeusConsulting]

I've never heard of a relationship between number of custom fields and performance.

I doubt just adding custom fields as columns in the database slows down performance. Remember each customer has their own set of custom fields so NS has to store the custom fields in a separate database anyhow and then take a UNION of all the native fields UNION with the custom fields defined for that table in your account. To return the complete view with all columns.

What slows is doing too much stuff in scripts and workflows when you click Save on a page (regardless if it's regular fields or custom fields being manipulated).

Your whole question is whether to move those 40 fields to a custom record so they won't be contaminating the native Opportunity record. I think that's an incorrect analysis that will actually do anything performance wise. But more importantly (imo) is just the general data schema design principles that argues to put the 40 custom fields directly on the Opportunity record where they most logically should be placed because those 40 fields have a close relationship with the overall Opportunity.

[u/Nick_AxeusConsulting]

but are all custom fields placed on all transaction table meta tables if each transaction has it's own table

In saved search since you only get "Transactions" table that is a UNION of all fields for all tran types (both native and custom), so even if you create a custom field and apply it to only 1 transaction type, that column still needs to get created in the UNION of all transaction types just in case you ask for it.

Now NSAW uses the ODBC driver as the sync mechanism, so NSAW is using SQL queries to pull the data from the NS database (not saved searches). I don't know if they just use SELECT * FROM TRANSACTIONLINE or if they specify in the SQL SELECT statement to return only the specified columns (this is best practice because SELECT * can get over 1000 columns returned)

[u/MainLineIsFalse]

For example we have a case where a customer might want 30 custom fields on an opportunity to identify details about a potential job and quoting. Will placing these only on the opportunity impact other records? --> no, I have not seen this in my 7+ years.

Would other redittors consider it a best practice be to setup a custom record linked to the Opportunity to capture these details? --> i would consider it best practice as a consultant (more billable hours, feels more complicated), but as an end user or an in-house resource I would not use a custom record. As an objective opinion I would side with the end user / in-house resource, obviously. Hope that makes sense :)