Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved Datawindow retrieve timeout issue in Web API

  1. Issue
0
Votes
Undo
  1. Miguel Alzate
    Miguel Alzate
  2. PowerServer
  3. Wednesday, 13 December 2023 13:40 PM UTC

Hi, everyone!

In client/server, a retrieve on a specific datawindow takes less than 1 second to execute, but in the same PowerServer cloud app it does not finish fetching the information and, after several seconds, a timeout error message displays. What can be the reason for this?

In the Applications.json file of the Web API, the timeout values for Transaction, Session and Request are set to 0.

Many other datawindow retrievals (with more complex queries) of the same cloud app run without timeout issues.

I´m using PowerServer 2022 R2 Build 2828

Any help will be much appreciated.

Just in case it's necessary, this is the SELECT of the datawindow:

SELECT tecno.autoliq_detalle.empleado,
tecno.autoliq_detalle.ncontrato,
max(tecno.aom_empresa.componente) componente,
max(tecno.grm_componente.nombre) comp_nombre,
max(tecno.autoliq_detalle.tipo_doc_id) tipo_doc_id,
max(tecno.autoliq_detalle.ndoc_id) ndoc_id,
max(tecno.autoliq_detalle.dv) dv,
max(tecno.autoliq_detalle.nombre) nombre,
max(tecno.autoliq_detalle.papellido) papellido,
max(tecno.autoliq_detalle.sapellido) sapellido,
max(tecno.autoliq_detalle.pnombre) pnombre,
max(tecno.autoliq_detalle.snombre) snombre,
max(tecno.autoliq_detalle.dia_anterior) dia_anterior,
max(tecno.autoliq_detalle.dias_duracion) dias_duracion,
sum(tecno.autoliq_detalle.dias_trabajados) dias_trabajados,
sum(tecno.autoliq_detalle.dias_trabaja_afp) dias_trabaja_afp,
sum(tecno.autoliq_detalle.dias_trabaja_arp) dias_trabaja_arp,
max(tecno.autoliq_detalle.salario_basico) salario_basico,
sum(tecno.autoliq_detalle.ingreso_base) ingreso_base,
sum(tecno.autoliq_detalle.ingreso_base_afp) ingreso_base_afp,
sum(tecno.autoliq_detalle.ingreso_base_arp) ingreso_base_arp,
sum(tecno.autoliq_detalle.aporte_pension) aporte_pension,
sum(tecno.autoliq_detalle.aporte_solidaridad) aporte_solidaridad,
sum(tecno.autoliq_detalle.aporte_salud) aporte_salud,
sum(tecno.autoliq_detalle.aporte_riesgo) aporte_riesgo,
sum(tecno.autoliq_detalle.aportes_vol_empl) aportes_vol_empl,
sum(tecno.autoliq_detalle.aportes_vol_empr) aportes_vol_empr,
sum(aporte_adicional) aporte_adicional,
sum(tecno.autoliq_detalle.vres_no_retenidos) vres_no_retenidos,
max(tecno.autoliq_detalle.tarifa) tarifa,
max(tecno.autoliq_detalle.tarifa_riesgo) tarifa_riesgo,
max(tecno.autoliq_detalle.centro_trabajo) centro_trabajo,
max(tecno.autoliq_detalle.nueva_salud) nueva_salud,
max(tecno.autoliq_detalle.nueva_pension) nueva_pension,
max(tecno.autoliq_detalle.pension) pension,
max(tecno.autoliq_detalle.salud) salud,
max(tecno.autoliq_detalle.riesgo) riesgo,
max(tecno.autoliq_detalle.ing) ing,
max(tecno.autoliq_detalle.ret) ret,
max(tecno.autoliq_detalle.tda) tda,
max(tecno.autoliq_detalle.taa) taa,
max(tecno.autoliq_detalle.vsp) vsp,
max(tecno.autoliq_detalle.vst) vst,
max(tecno.autoliq_detalle.vte) vte,
max(tecno.autoliq_detalle.avp) avp,
max(tecno.autoliq_detalle.vct) vct,
max(tecno.autoliq_detalle.sln) sln,
max(tecno.autoliq_detalle.ige) ige,
max(tecno.autoliq_detalle.lma) lma,
max(tecno.autoliq_detalle.vac) vac,
max(tecno.autoliq_detalle.irp) irp,
max(tecno.autoliq_detalle.eps) eps,
max(tecno.autoliq_detalle.afp) afp,
max(tecno.autoliq_detalle.arp) arp,
max(tecno.autoliq_detalle.tde) tde,
max(tecno.autoliq_detalle.tae) tae,
max(tecno.autoliq_detalle.tdp) tdp,
max(tecno.autoliq_detalle.tap) tap,
max(tecno.autoliq_detalle.ninc_general) ninc_general,
sum(tecno.autoliq_detalle.vr_inc_general) vr_inc_general,
max(tecno.autoliq_detalle.ninc_maternidad) ninc_maternidad,
sum(tecno.autoliq_detalle.vr_inc_maternidad) vr_inc_maternidad,
sum(tecno.autoliq_detalle.aporte_sal_empl) aporte_sal_empl,
sum(tecno.autoliq_detalle.aporte_sal_empr) aporte_sal_empr,
sum(tecno.autoliq_detalle.aporte_pens_empl) aporte_pens_empl,
sum(tecno.autoliq_detalle.aporte_pens_empr) aporte_pens_empr,
sum(tecno.autoliq_detalle.aporte_sol_empl) aporte_sol_empl,
sum(tecno.autoliq_detalle.aporte_sol_empr) aporte_sol_empr,
max(tecno.autoliq_detalle.tipo_registro) tipo_registro,
sum(tecno.autoliq_detalle.vaprobado_incgen) vaprobado_incgen,
sum(tecno.autoliq_detalle.vaprobado_incmat) vaprobado_incmat,
sum(tecno.autoliq_detalle.vaprobado_incrie) vaprobado_incrie,
max(tecno.autoliq_detalle.dias_lnr) dias_lnr,
max(tecno.autoliq_detalle.ibc_afp_real) ibc_afp_real,
max(tecno.autoliq_detalle.ibc_eps_real) ibc_eps_real,
max(tecno.autoliq_detalle.solidaria) solidaria,
max(tecno.autoliq_detalle.subsistencia) subsistencia,
max(tecno.autoliq_detalle.ibc_lnr_eps) ibc_lnr_eps,
max(tecno.autoliq_detalle.ibc_lnr_afp) ibc_lnr_afp,
max(tecno.autoliq_detalle.sucursal) sucursal,
max(ibc_lnr_eps_real) ibc_lnr_eps_real,
max(aporte_lnr_eps) aporte_lnr_eps,
max(ibc_dt_eps_real) ibc_dt_eps_real,
max(ibc_dt_eps) ibc_dt_eps,
max(aporte_dt_eps) aporte_dt_eps,
max(ibc_lnr_afp_real) ibc_lnr_afp_real,
max(aporte_lnr_afp) aporte_lnr_afp,
max(ibc_dt_afp_real) ibc_dt_afp_real,
max(ibc_dt_afp) ibc_dt_afp,
max(aporte_dt_afp) aporte_dt_afp,
max(tecno.empleado.entidad_salud) entidad_salud,
max(tecno.empleado.entidad_pension) entidad_pension,
max(tecno.empleado.entidad_riesgo) entidad_riesgo,
max(tecno.empleado.caja_compensacion) caja_compensacion,
max(tecno.empleado.tipo_empleado) tipo_empleado,
max(tecno.empleado.aporte_salud) ind_salud,
max(tecno.empleado.aporte_riesgo) ind_riesgo,
max(tecno.empleado.indicador_6) ind_pension,
sum(tecno.autoliq_detalle.aporte_subsistenci) aporte_subsistenci,
max(tecno.autoliq_detalle.porc_pen_empresa) porc_pen_empresa,
max(tecno.autoliq_detalle.porc_pen_empleado) porc_pen_empleado,
max(p.dias) dias_caja,
max(tecno.empleado.sucursal ) suc_emp,
min(tecno.hcontratos.finicial) fini_contrato,
max(tecno.hcontratos.fterminacion) ffin_contrato,
max(s.fcambio) fsueldo,
max(tecno.autoliq_detalle.ind_exonerado) ind_exonerado,
max(tecno.autoliq_detalle.finicial_vct) finicial_vct,
max(tecno.autoliq_detalle.ffinal_vct) ffinal_vct
FROM
tecno.autoliq_detalle
LEFT OUTER JOIN tecno.parafis_detalle p ON
p.compania = tecno.autoliq_detalle.compania AND p.ano = tecno.autoliq_detalle.ano AND p.mes = tecno.autoliq_detalle.mes AND p.nproceso= tecno.autoliq_detalle.nproceso AND p.empleado = tecno.autoliq_detalle.empleado AND p.ncontrato = tecno.autoliq_detalle.ncontrato AND p.entidad= 'SENA'
LEFT OUTER JOIN tecno.empleado ON
tecno.autoliq_detalle.compania = tecno.empleado.compania AND tecno.autoliq_detalle.empleado = tecno.empleado.empleado
LEFT OUTER JOIN tecno.hcontratos ON
tecno.autoliq_detalle.compania = tecno.hcontratos.compania AND tecno.autoliq_detalle.empleado = tecno.hcontratos.empleado AND tecno.autoliq_detalle.ncontrato = tecno.hcontratos.ncontrato
LEFT OUTER JOIN tecno.hsueldo s ON
tecno.autoliq_detalle.compania = s.compania AND tecno.autoliq_detalle.empleado = s.empleado AND tecno.autoliq_detalle.ncontrato = s.ncontrato
LEFT OUTER JOIN tecno.grm_vinculo ON
tecno.autoliq_detalle.compania = tecno.grm_vinculo.compania AND tecno.autoliq_detalle.empleado = tecno.grm_vinculo.componente
LEFT OUTER JOIN tecno.grm_componente ON
tecno.grm_vinculo.compania = tecno.grm_componente.compania AND tecno.grm_vinculo.componente2 = tecno.grm_componente.componente
LEFT OUTER JOIN tecno.aom_empresa ON
tecno.grm_componente.compania = tecno.aom_empresa.compania AND tecno.grm_componente.componente = tecno.aom_empresa.componente
WHERE
tecno.grm_vinculo.tipo_vinculo = :arg1 AND
tecno.grm_vinculo.vinculo = :arg2 AND
tecno.grm_vinculo.componente2 = :arg3 AND
tecno.grm_componente.tipo_componente = :arg4 AND
tecno.grm_vinculo.finicial <= tecno.hcontratos.fterminacion AND
tecno.grm_vinculo.ffinal >= tecno.hcontratos.fterminacion AND
tecno.autoliq_detalle.compania = :arg5 AND
tecno.autoliq_detalle.ano = :arg6 AND
tecno.autoliq_detalle.mes = :arg7 AND
tecno.autoliq_detalle.nproceso = :arg8 AND
tecno.autoliq_detalle.sucursal = :arg9 AND
tecno.autoliq_detalle.activo_pensionado = :arg10 AND
tecno.autoliq_detalle.tipo_registro <> '4' AND
s.fcambio =
(SELECT MAX(s2.fcambio) FROM tecno.hsueldo s2 WHERE s2.compania = s.compania AND s2.empleado = s.empleado AND s2.ncontrato = s.ncontrato) AND
((:arg11= 'N' AND tecno.empleado.tipo_cotizante <> '23') OR (:arg11 = 'S' AND tecno.empleado.tipo_cotizante = '23'))
GROUP BY tecno.autoliq_detalle.empleado,
tecno.autoliq_detalle.ncontrato
ORDER BY 1 ASC, 2 ASC, 35 DESC, 36 ASC

Session creation failed. status code: 404
PS application ran fine, then suddenly won't run.
Accepted Answer
Miguel Alzate
Miguel Alzate Accepted Answer Pending Moderation
  1. Thursday, 14 December 2023 02:03 AM UTC
  2. PowerServer
  3. # Permalink

I was able to solve the problem by adding the OPTION (FORCE ORDER) hint to the datawindow's SELECT.

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Thursday, 14 December 2023 02:44 AM UTC
Hi Miguel;

Thanks for the update on your SQL change & the great news that this is what solved your timeout issue!

Regards ... Chris
  1. Helpful
There are no comments made yet.
Responses (3)
mike S
mike S Accepted Answer Pending Moderation
  1. Wednesday, 13 December 2023 16:15 PM UTC
  2. PowerServer
  3. # 1

use sql profiler to see exactly what is being sent and exactly what indexes are being used.

 

in your WHERE clause, are any of the column types varchar?  If so, do you have indexes on those columns?  

there is a (bug?) in sql server that prevents the use of an index if the column type is varchar and the argument type is set as nvarchar.  There is a setting available in the PB/PS connection string that forces the use of char instead of nchar.  With that setting, the indexes will be used.

 

 

 

 

 

 

Comment
Load more comments
mike S
  1. mike S
  2. Wednesday, 13 December 2023 18:54 PM UTC
