How to subtract 2 columns derived from a MySQL function

Navigation
0

I have 2 columns both generated by means of 2 different functions, as there is a lot of data the query takes 190 seconds.

Example of what I do not want to do:

  • I want to subtract the result of both columns but I do not want it to take another 190 seconds because I have to take over the functions again.

Is it possible to do the subtraction more quickly?

Example of what I want to avoid: 

Select
 Funcion1(parametros) Columna1,
 Funcion2(parametros) Columna2,
 Funcion1(parametros)-Funcion2(parametros) Columna3
From
 Tabla
    
asked by Joel 28.03.2017 в 17:30
source

2 answers

1

You can assign the result of what each function returns to a variable and perform a selection of those variables: 

declare @primerResultado as decimal
declare @segundoResultado as decimal

set @primerResultado = Funcion1(parametros)
set @segundoResultado = Funcion2(parametros)

Select @primerResultado - @segundoResultado as ResultadoFinal

You could also add the function code to see if there is anything that can be improved in the functions so that they do not take so long.

    
answered by 28.03.2017 / 17:46
source
1

I suggest that the subtraction is not done in the BD; that is to say that you make it in the code of the application.

Review what is done in the functions; add them to this DETERMINISTIC ; other things to consider is if in the functions you use Query for example to return a value you add indices of each field in the WHERE, JOIN ... that is used in order to agile the comparison.

    
answered by 28.03.2017 в 17:47
How to send data from a dataGrid that is constantly changing to an SQL table Problem with Oracle OJDBC6