Quais são os prós e contras de executar cálculos no sql vs. em sua aplicação

shopkeeper tabela do shopkeeper tem os seguintes campos:

 id (bigint),amount (numeric(19,2)),createddate (timestamp) 

Vamos dizer que eu tenho a tabela acima. Quero obter os registros de ontem e gerar um relatório com o valor impresso em centavos.

Uma maneira de fazer é executar cálculos em meu aplicativo java e executar uma consulta simples

 Date previousDate ;// $1 calculate in application Date todayDate;// $2 calculate in application select amount where createddate between $1 and $2 

e, em seguida, percorrer os registros e converter o valor em centavos no meu aplicativo java e gerar o relatório

Outra maneira é como executar cálculos na própria consulta sql:

 select cast(amount * 100 as int) as "Cents" from shopkeeper where createddate between date_trunc('day', now()) - interval '1 day' and date_trunc('day', now()) 

e, em seguida, percorrer os registros e gerar o relatório

De uma forma, todo o meu processamento é feito no aplicativo java e uma consulta simples é triggersda. Em outro caso, todas as conversões e cálculos são feitos na consulta Sql.

O caso de uso acima é apenas um exemplo, em um cenário real, uma tabela pode ter muitas colunas que requerem processamento do mesmo tipo.

Você pode por favor me dizer qual abordagem é melhor em termos de desempenho e outros aspectos e por quê?

Depende de muitos fatores – mas mais crucialmente:

  • complexidade de cálculos (prefira fazer processamento complexo em um servidor de aplicativos, desde que seja escalonado; em vez de um servidor de database, que aumenta)
  • volume de dados (se você precisar acessar / agregar muitos dados, fazer isso no servidor db economizará largura de banda e disco io se os agregados puderem ser feitos dentro de índices)
  • conveniência (o sql não é a melhor linguagem para trabalhos complexos – especialmente não é bom para trabalhos procedimentais, mas é muito bom para trabalhos baseados em configurações; no entanto, é difícil lidar com erros)

Como sempre, se você trouxer os dados de volta para o servidor de aplicativos, minimizar as colunas e linhas será vantajoso para você. Certificar-se de que a consulta esteja ajustada e adequadamente indexada ajudará qualquer um dos cenários.

Re sua nota:

e, em seguida, percorra os registros

Looping através de registros é quase sempre a coisa errada a fazer em sql – escrevendo uma operação baseada em conjunto é o preferido.

Como regra geral , prefiro manter o trabalho do database no mínimo “armazene esses dados, busque esses dados” – no entanto, sempre há exemplos de cenários em que uma consulta elegante no servidor pode economizar muita largura de banda.

Considere também: se isso é computacionalmente caro, ele pode ser armazenado em algum lugar?

Se você quer um preciso “que é melhor”; codificá-lo nos dois sentidos e compará-lo (observando que um primeiro rascunho provavelmente não está 100% ajustado). Mas fatorar o uso típico para isso: se, na verdade, ele está sendo chamado 5 vezes (separadamente) de uma só vez, então simule isso: não compare apenas um único “1 desses contra 1 daqueles”.

Deixe-me usar uma metáfora: se você quer comprar um colar de ouro em Paris, o ourives poderia sentar na Cidade do Cabo ou em Paris, isso é uma questão de habilidade e bom gosto. Mas você nunca enviaria toneladas de minério de ouro da África do Sul para a França por causa disso. O minério é processado no local de mineração (ou pelo menos na área geral), apenas o ouro é enviado. O mesmo deve ser verdade para aplicativos e bancos de dados.

No que diz respeito ao PostgreSQL , você pode fazer quase tudo no servidor, de forma bastante eficiente. O RDBMS é excelente em consultas complexas. Para necessidades procedurais, você pode escolher entre uma variedade de linguagens de script do lado do servidor : tcl, python, perl e muito mais. Principalmente eu uso PL / pgSQL , no entanto.

O pior cenário seria ir repetidamente ao servidor para cada linha de um conjunto maior. (Isso seria como enviar uma tonelada de minério por vez.)

