Performance of SQL database visualization in SC

[kwinter]

Hi folks,

I need an online visualization of inventory data stored in an oracle database of TIVOLI Inventory within SC.
To do that, I created several forms (and corresponding files) fitting to certain views within the TIVOLI database and display these forms as virtual joins. Everything works fine, except of one single form fitting to a harddisk view within TIVOLI Inventory. To visualize this single view within SC takes ages (about 70 seconds). The views are all pretty similar but the harddisk view takes its data from two tables which are joined by 3 (!) keys. I thought, this could be the reason of the delay, but if I state the select statement on the Inventory database host, it's very fast and makes no problems.
Has anybody any idea, why this view is so slowly when used out of SC??? Any reply is very welcome! Thanks in advance, Kai

 

[dx111ge]

try a sql debug and check the query , normally it depends on the nameh parameter ... tell us more about the sc/ sql version

 

[kwinter]

The view is very fast if stated from an SQL plus window, so I think the query is okay...?

We run SC 4.0.1 and an oracle8i enterprise edition release 8.1.7.0.0

 

[chrishaw]

I think what dx was suggesting is that you turn on sql debugging (add sqldebug:1 to sc.ini). This will dump all the SQL that is passed to sc.log. This will be *a lot*, so just do it for a short time. Run the operation that's taking a long time and then check it for queries that aren't so good.

 

[kwinter]

Hi!

First of all, thanks for all your help!!!

So, I did the SQL debug, but I guess, I can't really interprete all this stuff very well. So I give you one statement which ist very fast:

980 12/06/2002 09:58:04 PREPARE SELECT * FROM IP_SERVICECENTER_VIEW WHERE TME_OBJECT_LABEL=:nameh0
980 12/06/2002 09:58:04 sql diff 0.000 total 4.236 call#:44
980 12/06/2002 09:58:04 sql diff 0.000 total 4.236 call#:45
980 12/06/2002 09:58:04 EXECUTE SELECT * FROM IP_SERVICECENTER_VIEW WHERE TME_OBJECT_LABEL=:nameh0
980 12/06/2002 09:58:04 sql diff 0.180 total 4.416 call#:46

and another one, which is the slow one:

980 12/06/2002 09:58:16 PREPARE SELECT * FROM PC_DISK_SERVICECENTER_VIEW
980 12/06/2002 09:58:16 sql diff 0.000 total 8.131 call#:59
980 12/06/2002 09:58:16 sql diff 0.000 total 8.131 call#:60
980 12/06/2002 09:58:16 DESCRIBE SELECT * FROM PC_DISK_SERVICECENTER_VIEW
980 12/06/2002 09:58:16 COMMIT
980 12/06/2002 09:58:16 sql diff 0.000 total 8.131 call#:61
980 12/06/2002 09:58:16 PREPARE SELECT m1.TME_OBJECT_LABEL FROM PC_DISK_SERVICECENTER_VIEW m1 WHERE ((m1.TME_OBJECT_LABEL LIKE :nameh0)) ORDER BY m1.TME_OBJECT_LABEL ASC nulls first
980 12/06/2002 09:58:16 sql diff 0.000 total 8.131 call#:62
980 12/06/2002 09:58:50 sql diff 0.000 total 8.131 call#:63
980 12/06/2002 09:58:50 DESCRIBE SELECT m1.TME_OBJECT_LABEL FROM PC_DISK_SERVICECENTER_VIEW m1 WHERE ((m1.TME_OBJECT_LABEL LIKE :nameh0)) ORDER BY m1.TME_OBJECT_LABEL ASC nulls first
980 12/06/2002 09:58:50 SINGLE FETCH RECORDS FROM SELECT m1.TME_OBJECT_LABEL FROM PC_DISK_SERVICECENTER_VIEW m1 WHERE ((m1.TME_OBJECT_LABEL LIKE :nameh0)) ORDER BY m1.TME_OBJECT_LABEL ASC nulls first
980 12/06/2002 09:58:50 FETCH SELECT m1.TME_OBJECT_LABEL FROM PC_DISK_SERVICECENTER_VIEW m1 WHERE ((m1.TME_OBJECT_LABEL LIKE :nameh0)) ORDER BY m1.TME_OBJECT_LABEL ASC nulls first
980 12/06/2002 09:58:50 FETCH SELECT m1.TME_OBJECT_LABEL FROM PC_DISK_SERVICECENTER_VIEW m1 WHERE ((m1.TME_OBJECT_LABEL LIKE :nameh0)) ORDER BY m1.TME_OBJECT_LABEL ASC nulls first
980 12/06/2002 09:58:50 sql diff 0.000 total 8.131 call#:64
980 12/06/2002 09:58:50 PREPARE SELECT * FROM PC_DISK_SERVICECENTER_VIEW WHERE TME_OBJECT_LABEL=:nameh0
980 12/06/2002 09:58:50 sql diff 0.000 total 8.131 call#:65
980 12/06/2002 09:58:50 sql diff 0.000 total 8.131 call#:66
980 12/06/2002 09:58:50 EXECUTE SELECT * FROM PC_DISK_SERVICECENTER_VIEW WHERE TME_OBJECT_LABEL=:nameh0
980 12/06/2002 09:59:35 sql diff 45.245 total 53.376 call#:67
980 12/06/2002 09:59:35 sqllimit exceeded, user=kwinter limit=30.000 actual=45.245 SQL statement follows
980 12/06/2002 09:59:35 SELECT * FROM PC_DISK_SERVICECENTER_VIEW WHERE TME_OBJECT_LABEL=:nameh0

Okay, I also see, that there is something different like this "sql limit exceeded", but how can I change something like this or better: what can I do??????????