Performance as compared to simple sql db query is quite bad

[zevon]

The project I am looking into needs to store friendly ids to a file, and
there could be multiple types of file. I want to show the result which lists
all the id's. Below is the JR structure and SQL table and their performance.
As per configuration, I am using MSSqlPersistenceManager and using DataStore
for files. Otherwise rest of the config is defaults.

JR structure:

/content/data/Ids/type1/*
/content/data/Ids/type2/*
/content/data/Ids/type3/*
/content/data/Ids/type4/*
/content/data/Ids/type5/*
 
All the ids are equally distributed under the specific type.

SQL structure:

A table with columns: Name, Type

Performance numbers in ms (items are spread equally among types), when using
the below query. As the numbers show, it's pretty bad. Is this expected? any
way to better this?

Items:   JR        SQL     
150       551       15      
1000     2969     78      
2000     6470     94      
4000     16816   94     
8000     58966   125  

                        Workspace workSpace = session.getWorkspace();
                        QueryManager queryManager = workSpace.getQueryManager();

                         StringBuffer queryStr = new
                         StringBuffer("//data/componentIds/*/*");
                         Query query = 
queryManager.createQuery(queryStr.toString(),
                         Query.XPATH);

                        Query query = 
queryManager.createQuery(queryStr.toString(),
                                        Query.XPATH);

                        long begin = System.currentTimeMillis();
                        QueryResult queryResult = query.execute();
                        int iSize = 0;
                        NodeIterator queryResultNodeIterator = 
queryResult.getNodes();
                        while (queryResultNodeIterator.hasNext()) {

                                Node componentIdNode = 
queryResultNodeIterator.nextNode();
                                iSize++;
                                // 
System.out.println(componentIdNode.getName());
                        }
                        long end = System.currentTimeMillis();
                        System.out.println("**** time for: " + iSize + " : "
                                        + (end - begin));

For SQL, it's a simple JDBC call:
                long begin = System.currentTimeMillis();

                Statement stmt = con.createStatement();
                ResultSet rs = stmt.executeQuery("SELECT * FROM 
ArtifactFriendlyName");
                int iSize = 0;
                while (rs.next()) {
                        iSize++;
                        String s = rs.getString("FriendlyName");
                }

                long end = System.currentTimeMillis();

                System.out.println("Time taken for: " + iSize + " : " + (end - 
begin));


Thanks,
Sateesh.

-- 
View this message in context: 
http://www.nabble.com/Performance-as-compared-to-simple-sql-db-query-is-quite-bad-tp15218031p15218031.html
Sent from the Jackrabbit - Users mailing list archive at Nabble.com.