agorian Trusted Contributor.
Trusted Contributor.
981 views

SQL Query takes long time to execute


Hi,

I’m selecting 4 fields from a table to populate a data set. This table
is de main user table from the system and has about 10300 lines. The
select in a SQL tool is very quick, but in Analyser takes a lot of
time.

DSTrace shows not only select in this table, but in a lot of tables
that uses same PK. Code below:

Code:
--------------------

[03/23/12 15:16:16.946]: SELECT CD_USUAR, CD_ATIVO_USUAR, CD_LOGIN_USUAR, CD_ORGAO, NM_USUAR FROM CONTROLE.USUAR WHERE CD_USUAR = ?
[03/23/12 15:16:16.946]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:16.948]: RS field 'CD_ATIVO_USUAR', length: 1, value = '1'
[03/23/12 15:16:16.948]: RS field 'CD_LOGIN_USUAR', length: 7, value = 'DBREETZ'
[03/23/12 15:16:16.948]: RS field 'CD_ORGAO', length: 3, value = '988'
[03/23/12 15:16:16.948]: RS field 'NM_USUAR', length: 13, value = 'Dolores Reetz'
[03/23/12 15:16:16.948]: SELECT CD_USUAR, CD_ORGAO FROM PGF_WEB.VALMETA WHERE CD_USUAR = ?
[03/23/12 15:16:16.948]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:16.949]: SELECT CD_USUAR, CD_ORGAO FROM LUIS.PESSOA WHERE CD_USUAR = ?
[03/23/12 15:16:16.949]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:17.035]: SELECT CD_USUAR, CD_ORGAO FROM CORPORATIVO.PESSOA WHERE CD_USUAR = ?
[03/23/12 15:16:17.035]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:18.356]: SELECT CD_USUAR, CD_ORGAO FROM EXEC.GUIEXE WHERE CD_USUAR = ?
[03/23/12 15:16:18.356]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:18.358]: SELECT CD_USUAR_DISTRI, CD_ORGAO FROM SEGUNDA.DISTRI WHERE CD_USUAR_DISTRI = ?
[03/23/12 15:16:18.358]: IN @ index 1, field 'CD_USUAR_DISTRI', value = 106
[03/23/12 15:16:18.359]: SELECT CD_USUAR, CD_ORGAO FROM LUIS.DISTRI WHERE CD_USUAR = ?
[03/23/12 15:16:18.359]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:18.363]: SELECT CD_USUAR, CD_ORGAO FROM PGF_WEB.DISTRI WHERE CD_USUAR = ?
[03/23/12 15:16:18.363]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:18.366]: SELECT CD_USUAR, CD_ORGAO FROM PGF_WEB.DECISAO WHERE CD_USUAR = ?
[03/23/12 15:16:18.367]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:18.369]: SELECT CD_USUAR, CD_ORGAO FROM SEGUNDA.DECMON WHERE CD_USUAR = ?
[03/23/12 15:16:18.369]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:18.370]: SELECT CD_USUAR_SOLIC, CD_ORGAO FROM HELPDESK.CHAMD WHERE CD_USUAR_SOLIC = ?
[03/23/12 15:16:18.370]: IN @ index 1, field 'CD_USUAR_SOLIC', value = 106
[03/23/12 15:16:18.372]: SELECT CD_USUAR, CD_ORGAO FROM EXEC.EXECUC WHERE CD_USUAR = ?
[03/23/12 15:16:18.372]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:18.375]: SELECT CD_USUAR, CD_ORGAO FROM CORPORATIVO.ORGAO WHERE CD_USUAR = ?
[03/23/12 15:16:18.376]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:18.377]: SELECT CD_USUAR, CD_ORGAO FROM CORPORATIVO.AGENDA WHERE CD_USUAR = ?
[03/23/12 15:16:18.377]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:18.379]: SELECT CD_USUAR, CD_ORGAO FROM PGF_WEB.AGENDA WHERE CD_USUAR = ?
[03/23/12 15:16:18.379]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:18.380]: SELECT CD_USUAR, CD_ORGAO FROM PGF_WEB.DESPACHO WHERE CD_USUAR = ?
[03/23/12 15:16:18.380]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:18.382]: SELECT CD_USUAR_RECEB_MOVCAR, CD_ORGAO FROM SEGUNDA.MOVCAR WHERE CD_USUAR_RECEB_MOVCAR = ?
[03/23/12 15:16:18.382]: IN @ index 1, field 'CD_USUAR_RECEB_MOVCAR', value = 106
[03/23/12 15:16:18.384]: SELECT CD_USUAR, CD_ORGAO FROM PGF_WEB.TRAPRO WHERE CD_USUAR = ?
[03/23/12 15:16:18.384]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:20.448]: SELECT CD_USUAR, CD_ORGAO FROM CONTROLE.ORGUSR WHERE CD_USUAR = ?
[03/23/12 15:16:20.448]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:20.450]: SELECT CD_USUAR_SISTEM, CD_ORGAO FROM CORPORATIVO.COMSIS WHERE CD_USUAR_SISTEM = ?
[03/23/12 15:16:20.450]: IN @ index 1, field 'CD_USUAR_SISTEM', value = 106
[03/23/12 15:16:20.451]: SELECT CD_USUAR_RECEB_MOVPET, CD_ORGAO FROM SEGUNDA.MOVPET WHERE CD_USUAR_RECEB_MOVPET = ?
[03/23/12 15:16:20.451]: IN @ index 1, field 'CD_USUAR_RECEB_MOVPET', value = 106
[03/23/12 15:16:20.453]: SELECT CD_USUAR, CD_ORGAO FROM PGF_WEB.AUDIEN WHERE CD_USUAR = ?
[03/23/12 15:16:20.453]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:20.454]: SELECT CD_USUAR, CD_ORGAO FROM LUIS.PROCES WHERE CD_USUAR = ?
[03/23/12 15:16:20.454]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:20.459]: SELECT CD_USUAR, CD_ORGAO FROM PGF_WEB.PROCES WHERE CD_USUAR = ?
[03/23/12 15:16:20.459]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:20.559]: SELECT CD_USUAR_CONCLU_PAUTA, CD_ORGAO FROM SEGUNDA.SESSAO WHERE CD_USUAR_CONCLU_PAUTA = ?
[03/23/12 15:16:20.560]: IN @ index 1, field 'CD_USUAR_CONCLU_PAUTA', value = 106
[03/23/12 15:16:20.566]: RS field 'CD_ORGAO', length: 3, value = '988'
[03/23/12 15:16:20.566]: RS field 'CD_ORGAO', length: 3, value = '988'
[03/23/12 15:16:20.566]: RS field 'CD_ORGAO', length: 3, value = '988'
[03/23/12 15:16:20.566]: RS field 'CD_ORGAO', length: 3, value = '988'
[03/23/12 15:16:20.566]: RS field 'CD_ORGAO', length: 3, value = '988'
[03/23/12 15:16:20.566]: RS field 'CD_ORGAO', length: 3, value = '988'
[03/23/12 15:16:20.566]: SELECT CD_USUAR, CD_ORGAO FROM SEGUNDA.PETIC WHERE CD_USUAR = ?
[03/23/12 15:16:20.566]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:20.799]: SELECT CD_USUAR, CD_ORGAO FROM CONTROLE.HISTLOGIN WHERE CD_USUAR = ?
[03/23/12 15:16:20.799]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:20.800]: SELECT CD_USUAR, CD_ORGAO FROM CNJ.ESTAT WHERE CD_USUAR = ?
[03/23/12 15:16:20.801]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:20.802]: SELECT CD_USUAR, CD_ORGAO FROM RECHUM.FREQ WHERE CD_USUAR = ?
[03/23/12 15:16:20.802]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:20.804]: SELECT CD_USUAR, CD_ORGAO FROM PGF_WEB.ARQVMT WHERE CD_USUAR = ?
[03/23/12 15:16:20.804]: IN @ index 1, field 'CD_USUAR', value = 106
[03/23/12 15:16:20.805]: SELECT CD_USUAR, CD_ORGAO FROM PGF_WEB.SENTEN WHERE CD_USUAR = ?
[03/23/12 15:16:20.805]: IN @ index 1, field 'CD_USUAR', value = 106

