Isso é uma das brigas que não tem fim na área.
Programador quase sempre vai usar o DB apenas como meio de armezenamento.
Isso tem inúmeras vantages e alguns desvantagens também.
O DBA vai preferir por tudo no DB. Claro aí estamos falando em um DBA com poderes para forçar isso na aplicação ou que o próprio programador é um DBA.
Também é algo que tem vantagens e desvantagens e muitas vezes você só vai descobrir muito tempo depois de ter adotada uma das estratégias.
Eu sou programador. Adivinha op que eu fiz em quase todos os meus sistemas?
Mas eu também já fiz um pouco da inteligência e organização do sistema estar no banco de dados, sem seguir as regras de DBA, ou seja, eu criei um mecanismo meu que ajudava na manutenção do sistema e mesmo tendo muita informação do sistema no DB, era redundância, documentação, formas de facilitar algumas tarefas e operações, não era o uso dos mecanismos típicos de um DB. Eu não gosto de quase nenhum mecanismo básico dos SGDBs existentes em relação a qualquer coisa além do storage e otimização de acesso. È uma resposta universal? Não.
Aí vem outras questão junta na maioria das vezes. Devo fazer para funcionar com um banco de dados específico e otimizar ao máximo para ele, usar recursos que só tem nele, e portanto você praticamente cai no banco da dados ajudando muito, mas talvez menos do que você imagina.
Quem faz para rodar em vários bancos de dados diferentes, ou porque ele vend para várias empresas que ele precise se adaptar, ou porque ele tem uma necessidade muito específica, muito rara, ou porque ele não entende o que está fazendo e acha que vai se dar bem com essa flexibilidade que só funcionará tirando todo poder do DB e passando para a aplicação, ou seja, você vai jogar fora os mecanismos de otimização de cada um dos DBS (bem, você pode fazer a otimização para cada um, mas aí é fazer 3, 5 , 10 sistemas diferentes).
Pior são os casos que o cara deixa mais genérico porque se o DB não der conta do recado ele pode trocar de DB. E vira uma profecia auto-realizável, porque como ele faz genérico, não otimiza, fica ruim e talev ele tenha que ttrocar de DB. Talvez não porque a troca pode pegar 6 no lugar de meia dúzia ou nem isso. Se quer garantir escalabilidade use os recursos do seu DB, não faça genérico, escolha um e seja feliz, tem todos os tamanhos de site rodando MYSQL/MariaDB, PostgreSQL, SQL Server, Oracle, DB2 e muitos rodando até em Firebase ou SQLite (cada dia mais), embora esses últimos são mais arriscados. Você estará bem com qualquer um deles se souber programar bem e entender um pouco de DB, especialmente o que escolheu.
Não tem solução mágica, se tá lento, é porque você fez tudo mal feito.
Claro que tem uns casos que pode ser que preci de um bando de dados de documento como o MongoDB ou um de grafo. Mas os recursos que esses DBS têm existem nos relacionais também. Qualquer relacional vai atender qualquer demanda mesmo que o DB seja quase só esse modelo diferente do relacional? Não, mas para a maioria dos casos vai atender bem, mesmo tendo que fazer algumas adaptações e não ter o máximo da eficiência. Aí tem que pensar, mas raros os DBs que não devam ser relacionais. De qualquer forma se não for relacional, espere fazer tudo na aplicação, a maioria não costuma deixar nada para ser feito dentro dele.
Stored Procedured é bom? EM alguns casos ele pode fazer alguma diferença, mas é raro, porque pensa bem, se você fizer uma query de um jeito e fizer a mesma coisa dentro de uma SP, porque a SP será mais eficiente? Ambas executarão dentro do banco de dados. A transmissão e compilação de uma query solta não fará cócegas no DB em comparação com a SP. E se fizer diferença, o problema era tão bobo que tanto faz o que você usou. A query só será mais lenta que a SP se a comparação não for justa, ou seja, a SP tá feita direito e a query enviada pela aplicação está ruim. Tudo que pode fazer em uma pode fazer na outra, mas tem alguns casos que fazer em uma SP pode see mais fácil de criar a query (porque uma SP não deixará de ser uma query em qualquer situação que valha comparar). Tem caso que não é fácil pegar dados variados de uma vez só com uma query, ela pode ficar complexa, exigir um DBA/programador que manje mais daquele SQL, e na SP pode ficar um pouco mais fácil porque você pode fazer em etapas, mantendo estado.
Se tá tudo funcionando a contento, não tem muito porque mexer. Mas cada casos é um caso.
O resto é organização de código.
Uma coisa que eu abomino, mas aceito em sistemas simples é usar o DB como API. E quem usa o jeito mais de DBA de programar costuma fazer mais isso. È dar tiro no pé em sistemas complexos com grande manutenção, você vai se perder, vai ter que fazer gambiarra para funcionar para as várias necessidades diferentes de consumo dessa API. Acredite tenho experiência nisso, e o sistema mais famosos que faz isso tem a maioria dos seus problemas, reclamações, custosos altos de manutenção por causa disso. EU sei bem, eu trabalhei nele. Nâo sei quantas pessoas tiveram a oportunidade de trabalhar com algo nessa escala e ver os sofrimentos e como seria mais fácil se tivessem optado pelo jeito certo.
Então já que a API será entregue por código de aplicação, deixa o DB mais limpo, só use recursos que permitam claro ganho no acesso.
Cuidados co modinhas, muita coisa é usada só porque tem gente dizendo que é bom. E ou é só para ele ou nem isso é, é só um hoax que foi longe demais. Nunca coloque nada no seu sistema sem ter certeza que dará ganhos e compensará os prejuízos. Não coloque porque os outros estão colocando, a tal ada modinha.
Cada operação nova tem que ser pensada para usar o que é melhor, não siga receitas de bolo. Nunca coloque algo que você não precisa muito usar. Não crie complexidade para dizer que é moderno.
Meça de todos os casos, o que pode ser verdade aqui pode não ser ali.
De qualquer forma é o que eu falo, você precisa aprender toda a computação para tomar decisões certas, se apenas copia o que disseram para você fazer tem grande chance de cometer um erro.
S2
Farei algo que muitos pedem para aprender a programar corretamente, gratuitamente (não vendo nada, é retribuição na minha aposentadoria) (links aqui no perfil também).