\documentclass[conference]{IEEEtran}
\IEEEoverridecommandlockouts
% The preceding line is only needed to identify funding in the first footnote. If that is unneeded, please comment it out.
\usepackage{cite}
\usepackage{amsmath,amssymb,amsfonts}
\usepackage{algorithmic}
\usepackage{graphicx}
\usepackage{makecell}
\usepackage{textcomp}
\usepackage{xcolor}
\PassOptionsToPackage{hyphens}{url}\usepackage{hyperref}
\def\BibTeX{{\rm B\kern-.05em{\sc i\kern-.025em b}\kern-.08em
    T\kern-.1667em\lower.7ex\hbox{E}\kern-.125emX}}
\begin{document}

\title{Database Index Costs in the Cloud-Based Multitenant Architecture on the Salesforce Platform\\

}

\author{\IEEEauthorblockN{Stanisław Zań}
\textit{Polish-Japanese Academy of Information Technology}\\
Warsaw, Poland \\
zanstaszek9@gmail.com}


\maketitle

\begin{abstract}
This paper is a study on how in the multitenant cloud architecture of the Salesforce platform, different field configurations available to administrators impact database index usage, query costs, and data manipulation language operations. Results show that while "External ID" and "Unique" settings consistently create indexes with varying performance effects, the "Required" flag unexpectedly affects query costs in a meaningful manner. Results highlight the need for case-by-case tuning using the Salesforce Query Planner.
\end{abstract}

\begin{IEEEkeywords}
database indexes, Salesforce, multi-tenant architecture, database tuning, CRM
\end{IEEEkeywords}

\section{Introduction}
Database tuning is one of the techniques used to achieve better performance and lower response time in the running software. In relational-based SQL databases, it requires from tuning person a deep understanding of the underlying database engine and its optimizers, as well as the end users' use cases and what operations they perform in the application.

Salesforce is a Custom Relationship Management (CRM) application, running in a cloud environment using a multi-tenant architecture, which means that servers' resources are shared between multiple running users at the same time.

The database that the end-user operates with does not have a traditional structure with tables representing real-world entities, but it's being virtualized on demand, based on the stored metadata, data and pivot tables\cite{b1}.

\begin{figure}[htbp]
\centerline{\includegraphics[width=\linewidth]{fig1.png}}
\caption{Metadata architecture in the Salesforce Platform \cite{b1}}
\label{fig}
\end{figure}


This approach makes the traditional performance-tuning method not suitable to implement on the Salesforce instance. 

The aim of this paper is to look into how field's different configuration options, available for the Administrator in the Setup panel, can affect database indexes, their influence on the query costs, and Data Manipulation Language (DML) operations' markup.

\section{State of the Art}
Any work that identifies and describes the approach of tuning multi-tenant systems from the End-User in an administrative role could not be found. There exist software documentation and guidelines\cite{b2,b3,b4}, but they are targeting the database designers and architects who create and maintain those systems from the supplier side.

\section{Methodology}
Tests were conducted using 1.5 million rows of randomly generated data using Faker's\cite{b5} library. Standard Account object with private sharing settings and no additional functionalities enabled was used. Fields of different datatypes and configurations were tested, clearing the database between each of the configuration tests. Insertion and deletion operations were performed using Salesforce's Bulk API 2.0\cite{b6}, and their time was taken from Setup's Bulk Data Jobs view. Indexes performance were retrieved from the Salesforce Query Planner\cite{b7} using a dedicated REST endpoint\cite{b8} on the Salesforce platform. Query Costs were retrieved for different types of queries, as proposed in the Database Tuning book\cite{b9}.

Salesforce Production Trial instance was used, received by requesting Feedback Management Free Trial\cite{b10} and deleting all preinstalled customizations. The database server type was running on the Amazon Web Services (AWS) infrastructure, called Hyperforce\cite{b11}.

