Bind de variables en la base de datos y performance

Teníamos una sentencia de la forma

//&SessionCollection = Collection de Session (Tiene al menos un elemento)
For Each 
    Where ATTSession in &SessionCollection  

...

Endfor

Esto genera un sentencia SQL del tipo 

SELECT ATTSession,ATT2.. from Table
WHERE ATTSession in ('314159','271828')


Es bastante dificil el binding de estas variables pues no se conoce a priori la cantidad de valores que puede tener la lista. 

La performance de la sentencia  es buena, pero los DBA se empezaron a quejar que como la sentencia no tiene binding de variables, el espacio donde Oracle guarda las sentencias y sus planes de ejecución se llenaba rápidamente con sentencias muy parecidas entre, pero con diferentes valores en la comparación IN. El mayor problema es que al entrar estas sentencias, se borran otras que cuando se vuelvan a ejecutar van a tener que volver a evaluar se plan de ejecución.
Analizando el caso, vimos que la sentencia se ejecutaba varias decenas de miles de veces por día y vimos también que la gran mayoría de las sentencias, era que si bien según el modelo de datos, se podía necesitar recuperar mas de una sesión, en la practica, la inmensa mayoría de las sentencias eran con un solo valor, por ejemplo:

SELECT ATTSesion,ATT2..... from Table
WHERE ATTSession in ('141721')

En lo casos que solo se tiene un valor en la Collection es facil lograr que se use el Binding de variables de la forma


For Each 
    Where ATTSession in &SessionCollection  where &SessionCollection.Count > 1
    Where ATTSession = &SessionCollection.Item(1) where &SessionCollection.Count = 1

...

Endfor

GeneXus no optimiza la condicion ATTSession = &SessionCollection.Item(1) y la pasa a una condicion que se evalua en el cliente, por lo que traia todos los registros y los filtraba en el cliente, haciendo que el proceso cancelara, pues tenia que traer mas de 3Gb de datos.

Cambiando a

&SessionUnique = &SessionCollection.Item(1)
&SessionCount = &SessionCollection.Count
For Each 
    Where ATTSession in &SessionCollection  where  &SessionCount > 1
    Where ATTSession = &SessionUnique where &SessionCount = 1

...

Endfor

funciono correctamente y en la mayoría de los casos va a utilizar variables con binding y podremos dejar a algun DBA contento. 

Comentarios

  1. Excelente post! esto es así en todos los motores de base de datos.
    El uso de valores estáticos en where, el When así como el IN son cosas que se debe de tener cuidado.

    ResponderEliminar
  2. Pareceria que ese ejemplo del final podría ser optimizado automaticamente por GeneXus, así como se hizo con los casos mencionados en el último punto de este doc: http://wiki.genexus.com/commwiki/servlet/wiki?16993

    ResponderEliminar
    Respuestas
    1. Pablo, gracias por el comentario y si, podria optimizarse en ese caso, aunque como en los casos que nombra la pagina del wiki, podria tener algun efecto secundario.
      Tambien hay que ver si no hay alguna otra forma de optmizar en las bases de datos los IN ()

      Eliminar

Publicar un comentario

1) Lee el post
2) Poné tu opinión sobre el mismo.
Todos los comentarios serán leidos y la mayoría son publicados.

Entradas más populares de este blog

El Sordo

StackOverflow Documentation

Codigo simple