Mejora con tiempo para búsqueda de smartlist

Question

Compañeros,

No se si ya existirá una mejora disponible para el tiempo de los smarthlist cuando se encuentran demasiados datos almacenados, pero me toco el caso que en el modulo de ingresos eventuales se tardaba demasiado en consultar la información y esto daba un timeout, consulte el smarthlist de otros ingresos y este en su consulta hace un filtro de permisos por empleo, (permiso_empleo_tabla) y este lo envia a otro llamado permiso_tipo_planilla_tabla y el filtro lo realiza de la siguiente manera:

where exists (select null from sco.permiso_tipo_planilla_tabla(@usuario) where emp_codtpl = codtpl)

Donde se determino que en este es donde se lleva mas tiempo la consulta ya que busca a todos los empleados con el usuario que se envia, luego realiza el filtro (where emp_codtpl = codtpl) por cada empleado, la solución fue modificar este proceso para que recibiera el codtpl y realizara el filtro dentro del script y esto mejoro bastante el tiempo y ya no da timeout.

Esta fue la función que se modifico: (tabla) ALTER FUNCTION [sco].[permiso_tipo_planilla_tabla] (@usuario varchar(50), @codtpl int) RETURNS TABLE AS return ( select distinct tpr_codtpl codtpl from ( select tpr_codtpl from sco.tpr_tipos_planilla_roles join (sec.rus_roles_users join sec.usr_users on usr_codigo = rus_codusr) on rus_rol_id = tpr_codrol where usr_username = @usuario and tpr_codtpl = @codtpl union all select tpl_codigo from sal.tpl_tipo_planilla where exists ( select null from sco.cir_companias_roles join (sec.rus_roles_users join sec.usr_users on usr_codigo = rus_codusr) on rus_rol_id = cir_codrol where usr_username = @usuario and not exists( select null from sco.tpr_tipos_planilla_roles join sal.tpl_tipo_planilla on tpl_codigo = tpr_codtpl join (sec.rus_roles_users join sec.usr_users on usr_codigo = rus_codusr) on rus_rol_id = tpr_codrol where usr_username = @usuario and tpl_codcia = cir_codcia and tpr_codrol = cir_codrol and tpl_codigo = @codtpl) and tpl_codcia = cir_codcia) union all select tpl_codigo from sal.tpl_tipo_planilla join eor.cia_companias on cia_codigo = tpl_codcia where not exists ( select null from sco.tpr_tipos_planilla_roles join (sec.rus_roles_users join sec.usr_users on usr_codigo = rus_codusr) on rus_rol_id = tpr_codrol join sal.tpl_tipo_planilla tpl1 on tpl1.tpl_codigo = tpr_codtpl where usr_username = @usuario and tpl1.tpl_codcia = cia_codigo and tpl_codigo = @codtpl) and not exists ( select null from sco.cir_companias_roles join (sec.rus_roles_users join sec.usr_users on usr_codigo = rus_codusr) on rus_rol_id = cir_codrol where usr_username = @usuario and cir_codcia = cia_codigo) and exists( select null from sco.gcr_grupos_roles join (sec.rus_roles_users join sec.usr_users on usr_codigo = rus_codusr) on rus_rol_id = gcr_codrol where usr_username = @usuario and cia_codgrc = gcr_codgrc and not exists ( select null from sco.cir_companias_roles join (sec.rus_roles_users join sec.usr_users on usr_codigo = rus_codusr) on rus_rol_id = cir_codrol join eor.cia_companias cia1 on cir_codcia = cia1.cia_codigo where cia1.cia_codgrc = gcr_codgrc and usr_username = @usuario)) ) v1 ); El tiempo de respuesta de la consulta mejoro de 1 minuto a 3 segundos.

Cabe mencionar que mando a llamar la funcion permiso_empleo_tabla que a su vez llama a esta función.

Answer 1

Si vos NO queres utilizar la función que retorna tabla, podes utilizar la función que retornar un valor 1 o 0, [sco].[permiso_tipo_planilla] que lleva como parámetros el usuario y el codtpl.

La función que retorna tabla es mucho más rápida que la función que retorna 1 o 0, porque la evalúa una vez nada mas y no una vez por cada registro de la tabla. Esto lo podes comprobar utilizando el Execution Plan para el query en SQL Management Studio.

En el plan de ejecución evalúa primero la función y luego aplica la función exists 'n' veces por cada registro del query. Si se agrega como parámetro el emp_codtpl a la función, entonces la función se evaluaría una vez por cada registro.

Entonces, más bien depende de la configuración de los datos, lo positivo o negativo de lo que hiciste.

Sin embargo, siempre sería mejor que cambiaras la función a la que retorna 1 o 0 y no cambiaras la función ya existente, porque corres el riesgo que se necesite modificar el uso de la función en otros smartlists, listas de valores, procedimientos, reportes y habría que revisar si no lo usa el código fuente de Evolution

Answer 2

Los smartlist de ingresos y descuentos eventuales son los que mas suelen crecer.

En mi caso lo que hago es quitar el cheque de "peticion automatica de datos" para que el smartlist cargue en blanco y tambien configurar el querybuilder porque así la consulta se hace con los filtros del querybuilder y la información cargará rápidamente

Otra sugerencia también sería actualizar a la versión 1.9.1 ya que el nuevo componente para mostrar los smartlist es mucho mas rápido que la interfaz con silverlight

Answer 3

El cambio que hiciste en la función no incide en la mejora que planteaste sobre el tiempo de respuesta.

Aquí van unos datos sacados con "Execution Plan" y con "Clients Statistics" obtenidos al ejecutar la función 10 veces, primero con el parámetro adicional y luego sin este.

Primero con la modificación hecha a la función:

                            Average (ms)
Client processing time        7.4000
Total execution time         37.5000
Wait time on server replies  30.1000

Luego con la función como estaba originalmente:

                            Average (ms)
Client processing time        8.0000
Total execution time         19.9000
Wait time on server replies  11.9000

Como podes ver el cambio realizado no mejora el rendimiento de la función, al contrario es ligeramente más rápida cuando no se le agrega el parámetro.

Por lo que al verdadera ganancia de tiempo está en el query del SmartList y no en el cambio de la proppia función.

Yo sugeriría que cambiaras la redacción del WHERE para que utilice una cláusula IN en lugar de EXISTS y midieras el tiempo.

where emp_codtpl in (select codtpl from sco.permiso_tipo_planilla_tabla(@usuario))

¿Se lográ la misma reducción de tiempo?

Mejora con tiempo para búsqueda de smartlist

Follow this question

Evolution en BitBucket

Evolution en JIRA

Related questions