c# – SQL in code or in the database?

Question:

I have a big doubt about where I should put my SQL query , whether in code or in the database .

I currently have a procedure that is assembled according to the parameters I have, that is, filtros .

Procedure

@escopoVerificacao varchar(MAX) = NULL
SET @sql = 'SELECT OrdemOrigem, OrdemTipo, IDCal, FROM  NormasCALClientes AS NCC' 
IF @escopoVerificacao IS NOT NULL BEGIN
    SET @sql = @sql + ' inner join VerificacoesClientes on VerificacoesClientes.IDCalCliente = NCC.IDCalCliente 
                                    AND VerificacoesClientes.IDCal = NCC.IDCal ' END exec(SQL)

No C#.

  StringBuilder query = new StringBuilder();
query.Append("SELECT OrdemOrigem, OrdemTipo, IDCal, FROM  NormasCALClientes AS NCC");
    if (!String.IsNullOrEmpty(escopoVerificacao.Text))
        query.Append("inner join VerificacoesClientes on VerificacoesClientes.IDCalCliente = NCC.IDCalCliente 
AND VerificacoesClientes.IDCal = NCC.IDCal");`

Doubts:

  • Is it good practice to transfer the text created in C# to the database as in the 2nd example? I've heard that it wouldn't be good practice.
  • In matters of maintainability and readability, which example is better?
  • Could example 1 affect the performance of the database, by executing the ifs ?
  • Which one to use?

Answer:

Take a test with both of them and come to the conclusion for yourself under the conditions of what you are going to use. The code has less influence than the database where only you have access. And keep in mind that in the future with a base slightly different from the current one, this could change.

Maybe you take a test and you can't tell the difference. Then you'll know that performance doesn't matter and should choose to do what's easier to maintain. Performance concerns rarely make sense if you're not seeing a big issue. And if the problem exists, the way out is to check on your own if another way helps significantly.

It's possible that the first code will run slightly faster by some SQL Server optimization but I wouldn't count on a big difference, at least not in most cases.

There are controversies as to which is more readable and easier to maintain. I think the application code is always more readable but there are people who say otherwise. Which do you think you'll do better to mess with in the future? Is there any reason to put in the database?

Even those who prefer to put them in the database admit that it is not so simple to maintain code in the database. So that's all the more reason to only choose this option if you really get a considerable gain, which is rarely the case.

See more at Software Engineering and at DBA.SE. Note that if you are a programmer you will probably want to put the logic in the application and if you are a DBA you will try at all costs to put the logic in the database 🙂

Scroll to Top