\section{Results}
\subsection{DML Times}
Bulk API 2.0 does not allow using insert operation, providing upsert instead, however without existing database and present ID in the input file, upsert works exactly as insert and it will be named as it later on the paper.

Inserting and deleting records using Bulk API 2.0 showed significant differences in execution time depending on the field configuration. Fields marked as Required in most cases resulted in slower DML operations. This was especially noticeable for fields of type Text and Email, where required validation added a hidden computational cost.

On the other hand, configurations involving External ID and Unique options often performed better than expected, with some indexed fields showing shorter insertion times than non-indexed ones. This contradicts the common assumption that indexing introduces overhead for write operations.

\begin{table}
\centering
\caption{The most significant comparison of insertion times between indexed and not-indexed configurations on Standard Account Object}
\begin{tabular}{|l|r|}
\hline
\textbf{FieldApiName} & \textbf{Diff to No Index} \\
\hline
Email\_required\_\_c & -11.66\% \\
Number\_short\_externalId\_nulls\_\_c & -9.36\% \\
Number\_short\_unique\_external\_\_c & -6.39\% \\
Email\_unique\_external\_\_c & -6.28\% \\
Number\_short\_all\_\_c & -5.99\% \\
Number\_short\_unique\_\_c & -5.63\% \\
Email\_externalId\_\_c & -5.14\% \\
Email\_unique\_\_c & -5.09\% \\
\hline
Text\_autonumber\_unique\_extId\_case\_sensi\_\_c & 5.32\% \\
Number\_short\_unique\_required\_\_c & 6.56\% \\
Text\_email\_unique\_case\_insensitive\_\_c & 6.57\% \\
Text\_autonumber\_required\_externalId\_\_c & 7.10\% \\
Text\_autonumber\_unique\_req\_case\_insen\_\_c & 8.20\% \\
Text\_unique\_required\_insen\_\_c & 8.34\% \\
Text\_autonumber\_unique\_req\_case\_sensi\_\_c & 11.68\% \\
Text\_unique\_required\_sensi\_\_c & 12.56\% \\
Text\_email\_required\_\_c & 49.02\% \\
Text\_email\_required\_externalId\_\_c & 51.93\% \\
Text\_email\_unique\_required\_insen\_\_c & 52.42\% \\
Text\_email\_all\_case\_insensitive\_\_c & 53.51\% \\
Text\_email\_unique\_required\_sensi\_\_c & 53.85\% \\
Text\_email\_all\_case\_sensitive\_\_c & 54.04\% \\
\hline
\end{tabular}
\end{table}

\begin{table}
\centering
\caption{The most significant comparison of deletion times between indexed and not-indexed configurations on Standard Account Object}
\begin{tabular}{|l|r|}
\hline
\textbf{FieldApiName} & \textbf{Diff to No Index} \\
\hline
Text\_email\_required\_\_c & -21.03\% \\
Text\_email\_all\_case\_insensitive\_\_c & -18.39\% \\
Text\_email\_all\_case\_sensitive\_\_c & -18.31\% \\
Text\_email\_required\_externalId\_\_c & -17.81\% \\
Text\_email\_unique\_required\_sensi\_\_c & -17.77\% \\
Text\_email\_unique\_required\_insen\_\_c & -17.58\% \\
Email\_required\_\_c & -5.55\% \\
\hline
Text\_unique\_case\_insensitive\_\_c & 5.01\% \\
Email\_unique\_required\_\_c & 5.18\% \\
Text\_unique\_required\_sensi\_\_c & 5.43\% \\
Number\_externalId\_nulls\_\_c & 5.56\% \\
Text\_unique\_externalId\_sensi\_\_c & 6.06\% \\
Email\_externalId\_nulls\_\_c & 6.22\% \\
Text\_autonumber\_unique\_extId\_case\_sensi\_\_c & 7.27\% \\
Text\_unique\_required\_insen\_\_c & 8.10\% \\
Text\_autonumber\_unique\_req\_case\_sensi\_\_c & 10.58\% \\
\hline
\end{tabular}
\end{table}

