[Registry-dev] Increasing mooshup.com performance

[Sumedha Rubasinghe]

Hi all,
Since the performance of mooshup.com <http://mooshup.com> is going down 
everyday, I examined a dump of registry database mooshup is using.
As per the dump, it has following tables with specified number of records.

ARTIFACTS -379
CHILDREN -0
COMMENTS -3
DEPENDENCY - 144889
LOGS - 2235
PROPERTIES -128
RATINGS - 46
TAGS - 63
VERSIONS - 7761

Out of these 'VERSIONS' table is special,as it contains a BLOB field. 
Structure of the table is as follows :

AID (INTEGER(10)
VN  (INTEGER(10)
CONTENT (BLOB)
AUTHOR (VARCHAR)
UPDATED_TIME (TIMESTAMP)

Due to BLOB field, doing a SELECT * FROM VERSION is a very costly 
operation.

In the registry code base, DAO class  
https://wso2.org/repos/wso2/trunk/registry/modules/core/src/main/java/org/wso2/registry/jdbc/dao/VersionedResourceDAO.java 
<https://wso2.org/repos/wso2/trunk/registry/modules/core/src/main/java/org/wso2/registry/jdbc/dao/VersionedResourceDAO>
has 4 locations where 'SELECT * FROM VERSION WHERE ...' is used. This 
might be a reason for performance degradation. And it's a very bad practice
when it comes to writing SQL statements too. You should only select the 
columns needed for subsequent program execution.

Following are 4 methods mentioned above.

public ResourceImpl get(String path, long versionNumber, Connection conn)
public ResourceImpl getResourceByID(long artifactID, long versionNumber, 
Connection conn)
public InputStream getResourceContentStream(long resourceID, long 
versionNumber, Connection conn)
public ResourceImpl getLatestVersion(String path, Connection conn)

out of these, only getResourceContentStream() method really needs the 
value of 'CONTENT' field.

So I suggest we change other 3 sql statements to return only required 
fields & see how registry on mooshup performs.
This will definitely create a positive impact, as 'get' method is 
heavily used.

Additionally we can create indexes to support frequently used SELECT 
queries too.

/sumedha