--------------------


In schema map and table/view names parameter I have only the
controle.usuar table.

Why is this happening?


--
agorian
------------------------------------------------------------------------
agorian's Profile: http://forums.novell.com/member.php?userid=53023
View this thread: http://forums.novell.com/showthread.php?t=453851

Labels (1)
0 Likes
2 Replies
Anonymous_User Absent Member.
Absent Member.

Re: SQL Query takes long time to execute

agorian,

It appears that in the past few days you have not received a response to your
posting. That concerns us, and has triggered this automated reply.

Has your problem been resolved? If not, you might try one of the following options:

- Visit http://support.novell.com and search the knowledgebase and/or check all
the other self support options and support programs available.
- You could also try posting your message again. Make sure it is posted in the
correct newsgroup. (http://forums.novell.com)

Be sure to read the forum FAQ about what to expect in the way of responses:
http://forums.novell.com/faq.php

If this is a reply to a duplicate posting, please ignore and accept our apologies
and rest assured we will issue a stern reprimand to our posting bot.

Good luck!

Your Novell Product Support Forums Team
http://forums.novell.com/

0 Likes
Anonymous_User Absent Member.
Absent Member.

Re: SQL Query takes long time to execute


Which version of Analyzer are you using.

With Analyzer 4.0.2 the data import performance is improved to large
extent with MySQL as the Analyzer DB.


-KPRajesh


--
kprajesh
------------------------------------------------------------------------
kprajesh's Profile: https://forums.netiq.com/member.php?userid=333
View this thread: https://forums.netiq.com/showthread.php?t=333

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.