Solution for slow SQL query caused by custom functions, function SQL

Solution for slow SQL query caused by custom functions, function SQL

During the report process, one report queries the exchange rate of the corresponding currency, and the exchange rate is in another table, the condition for querying the exchange rate is to query the exchange rate of the previous month of the statistical date in the Exchange Rate Table based on the statistical date (parameters introduced on the page) of the report. If there is no exchange rate for the previous month, then, the latest exchange rate of the corresponding currency is queried. Therefore, a function is defined to get the exchange rate. In SQL, the input parameter is the corresponding currency code and statistical date, there is no way to create a function index for this function. As a result, the query result is very slow. It takes more than 10 minutes to export 1.5 million of data.

Later, we made some improvements. Since the statistical date was determined, the corresponding exchange rate was also determined. Therefore, another Exchange Rate Table was created, and the reserved exchange rate was the expected exchange rate, when querying the page, we can execute the stored procedure and store the expected currency and corresponding exchange rate to this table.

In the SQL statement, we only need to query the exchange rate of the corresponding currency in the table, instead of calling the function to obtain the exchange rate, which is much faster and the time is shortened by nearly half.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.