User-Defined Financial Functions for MS SQL Server

org


I. INTRODUCTION
Aggregate queries over big economic relational databases, prepared with using of Structure Query Language (SQL) and special programs belong to the most used tools in the area of Business Intelligence (BI) and Data Analytics (DA).One of the most important Relational Database Management Systems (RDBMS) for data saving, processing and analyzing, in the area of huge corporate or financial databases, is MS SQL Server.SQL Server runs on Transact -SQL (T-SQL), a set of programming extensions, that add several features to standard SQL, including transaction control, error handling, row processing and declared variables.SQL Server also allows stored procedures to be defined.Functions are a special type of stored procedures.They accept parameters, perform some sort of action and return a result.Functions do all of this with no side effects [14].As Simhadri, V., at all said [12], queries containing user-defined functions (UDFs) are widely used, since they allow queries to be written using a mix of imperative language constructs and SQL, thereby increasing the expressive power of SQL; further, they encourage modularity, and make queries easier to understand.Writing user-defined functions or stored procedures presents common way in application development using a relational database management system.It allows to embed application code inside of RDBMS [15].SQL Server provides numerous types of built-in scalar functions, for example, there are many builtin mathematical functions, date functions, string functions or aggregate functions.The types of user-defined functions (UDFs), that SQL Server supports, are scalar (return a single value) and table-valued (return a table).Most commercial SQL database systems support user-defined functions that can be used in WHERE clause filters, SELECT list items, or in sorting/grouping clauses.Often, user-defined functions are used as inexact search filters and then the filtered rows are sorted by a relevance measure [8].Running analytics computation inside a database engine through the use of UDFs (User Defined Functions) has been investigated, but not yet become a scalable approach due to several technical limitations.One limitation lies in the lack of generality for UDFs to express complex applications and to compose them with relational operators in SQL queries.Another limitation lies in the lack of systematic support for a UDF to cache relations initially for efficient computation in multi-calls.Further, having UDF execution interacted efficiently with query processing requires detailed system programming, which is often beyond the expertise of most application developers [4].Ordonez, C., at all described in [10], [11] vector and matrix operations programmed with UDFs in a relational DBMS and a data mining system based on SQL queries and UDFs for relational databases.Sousa, M., at all [13] dealt with consolidation of queries with UDFs.
UDFs can also be used in Excel.Lester in [6] recommended them as alternative methods to perform duct calculations.

II. OBJECTIVE AND METHODS
In SQL Server, any financial functions are not offered for financial data processing, how such as in program MS Excel.This fact we felt like a big shortage in processing of financial data.Because of this problem, we decided to prepare main financial functions, available in program MS Excel, also in MS SQL Server with using of program extension T-SQL.There were particularly financial functions: for calculation of the future value of an investment based on a constant interest rate, for returning the number of periods for an investment based on periodic, constant payments and a constant interest rate, for calculation of the payment for a loan based on constant payments and a constant interest rate, or for calculation of the present value of a loan or an investment, based on a constant interest rate.The concepts of financial mathematics are described in sources [1] - [3], [9].
Finally we compared the speed and efficiency of work with classical formulas and UDFs in SQL Server 2012 with using of special tools -Execution plan and Client Statistics.Execution plans display how the database engine navigates tables and uses indexes to access or process the data for a query or other DML (Data Manipulation Language) statement, such as an update [7].This graphical approach is very useful for understanding the performance characteristics of a query.www.ijacsa.thesai.orgClient Statistics is SQL Server data tool which is very helpful in determining the statistics that how much data received from server to the client side.It means, client statistics helps in analyzing the traffics load like packets/bytes sent and received at clientserver side.When we run a script or query in T-SQL editor, we can enable Client statistics to collect statistics like application profile, time statistics and network statistics which help in checking the efficiency of the script.