Em segundo lugar , se você enviar uma cascata de consultas, cada uma dependendo da anterior, tudo isso poderá ser feito em uma consulta ou procedimento no servidor. (Isso é como enviar o ouro e cada uma das jóias com um navio separado, sequencialmente).

Ir e voltar entre aplicativo e servidor é caro. Para servidor e cliente. Tente reduzir isso e você ganhará – ergo: use procedimentos do lado do servidor e / ou SQL sofisticado quando necessário.

Acabamos de concluir um projeto em que reunimos quase todas as consultas complexas nas funções do Postgres. O aplicativo entrega parâmetros e obtém os conjuntos de dados necessários. Rápido, limpo, simples (para o desenvolvedor do aplicativo), a E / S reduziu ao mínimo … um colar shiny com uma pegada de baixo carbono.

Nesse caso, você provavelmente está um pouco melhor em fazer o cálculo no SQL, pois é provável que o mecanismo de database tenha rotinas aritméticas decimais mais eficientes do que o Java.

Geralmente, no entanto, para cálculos de nível de linha, não há muita diferença.

Onde isso faz a diferença é:

  • Cálculos agregados como SUM (), AVG (), MIN (), MAX () aqui, o mecanismo do database será uma ordem de grandeza mais rápida que uma implementação Java.
  • Em qualquer lugar, o cálculo é usado para filtrar linhas. A filtragem no database é muito mais eficiente do que ler uma linha e, em seguida, descartá-la.

Não há preto / branco com relação a quais partes da lógica de access a dados devem ser executadas no SQL e quais partes devem ser executadas em seu aplicativo. Eu gosto do texto de Mark Gravell , distinguindo entre

  • cálculos complexos
  • cálculos com muitos dados

O poder e a expressividade do SQL são fortemente subestimados. Desde a introdução das funções de janela , muitos cálculos não estritamente orientados podem ser executados de forma muito fácil e elegante no database.

Três regras práticas devem sempre ser seguidas, independentemente da arquitetura geral do aplicativo:

  • manter a quantidade de dados transferidos entre o database e o aplicativo slim (em favor de calcular o material no database)
  • manter a quantidade de dados carregados do disco pelo database (em favor de permitir que o database otimize as instruções para evitar access a dados desnecessários)
  • não empurre o database para seus limites de CPU com cálculos complexos e simultâneos (em favor de puxar dados para a memory do aplicativo e realizar cálculos lá)

Na minha experiência, com um DBA decente e algum conhecimento decente sobre o seu database decente, você não vai correr em seus limites de CPU do database muito em breve.

Algumas outras leituras onde essas coisas são explicadas:

  • 10 erros comuns que os desenvolvedores Java cometem ao escrever SQL
  • 10 erros mais comuns que os desenvolvedores Java cometem ao escrever SQL

Em geral, faça coisas no SQL se houver chances de que outros módulos ou componentes no mesmo ou em outros projetos precisem obter esses resultados. uma operação atômica feita do lado do servidor também é melhor porque você só precisa chamar o proc armazenado de qualquer ferramenta de gerenciamento de database para obter valores finais sem processamento adicional.

Em alguns casos, isso não se aplica, mas quando isso acontece, faz sentido. também em geral a checkbox db tem o melhor hardware e performances.

Se você estiver escrevendo em cima do ORM ou escrevendo aplicativos casuais de baixo desempenho, use qualquer padrão que simplifique o aplicativo. Se você está escrevendo um aplicativo de alto desempenho e pensando cuidadosamente sobre escala, você ganhará movendo o processamento para os dados. Eu defendo fortemente mover o processamento para os dados.

Vamos pensar sobre isso em duas etapas: (1) transactions OLTP (pequeno número de registros). (2) OLAP (varreduras longas de muitos registros).

No caso do OLTP, se você deseja ser rápido (10k – 100k transactions por segundo), é necessário remover a contenção de trava, bloqueio e bloqueio inativo do database. Isso significa que você precisa eliminar longas paradas nas transactions: viagens de ida e volta do cliente para o database para mover o processamento para o cliente são uma delas. Você não pode ter transactions de longa duração (para tornar o read / update atomic) e ter um throughput muito alto.

