¿Cuáles son los pros y los contras de realizar cálculos en sql vs en su aplicación

Déjame usar una metáfora: si quieres comprar un collar de oro en París, el orfebre podría sentarse en Ciudad del Cabo o París, eso es una cuestión de habilidad y gusto. Pero ustednunca enviar toneladas de mineral de oro de Sudáfrica a Francia para eso. El mineral se procesa en el sitio minero (o al menos en el área general), solo se envía el oro. Lo mismo debería ser cierto para las aplicaciones y bases de datos.

En lo que respecta a PostgreSQL, puede hacer casi cualquier cosa en el servidor, bastante eficiente. El RDBMS sobresale en consultas complejas. Para necesidades de procedimiento puede elegir entre una variedad de lenguajes de script del lado del servidor : tcl, python, perl y muchos más. Aunque sobre todo uso PL/PgSQL.

El peor escenario sería ir repetidamente al servidor para cada fila de un conjunto más grande. (Eso sería como enviar una tonelada de mineral por vez.)

Segundo en la línea, si envía una cascada de consultas, cada una dependiendo en el anterior, mientras que todo podría hacerse en una consulta o procedimiento en el servidor. (Eso es como enviar el oro, y cada una de las joyas con una nave separada, secuencialmente.)

Ir y venir entre la aplicación y el servidor es caro. Para el servidor y el cliente. Intente reducir eso, y ganará-ergo: use procedimientos del lado del servidor y / o SQL sofisticado cuando sea necesario.

Acabamos de terminar un proyecto donde empaquetamos casi todas las consultas complejas en Funciones Postgres. La aplicación entrega parámetros y obtiene los conjuntos de datos que necesita. Rápido, limpio, simple( para el desarrollador de la aplicación), E / S reducido a un mínimo ... un collar brillante con una huella de carbono baja.

En este caso usted es probablemente un poco mejor haciendo el cálculo en SQL como el motor de base de datos es probable que tenga una rutinas aritméticas decimales más eficientes que Java.

Generalmente, sin embargo, para los cálculos de nivel de fila no hay mucha diferencia.

Donde hace una diferencia es:

• Cálculos agregados como SUM(),AVG(), MIN (), MAX () aquí el motor de base de datos será un orden de magnitud más rápido que un Java aplicación.
• En cualquier lugar el cálculo se utiliza para filtrar filas. Filtrar en la base de datos es mucho más eficiente que leer una fila y luego descartarla.

No hay blanco / negro con respecto a qué partes de la lógica de acceso a datos se deben realizar en SQL y qué partes se deben realizar en su aplicación. Me gusta la redacción de Mark Gravell , distinguiendo entre

• cálculos complejos
• cálculos intensivos en datos

La potencia y expresividad de SQL está muy subestimada. Desde la introducción de funciones de ventana , muchos cálculos no estrictamente orientados a conjuntos pueden ser realizado muy fácil y elegantemente en la base de datos.

Siempre se deben seguir tres reglas generales, independientemente de la arquitectura general de la aplicación:

• mantenga la cantidad de datos transferidos entre la base de datos y la aplicación delgada (a favor de calcular cosas en la base de datos)
• mantenga la cantidad de datos cargados desde el disco por la base de datos delgada (a favor de permitir que la base de datos optimice las declaraciones para evitar el acceso innecesario a los datos)
• no envíes la base de datos a sus límites de CPU con cálculos complejos y concurrentes (a favor de extraer datos a la memoria de la aplicación y realizar cálculos allí)

En mi experiencia, con un DBA decente y un poco de conocimiento decente sobre su base de datos decente, no se encontrará con los límites de su CPU DBs muy pronto.

Algunas lecturas adicionales donde se explican estas cosas:

• 10 Errores Comunes Que Cometen Los Desarrolladores De Java Al Escribir SQL
• 10 Errores Más Comunes Java Los Desarrolladores Hacen Al Escribir SQL

