A problem to be avoided is thinking that parameters within a query are like macro-replacements, a kind of Search/Replace within queries, as they are not. Parameters are ways to identify values and their types to the DBMS server in an organized and error-free manner and, in addition, the possibility of repeating the same SQL sentence to be more performant. Next, I describe how this is possible.
Using parameterized queries
Most programmers like to use literal queries, that is, concatenating values to produce a query that you will later read and understand what was requested from the server and compare with what was returned. Here's an example of a literal query:
if Query1.Active then Query1.Close; Query1.SQL.Text:= 'UPDATE CLIENTS SET '+ ' RAZAO_SOCIAL='''+Trim(edtRazaoSocial.Text)+'''' ' WHERE ID_CLIENTE='+edtID_CLIENTE.Text; Query1.ExecSQL;
What's the problem with the query above? Imagine that the corporate name is INTELIG S/A and the CLIENT_ID is 666 then the query with all the concatenation would be as follows:
UPDATE CLIENTS SET RAZAO_SOCIAL='INTELIG S/A' WHERE ID_CLIENTE=666
If you've come this far, you probably haven't seen much of anything. But let's say now that the corporate name entered was ”–INTELIG S/A(two single quotes on purpose), what then would the SQL sentence look like? Let's see:
UPDATE CLIENTS SET RAZAO_SOCIAL=''--INTELIG S/A' WHERE CLIENT_ID=666If you're good at SQL, you've noticed that I've turned what's from the two forward dashes into a comment and now all the customer names will be deleted from the base. So the main problem in writing literal queries is dealing with code injection. Some can use QuotedStr() and this function will solve the issue of quotes, but it is anti-performative especially when we are dealing with long texts, imagine HTML code, RTF or long memos counting the position of the quotes and doubling them. Also, QuotedStr doesn't resolve issues involving unicode codes, escape characters, and other methods that can sabotage your query. So what's the solution? Use parameters and signal the end of the query, see the example:
if Query1.Active then Query1.Close; Query1.SQL.Clear; Query1.SQL.Add('UPDATE CLIENTS SET '); Query1.SQL.Add(' RAZAO_SOCIAL=:P_RAZAO_SOCIAL'); Query1.SQL.Add('WHERE ID_CLIENTE=:P_ID_CLIENTE'); Query1.SQL.Add(';'); // signaling the end of the query Query1.ParamByName('P_RAZAO_SOCIAL').AsString:=edtRazaoSocial.Text; Query1.ParamByName('P_ID_CLIENTE').AsInteger:=StrToInt(edtID_CLIENTE.Text); Query1.ExecSQL;Em nosso exemplo, sob nenhuma hipótese nossa query poderá ser sabotada pelo tipo de dados que informamos num formulário. Talvez se pergunte porque eu não usei SQL.Text e concatenei tudo, se você viu o artigo anterior a respeito de produtividade na IDE Delphi e Lazarus deve ter notado que faço uso do MultiPaste ou Column Mode. Sim, usando estes recursos, escrevo a query dentro do editor SQL(IBExpert, Flamerobin,…), testo a query, e depois de funcionando então vou colá-lo na nossa IDE de programação, daí usando usando MultiPaste ou Column Mode gero o código de forma rápida como visto acima, mão na roda!
But we're not done yet, parameterized queries have yet another advantage, if they are very repetitive in our application we can prepare them just once and use them many times, optimizing them for network use.
Using parameterized queries with preparation
Aprendemos até aqui a orientar programadores a não usarem querys literais, mas usar querys parametrizadas, mas há outro cenário que elas também nos beneficiam, vamos imaginar um sistema onde em várias partes é preciso sempre repetir a mesma busca:
if Query1.Active then Query1.Close; Query1.SQL.Clear; Query1.SQL.Add('SELECT status, corporate_id, client_id FROM CLIENTS'); Query1.SQL.Add('WHERE CNPJ=:P_CNPJ'); Query1.ParamByName('P_CNPJ').AsString:=Trim(Search.Text); Query1.Open();Querys como a acima serão executadas no servidor SGBD sempre da mesma forma, (1) fazendo a analise sintatica da query, (2) otimizando a query, (3) calculando custo de operação com índices, (4) compilando para o formato BLR e finalmente (5) executar e se necessário então retornar o resultado. De todas essas etapas a mais custosa é o PLANO(plan) para escolher o índice adequado porque quanto mais índices existirem, mais tempo levará a analise, isso contradiz um pouco a ideia de que quanto mais indices existirem é melhor, não? Estou me desviando do tópico, retomando…imagine a query acima dentro de um sistema de atendimento com mais de 400 pessoas realizando a mesma consulta, apenas com clientes diferentes (WHERE CNPJ=:P_CNPJ), todas serão executadas inúmeras vezes e passando pelo mesmo processo a cada consulta.
There is a way to optimize this using parameters, see:
if Query1.Active then Query1.Close; Query1.SQL.Clear; Query1.SQL.Add('SELECT status, social_reason, cnpj FROM CLIENTS'); Query1.SQL.Add('WHERE ID_CLIENTE=:P_ID_CLIENTE'); if not Query1.Prepared then Query1.Prepare;Notou o Query1.Prepare? O que ele faz? Ele submete ao SGBD a uma “preparação”, isto é, nossa query vai forçar o SGBD a ir até a última etapa, mas não executa, mas manterá o BLR e o PLANO e assim nas próximas execuções repetitivas não será necesspario passar por todo o processo novamente.
E se eu não usar o método Prepare? Ferramentas de programação como o Delphi e Lazarus fazem inferencia, isto é, acabam preparando do mesmo jeito só que isso pode acontecer num momento incoveniente do programa e mais de uma vez , então é bom que você faça a preparação num momento bem antes a execução a querie para que o tempo de preparação não se some ao tempo da execução.
Como o SGBD saberá que uma query preparada já existe? O servidor manterá na memória todas as querys já preparadas em forma de assinatura:
SELECT status, social_reason, cnpj FROM CLIENTS WHERE CLIENT_ID=?Esta é a assinatura, em alguns SGBDs os nomes dos campos são irrelevantes. Qualquer query similar a assinatura será reconhecida imediatamente como uma query preparada.
Não importa para o SGBD de qual estação, programa ou parte do programa venha a query, o que importa é a assinatura, se ela for reconhecida então usará o que já foi preparado. Por isso podemos dizer que querys preparadas não é feita para beneficiar apenas 1 conexão/programa, mas muitas conexões e muitos programas diferentes onde estatisticamente a mesma consulta acontece.
Notou que eu faço um teste antes de prepará-las? Olha só:
if not Query1.Prepared then Query1.Prepare;
Porque faço isso? Parece óbvio, mas se prepararmos todas as vezes não obteremos a performance que desejamos. A preparação deve ser feita uma única vez, e nunca mais repetida. Mesmo que eu feche o programa e abrir de novo, a preparação deve estar lá em alguma parte da memória do SGBD.
O SGBD irá eliminar da memória os recursos não utilizados e isso inclui preparações “obsoletas” ou em “desuso”, mas também quando explicitamente o programador chama um método conhecido como “UnPrepare” – falaremos dele mais tarde.
A “preparação” também é excelente em transferencias de dados em lote, veja o exemplo:
if Query1.Active then
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('SELECT status, razao_social, cnpj FROM CLIENTES');
Query1.SQL.Add('WHERE ULTIMO_PEDIDO<dateadd (-5 year to current_date)');
Query1.Open()
if Query2.Active then
Query2.Close;
Query2.SQL.Clear;
Query2.SQL.Add('UPDATE CLIENTES SET STATUS=''C'' ');
Query2.SQL.Add('WHERE ID_CLIENTE=:P_ID_CLIENTE');
if not Query2.Prepared then
Query2.Prepare;
while not Query1.eof do
begin
(...)
if isPrecisaCancelar then
begin
Query2.ParamByName('P_ID_CLIENTE').AsInteger:=Query1.FieldByName('P_ID_CLIENTE').AsInteger;
Query2.ExecSQL;
end;
Query1.Next;
end;
if Query2.Prepared then
Query2.UnPrepare;In the example above, the repetition of the same query would be obvious so we performed the “preparation” and saved time. Another interesting thing that I didn't mention earlier was that at the end of the process, when I'm not going to use the prepared query anymore, then run:
if Query2.Prepared then Query2.UnPrepare;
O UnPrepare acima fará com que a assinatura de nossa “preparação” no SGBD seja eliminada, isso é útil porque elimina da memória do SGBD todos os recursos alocados.
Devo fazer sempre o UnPrepare no final? Quase sempre a resposta é “nunca”. Geralmente só usamos o “UnPrepare” nas situações de transferencia de lote de dados, um cenário onde não há outros na rede que poderiam se beneficiar de uma query preparada. Um “UnPrepare” quando outros na erde estão se beneficiando de queries repetitivas mataria a performance. Então a regra é simples: quando a chance é zero de outros repetirem o mesmo procedimento depois que o mesmo já foi executado então podemos com tranquilidade chamar o método “UnPrepare”, nos outros cenários, você nunca irá usá-lo.
No vídeo a seguir, eu demonstro a diferença entre uma query preparada e outra não preparada:
When not to use the preparation
Sentenças SQL são muito flexíveis e cada SGBD tem suas peculiaridades e não é possível descrever todas as situações, mas quando uma sentença é ambigua para tomada de decisão você deve evitar uma preparação. Com exemplo podemos citar o like, quando usamos:
SELECT * FROM CLIENTS WHERE RAZAO_SOCIAL LIKE 'INDUSTRIA%'
In the example above, it is very clear that if there is an index associated with the company name, the DBMS should use it, but what if the query was:
SELECT * FROM CLIENTS WHERE RAZAO_SOCIAL LIKE '%INDUSTRIA%'
Neste novo exemplo, o SGBD irá ignorar qualquer índice, pois não faria sentido usá-los já que todos os registros precisarão ser lidos de qualquer forma. Então imaginaria o que aconteceria se preparasse uma query assim:
if Query1.Active then Query1.Close; Query1.SQL.Clear; Query1.SQL.Add('SELECT * FROM CUSTOMERS'); Query1.SQL.Add('WHERE RAZAO_SOCIAL LIKE :PESQUISA'); if not Query1.Prepared then Query1.Prepare;Pausa dramática. Como você não consegue determinar o que a pessoa digitará na barra de pesquisa, vamos inventar que a pessoa tenha pesquisado por:
'INDUSTRIA%'Como ela é a primeira pessoa a fazer a preparação, nosso PLANO seria usar um índice por Razão Social, certo? Sim, isto estaria certo, mas note que a mesma já foi preparada e há no SGBD a assinatura que já indicará o PLANO a ser executado. Daí vem a segunda pessoa e no campo de pesquisa faz a seguinte busca:
'%INDUSTRIA%' Como a query já foi preparada, as pesquisas seguintes usarão o mesmo PLANO, ou seja, você forçou o uso de um índice imprestável ao LIKE que só o fará perder tempo.
Alguns SGBD podem detectar a situação acima através de probabilidades e estatísticas e agir de forma preditiva, mas é muito melhor quando programadores evitam situações assim. Hoje um programador pode usar um banco e amanhã outro completamente diferente que não seja tão preditivo e espere que o desenvolvedor seja mais esperto e não submeta armadilhas ao próprio SGBD.
Respondendo logo a questão, em situações ambíguas evite usar parâmetro e prefira o QuotedStr() em seu lugar:
if Query1.Active then Query1.Close; Query1.SQL.Clear; Query1.SQL.Add('SELECT * FROM CUSTOMERS'); Query1.SQL.Add('WHERE RAZAO_SOCIAL LIKE '+QuotedStr(edtPesquisa.Text));Conclusion
Both the parameters and the preparation of a query do not depend on the programming language, as this feature is in all of them. These features serve a well-defined purpose: to improve and streamline research. If you notice that programmers on your team aren't using them, send the link to this article.
Each DBMS is different, some of them subvert poorly made queries, others use algorithms to improve a poorly performing query, but make no mistake, if programmers are efficient in the origin of the query, the database will be much more performant, otherwise, the database will be a obese turtle with 3 legs.