cancel
Showing results for 
Search instead for 
Did you mean: 

Problem subquery

Former Member
0 Kudos

Pessoal, falo em português do brasil e venho solicitar ajuda para resolver problema com esta query:

( Guys, I speak in Portuguese in Brazil and I request help with wrong with this query: )

CREATE VIEW V_CRMSMS_EXAMEIMPRESSO

AS

SELECT

osm.osm_serie AS OSSerie,

osm.osm_num AS OSNumero,

(DATE(rcl.rcl_dthr_web) || ' ' || TIME(rcl.rcl_dthr_web)) AS OsDataImpressao,

(DATE(osm.osm_dt_result) || ' ' || TIME(osm.osm_dt_result)) AS OsDataResultado,

COUNT(pac.pac_reg) AS OsQtdeExames,

(pac.pac_reg || osm.osm_num) as OsUsuario,

pac.PAC_W_SENHA as OsSenha,

pac.pac_reg AS PacienteRegistro,

pac.pac_nome AS PacienteNome,

pac.pac_sexo AS PacienteSexo,

FLOAT((DATEDIFF(pac.pac_nasc, timestamp)/365.25), 2) AS PacienteIdade,

pac.pac_email AS PacienteEmail,

pac.PAC_CELULAR AS PacienteCelular,

pac.pac_ind_aceita_sms AS PacienteAceita,

pac.pac_cnv AS ConvenioCod,

cnv.cnv_nome AS ConvenioNome,

str.str_str_cod AS UnidadeCod,

str1.str_nome AS UnidadeNome,

emp.emp_fone AS UnidadeFone,

emp.emp_fax AS UnidadeFax

FROM

osm

INNER JOIN pac ON (osm.osm_pac = pac.pac_reg)

INNER JOIN cnv ON (pac.pac_cnv = cnv.cnv_cod)

INNER JOIN rcl ON (osm.osm_serie = rcl.rcl_osm_serie)

AND (osm.osm_num = rcl.rcl_osm)

INNER JOIN str ON (osm.osm_str = str.str_cod)

INNER JOIN str str1 ON (str.str_str_cod = str1.str_cod)

INNER JOIN emp ON (str1.str_emp_cod = emp.emp_cod)

WHERE

((pac.pac_email IS NOT NULL) OR

(pac.PAC_CELULAR IS NOT NULL)) AND

(DATE(timestamp) = (SELECT MAX(DATE(rcl.rcl_dthr)) AS RegistroImpresso FROM rcl WHERE (rcl.rcl_osm_serie = osm.osm_serie) AND (rcl.rcl_osm = osm.osm_num)))

GROUP BY

osm.osm_serie,

osm.osm_num,

(DATE(rcl.rcl_dthr_web) || ' ' || TIME(rcl.rcl_dthr_web)),

(DATE(osm.osm_dt_result) || ' ' || TIME(osm.osm_dt_result)),

CONCAT(pac.pac_reg,osm.osm_num),

pac.PAC_W_SENHA,

pac.pac_reg,

pac.pac_nome,

pac.pac_sexo,

FLOAT((DATEDIFF(pac.pac_nasc, timestamp)/365.25), 2),

pac.pac_email,

pac.PAC_CELULAR,

pac.pac_ind_aceita_sms,

pac.pac_cnv,

cnv.cnv_nome,

str.str_str_cod,

str1.str_nome,

emp.emp_fone,

emp.emp_fax

HAVING

(COUNT(pac.pac_reg) = (SELECT COUNT(rcl.rcl_pac) AS Laudos FROM rcl WHERE (rcl.rcl_osm_serie = osm.osm_serie) AND (rcl.rcl_osm = osm.osm_num) AND (rcl.rcl_stat = 'I')))

O problema está na subquery do HAVING, gostaria de saber como faço para tirar essa subquery e se possível, resolver o problema com uma procedure ou variáveis?

( The problem lies in the Subquery HAVING, I wonder how do I kill Subqueries this and if possible, resolve the problem with a procedure or variable? )

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

