[Bug 121856] New: JT400 takes too much time to return SQL results

[bu...@apache.org]

https://issues.apache.org/ooo/show_bug.cgi?id=121856

            Bug ID: 121856
        Issue Type: DEFECT
           Summary: JT400 takes too much time to return SQL results
    Classification: Application
           Product: Base
           Version: AOO 3.4.1
          Hardware: PC
                OS: All
            Status: UNCONFIRMED
          Severity: major
          Priority: P3
         Component: code
          Assignee: issues@openoffice.apache.org
          Reporter: amalia.lopez@dpteruel.es
                CC: issues@openoffice.apache.org

Connecting from Windows XP + AOO 3.4.1 to ISeries (OS400) v7r1m0 with jt400
takes at least 1 minute.


Testing with IBM developers I’ve been reported this:

Looking over this trace, the problem is in the application design (Open
Office being the application here).                                    

I have broken down the time using the trace data.                      
The total amount of time is 1 minute, 2 seconds:                       
11:44:10.296212  set attributes                                        
11:45:12.501284  disconnect                                            
---------------                                                        
00:01:02.205072  Total time                                            

Of that time, the application spends most of its time getting metadata.
The first thing it gets is the table types that db2 supports, that take
 0.3 seconds.                                                          
Then it makes a call to get metadata for just about every table on the 
system.  That takes 33.2 seconds:                                      
CALL SYSIBM.SQLTABLES(?,?,?,?,?)                                       
Parameters:  <NULL>, <NULL>, %, ,                                      
DATATYPE='JDBC';DYNAMIC=0;REPORTPUBLICPRIVILEGES=1;CURSORHOLD=1        

11:44:44.004708  end of data retrieval                                 
11:44:10.759204  beginning of prepare of statement                     
00:00:33.245504  total time                                            

Then it spends 13.6 seconds selecting even more metadata:              
CALL SYSIBM.SQLTABLES(?,?,?,?,?)                                       
Parameters:  <NULL>, <NULL>, %, VIEW,                                  
DATATYPE='JDBC';DYNAMIC=0;REPORTPUBLICPRIVILEGES=1;CURSORHOLD=1        

11:44:57.909348  end of data retrieval                                 
11:44:44.293124  beginning of prepare                                  
00:00:13.616224  total time                                            

Then it spends 0.067 seconds actually running the query that you want i
 to run:                                                               

SELECT * FROM "QIWS"."QCUSTCDT"                                        

11:44:58.050436  end of data retrieval                                 
11:44:57.983076  beginning of prepare                                  
00:00:00.067360  total time                                            

Finally it does another strange thing, it calls the following stored   
procedure 11 times with exactly the same parameter data and all of thos
 took a total of 14.4 seconds.                                         

CALL SYSIBM.SQLSPECIALCOLUMNS(?,?,?,?, ?,?,?)                          
Parameters: 2, 'BENDERTS', '', '', 0, 1,                               
'DATATYPE='JDBC';DYNAMIC=0;REPORTPUBLICPRIVILEGES=1;CURSORHOLD=1'      
That returns an empty result set each time.                            

So the total breakdown is 0.067 seconds to run the query that you want 
it to run and 61 seconds getting metadata that you don't want or need. 

I have no idea why Open Office gets this data or what you might do to  
make it stop but I can point out that the problem isn't the JDBC driver
or the i.  They returned a lot of data quickly to this application but 
the application doesn't need any of that data in order to run your     
query so it would seem to be a lot of wasted effort.                   

This is something that the customer will need to address with the      
application support if they want this to improve.

-- 
You are receiving this mail because:
You are on the CC list for the bug.
You are the assignee for the bug.