En general, haga cosas en SQL si hay posibilidades de que también otros módulos o componentes en el mismo u otros proyectos necesiten obtener esos resultados. una operación atómica realizada en el lado del servidor también es mejor porque solo necesita invocar el proc almacenado desde cualquier herramienta de administración de bases de datos para obtener los valores finales sin más procesamiento.

En algunos casos esto no se aplica, pero cuando lo hace tiene sentido. también en general la db box tiene el mejor hardware y rendimiento.

Si está escribiendo sobre applications o escribiendo aplicaciones casuales de bajo rendimiento, utilice cualquier patrón que simplifique la aplicación. Si está escribiendo una aplicación de alto rendimiento y pensando cuidadosamente en la escala, ganará moviendo el procesamiento a los datos. Estoy firmemente a favor de trasladar el procesamiento a los datos.

Pensemos en esto en dos pasos: (1) transacciones OLTP (small number of record). (2) OLAP (escaneos largos de muchos registros).

En el caso OLTP, si quieres para ser rápido (10k-100k transacciones por segundo), debe eliminar la contención de bloqueo, bloqueo y bloqueo muerto de la base de datos. Esto significa que debe eliminar los largos bloqueos en las transacciones: los viajes de ida y vuelta desde el cliente a la base de datos para mover el procesamiento al cliente son uno de esos largos bloqueos. No puede tener transacciones de larga duración (para hacer lectura/actualización atómica) y tener un rendimiento muy alto.

Re: escala horizontal. Las bases de datos modernas se escalan horizontalmente. Esos sistemas implementan HA y tolerancia a fallas ya. Aproveche eso e intente simplificar el espacio de su aplicación.

Echemos un vistazo a OLAP in en este caso debería ser obvio que arrastrar posiblemente terrabytes de datos a la aplicación es una idea horrible. Estos sistemas están diseñados específicamente para funcionar de manera extremadamente eficiente contra datos en columnas comprimidos y preorganizados. Los sistemas OLAP modernos también escalan horizontalmente y tienen sofisticados planificadores de consultas que dispersan el trabajo horizontalmente (trasladando internamente el procesamiento a los datos).

Si realizar cálculos en el front-end o en el back-end es muy decidido si podemos determinar nuestro objetivo en la implementación del negocio. En un momento, el código java podría funcionar mejor que un código sql bien escrito o podría ser viceversa. Pero aún así, si está confundido, puede tratar de determinar primero -

1. Si puede lograr algo sencillo a través de sql de base de datos, es mejor que lo haga, ya que db funcionará mucho mejor y hará cálculos allí y luego con el resultado tráelo. Sin embargo, si el cálculo real requiere demasiado cálculo de aquí y allá, entonces puede ir con el código de la aplicación. ¿Por qué? Debido a que los escenarios como looping en la mayoría de los casos no son mejor manejados por sql wherease, los lenguajes front-end están mejor diseñados para estas cosas.
2. En caso de que se requiera un cálculo similar desde muchos lugares, obviamente colocar el código de cálculo en el extremo de la base de datos será mejor para mantener las cosas en el mismo lugar.
3. Si hay un montón de los cálculos que se deben hacer para lograr el resultado final a través de muchas consultas diferentes también van para db end, ya que puede colocar el mismo código en un procedimiento almacenado para realizar mejor que recuperar los resultados del backend y luego calcularlos en el front-end.

Hay muchos otros aspectos que puedes pensar antes de decidir dónde colocar el código. Una percepción es totalmente incorrecta-Todo se puede hacer mejor en Java (código de aplicación) y / o todo es mejor hacerlo por la base de datos (sql codificar).

Formar un punto de vista de rendimiento: Esta es una operación aritmética muy simple que casi seguramente se puede realizar mucho más rápido que en realidad obtener los datos de los discos que subyacen a la base de datos. Además, calcular los valores en la cláusula where es probable que sea muy rápido en cualquier tiempo de ejecución. En resumen, el cuello de botella debe ser disk IO, no el cálculo de los valores.

Según la legibilidad, creo que si usa un OR debe hacerlo en su entorno de servidor de aplicaciones, porque el OR le permitirá trabajar con los datos subyacentes muy fácilmente, utilizando operaciones basadas en conjuntos. Si vas a escribir SQL raw de todos modos, no hay nada de malo en hacer el cálculo allí, Tu SQL también se vería un poco mejor y más fácil de leer si se formatea correctamente.

Crucialmente, "rendimiento" no está definido.

Lo que más me importa es el tiempo de desarrollador.

Escriba la consulta SQL. Si es demasiado lento o el DB se convierte en un cuello de botella, entonces reconsidere. En ese momento, podrá comparar los dos enfoques y tomar su decisión basada en datos reales relevantes para su configuración (hardware y cualquier pila en la que se encuentre).

No creo que las diferencias de rendimiento se puedan razonar sin ejemplos específicos y puntos de referencia, pero tengo otra opinión:

¿Cuál puedes mantener mejor? Por ejemplo, es posible que desee cambiar su front-end de Java a Flash, o HTML5, o C++, o algo más. Un gran número de programas han pasado por tal cambio, o incluso existen en más de un idioma para empezar, porque necesitan trabajar en múltiples dispositivos.

Incluso si tienes un centro adecuado capa (del ejemplo dado, parece que no es el caso), esa capa podría cambiar y JBoss podría convertirse en Ruby/Rails.

Por otro lado, es poco probable que reemplace el motor SQL con algo que no sea una base de datos relacional con SQL e incluso si lo hace, tendrá que reescribir el front-end desde cero de todos modos, por lo que el punto es discutible.

Mi idea es que si haces cálculos en la base de datos, será mucho más fácil escribir un segundo front-end o capa intermedia más tarde, porque no tienes que volver a implementar todo. En la práctica, sin embargo, creo que "dónde puedo hacer esto con un código que la gente entienda" es el factor más importante.

Para simplificar cómo responder esto sería mirar el equilibrio de carga. Usted quiere poner la carga donde usted tiene la mayor capacidad (si tiene algún sentido). En la mayoría de los sistemas, es el servidor SQL el que rápidamente se convierte en un cuello de botella, por lo que la respuesta probablemente sea que no desea que SQL haga una onza de trabajo más de lo necesario.

También en la mayoría de las arquitecturas son los servidores SQL los que conforman el núcleo del sistema y los sistemas externos los que se agregan.

Pero la matemática anterior es tan trivial, que a menos que usted está empujando su sistema al límite el mejor lugar para poner donde quieras. Si las matemáticas no fueran triviales, como calcular sin / cos / tan para, por ejemplo, un cálculo de distancia, entonces el esfuerzo podría volverse no trivial y requerir una planificación y pruebas cuidadosas.

Las otras respuestas a esta pregunta son interesantes. Sorprendentemente, nadie ha respondido a su pregunta. Te estás preguntando:

1. ¿Es mejor convertir a centavos en la consulta? No creo que el elenco a cents agrega cualquier cosa en su consulta.
2. ¿Es mejor usar now() en la consulta? Preferiría pasar fechas en la consulta en lugar de calcularlas en la consulta.

Más información: Para la primera pregunta que desea estar seguro de que la agregación de las fracciones obrar sin errores de redondeo. Creo que numérico 19,2 es razonable para el dinero y en el segundo caso los enteros están bien. El uso de un flotador para el dinero es incorrecto por esta razón.

Para la pregunta dos, me gusta tener el control total como programador de lo que la fecha se considera "ahora". Puede ser difícil escribir la unidad automática pruebas al usar funciones como now(). También, cuando usted tiene un más largo script de transacción puede ser bueno establecer una variable igual a now() y usar la variable que todo de la lógica utiliza exactamente el mismo valor.