Hi Marcel,

please change the table aliases in the HAVING subquery to something else than what you've used in the rest of the query. Does it work then?

In general HAVING accepts subqueries in MaxDB (otherwise this clause would be pretty useless, would it?).

If this does not help, please post the create DDLs for the tables/indexes so that we can reproduce the issue.

regards,

Lars

Former Member
0 Kudos

Hi Lars,

Em verdade parece ser possível a utilização de subquerys no HAVING, porém essa subquery não pode ser correlacionado, como é o caso. A consulta que fiz faz referência a dados na consulta acima.

(By Google: In truth seems to be possible to use subquerys in HAVING, but this Subquery can not be correlated, as is the case. The consultation that did make reference to data in the query above.)

[

SELECT

osm.osm_serie

AS OSSerie,

osm.osm_num

AS OSNumero,
...
HAVING
(COUNT(pac.pac_reg) = (SELECT COUNT(rcl.rcl_pac) AS Laudos FROM rcl WHERE (rcl.rcl_osm_serie =

osm.osm_serie

) AND (rcl.rcl_osm =

osm.osm_num

) AND (rcl.rcl_stat = 'I')))

]

Encontrei uma alternativa, que funcionou, inclusive, ainda mais rápido (ao menos no MSSQL):

(I found an alternative that worked, even faster (at least in MSSQL):)

[

SELECT

osm.osm_serie AS OSSerie,

osm.osm_num AS OSNumero,

(DATE(rcl.rcl_dthr_web) || ' ' || TIME(rcl.rcl_dthr_web)) AS OsDataImpressao,

(DATE(osm.osm_dt_result) || ' ' || TIME(osm.osm_dt_result)) AS OsDataResultado,

COUNT(pac.pac_reg) AS OsQtdeExames,

(pac.pac_reg || osm.osm_num) as OsUsuario,

pac.PAC_W_SENHA as OsSenha,

pac.pac_reg AS PacienteRegistro,

pac.pac_nome AS PacienteNome,

pac.pac_sexo AS PacienteSexo,

FLOAT((DATEDIFF(pac.pac_nasc, timestamp)/365.25), 2) AS PacienteIdade,

pac.pac_email AS PacienteEmail,

pac.PAC_CELULAR AS PacienteCelular,

pac.pac_ind_aceita_sms AS PacienteAceita,

pac.pac_cnv AS ConvenioCod,

cnv.cnv_nome AS ConvenioNome,

str.str_str_cod AS UnidadeCod,

str1.str_nome AS UnidadeNome,

emp.emp_fone AS UnidadeFone,

emp.emp_fax AS UnidadeFax

FROM

osm

INNER JOIN pac ON (osm.osm_pac = pac.pac_reg)

INNER JOIN cnv ON (pac.pac_cnv = cnv.cnv_cod)

INNER JOIN rcl ON (osm.osm_serie = rcl.rcl_osm_serie)

AND (osm.osm_num = rcl.rcl_osm)

INNER JOIN str ON (osm.osm_str = str.str_cod)

INNER JOIN str str1 ON (str.str_str_cod = str1.str_cod)

INNER JOIN emp ON (str1.str_emp_cod = emp.emp_cod)

WHERE

((pac.pac_email IS NOT NULL) OR (pac.PAC_CELULAR IS NOT NULL)) AND

(0 = (SELECT COUNT(rcl.rcl_osm) FROM rcl WHERE (rcl.rcl_stat <> 'I') AND (rcl.rcl_stat <> 'C') AND (rcl.rcl_osm_serie = osm.osm_serie) AND (rcl.rcl_osm = osm.osm_num))) AND

(DATE(timestamp) = (SELECT MAX(DATE(rcl.rcl_dthr)) FROM rcl WHERE (rcl.rcl_osm_serie = osm.osm_serie) AND (rcl.rcl_osm = osm.osm_num)))

GROUP BY

osm.osm_serie,

osm.osm_num,

(DATE(rcl.rcl_dthr_web) || ' ' || TIME(rcl.rcl_dthr_web)),

(DATE(osm.osm_dt_result) || ' ' || TIME(osm.osm_dt_result)),

CONCAT(pac.pac_reg,osm.osm_num),

pac.PAC_W_SENHA,

pac.pac_reg,

pac.pac_nome,

pac.pac_sexo,

FLOAT((DATEDIFF(pac.pac_nasc, timestamp)/365.25), 2),

pac.pac_email,

pac.PAC_CELULAR,

pac.pac_ind_aceita_sms,

pac.pac_cnv,

cnv.cnv_nome,

str.str_str_cod,

str1.str_nome,

emp.emp_fone,

emp.emp_fax

]