well, if you don't use sql profiler you won't know exactly what is being received by the database server.



This issue was very important to me when i hit it, so i looked to see what was actually received by the database rather than make assumptions.
  1. Helpful
mike S
  1. mike S
  2. Wednesday, 13 December 2023 19:04 PM UTC
Is the database SQL SERVER? I dont' see where you mentioned that. If it is NOT sql server, then you should find a tool similar to sql profiler that your database uses. I thought it was sql server, but apparently you didn't mention that. You should always specify the database and version
  1. Helpful
Miguel Alzate
  1. Miguel Alzate
  2. Thursday, 14 December 2023 02:21 AM UTC
Sorry I forgot the specification; it's SQL Server. I finally found the work around to the issue (please, see the accepted answer). Thanks a lot again, Mike!
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 13 December 2023 13:43 PM UTC
  2. PowerServer
  3. # 2

Hi Miguel;

 I would suggest changing PS's logging level to "Debug" & then run this SQL again to see what gets logged around this SQL execution.  Hopefully, the PS log might then point to the reason that you are getting this issue.

Regards .... Chris 

Comment
Load more comments
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Wednesday, 13 December 2023 20:48 PM UTC
Interesting .. does your PB App use the "CacheName=xxxxx" in its Transaction Object's DBParm field?

I ask that because the PS Error log is complaining about a missing CacheName.
  1. Helpful
Miguel Alzate
  1. Miguel Alzate
  2. Thursday, 14 December 2023 02:16 AM UTC
That’s right, Chris. The not found "atr_dw" transaction is because I used that name as an argument in a function. The passed argument is transaction "itr_dw" which does exist. I changed the name of the argument to "itr_dw" and the “transaction not found error” disappeared, despite going contrary to the recommended practice of naming function arguments beginning with the letter "a".
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Thursday, 14 December 2023 02:46 AM UTC
That's a great find Miguel! :-)
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.