Re: escala horizontal. Bancos de dados modernos são dimensionados horizontalmente. Esses sistemas implementam HA e tolerância a falhas já. Aproveite isso e tente simplificar seu espaço de aplicativo.

Vamos dar uma olhada no OLAP – neste caso, deve ser óbvio que arrastar possivelmente terrabytes de dados de volta para o aplicativo é uma idéia horrível. Estes sistemas são construídos especificamente para operar de forma extremamente eficiente contra dados colunares comprimidos e pré-organizados. Os sistemas OLAP modernos também são dimensionados horizontalmente e possuem planejadores de consulta sofisticados que dispersam o trabalho horizontalmente (processamento de movimentação interna para dados).

Se executar cálculos no front-end ou no back-end é muito decidido se podemos determinar nosso objective na implementação do negócio. No momento, o código java pode ter um desempenho melhor que um código sql bem escrito ou pode ser vice-versa. Mas ainda se confuso, você pode tentar determinar primeiro –

  1. Se você pode conseguir algo simples através do database sql, então é melhor ir para ele, pois o database irá executar muito melhor e fazer cálculos lá e, em seguida, com o resultado buscar. No entanto, se o cálculo real requer muito cálculo de aqui e ali coisas, então você pode ir com o código do aplicativo. Por quê? Como o loop do cenário, na maioria dos casos, não é melhor manipulado pelo sql, onde as linguagens front-end são melhor projetadas para essas coisas.
  2. Caso seja necessário um cálculo semelhante em muitos lugares, obviamente, colocar o código de cálculo no final do database será melhor para manter as coisas no mesmo local.
  3. Se houver muitos cálculos a serem feitos para atingir o resultado final por meio de muitas consultas diferentes, vá para db end, pois você pode colocar o mesmo código em um procedimento armazenado para executar melhor do que recuperar resultados do backend e depois computá-los na frente fim.

Há muitos outros aspectos que você pode pensar antes de decidir onde colocar o código. Uma percepção é totalmente errada – Tudo pode ser feito melhor em Java (código de aplicativo) e / ou tudo é melhor para ser feito pelo db (código sql).

Forme um ponto de vista de desempenho: essa é uma operação aritmética muito simples que quase certamente pode ser executada muito mais rapidamente do que buscar os dados dos discos que são subjacentes ao database. Além disso, o cálculo dos valores na cláusula where provavelmente será muito rápido em qualquer tempo de execução. Em resumo, o gargalo deve ser disco IO, não o cálculo dos valores.

De acordo com a legibilidade, acho que, se você usar um ORM, deverá fazê-lo no ambiente do seu servidor de aplicativos, pois o ORM permitirá que você trabalhe com os dados subjacentes com muita facilidade, usando operações baseadas em conjuntos. Se você vai escrever SQL bruto de qualquer maneira, não há nada de errado em fazer o cálculo lá, seu SQL também pareceria um pouco mais agradável e mais fácil de ler se formatado corretamente.

Crucialmente, “performance” não está definido.

O que mais me interessa é o tempo do desenvolvedor.

Escreva a consulta SQL. Se for muito lento ou o database se tornar um gargalo, reconsidere. A essa altura, você poderá avaliar as duas abordagens e tomar sua decisão com base em dados reais relevantes para sua configuração (hardware e qualquer pilha em que você esteja).

Eu não acredito que as diferenças de desempenho possam ser fundamentadas sem exemplos específicos e benchmarks, mas eu tenho outra opinião:

Qual você pode manter melhor? Por exemplo, você pode querer mudar seu front-end de Java para Flash, HTML5 ou C ++ ou outra coisa. Um vasto número de programas passou por essa mudança, ou até mesmo existe em mais de um idioma para começar, porque eles precisam trabalhar em vários dispositivos.

Mesmo se você tiver uma camada intermediária adequada (a partir do exemplo dado, parece que não é o caso), essa camada pode mudar e o JBoss pode se tornar Ruby / Rails.