lbreddemann
Active Contributor
0 Kudos

Hi Marcel,

sorry to disagree, but with the change you made, it's obvious that the query is faster.

It's just because the new where clause is nonsense:


AND 
(0 = (SELECT COUNT(rcl.rcl_osm) 
FROM rcl 
WHERE 
    (rcl.rcl_stat 'I')      <---
AND (rcl.rcl_stat 'C')      <---
AND (rcl.rcl_osm_serie = osm.osm_serie) 
AND (rcl.rcl_osm = osm.osm_num))) 

Check the lines with the '<---'! It's a contradiction, which will always lead to a result of 0.

I don't see, why the subquery needs to be correlated in this case.

Perhaps it would be easier if you tell us, what your data looks like and what infomation you want to get from it.

Ah, yes... BTW... correlated subqueries in HAVING clause - works fine.

Anyhow, it would really be a good idea to rethink your ALIASing scheme. I'm pretty sure that everybody will get confused by the different levels of the same aliases in the different subqueries.

regards,

Lars

Former Member
0 Kudos

Hi Lars,

Obrigado pelos questionamentos e explanações, tem me trazido novos e bons conhecimentos.

(Thank you for questions and explanations, I have brought new and good knowledge.)

1.

Quanto à primeira questão, o problema foi com o fórum, que não exibiu o sinal de diferente:

(The first question, the problem was with the forum, which showed no sign of the different:)


AND 
(0 = (SELECT COUNT(rcl.rcl_osm) 
FROM rcl 
WHERE 
    (rcl.rcl_stat != 'I')      <---
AND (rcl.rcl_stat != 'C')      <---
AND (rcl.rcl_osm_serie = osm.osm_serie) 
AND (rcl.rcl_osm = osm.osm_num))) 

  • Coloquei != para representar, por causa do fórum, que está suprimindo o '<>'.

  • (* Placed != to represent because of the forum, which is removing the '<>'.)

2.

Eu não entendi o que é BTW

(I do not understand what is BTW)

3.

Tentarei melhorar os meus aliases afim de melhorar a compreensão da query.

(Try to improve my aliases in order to improve understanding of the query.)

4.

Explicando a consulta:

Essa query fazer parte de um sistema de envio de notificação a pacientes da clínica onde trabalho.

Com esta query eu trago os pacientes que tiveram todos os laudos da sua ordem de serviço impressos (rcl_stat = 'I'). No momento do atendimento, alguns laudos podem ser cancelados (rcl_stat = 'C'), então, eu filtro contando se existe algum exame já foi entregue (rcl_stat = 'E' , ou (rcl_stat = 'X') -> ainda está sendo executado no laboratório), vez que a subquery tras todos os resultados diferentes de I ou C, se for diferente de 0, o paciente não receberá uma mensagem.

(+Explaining the query:

This query is part of a system of sending notification to patients of the clinic where I work.

With this query I bring the patients who had all of their reports printed order of service (rcl_stat = 'P'). At the time of care, some reports may be canceled (rcl_stat = 'C'), then I filter counting if there is any examination has been delivered (rcl_stat = 'D', or (rcl_stat = 'X') -> is still being performed in the laboratory), as the Subquery after all the different results of P or C, if different from 0, the patient did not receive a message.+)

lbreddemann
Active Contributor
0 Kudos

> (<i>Thank you for questions and explanations, I have brought new and good knowledge.</i>)

Hi Marcel - no problem, you're wecome!

>

> (<i>The first question, the problem was with the forum, which showed no sign of the different:</i>)

> * (<i>* Placed != to represent because of the forum, which is removing the '<>'.</i>)

Ah! I see - my fault. Interesting to realize that I overread the actual missing of the comparison operators and automatically assumed a '=' operator...

> 2.

> (<i>I do not understand what is BTW</i>)

Oh, sorry - It's just an abbrevation for By The Way.

>

> 4.

> (<i>Explaining the query:

> This query is part of a system of sending notification to patients of the clinic where I work.

> With this query I bring the patients who had all of their reports printed order of service (rcl_stat = 'P'). At the time of care, some reports may be canceled (rcl_stat = 'C'), then I filter counting if there is any examination has been delivered (rcl_stat = 'D', or (rcl_stat = 'X') -> is still being performed in the laboratory), as the Subquery after all the different results of P or C, if different from 0, the patient did not receive a message.</i>)

Ok, understood. As the query now suits your needs I assume that the problem is solved?

If so, you may want to mark this thread as closed.

regards,

Lars

Former Member
0 Kudos

Hi Lars,

Até gostaria de encerrar o tópico, mas meus problemas ainda não terminaram. A query está muito pesada e por isso precisei simplificá-la ainda mais, o que trouxe outros problemas (quantidade de ítens). Isso não impactará em sua utilização, porém a query continua travando.

Será que tem como otimizá-la ainda mais?

(+So I would like to close the topic, but my problems have not yet finished. The query is too heavy and therefore have to simplify it further, which brought other problems (number of items). This does not impact on your use, but the query remains slow.

Do we have to optimize it further?

+)

lbreddemann
Active Contributor
0 Kudos

Hi Marcel,

to be able to tell whether the performance could be improved, we need more information.

We need to know:

- parameter setup of the db

- exact table design (DDL)

- exact index design

- Index + Table statistics

- execution plan of the query: explain, explain join

Do you think you can provide these information ?

regards,

Lars

Former Member
0 Kudos

Hi Lars,

Mais uma vez agradeço toda atenção dada por você para a solução deste meu problema.

(Once again I thank you for all attention to the solution of my problem.)

No site do desenvolvedor do sistema tem uma base padrão completa, justamente para testes, através do endereço:

http://medicware.tecnova.com.br/publico/backup_edmar.bz2 (38mb)

Por fazer algum tempo que foi disponibilizada, não deve possuir todas as colunas atualmente utilizadas, contudo, não fará falta em nossas querys.

(+In the site's developer of the system has a basic pattern complete, just for testing, using the address:

http://medicware.tecnova.com.br/publico/backup_edmar.bz2 (38mb)

Why do some time that was available, should not have all the columns currently used, however, will not lack in our querys.+)

Informações sobre a query (view):

(Information on the query (view):)

osm - tabela que armazena registros de atendimentos (ordens de serviço)

rcl - armazena informações sobre ítens da o.s. (ex.: controle de status dos laudos - impresso, em execução, pendente ... )

pac - cadastro dos pacientes

cnv - cadastro de convênios médicos

str - setor / unidade da empresa que atendeu o paciente

emp - se relaciona com o setor, e traz dados da emrpesa

(+OSM - table that stores records for attendance (orders of service)

RCL - stores information about items of O.S. (eg control of status reports - printed in execution, pending ...)

pac - register of patients

CNV - registration of medical plans

str - sector / unit of the company that picked up the patient

Emp - is related to the industry, and brings data from emrpesa+)


SELECT
  osm.osm_serie AS OSSerie,  
---^ O.S serie
  osm.osm_num AS OSNumero, 
---^ O.S number
  MAX(DATE(rcl.rcl_dthr_web) || ' ' || TIME(rcl.rcl_dthr_web)) AS OsDataImpressao, 
---^ Date of print
  (DATE(osm.osm_dt_result) || ' ' || TIME(osm.osm_dt_result)) AS OsDataResultado, 
---^ Date of result
  COUNT(pac.pac_reg) AS OsQtdeExames, 
---^ number of exames
  (pac.pac_reg || osm.osm_num) as OsUsuario, 
---^ user to access the patient via web
  pac.PAC_W_SENHA as OsSenha, 
---^ password to access the patient via web
  pac.pac_reg AS PacienteRegistro, 
---^ register of patient 
  pac.pac_nome AS PacienteNome, 
---^ name of patient
  pac.pac_sexo AS PacienteSexo, 
---^ sex of the patient (male / female)
  FLOAT((DATEDIFF(pac.pac_nasc, timestamp)/365.25), 2) AS PacienteIdade, 
---^ age of the patient
  pac.pac_email AS PacienteEmail,
  pac.PAC_CELULAR AS PacienteCelular,
  pac.pac_ind_aceita_sms AS PacienteAceita, 
---^ (recent column) is accepting patients receive information via mobile
  pac.pac_cnv AS ConvenioCod, 
---^ number of the center for health
  cnv.cnv_nome AS ConvenioNome, 
---^ name of the center for health
  str.str_str_cod AS UnidadeCod, 
---^ number of the sector
  str1.str_nome AS UnidadeNome, 
---^ name of the sector
  emp.emp_fone AS UnidadeFone, 
---^ phone number of the sector
  emp.emp_fax AS UnidadeFax

FROM

  osm <--- table with records of attendances
  INNER JOIN pac ON (osm.osm_pac = pac.pac_reg) 
---^ table lists for care with their patients
  INNER JOIN cnv ON (pac.pac_cnv = cnv.cnv_cod) 
---^ plan relates to health of the patient table of plans
  INNER JOIN rcl ON (osm.osm_serie = rcl.rcl_osm_serie)
  AND (osm.osm_num = rcl.rcl_osm) 
---^ table lists the records of attendance, bringing about their exams
  INNER JOIN str ON (osm.osm_str = str.str_cod) 
---^ table lists the record of service to the sectors of 
[sector which generated the order of service]
  INNER JOIN str str1 ON (str.str_str_cod = str1.str_cod) 
---^ table lists of sectors to bring the main unit, as a unit may have 
several sectors
  INNER JOIN emp ON (str1.str_emp_cod = emp.emp_cod) 
---^ provides information about the company

WHERE

  ((pac.pac_email IS NOT NULL) OR (pac.PAC_CELULAR IS NOT NULL)) AND 
---^ filter patients who have e-mail or cell phone registered

  ( rcl.rcl_dthr_web BETWEEN (DATE(timestamp) || ' ' || TIME('00:00:00')) AND 
(DATE(timestamp) || ' ' || TIME('23:59:59')) ) AND 
---^ brings examinations that have been printed (for web) in the period defined

  (0 = (SELECT COUNT(sub1_rcl.rcl_osm) FROM rcl sub1_rcl 
WHERE (sub1_rcl.rcl_stat <> 'I') AND (sub1_rcl.rcl_stat <> 'C') 
AND (sub1_rcl.rcl_osm_serie = osm.osm_serie) 
AND (sub1_rcl.rcl_osm = osm.osm_num))) 
---^ verifies that all examinations are in the OS (I) printed or (C) canceled

GROUP BY

  osm.osm_serie,
  osm.osm_num,
  (DATE(osm.osm_dt_result) || ' ' || TIME(osm.osm_dt_result)),
  (pac.pac_reg || osm.osm_num),
  pac.PAC_W_SENHA,
  pac.pac_reg,
  pac.pac_nome,
  pac.pac_sexo,
  FLOAT((DATEDIFF(pac.pac_nasc, timestamp)/365.25), 2),
  pac.pac_email,
  pac.PAC_CELULAR,
  pac.pac_ind_aceita_sms,
  pac.pac_cnv,
  cnv.cnv_nome,
  str.str_str_cod,
  str1.str_nome,
  emp.emp_fone,
  emp.emp_fax