\subsection{External ID, or Unique, or both}
Field configurations using the External ID or Unique options provided an indexing benefit, as observed through the Query Plan Operation Type and costs. Fields with either configuration in most cases appeared with the lower cost units in SELECT queries, confirming that an index was indeed in use. 

In all cases, External ID and Unique results were different from each other. The combination of both of them yielded another result, which could be higher or lower than a single configuration of them. On every datatype used, different combinations of External ID and Unique were most optimal.

In text fields, two different types of Unique are available - Case Sensitive, and Case Insensitive. During testing, both of them resulted in different query costs and DML times.

The behaviors above confirm that these configurations are treated differently by the database's internal engine and query optimizer, and no general rule emerged during the testing. Each case in the field configuration should be tuned with Query Planner, considering all possible combinations, to get the most performative results.

\subsection{Required fields}
Fields configured as Required did not generate an index, which follows the Salesforce's documentation. However, the Required flag had a measurable impact on performance, on both queries and in the DML operations. In most cases, adding the Required configuration to the field resulted in higher query costs, but it is not a strict rule.

\begin{table}[htbp]
\caption{Example result with the comparison of query cost between setting up a Required configuration on not indexed field on Standard Account Object}
\begin{center}
\begin{tabular}{|p{3.5cm}|c|r|c|}
\hline
\textbf{Query} & \makecell{\textbf{Email, }\\\textbf{No Config}} &  \makecell{\textbf{Email, }\\\textbf{Required}$^{\mathrm{a}}$} & \textbf{Diff} \\
\hline
Query all & 0.67731113 & 1.06404114 & 57.1\% \\
Point Query & 0.67731113 & 0.9255551 & 36.65\% \\
Point Query on not existing records & 0.67731113 & 0.9255551 & 36.65\% \\
Multipoint Query & 0.67731113 & 0.9255551 & 36.65\% \\
\hline 
Range Query on $<$ 'rebeccakrueger@gmail.com' & 0.67731113 & 1.06404114 & 57.1\% \\
Range Query on $>$ 'margarethenson@gmail.com' & 0.67731113 & 1.07996657 & 59.45\% \\
Range Query on $<$ 'margarethenson@gmail.' & 0.67731113 & 1.06404114 & 57.1\% \\
Range Query on $>$ 'margarethenson@gmail.' AND $<$ 'rebeccakrueger@gmail.com' & 0.67731113 & 0.98494413 & 45.42\% \\
\hline
\multicolumn{4}{l}{$^{\mathrm{a}}$Making field \textit{Required} had detrimental effect on the Index cost.} \\
\end{tabular}
\label{tab:required-impact}
\end{center}
\end{table}

Thus, while the Required setting should be used to improve and maintain the data integrity, it impacts the query and DML performance, introducing unexpected build-up in high-volume scenarios.

\section{Conclusion}
This paper discusses three field configurations and their effect on database index costs in the Salesforce platform. The "Required" configuration does not create an index, but it affects data manipulation time and query costs, making it worse in most of the cases. The negative effect of the field's requirements on the SELECT statement is an unexpected result, because it should have either a positive effect\cite{b12,b13}, or no effect at all. 

Both "External ID" and "Unique" field configurations create indexes, but their query costs and DML performance are not the same. Mixing two of the configurations resulted in another outcome, different from the alone configurations. It implies that these indexes are treated differently by Salesforce's database engine. The exact influence or pattern on the performance could not be determined during the testing. 

\begin{table}[htbp]
\caption{Recommended Index on Object Type - Unique, External ID or both. Required configuration was not accounted.}
\begin{center}
\begin{tabular}{|c|c|}
\hline
\textbf{Field} & \textbf{Standard} \\
\hline
Email & Unique + External ID \\
Text & Unique Insensitive + External ID \\
Number short & Unique + External ID \\
Number long & Unique \\
\hline
\end{tabular}
\label{tab3}
\end{center}
\end{table}

