c# – SQL in code or in database?

Question:

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

Currently I 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 bank as in the 2nd example? I've heard that it wouldn't be a good practice.
  • In terms of maintainability and readability, which example is better?
  • Could example 1 affect the bank's performance, by executing the ifs ?
  • Which one to use?

Answer:

Test them both 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 that only you have access to. And keep in mind that in the future with a slightly different basis than the current one, this may change.

Maybe you take a test and can't tell the difference. Then you'll know that performance doesn't matter and you should choose to do what's easiest to maintain. Performance concerns rarely make sense if you're not seeing a big problem. And if the problem exists, the way is to check for yourself 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 expect a big difference, at least not in most cases.

There is controversy 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 one do you think you will do better to move in the future? Is there any reason to put it in the database?

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

See more in Software Engineering and 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 🙂

Post Views: 9
Scroll to Top