Permítanme tomar un ejemplo real para abordar esta pregunta

Necesitaba calcular una media móvil ponderada en mis datos ohlc, tengo alrededor de 134000 velas con un símbolo para cada una para hacerlo

1. Opción 1 Hacerlo en Python / Nodo etc etc
2. Opción 2 Hacerlo en SQL mismo!

¿Cuál es mejor?

• Si tuviera que hacer esto en Python, esencialmente, tendría que recuperar todos los registros almacenados en el peor de los casos, realizar el cálculo y guardar todo lo que en mi opinión es un enorme desperdicio de IO
• La media móvil ponderada cambia cada vez que obtienes una vela nueva, lo que significa que estaría haciendo cantidades masivas de IO a intervalos regulares, lo que no es un buena opinión en mi signo
• En SQL, todo lo que tengo que hacer es probablemente escribir un disparador que calcula y almacena todo, por lo que solo necesito recuperar los valores WMA finales para cada par de vez en cuando y eso es mucho más eficiente

Requisitos

• Si tuviera que calcular WMA para cada vela y almacenarla, lo haría en Python
• Pero como solo necesito el último valor, SQL es mucho más rápido que Python

Para darle un poco de aliento, esta es la versión de Python para hacer una media móvil ponderada

AMM hecho a través del código

import psycopg2
import psycopg2.extras
from talib import func
import timeit
import numpy as np
with psycopg2.connect('dbname=xyz user=xyz') as conn:
with conn.cursor() as cur:
t0 = timeit.default_timer()
cur.execute('select distinct symbol from ohlc_900 order by symbol')
for symbol in cur.fetchall():
cur.execute('select c from ohlc_900 where symbol = %s order by ts', symbol)
ohlc = np.array(cur.fetchall(), dtype = ([('c', 'f8')]))
wma = func.WMA(ohlc['c'], 10)
# print(*symbol, wma[-1])
print(timeit.default_timer() - t0)
conn.close()

WMA A través de SQL

"""
if the period is 10
then we need 9 previous candles or 15 x 9 = 135 mins on the interval department
we also need to start counting at row number - (count in that group - 10)
For example if AAPL had 134 coins and current row number was 125
weight at that row will be weight = 125 - (134 - 10) = 1
10 period WMA calculations
Row no Weight c
125 1
126 2
127 3
128 4
129 5
130 6
131 7
132 8
133 9
134 10
"""
query2 = """
WITH
condition(sym, maxts, cnt) as (
select symbol, max(ts), count(symbol) from ohlc_900 group by symbol
),
cte as (
select symbol, ts,
case when cnt >= 10 and ts >= maxts - interval '135 mins'
then (row_number() over (partition by symbol order by ts) - (cnt - 10)) * c
else null
end as weighted_close
from ohlc_900
INNER JOIN condition
ON symbol = sym
WINDOW
w as (partition by symbol order by ts rows between 9 preceding and current row)
)
select symbol, sum(weighted_close)/55 as wma
from cte
WHERE weighted_close is NOT NULL
GROUP by symbol ORDER BY symbol
"""
with psycopg2.connect('dbname=xyz user=xyz') as conn:
with conn.cursor() as cur:
t0 = timeit.default_timer()
cur.execute(query2)
# for i in cur.fetchall():
# print(*i)
print(timeit.default_timer() - t0)
conn.close()

Lo creas o no, la consulta se ejecuta más rápido que la versión pura de Python de hacer una MEDIA MÓVIL PONDERADA!!! Fui paso a paso a escribir esa consulta, así que aguanta ahí y lo harás bien

Velocidad

0.42141127300055814 segundo Python

0.23801879299935536 segundos SQL

Tengo 134000 registros OHLC falsos en mi base de datos divididos entre 1000 acciones, por lo que es un ejemplo de dónde SQL puede superar a su servidor de aplicaciones