Besides, some indexed configurations had better insertion times than the non-indexed ones, which is another unexpected result.

Following the above, it is hard to predict the effect of the field's configuration on Salesforce's platform performance, therefore any index tuning should be investigated with the Query Plan tool, and all the possible combinations should be considered to achieve the best performance.

\bibliographystyle{IEEEtran}

\begin{thebibliography}{00}
\bibitem{b1} "Multitenancy and Metadata Overview | Best Practices for Deployments with Large Data Volumes | Salesforce Developers”. Available: \url{https://developer.salesforce.com/docs/atlas.en-us.salesforce\_large\_data\_volumes\_bp.meta/salesforce\_large\_data\_volumes\_bp/ldv\_deployments\_concepts\_multitenancy\_and\_metadata.htm}
\bibitem{b2} "The Force.com Multitenant Architecture”, Understanding the Design of Salesforce.com's Internet Application Development Platform. Available: \url{https://www.developerforce.com/media/ForcedotcomBookLibrary/Force.com\_Multitenancy\_WP\_101508.pdf}  
\bibitem{b3} "Database Concepts”. Available: \url{https://docs.oracle.com/database/121/CNCPT/cdbovrvw.htm\#CNCPT89234}  
\bibitem{b4} F. Chong, G. U. Carraro, i R. Wolter, "Multi ‐ Tenant Data Architecture June 2006”, 2016. Available: \url{https://www.semanticscholar.org/paper/Multi-%E2%80%90-Tenant-Data-Architecture-June-2006-Chong-Carraro/9a0c3466190d83fb5fbc18730413b232f3fdae06}  
\bibitem{b5} Faker: Faker is a Python package that generates fake data for you. Python. Available: \url{https://pypi.org/project/Faker/26.3.0/}  
\bibitem{b6} "Bulk API 2.0 | Bulk API 2.0 and Bulk API Developer Guide | Salesforce Developers”. Available: \url{https://developer.salesforce.com/docs/atlas.en-us.api\_asynch.meta/api\_asynch/bulk\_api\_2\_0.htm}  
\bibitem{b7} "Developer Console Query Plan Tool FAQ”, Salesforce. Available: \url{https://help.salesforce.com/s/articleView?id=000386864\&language=en\_US\&type=1}  
\bibitem{b8} "Query Performance Feedback (Beta) | REST API Developer Guide | Salesforce Developers”. Available: \url{https://developer.salesforce.com/docs/atlas.en-us.api\_rest.meta/api\_rest/resources\_query\_performance\_feedback.htm}  
\bibitem{b9} D. E. Shasha i P. Bonnet, Database tuning: principles, experiments, and troubleshooting techniques, [Rev. ed.]. w Morgan Kaufmann series in data management systems. San Francisco, Calif.: Morgan Kaufmann Publishers, 2003. Available: \url{https://search.ebscohost.com/login.aspx?direct=true\&scope=site\&db=nlebk\&db=nlabk\&AN=81854}  
\bibitem{b10} "Try Salesforce Feedback Management for Free”. Available: \url{https://developer.salesforce.com/free-trials/comparison/feedback-management}  
\bibitem{b11} "Introducing Hyperforce - General Information \& FAQ”. Available: \url{https://help.salesforce.com/s/articleView?id=000388902\&type=1}  
\bibitem{b12} "Performance Benefits of NOT NULL Constraints on Foreign Key Reference Columns”. Available: \url{https://www.cockroachlabs.com/blog/performance-benefits-of-not-null-constraints/}  
\bibitem{b13} J. Darnell, "Answer to 'Is the query optimizer able to optimize away IS NOT NULL conditions if the column has a NOT NULL constraint?'”, Database Administrators Stack Exchange. Available: \url{https://dba.stackexchange.com/a/274780}
\end{thebibliography}

\vspace{12pt}


\end{document}