Thanks again!

lbreddemann
Active Contributor
0 Kudos

Hi Marcel,

unfortunately there seems to be something weired with the internal user management of the database for which you supplied the backup download (thanks for that !).

Therefore, please let me know the passwords for the DBA and the DBSERVICE user, so that I can access the data.

Moreover, since 7.6 Build 28 is very very old, it might be a good choice to install the current patch 7.6.05 Build 9 (see Downloads-Section) first.

regards,

Lars

Former Member
0 Kudos

Hi Lars,

Eu que agradeço todo o seu empenho.

Ao que me recordo, a senha é também 'dba', caso não funcione, precisarei entrar em contato com o administrador do banco para solicitar (o que já fiz) e também para sugerir a atualização da sua versão.

(+I appreciate all your efforts.

By that I remember, the password is also 'dba', if not work, need to contact the bank manager to ask (which I did) and also to suggest to upgrade your version.+)

lbreddemann
Active Contributor
0 Kudos

Hi Marcel,

thanks for the info.

Appearently the problem only appears when the backup of the old 7.6 Build 27 is restored to the more recent 7.6.04 Build 11 (or higher !?) version.

Well, anyhow - I managed to find an old installer, setup a 7.6 Build 38 instance, sucessfully recovered the database, loaded the system tables, logged in and found: a little disaster ...

- All 500+ tables of your application are stored directly into the SYSDBA schema which not really a good idea, especially concerning permission handling within the database.

Even worse, each of the tables has a name of just three letters - not even SAP does this...!

All columns bear the tablename in the first three letters of each columnname...

- Most of the tables are really wide (that is: they have many many columns), where most of them are NULLABLE.

You know, there is a difference between "WE'VE DON'T NO ANYTHING ABOUT THE DATA" a.k.a. NULL and "THE DATA HAS JUST NOT BEEN ENTERED YET".

- No UPDATE STATISTICS has ever been done, which is one of the worst things to do with the optimizer.

- Also the tables important for your query are actually empty - so we cannot do any performance testing here.

Bottomline:

I don't understand the schema and I don't understand your data. So even if the other design 'mistakes' wouldn't be there, I still could only give you very vage hints on how to speed up the query.

Don't get me wrong, but I cannot effort to spend more time on this.

Currently my impression is, that it's not a question of database management but rather a question of designing for performance.

Anyhow, perhaps you'll see already some better performance after installing the current patch, setting the parameters as proposed in the SDN Wiki and updating the statistics.

regards,

Lars

Former Member
0 Kudos

Hi Lars,

Compreendi todas as informações passadas por você.

Agradeço muito todo o apoio dispensado na tentativa de encontrar uma solução para este problema, mas sei que a modelagem tem algumas deficiências, o que dificulta também para mim, por não ser o desenvolvedor ou administrador do banco.

Passarei o link do tópico para o administrador responsável, assim como as suas dicas, e pedirei para que tente implementar alguma delas.

Mais uma vez, muito obrigado!

Atenciosamente,

Marcel Cerqueira

(+Hi Lars,

Understand all the information passed by you.

I very much appreciate all the support required in an attempt to find a solution to this problem, but I know that modeling is not the best, which also complicates my life, because to understand and live, even without being the developer or administrator of the database.

Let's link the topic to the administrator as well as their tips, and ask to try to implement any of them.

Again, thank you!

Regards,

Marcel Cerqueira+)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

can you tell us the MaxDb Version you are using.

Regards, Christiane

Former Member
0 Kudos

( <i>SELECT MAJORVERSION, MINORVERSION, CORRECTIONLEVEL, BUILD, OPERATINGSYSTEM, KERNELVARIANT FROM SYSINFO.VERSION</i> )

MAJORVERSION: 7

MINORVERSION: 6

CORRECTIONLEVEL: 0

BUILD: 27

OPERATINGSYSTEM: Linux 2.6.18-8.el5

KERNELVARIANT: fast