III. RESULTS AND DISCUSSION
In the following, we approached the basic knowledge and relationships of interest and rent numbers that we later used.We only dealt with compound interest when the interest is added to the original capital and the sum is further capitalized.In all considerations, we considered overdue (decursive) capitalization, in other words -interest paid at the end of the interest period.Throughout the text, we used the following symbols in Table I.We calculated the future value of the initial capital at compound interest over n years based on the following formula by [5].
In practice, it is common that the interest rates are considered more often than once a year, and then we talk about compound interest capitalization with conversions.We call the period between the two following interest charges conversion.
Interest is generally charged m -times annually.The future value of capital in compound interest with conversions in n years was determined based on the following formula by [5] .
In real-life economics, we often encounter a system of regularly repeated payments.This sequence of regularly repeated payments is called a rent or cash flow.In this analysis, we dealt only with constant, unconditional, temporary, immediate-term rents (cash flows).With constant rent, the amount of the individual payments does not change (remains unchanged).Unconditional or sure rent is a rent, where individual rent payments are not subject to any conditions.Temporary or terminal rent has the finite number of payment.We are talking about immediate rent, if the first payment is made at the beginning or end of the first rent period.When considering a p -term rent, p determines the number of payments per year.If the payments are always made at the end of the time period, we are talking about a strenuous (overdue, post-term) rent, if the payments are always made at the beginning of the period, we are talking about the pre-term rent.The future value of an annuity (rent payments, cash flow) is the sum of the future values of all annuity payments calculated at the end of the n -th year.
For the future value of a p -term strenuous (overdue, post- term) rent after years, the following formula applies by [5] .
The future value of a p -term rent, provided a pre-term (pre-paid) payment after n years was determined based on the following formula by [5] .
We were interested in the future value of the initial capital provided, that we regularly deposited payments to the initial capital p -times per year for n years with annual interest rate i and m conversions per year.

A. Future Value of a Series of Payments
At first we considered the overdue (post-term) rent, so we paid the payments at the end of p -th of the year each year.
Next we considered the pre-term rent, so we paid the instalments at the beginning of p -th of the year each year.

B. Present Value of a Capital
From the formulas for the future value FV (2), ( 3), (4), we expressed the initial deposit which is needed in the periodic payments to accumulate the future value using the following expressions in Table III.

C. Payment based on Regular Constant Payments
We determined the amount of a regular instalment PMT which would give us with the initial deposit PV after n years a future value FV .It was enough to express from formula for future value FV (2), ( 3), ( 4), p -term payment after n years to express the unknown PMT and we have got the following formulas in Table IV.

D. Number of Years
The same, on the basis of previous formulas (2), ( 3), (4), we have expressed a number of years n which are needed on obtaining of the future value FV .The corresponding formulas are as follows in Table V.

E. Number of Payments per Year
By expressing the number of payments per year p from future value formulas (2), ( 3), (4), we obtained the following formulas in Table VI.

F. The Comparison of Classical Calculations and
Calculations with using UDFs For comparison the speed and efficiency of work with classical formulas and UDFs we decided to use a training financial database with 1 048 575 records in DBMS MS SQL Server 2012.Firstly we run query with using of classical formula for calculation of Future Value and next with UDF Future Value.We also controlled Execution plans and Clients Statistics for this queries.
The Execution Plan consists of different operations and each operation has one output which is called the result set.The operations can have one or more inputs.There are many potential ways to execute a query thus SQL Server has to choose the most beneficial one.Client statistics helps in analyzing the traffics load like packets/bytes sent and received at clientserver side.When we run a script or query in T-SQL editor, we can enable Client statistics to collect statistics like application profile, time statistics and network statistics which help in checking the efficiency of the query.
But there is significant difference in Client processing time.This attribute is almost 2,37 times higher for the UDF than for the Classical formula.

IV. CONCLUSIONS
The comparison classical calculations and calculations with using UDFs showed, that UDFs don't bring acceleration of the computation process opposite to the classical formulas.On the contrary, with using of the UDF the calculation process takes longer.However, working with them is simpler and more comfortable than with classical formulas, because they encourage modularity and make queries easier to understand.This is main reason they are popular among users in financial area.Stored procedures and UDFs can be prepared also in other extended DBMS, such as Oracle (Pragma UDFs), mySQL, PostgreSQL, DB2, Informix, etc.

Fig. 2 .
Fig. 2. Execution Plan of UDF Future Value.As we can see at Figure1, in case of classical formula Future Value, totally amount Query cost was spent by TableScanof FinacialData (99%) and the rest (1%) was belong to Compute Scalar.The same situation was in case of UDF Future Value (Figure2).

TABLE VI .
NUMBER OF PAYMENTS PER YEAR-FORMULAS AND PROGRAM CODE

TABLE VII .
COMPARISON OF CLIENT STATISTIC FOR CLASSICAL FORMULA AND UDF If we compared Client Statistics of classical formula Future Value and Client Statistics of UDF Future Value (Table VII.), we can stated, that Query Profile Statistics is the same in both cases.