Bug #36057 Preformance issue between TCP/IP and JDBC
Submitted: 14 Apr 2008 12:51 Modified: 22 May 2008 9:07
Reporter: Philippe Campos Email Updates:
Status: No Feedback Impact on me:
None 
Category:Connector / J Severity:S5 (Performance)
Version: OS:Solaris
Assigned to: Assigned Account CPU Architecture:Any

[14 Apr 2008 12:51] Philippe Campos
Description:
We encountered a performance issue while testing JDBC last version and TCP/IP
PCP/IP is 20% quicker than JDBC in every circumstances (locally or remotely).
A script is ruuning insert/select and update on three tables.

How to repeat:
on :
DROP TABLE IF EXISTS `PROCESS_ACTIVITY`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `PROCESS_ACTIVITY` (
  `INSTANCE_ID` varchar(50) NOT NULL default '0',
  `ACTIVITY_ID` varchar(100) NOT NULL default '',
  `ITERATION_COUNT` varchar(100) NOT NULL default '',
  `PARENT_ID` varchar(100) NOT NULL default '',
  `ACTIVITY_NAME` varchar(250) default NULL,
  `ACTIVITY_TYPE` varchar(20) default NULL,
  `STATUS` varchar(50) default NULL,
  `START_TIME` decimal(16,0) default NULL,
  `END_TIME` decimal(16,0) default NULL,
  `PARTICIPANT` text,
  `REQUEST` longtext,
  `RESPONSE` longtext,
  `MESSAGE_ID` varchar(50) default NULL,
  `EXECUTED_BY` varchar(500) default NULL,
  PRIMARY KEY  (`INSTANCE_ID`,`ACTIVITY_ID`,`ITERATION_COUNT`),
  CONSTRAINT `F_PROCESS_ACTIVITY` FOREIGN KEY (`INSTANCE_ID`) REFERENCES `PROCESS_INSTANCE` (`INSTANCE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `PROCESS_INSTANCE`
--

DROP TABLE IF EXISTS `PROCESS_INSTANCE`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `PROCESS_INSTANCE` (
  `INSTANCE_ID` varchar(50) NOT NULL default '0',
  `PARENT_ID` varchar(50) NOT NULL default '0',
  `PROCESS_NAME` varchar(200) default NULL,
  `MESSAGE` longtext,
  `MESSAGE_MAP` longtext,
  `START_TIME` decimal(16,0) default NULL,
  `END_TIME` decimal(16,0) default NULL,
  `VERSION` varchar(20) default NULL,
  `DESCRIPTION` varchar(100) default NULL,
  `ERROR_TEXT` text,
  `TYPE` varchar(100) default NULL,
  `USER_NAME` text,
  `ORGANIZATION` text,
  `CURRENT_OWNER` text,
  `SOAP_PROCESSOR` text,
  `PROCESS_TYPE` decimal(1,0) NOT NULL default '0',
  `PROCESS_PRIORITY` decimal(1,0) NOT NULL default '3',
  `STATUS` varchar(15) default NULL,
  `BPMN` varchar(250) default NULL,
  `IS_ARCHIVED` tinyint(1) default NULL,
  `PUBLISHED_TO` tinyint(1) default NULL,
  `VARIABLE1_NAME` varchar(50) default NULL,
  `VARIABLE1_VALUE` varchar(255) default NULL,
  `VARIABLE2_NAME` varchar(50) default NULL,
  `VARIABLE2_VALUE` varchar(255) default NULL,
  PRIMARY KEY  (`INSTANCE_ID`),
  KEY `IX_PROCESS_INSTANCE_START_TIME` (`START_TIME`),
  KEY `IX_VARIABLE1` (`VARIABLE1_NAME`,`VARIABLE1_VALUE`),
  KEY `IX_VARIABLE2` (`VARIABLE2_NAME`,`VARIABLE2_VALUE`),
  KEY `IX_PROCESS_NAME` (`PROCESS_NAME`),
  KEY `IX_PARENT_ID` (`PARENT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `PROCESS_INSTANCE_DATA`
--

DROP TABLE IF EXISTS `PROCESS_INSTANCE_DATA`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `PROCESS_INSTANCE_DATA` (
  `INSTANCE_ID` varchar(50) NOT NULL default '0',
  `INSTANCE_DATA` longtext,
  PRIMARY KEY  (`INSTANCE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Table structure for table `RULEGROUP`
--

DROP TABLE IF EXISTS `RULEGROUP`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `RULEGROUP` (
  `RULEGROUPID` varchar(50) NOT NULL default '0',
  `ORGCONTEXT` text NOT NULL,
  `RULEGROUPNAME` varchar(250) NOT NULL default '',
  `RULEGROUPOWNER` varchar(250) NOT NULL default '',
  `RULEGROUPDESCRIPTION` varchar(150) default NULL,
  `RULEGROUPPRIORITY` decimal(2,0) NOT NULL default '5',
  PRIMARY KEY  (`RULEGROUPID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
[14 Apr 2008 13:15] Philippe Campos
KList of queries accessing the tables

Attachment: query.zip (application/zip, text), 223.45 KiB.

[14 Apr 2008 13:17] Philippe Campos
Hello,

Add file containing the queries on 14-04-08
[22 Apr 2008 9:07] Tonci Grgin
Note sent to Philippe, more info needed.
[22 May 2008 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".