on 10-21-2008 7:42 PM
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? )
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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,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
]
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
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.+)
> (<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
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?
+)
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
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!
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
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.+)
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
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+)
Hi,
can you tell us the MaxDb Version you are using.
Regards, Christiane
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
83 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.