Por outro lado, é improvável que você substitua o SQL-backend por algo que não seja um database relacional com SQL e, mesmo se o fizer, terá que rewrite o front-end do zero, então o ponto é discutível.

Minha ideia é que, se você fizer cálculos no database, será muito mais fácil escrever um segundo front-end ou uma camada intermediária depois, porque você não precisará reimplementar tudo. Na prática, porém, acho que “onde posso fazer isso com código que as pessoas vão entender” é o fator mais importante.

Para simplificar como responder isso seria para olhar para o balanceamento de carga. Você quer colocar a carga onde você tem mais capacidade (se faz algum sentido). Na maioria dos sistemas, é o SQL Server que rapidamente se torna um gargalo, então a resposta provável é que você não quer que o SQL faça mais de uma onça de trabalho do que o necessário.

Também na maioria das arquiteturas, são os servidores SQL que compõem o núcleo do sistema e os sistemas externos que são adicionados.

Mas a matemática acima é tão trivial que a menos que você esteja empurrando seu sistema ao limite, o melhor lugar para colocá-lo é onde você quer colocá-lo. Se a matemática não fosse trivial, como o cálculo de sin / cos / tan para um cálculo de distância, o esforço poderia se tornar não trivial e exigir planejamento e testes cuidadosos.

As outras respostas a essa pergunta são interessantes. Surpreendentemente, ninguém respondeu à sua pergunta. Você está se perguntando:

  1. É melhor transmitir para Cents na consulta? Eu não acho que o casting para centavos acrescente nada em sua consulta.
  2. É melhor usar now () na consulta? Eu preferiria passar datas para a consulta em vez de calculá-las na consulta.

Mais informações: Para a pergunta um, você quer ter certeza de que a agregação das frações funciona sem erros de arredondamento. Eu acho que o numérico 19,2 é razoável para o dinheiro e no segundo caso os números inteiros são OK. Usar um float por dinheiro é errado por esse motivo.

Para a questão dois, gosto de ter controle total como programador de qual data é considerada “agora”. Pode ser difícil escrever testes de unidade automáticos ao usar funções como now (). Além disso, quando você tem um script de transação mais longo, pode ser bom definir uma variável igual a now () e usar a variável para que toda a lógica use exatamente o mesmo valor.

Deixe-me dar um exemplo real para resolver esta questão

Eu precisava calcular uma média móvel ponderada em meus dados ohlc, eu tenho cerca de 134000 velas com um símbolo para cada um a fazê-lo

  1. Opção 1 Faça em Python / Node etc etc
  2. Opção 2 Faça isso no próprio SQL!

Qual é o melhor?

  • Se eu tivesse que fazer isso no Python, essencialmente, eu teria que buscar todos os registros armazenados no pior caso, realizar o cálculo e salvar tudo de volta, o que na minha opinião é um desperdício enorme de IO.
  • A média móvel ponderada muda toda vez que você recebe uma nova vela, o que significa que eu estaria fazendo grandes quantidades de IO em intervalos regulares, o que não é uma boa opinião no meu signo.
  • No SQL, tudo o que tenho que fazer é provavelmente escrever um gatilho que calcula e armazena tudo, então só é preciso buscar os valores WMA finais para cada par de vez em quando e isso é muito mais eficiente

Requisitos

  • Se eu tivesse que calcular WMA para cada vela e armazená-lo, eu faria isso em Python
  • Mas como eu só preciso do último valor, o SQL é muito mais rápido que o Python

Para te dar algum incentivo, esta é a versão do Python para fazer uma média móvel ponderada

WMA feito através do 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 através do 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() 

Acredite ou não, a consulta é mais rápida do que a versão Pure Python de fazer uma MÉDIA DE MOVIMENTO PONDERADA !!! Eu fui passo a passo para escrever essa consulta para pendurar lá e você vai fazer muito bem

Rapidez

0,42141127300055814 segundos Python

0,23801879299935536 segundos SQL

Eu tenho 134.000 registros OHLC falsos no meu database, divididos entre 1000 ações, de modo que é um exemplo de onde o SQL pode superar seu servidor de aplicativos