[esb-issues] [JBoss JIRA] Commented: (JBESB-1675) Possible Juddi Performance problems

[ https://jira.jboss.org/jira/browse/JBESB-1675?page=com.atlassian.jira.plu... ] Kevin Conner commented on JBESB-1675: ------------------------------------- There are currently eight services created at startup, a summary of the DB accesses will be shown after the commentary. The following sequences are generated when a service has *already been registered* with jUDDI, other operations will be invoked on the first occassion. public void registerEPR(String category, String serviceName, String serviceDescription, EPR epr, String eprDescription) protected Service findService(String category, String serviceName) throws JAXRException The first step when registering an EPR is to locate the service. In order to do this our code searches for the classification we are using to register the services, the SQL being invoked is sql is: SELECT M.TMODEL_KEY,M.LAST_UPDATE,M.NAME,M.DELETED FROM TMODEL M WHERE M.DELETED IS NULL AND M.NAME = ? ORDER BY M.NAME DESC,M.LAST_UPDATE DESC param[1] = 'org.jboss.soa.esb.:category' This returns a single row representing the model definition. The next query is to ascertain the correct authorised user of the model. sql is: SELECT AUTHORIZED_NAME,OPERATOR,NAME,LANG_CODE,OVERVIEW_URL,DELETED FROM TMODEL WHERE TMODEL_KEY=? AND DELETED IS NULL param[1] = 'uuid:C7536010-F786-11DB-A010-E6245C4E79A1' Once we have this information we can query the registry to locate the services BulkResponse response = bqm.findServices(null, findQualifiers, namePatterns, classifications, null); sql is: SELECT S.SERVICE_KEY,S.LAST_UPDATE FROM BUSINESS_SERVICE S,SERVICE_CATEGORY C WHERE C.SERVICE_KEY = S.SERVICE_KEY AND ((C.TMODEL_KEY_REF = ? AND C.KEY_VALUE = ?)) ORDER BY S.LAST_UPDATE DESC param[1] = 'uuid:C7536010-F786-11DB-A010-E6245C4E79A1' param[2] = 'JBossESB-Internal' Find all services with the correct category, returning a row for each service within that category (eight in a normal deployment) The next step is to narrow down this list to the right service. sql is: SELECT S.SERVICE_KEY,S.LAST_UPDATE,N.NAME FROM BUSINESS_SERVICE S,SERVICE_NAME N WHERE N.SERVICE_KEY = S.SERVICE_KEY AND ((UPPER(NAME) = ?)) AND S.SERVICE_KEY IN (?,?,?,?,?,?,?,?) ORDER BY N.NAME DESC,S.LAST_UPDATE DESC param[1] = 'INVOKERSERVICE' param[2] = 'B5C14C50-5CAB-11DD-ADED-96C4460C4C9A' param[3] = 'B5A55FE0-5CAB-11DD-ADED-A5C22D903D36' param[4] = 'B56E2340-5CAB-11DD-ADED-EFD604928108' param[5] = 'AD3C0DE0-5CAB-11DD-ADED-81D73D5DC0EE' param[6] = 'AA23D160-5CAB-11DD-ADED-A273F824BE84' param[7] = 'AA0F37F0-5CAB-11DD-ADED-E2928732C77E' param[8] = 'A9FC2520-5CAB-11DD-ADED-FD755562266D' param[9] = 'A9CC6290-5CAB-11DD-ADED-C7C43FE3D3A4' This will return a single row for the service in question. Following this jUDDI then obtains the Business Key and service name sql is: SELECT BUSINESS_KEY FROM BUSINESS_SERVICE WHERE SERVICE_KEY=? param[1] = 'A9CC6290-5CAB-11DD-ADED-C7C43FE3D3A4' sql is: SELECT LANG_CODE,NAME, SERVICE_NAME_ID FROM SERVICE_NAME WHERE SERVICE_KEY=? ORDER BY SERVICE_NAME_ID param[1] = 'A9CC6290-5CAB-11DD-ADED-C7C43FE3D3A4' Having found the services we then make yet another query to obtain the classification and authorised user ClassificationScheme cScheme = bqm.findClassificationSchemeByName(findQualifiers, "org.jboss.soa.esb.:category"); sql is: SELECT M.TMODEL_KEY,M.LAST_UPDATE,M.NAME,M.DELETED FROM TMODEL M WHERE M.DELETED IS NULL AND M.NAME LIKE ? ORDER BY M.NAME DESC,M.LAST_UPDATE DESC param[1] = 'org.jboss.soa.esb.:category%' sql is: SELECT AUTHORIZED_NAME,OPERATOR,NAME,LANG_CODE,OVERVIEW_URL,DELETED FROM TMODEL WHERE TMODEL_KEY=? AND DELETED IS NULL param[1] = 'uuid:C7536010-F786-11DB-A010-E6245C4E79A1' We are now in a position of saving the new EPR by adding a service binding. Obtain the publisher sql is: SELECT PUBLISHER_NAME,EMAIL_ADDRESS,IS_ADMIN,IS_ENABLED,MAX_BUSINESSES,MAX_SERVICES_PER_BUSINESS,MAX_BINDINGS_PER_SERVICE,MAX_TMODELS FROM PUBLISHER WHERE PUBLISHER_ID=? param[1] = 'jbossesb' Create an authentication token sql is: INSERT INTO AUTH_TOKEN (AUTH_TOKEN,PUBLISHER_ID,PUBLISHER_NAME,CREATED,LAST_USED,NUMBER_OF_USES,TOKEN_STATE) VALUES (?,?,?,?,?,0,1) param[1] = 'authToken:8309CCC0-5D4F-11DD-8CC0-C698BD83BA6D' param[2] = 'jbossesb' param[3] = 'JBoss ESB User' param[4] = '2008-07-29 10:19:57.357' param[5] = '2008-07-29 10:19:57.357' Retrieve the publisher for the authentication token we have just created (?) sql is: SELECT PUBLISHER_ID,PUBLISHER_NAME FROM AUTH_TOKEN WHERE AUTH_TOKEN=? param[1] = 'authToken:8309CCC0-5D4F-11DD-8CC0-C698BD83BA6D' Retrieve the state of the authentication token (?) sql is: SELECT TOKEN_STATE FROM AUTH_TOKEN WHERE AUTH_TOKEN=? param[1] = 'authToken:8309CCC0-5D4F-11DD-8CC0-C698BD83BA6D' Retrieve the 'last used' timestamp for the authentication token sql is: SELECT LAST_USED FROM AUTH_TOKEN WHERE AUTH_TOKEN=? param[1] = 'authToken:8309CCC0-5D4F-11DD-8CC0-C698BD83BA6D' Update the 'last used' token sql is: UPDATE AUTH_TOKEN SET LAST_USED=?,NUMBER_OF_USES=NUMBER_OF_USES+1 WHERE AUTH_TOKEN=? param[1] = '2008-07-29 10:19:57.475' param[2] = 'authToken:8309CCC0-5D4F-11DD-8CC0-C698BD83BA6D' Obtain the business key associated with the service sql is: SELECT BUSINESS_KEY FROM BUSINESS_SERVICE WHERE SERVICE_KEY=? param[1] = 'A9CC6290-5CAB-11DD-ADED-C7C43FE3D3A4' Obtain the business entity/service information (although it does not appear to be used subsequently) sql is: SELECT * FROM BUSINESS_ENTITY e, BUSINESS_SERVICE s WHERE e.BUSINESS_KEY = s.BUSINESS_KEY AND s.SERVICE_KEY=? AND e.PUBLISHER_ID=? param[1] = 'A9CC6290-5CAB-11DD-ADED-C7C43FE3D3A4' param[2] = 'jbossesb' Finally, add the binding sql is: INSERT INTO BINDING_TEMPLATE (SERVICE_KEY,BINDING_KEY,ACCESS_POINT_TYPE,ACCESS_POINT_URL,HOSTING_REDIRECTOR,LAST_UPDATE) VALUES (?,?,?,?,?,?) param[1] = 'A9CC6290-5CAB-11DD-ADED-C7C43FE3D3A4' param[2] = '83214C60-5D4F-11DD-8C60-D2901B2101B5' param[3] = 'other' param[4] = '<wsa:From xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/08/addressing">... xmlns:jbossesb="http://schemas.jboss.com/ws/2007/01/jbossesb"&g... xmlns:jbossesb="http://schemas.jboss.com/ws/2007/01/jbossesb"&g... xmlns:jbossesb="http://schemas.jboss.com/ws/2007/01/jbossesb"&g... xmlns:jbossesb="http://schemas.jboss.com/ws/2007/01/jbossesb"&g... xmlns:jbossesb="http://schemas.jboss.com/ws/2007/01/jbossesb"&g... xmlns:jbossesb="http://schemas.jboss.com/ws/2007/01/jbossesb"&g... xmlns:jbossesb="http://schemas.jboss.com/ws/2007/01/jbossesb"&g... xmlns:jbossesb="http://schemas.jboss.com/ws/2007/01/jbossesb"&g... xmlns:jbossesb="http://schemas.jboss.com/ws/2007/01/jbossesb"&g... xmlns:jbossesb="http://schemas.jboss.com/ws/2007/01/jbossesb"&g... param[5] = 'null' param[6] = '2008-07-29 10:19:57.487' In total, registering eight EPRs (without defining the services) creates the following access 8 sql is: INSERT INTO AUTH_TOKEN (AUTH_TOKEN,PUBLISHER_ID,PUBLISHER_NAME,CREATED,LAST_USED,NUMBER_OF_USES,TOKEN_STATE) VALUES (?,?,?,?,?,0,1) 8 sql is: INSERT INTO BINDING_TEMPLATE (SERVICE_KEY,BINDING_KEY,ACCESS_POINT_TYPE,ACCESS_POINT_URL,HOSTING_REDIRECTOR,LAST_UPDATE) VALUES (?,?,?,?,?,?) 16 sql is: SELECT AUTHORIZED_NAME,OPERATOR,NAME,LANG_CODE,OVERVIEW_URL,DELETED FROM TMODEL WHERE TMODEL_KEY=? AND DELETED IS NULL 16 sql is: SELECT BUSINESS_KEY FROM BUSINESS_SERVICE WHERE SERVICE_KEY=? 8 sql is: SELECT * FROM BUSINESS_ENTITY e, BUSINESS_SERVICE s WHERE e.BUSINESS_KEY = s.BUSINESS_KEY AND s.SERVICE_KEY=? AND e.PUBLISHER_ID=? 8 sql is: SELECT LANG_CODE,NAME, SERVICE_NAME_ID FROM SERVICE_NAME WHERE SERVICE_KEY=? ORDER BY SERVICE_NAME_ID 8 sql is: SELECT LAST_USED FROM AUTH_TOKEN WHERE AUTH_TOKEN=? 8 sql is: SELECT M.TMODEL_KEY,M.LAST_UPDATE,M.NAME,M.DELETED FROM TMODEL M WHERE M.DELETED IS NULL AND M.NAME LIKE ? ORDER BY M.NAME DESC,M.LAST_UPDATE DESC 8 sql is: SELECT M.TMODEL_KEY,M.LAST_UPDATE,M.NAME,M.DELETED FROM TMODEL M WHERE M.DELETED IS NULL AND M.NAME = ? ORDER BY M.NAME DESC,M.LAST_UPDATE DESC 8 sql is: SELECT PUBLISHER_ID,PUBLISHER_NAME FROM AUTH_TOKEN WHERE AUTH_TOKEN=? 8 sql is: SELECT PUBLISHER_NAME,EMAIL_ADDRESS,IS_ADMIN,IS_ENABLED,MAX_BUSINESSES,MAX_SERVICES_PER_BUSINESS,MAX_BINDINGS_PER_SERVICE,MAX_TMODELS FROM PUBLISHER WHERE PUBLISHER_ID=? 8 sql is: SELECT S.SERVICE_KEY,S.LAST_UPDATE FROM BUSINESS_SERVICE S,SERVICE_CATEGORY C WHERE C.SERVICE_KEY = S.SERVICE_KEY AND ((C.TMODEL_KEY_REF = ? AND C.KEY_VALUE = ?)) ORDER BY S.LAST_UPDATE DESC 8 sql is: SELECT S.SERVICE_KEY,S.LAST_UPDATE,N.NAME FROM BUSINESS_SERVICE S,SERVICE_NAME N WHERE N.SERVICE_KEY = S.SERVICE_KEY AND ((UPPER(NAME) = ?)) AND S.SERVICE_KEY IN (?,?,?,?,?,?,?,?) ORDER BY N.NAME DESC,S.LAST_UPDATE DESC 8 sql is: SELECT TOKEN_STATE FROM AUTH_TOKEN WHERE AUTH_TOKEN=? 8 sql is: UPDATE AUTH_TOKEN SET LAST_USED=?,NUMBER_OF_USES=NUMBER_OF_USES+1 WHERE AUTH_TOKEN=? The unregister will follow later, although that is much worse than registering -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: https://jira.jboss.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira